Skip to main content

​
Prerequisites

In order to connect Google BigQuery to Cube, you need to provide service account credentials. Cube requires the service account to have BigQuery Data Viewer and BigQuery Job User roles enabled. If you plan to use pre-aggregations, the account will need the BigQuery Data Editor role instead of BigQuery Data Viewer. You can learn more about acquiring Google BigQuery credentials here. In Cube Cloud, you can authenticate with OIDC workload identity federation instead of a key file — the same roles apply to the impersonated service account.

​
Setup

​
Manual

Add the following to a .env file in your Cube project: 
CUBEJS_DB_TYPE=bigquery
CUBEJS_DB_BQ_PROJECT_ID=my-bigquery-project-12345
CUBEJS_DB_BQ_KEY_FILE=/path/to/my/keyfile.json
You could also encode the key file using Base64 and set the result to CUBEJS_DB_BQ_CREDENTIALS: 
CUBEJS_DB_BQ_CREDENTIALS=$(cat /path/to/my/keyfile.json | base64)

​
Cube Cloud

In some cases you’ll need to allow connections from your Cube Cloud deployment IP address to your database. You can copy the IP address from either the Database Setup step in deployment creation, or from Settings → Configuration in your deployment.
The following fields are required when creating a BigQuery connection:
Cube Cloud BigQuery Configuration Screen

​
OIDC workload identity federation

Instead of a service account key file, Cube Cloud deployments can authenticate to BigQuery with OIDC workload identity federation: a Workload Identity Federation provider in your GCP project trusts Cube’s OIDC issuer, and the driver authenticates through the GCP default credential chain — no JSON key to provision or rotate. Select OIDC workload identity federation in the connection wizard, or set the equivalent environment variables: 
CUBEJS_DB_TYPE=bigquery
CUBEJS_DB_BQ_PROJECT_ID=my-bigquery-project-12345
GCP_POOL_AUDIENCE=//iam.googleapis.com/projects/123456789/locations/global/workloadIdentityPools/cube-pool/providers/cube
GCP_SERVICE_ACCOUNT_EMAIL=cube-deployment@my-bigquery-project-12345.iam.gserviceaccount.com
GCP_SERVICE_ACCOUNT_EMAIL selects the service account Cube impersonates; leave it unset to authenticate as the federated principal directly. See the GCP OIDC guide for the Workload Identity Pool, provider, and IAM setup. Cube Cloud also supports connecting to data sources within private VPCs if single-tenant infrastructure is used. Check out the VPC connectivity guide for details.

​
Environment Variables

Environment VariableDescriptionPossible ValuesRequired
CUBEJS_DB_BQ_PROJECT_IDThe Google BigQuery project ID to connect toA valid Google BigQuery Project ID✅
CUBEJS_DB_BQ_KEY_FILEThe path to a JSON key file for connecting to Google BigQueryA valid Google BigQuery JSON key file✅1
CUBEJS_DB_BQ_CREDENTIALSA Base64 encoded JSON key file for connecting to Google BigQueryA valid Google BigQuery JSON key file encoded as a Base64 string❌
CUBEJS_DB_BQ_LOCATIONThe Google BigQuery dataset location to connect to. Required if used with pre-aggregations outside of US. If not set then BQ driver will fail with Dataset was not found in location US errorA valid Google BigQuery regional location⚠️
CUBEJS_DB_EXPORT_BUCKETThe name of a bucket in cloud storageA valid bucket name from cloud storage❌
CUBEJS_DB_EXPORT_BUCKET_TYPEThe cloud provider where the bucket is hostedgcp❌
CUBEJS_DB_MAX_POOLThe maximum number of concurrent database connections to pool. Default is 40A valid number❌
CUBEJS_CONCURRENCYThe number of concurrent queries to the data sourceA valid number❌
1 Not required when authenticating with OIDC workload identity federation in Cube Cloud, or when providing the key material via CUBEJS_DB_BQ_CREDENTIALS.

​
Pre-Aggregation Feature Support

​
count_distinct_approx

Measures of type count_distinct_approx can be used in pre-aggregations when using Google BigQuery as a source database. To learn more about Google BigQuery’s support for approximate aggregate functions, click here.

​
Pre-Aggregation Build Strategies

To learn more about pre-aggregation build strategies, head here.
FeatureWorks with read-only mode?Is default?
Batching❌✅
Export Bucket❌❌
By default, Google BigQuery uses batching to build pre-aggregations.

​
Batching

No extra configuration is required to configure batching for Google BigQuery.

​
Export bucket

BigQuery only supports using Google Cloud Storage for export buckets.

​
Google Cloud Storage

For improved pre-aggregation performance with large datasets, enable export bucket functionality by configuring Cube with the following environment variables:
When using an export bucket, remember to assign the BigQuery Data Editor and Storage Object Admin role to your BigQuery service account.
CUBEJS_DB_EXPORT_BUCKET=export_data_58148478376
CUBEJS_DB_EXPORT_BUCKET_TYPE=gcp

​
SSL

Cube does not require any additional configuration to enable SSL as Google BigQuery connections are made over HTTPS.