BigQuery

datarelix.ai 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 data project, Job User on the billing project).
For OAuth: a Google account that can grant bigquery.readonly scope.

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). Used to initialize the BigQuery client and pay for queries.
Data project — specify it as the first segment of Allowed dataset, e.g. bigquery-public-data.usa_names. The BigQuery client transparently handles cross-project reads.

Connection form

Project ID (billing):  your-billing-project
Location:              US (or your preferred region)
Allowed dataset:       data-project.dataset_name
Auth mode:             one of the two below

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. The public bigquery-public-data.* datasets live in the US multi-region. See BigQuery locations.

Auth modes

Service account JSON

A Google Cloud service account authenticates using a JSON key file. The key is stored encrypted. Best for unattended/scheduled use where no user is present to authorize interactively.

Setup

In the Google Cloud Console, create a service account (IAM & Admin → Service Accounts → Create Service Account).
Give it a descriptive name (e.g. datarelix-bq-reader).
On the Grant this service account access to project step, assign these two roles:
- BigQuery Data Viewer (on the data project, if different from billing)
- BigQuery Job User (on the billing project)
After creation, create a JSON key for the service account (Keys → Add Key → Create new key → JSON). A .json file downloads automatically.
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.readonly 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.

Setup

No setup required on the database side — BigQuery access is controlled by the Google account’s IAM permissions. The account that authorizes the connection must have:

BigQuery Data Viewer on the data project (or 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.ai 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.readonly scope.

Scope semantics

BigQuery requires an explicit Allowed dataset — blank scope is not supported. The format is data-project.dataset_name. To analyze multiple datasets, create multiple connections.

Discovery

Fully supported using the BigQuery Python API (not SQL INFORMATION_SCHEMA, which can fail on public datasets). The introspector lists tables, detects partitioning requirements, and surfaces partition-required tables so the planner automatically includes the required partition filter in generated queries.

Limitations

Cross-dataset queries within one connection: technically possible in BigQuery SQL via fully-qualified project.dataset.table references, but the planner restricts to the allowed dataset for safety.
Partition-required tables: the planner adds the partition filter automatically when it has table metadata. Re-run discovery if you add new partitioned tables.

Troubleshooting

Symptom	Likely cause	Fix
`403 Access Denied`	Missing `BigQuery Data Viewer` role	Grant on the data project in IAM & Admin → IAM.
`403 caller does not have permission` (jobs)	Missing `BigQuery Job User` on billing project	Grant on the billing project.
`partitionFilter required`	Querying a partition-required table without a filter	Re-run discovery; the planner will surface the partition column automatically.
OAuth popup closes without token	Browser blocked the popup	Allow popups for `*.datarelix.ai`.
Service account JSON rejected	Wrong format or truncated paste	Paste the entire contents of the `.json` file, including the outer `{ }`.