BigQuery

How to connect BigQuery to Count.

Enter your connection details

You'll need:

  • The Project ID of the BigQuery instance

  • A service account key in JSON format for a service account with the following permissions

bigquery.datasets.get
bigquery.jobs.create
bigquery.routines.get
bigquery.routines.list
bigquery.tables.get
bigquery.tables.getData
bigquery.tables.list

The quickest way to grant these permissions is to assign the service account the roles BigQuery Data Viewer and BigQuery Job User.

Dataset access

By default, you are able to query any dataset in the BigQuery project (limited by the supplied credentials). You are also able to query datasets in other BigQuery projects (as long as the credentials have access to those projects), but datasets in other BigQuery projects will not appear in the Count UI.

If table access is restricted within a Count project, then canvases in that project will be unable to query tables outside of this BigQuery project.

BigQuery Storage API

When running queries with the default row limit disabled, it may take a long time to extract large result sets from BigQuery. In this case you may want to enable the BigQuery Storage API, which uses an alternative method to extract results more quickly for these queries. To use this API, you'll need to grant your service account the additional permissions:

bigquery.readsessions.create
bigquery.readsessions.getData

Use of the BigQuery Storage API may result in a small increase in your BigQuery costs.

Processed data estimations

If automatic execution for a BigQuery cell has been disabled, then an estimate of the data that will be processed by the query is shown in the cell body:

Troubleshooting

The most common issue when connecting to BigQuery data is getting the service account permissions correct. Double-check the service account permissions with the list above.

Additionally, if your BigQuery datasets contain tables that reference External datasources, please check that the service account has viewer access to the files/buckets/drives where the datasources reside.

Out of Memory Errors:

If you are connecting to a BigQuery project with many tables, you may see an out-of-memory error when you try to connect. To resolve this, you can limit the datasets you want to connect to using the following steps:

  1. Create a new service account with the BigQuery Job User permission

2. In the BigQuery console, go the dataset you want to assign permission to, and click Share Dataset (instructions here).

3. Add the service account email to the dataset with the BigQuery Data Viewer permission:

4. Repeat this for any other datasets you want this service account to have access to.

5. Add the service account key to Count and you'll only access the tables for the datasets you've specifically chosen.

If you are having trouble connecting, reach out to us to schedule a support session

Last updated