BigQuery

Trelica can import spend data from BigQuery. Configuration will require sufficient administrative rights in Google Cloud Platform (GCP). 

The first step involves creating a view in BigQuery. We have a recommended view structure but Trelica can also support custom views.

Once a view is created you will need to create a billing project and service account.

This account is then granted permissions on a view in the BigQuery dataset that you wish to share.

Create a BigQuery view

We recommend you create a view on your spend data with a standard structure which Trelica can process without additional work. If this is not possible, then you can open a request with support@trelica.com for manual view configuration.

The view should be called trelica_spend

Column name Type Notes
transaction_id String Unique ID for the transaction
transaction_date String Transaction date
last_modified DateTime Date the transaction was last updated
description String Description of the transaction
vendor String Vendor name
amount Float Amount
currency String Currency code (e.g. USD)
transaction_ref String Transaction reference (e.g. PO or Invoice number)
Optional fields:
user_email String User who created the expense
cost_center String Cost center code
service_start_date Date Date the service started
service_end_date Date Date the service ended.
status String Status e.g. Paid

Create a billing project and service account

  1. Log in to Google Cloud Console.
  2. Create a new project (if required), or select an existing project that you want to add the service account to.
  3. Click Create service account:
  4. Enter a Service account name: as you type a unique servive account ID will be automatically generated.
  5. Note the generated Email address (you will need to provide this when connecting Trelica to BigQuery - a copy will be in the JSON key file you will shortly download).
  6. Click Create and Continue.
  7. Assign the BigQuery Job User role by either searchign or selecting in one of the Quick access lists if available.
  8. Click Continue
  9. Click Done. You do not need to perform the Grant users access to this service account step.
  10. You will see a list showing your newly created service account. Click on it to view more details.
  11. Click the Keys tab, then Add Key and Create new key:
  12. Choose the JSON key type and click Create.
  13. A JSON file will be downloaded. Load it into a text editor:
  14. Copy the private_key value (starting with, and including -----BEGIN PRIVATE KEY----- through to, and including -----END PRIVATE KEY-----). The newline characters will be encoded with \n but Trelica can cope with this.
  15. Once you have entered these details into Trelica you should delete the file (i.e. also remove from any trash or recycle bin).
  16. You can regenerate the key and re-enter it into Trelica at any time if you need to.

Share a BigQuery view

  1. Switch to BigQuery in Google Cloud Console.
  2. Create and save a view of your data.
  3. Click on the view, and choose Share > Manage Permissions:
  4. Click Add Principal:
  5. In New principals, enter the email address for the client you created earlier (along the lines of trelica-bq-user@your_project.iam.gserviceaccount.com).
  6. Select the BigQuery Data Viewer role.
  7. Click Save:
  8. You may need to add the user to any underlying tables as well as the view.
  9. Finally, take a copy of the View ID by opening up the menu for the view and clicking Copy ID:

View configuration

If you are not using the proscribed Trelica spend data view structure then please contact support@trelica.com to configure mapping a custom view into Trelica.

Connecting Trelica to BigQuery

Login to Trelica and go to Admin > Integrations and search for BigQuery.

Click Connect and enter:

  • The service account email
  • The private key
  • The ID of the BigQuery view you created

 

Was this article helpful?

0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.