Skip to content

Connect Google BigQuery

Datarelix connects to BigQuery via a read-only service using the Google BigQuery API and GoogleSQL (also called Standard SQL).

Prerequisites

  • A Google Cloud project (the billing project that will pay for queries).
  • Access to the dataset(s) you want to analyze. BigQuery is unique in that the data project can differ from the billing project — e.g. querying bigquery-public-data.* datasets.
  • For service-account auth: a service account with BigQuery Data Viewer and BigQuery Job User roles (Data Viewer on the selected dataset, Job User on the billing project).
  • For OAuth: a Google account that can grant the BigQuery scope (https://www.googleapis.com/auth/bigquery).

The two-project model

BigQuery jobs run in your billing project but read from the data project:

  • Billing project — set this on the connection (Project ID field). Query jobs run and are billed here.
  • Data project — specify it as the first segment of Allowed dataset, e.g. bigquery-public-data.usa_names. Reading from another project needs no extra setup beyond the permissions below.

Connection form

Project ID (billing): your-billing-project
Location: US (or your preferred region)
Allowed dataset: data-project.dataset_name
Auth mode: select an available mode

Location must match the dataset’s region. BigQuery runs the query job in the location you set, and rejects it if that doesn’t match where the dataset lives — e.g. a dataset in asia-northeast1 queried with Location: US fails with “Not found: Dataset … was not found in location US.” Set Location to the dataset’s region. Check each public dataset’s location rather than assuming it is US. See BigQuery locations.

Auth modes

Service account JSON

A Google Cloud service account authenticates using a JSON key file. The key is stored encrypted. Use an identity approved for this connection; dashboard refresh remains manual.

Setup

  1. In the Google Cloud Console, create a service account (IAM & Admin → Service Accounts → Create Service Account).
  2. Give it a descriptive name (e.g. datarelix-bq-reader).
  3. On the Grant this service account access to project step, assign these two roles:
    • BigQuery Data Viewer (grant at the selected dataset, separately from project IAM)
    • BigQuery Job User (on the billing project)
  4. After creation, create a JSON key for the service account (Keys → Add Key → Create new key → JSON). A .json file downloads automatically.
  5. Open the file and copy its entire contents.

Where to find your credentials

  • The JSON key file you downloaded in step 4 above.
  • If you already have a service account: IAM & Admin → Service Accounts → click the SA → Keys tab → add a new JSON key.

What to enter

Project ID (billing): your-billing-project
Location: US
Allowed dataset: your-project.your_dataset
Auth mode: Service account JSON
Service account JSON: { "type": "service_account", "project_id": "...", ... }

Paste the entire contents of the downloaded JSON file into the Service account JSON field.


OAuth

The user grants BigQuery access via Google’s consent screen. A refresh token is stored encrypted, scoped to this connection. Best for interactive analysts where you want per-user query attribution.

Authorizing a BigQuery connection is always a separate, explicit step. Signing in to Datarelix with Google grants identity only and never touches your data — the two are deliberately unconnected.

About the scope Google shows you. Google’s consent screen names the https://www.googleapis.com/auth/bigquery scope, whose description mentions managing your data. Datarelix requests this scope for BigQuery API calls; effective access still depends on the identity’s IAM permissions. Datarelix only ever issues SELECT: statements are validated before execution and non-read operations are rejected by the query service, not merely discouraged. If you want the restriction enforced by Google as well as by us, use service-account auth with BigQuery Data Viewer + BigQuery Job User and no write roles.

Setup

Confirm the required IAM grants before authorising — BigQuery access is controlled by the Google account’s IAM permissions. The account that authorizes the connection must have:

  • BigQuery Data Viewer on the selected dataset.
  • BigQuery Job User on the billing project.

Where to assign permissions

Google Cloud Console → IAM & Admin → IAM → find or add the user → assign the roles above.

What to enter

Project ID (billing): your-billing-project
Location: US
Allowed dataset: your-project.your_dataset
Auth mode: OAuth

After saving, Datarelix opens a Google consent screen popup. Sign in with the Google account that has the required IAM permissions. The connection is authorized once you approve the BigQuery scope.


Scope semantics

Select the approved dataset(s), using data-project.dataset_name identifiers. Queries are restricted to the selected datasets. Confirm that all queried datasets have a compatible location.

Discovery

Fully supported, including public datasets. Discovery lists the tables in each selected dataset and detects tables that require a partition filter, so generated queries include that filter automatically.

Limitations

  • Cross-dataset queries within one connection: technically possible in BigQuery SQL via fully-qualified project.dataset.table references, but Datarelix restricts queries to the configured allowed datasets.
  • Partition-required tables: the partition filter is added automatically when table metadata is available. Re-run discovery if you add new partitioned tables.

Troubleshooting

SymptomLikely causeFix
403 Access DeniedMissing BigQuery Data Viewer roleGrant on the data project in IAM & Admin → IAM.
403 caller does not have permission (jobs)Missing BigQuery Job User on billing projectGrant on the billing project.
partitionFilter requiredQuerying a partition-required table without a filterRe-run discovery so the partition column is picked up automatically.
OAuth popup closes without tokenBrowser blocked the popupAllow popups for *.datarelix.ai.
Service account JSON rejectedWrong format or truncated pastePaste the entire contents of the .json file, including the outer { }.

Per-query billing limit

The connector sets maximum_bytes_billed to 10 GiB (10 × 1024³ bytes) per query. This is not a monthly spending cap. Repeated questions and manual dashboard refreshes create more jobs. Under on-demand billing, a result LIMIT does not generally reduce scanned bytes; use relevant partition filters and selected columns. Capacity billing needs its own review. BigQuery cost controls

Service-account impersonation

The Service Account Impersonation auth mode takes a target service-account email; Datarelix’s own service identity then obtains short-lived credentials for that account, so no key is stored. It requires an agreed arrangement: contact Datarelix before selecting it. The target service account needs dataset read and billing-project job permissions, and Datarelix’s service identity needs permission to impersonate it.