BigQuery is a serverless and cost-effective enterprise data warehouse that works across clouds and scales with your data.
🐕🦺 Setup guide
Prerequisites
To use BigQuery as a data source, you'll need:
- A Google Cloud Project (GCP) with BigQuery enabled
- A Service Account with the BigQuery User and BigQuery Data Editor roles in your GCP project
- A Service Account key to authenticate your Service Account
🤔 What is a Service Account?
A service account is a special kind of account typically used by an application or compute workload, such as a Compute Engine instance, rather than a person. A service account is identified by its email address, which is unique to the account.
Zenskar needs credentials for a Service Account with the BigQuery User and BigQuery Data Editor roles. These roles grant Zenskar the following permissions:
- Run BigQuery jobs
- Write to BigQuery Datasets
- Read table metadata
Service Account
We highly recommend that you create a Service Account exclusive to Zenskar for ease of permission and auditing. However, you can use a pre-existing Service Account that has the correct permissions.
🤔 How to create a Service Account?
Follow GCP's guide to create a service account. Once you've created the Service Account, keep its ID handy as you will need it while granting role-based permissions. Service Account IDs typically take the form <account-name>@<project-name>.iam.gserviceaccount.com
Add the Service Account as a Member in your GCP account with the BigQuery User role. To do this, follow the instructions for granting access in the Google documentation. The email address of the member you are adding is the same as the Service Account ID you just created.
At this point you should have a Service Account with the BigQuery User project-level permission. Similarly, assign the BigQuery Data Editor role to the Service Account.
🤔 How to create a Service Account Key?
Service Account keys are used to authenticate as Google Service Accounts. Zenskar requires the Service Account keys to leverage the role-based permissions you granted to the Service Account in the previous section.
Follow the Google documentation to create and manage a key. Currently, Zenskar supports only JSON keys. Ensure that:
- You create your key in JSON format
- You download the key immediately.
Google will not allow you to see the contents of the key once you navigate away.
Security
Delete the Service Account key JSON from your computer after setting up BigQuery as a data source.
Setup the BigQuery source in Zenskar
⚙️ Set up data source and type
- Log into your Zenskar account.
- In the left navigation bar, click Metering > Data Sources. In the top-right corner, click + ADD DATA SOURCE.
- In the Set Up Source section of the Add New Data Source page, enter a name for the QuickBooks data source connection.
- Select BigQuery from the Source Type dropdown.
⚙️ Configure data source
In the Source Config section of the Add New Data Source page, do the following:
- Default Dataset ID (optional): the schema name if you are interested in only one schema.
- Project ID: the labeled company ID whose data you would like to sync.
- Credentials JSON: the contents of the Service Account key JSON file.
Data-type mapping
The BigQuery source does not alter the schema present in your database. Depending on the destination connected to this source, however, the schema may be altered. See the destination's documentation for more details.
The BigQuery data types mapping:
| BigQuery Type | Resulting Type | Notes |
|---|---|---|
BOOL | Boolean | |
INT64 | Number | |
FLOAT64 | Number | |
NUMERIC | Number | |
BIGNUMERIC | Number | |
STRING | String | |
BYTES | String | |
DATE | String | In ISO8601 format |
DATETIME | String | In ISO8601 format |
TIMESTAMP | String | In ISO8601 format |
TIME | String | |
ARRAY | Array | |
STRUCT | Object | |
GEOGRAPHY | String |
Features
| Feature | Supported | Notes |
|---|---|---|
| Full Refresh Sync | Yes | |
| Incremental Sync | Yes | |
| Change Data Capture | No | |
| SSL Support | Yes |