Skip to content

BigQuery connector

BigQuery is reached over the jobs.query REST API with service-account authentication — no driver, no SDK. A short-lived assertion signed with the service account's private key is exchanged for an OAuth access token, which is cached and re-minted shortly before it expires.

The API is stateless: every request carries its own default dataset, so USE never persists between statements. Gated by exec-bigquery.

Experimental

The full executed-oracle corpus passes against a live project — 216 of 216 cases, no skips — alongside a smoke suite covering the type-decode matrix and multi-page results. Still experimental rather than preview: it is not yet in the nightly CI matrix, and results come back as rows rather than through a columnar read. See connector maturity.

Connection profile

datasources:
  bigquery:
    type: bigquery
    project: ${BIGQUERY_PROJECT}       # GCP project ID (or: catalog:)
    dataset: ${BIGQUERY_DATASET}       # default dataset (or: database:)
    credentials_path: ~/.gcp/service-account.json
    location: US                       # optional, e.g. US / EU
    billing_project_id: quota-project  # optional; defaults to project

The field names match the datus-bigquery adapter's, so one datasource entry configures both datus-agent and Dosi.

Parameters

Key Type Required Default Notes
type string yes — bigquery
project string yes — GCP project ID. Also spelled catalog: (Datus's name for this level); write one or the other, not both.
dataset string no — Default dataset, so a bare table name resolves. Also spelled database:; write one or the other.
credentials_path path one of the three — Path to a service-account JSON key file.
credentials_info string one of the three — The service-account JSON itself. Use ${VAR} — same rules as password, including the plaintext warning.
credentials_base64 string one of the three — The same JSON, base64-encoded, for secret stores that mangle newlines.
location string no API default Job location (US, EU, asia-northeast1, …). Required if your dataset is outside the API's default region.
billing_project_id string no project Project the job is billed to and created in.
default bool no false See connection profiles.

Parsed and ignored on this connector: host, port, username, password, uri (rejected outright), schema (also rejected — see below), sslmode, sslrootcert, warehouse, role, arrow_flight_port, compat_mode.

Authentication

Create a key for the service account and point credentials_path: at it:

$ gcloud iam service-accounts keys create ~/.gcp/service-account.json \
    --iam-account=<name>@<project>.iam.gserviceaccount.com

The account needs bigquery.jobs.create on the billing project and read access to the data. The assertion's issuer, audience and scope are derived from the key file — nothing else to configure.

To keep the key out of the filesystem, pass its contents instead:

credentials_info: ${BIGQUERY_CREDENTIALS_INFO}

Exactly one of the three credential keys may be set. Two would mean two possible keys, and silently preferring one is how you authenticate as the wrong principal.

Application Default Credentials are not supported. A file written by gcloud auth application-default login is an authorized_user credential, which needs a refresh-token grant rather than the service-account JWT grant; the connector rejects it by name rather than failing later on an opaque signing error.

Two projects, not one

billing_project_id is where the job runs and is billed; project is where the tables live. They are often the same, but a service account frequently may create jobs only in its own project while reading another's. Getting them backwards produces a permission error on job creation, not a missing-table error:

Access Denied: Project <p>: User does not have bigquery.jobs.create permission

Limitations

  • No schema level. BigQuery's namespace is project → dataset, with nothing below, so a schema: key is rejected rather than silently ignored. Put the dataset in dataset: (or database:).
  • Row path only. Results come back as JSON and are pivoted into Arrow by the shared normalizer. The columnar Storage Read API is not used yet; it is folded into the planned generic exec-adbc channel.
  • PRIMARY KEY and FOREIGN KEY in DDL must be declared NOT ENFORCED, and BigQuery has no AUTO_INCREMENT, SERIAL, storage ENGINE, or DISTRIBUTED BY clause — relevant if you run setup scripts through --execute.

Temporal and numeric values are handled for you. The API reports its legacy type names (INTEGER, FLOAT, BOOLEAN — never the GoogleSQL INT64, FLOAT64, BOOL you wrote in the DDL), renders TIMESTAMP as scientific-notation epoch seconds, and pads DATETIME and TIME fractions out to microseconds; all of these are normalized to the same form every other connector produces. NUMERIC and BIGNUMERIC values too wide for a float stay exact decimal text.

Verify the connection

$ dosi query --model model.yaml \
    --metrics revenue --group-by orders.status --execute --connection bigquery

An authentication problem surfaces as an HTTP 401 or a token-endpoint error carrying Google's own message, which distinguishes a revoked key from a wrong project.

Troubleshooting

Message Cause and fix
connection "x" (bigquery) needs project Set project: (or catalog:).
connection "x" (bigquery) has no credentials None of credentials_path / credentials_info / credentials_base64 is set. ADC is not consulted.
datasource "x" (bigquery) sets more than one of credentials_path, … Keep exactly one.
connection "x": credentials are of type "authorized_user", not "service_account" An ADC file from gcloud auth application-default login. Export a service-account key instead.
connection "x": credentials are not valid JSON / lack client_email / lack private_key Not a service-account key file, or a truncated copy.
connection "x": bad service-account private key The private_key field is not a PKCS#8 PEM — unusual for a Google-issued key, and a sign the JSON was edited.
datasource "x": credentials_info must be a string It was written as nested YAML. Use ${VAR}, or credentials_path. Rejected without echoing the value, so the key never reaches a log.
datasource "x": bigquery has no schema level below a dataset Drop schema:.
datasource "x": sets both "project" and "catalog" (or "dataset"/"database") Aliases — keep one.
token endpoint returned no access_token: <msg> Clock skew beyond the assertion window, a disabled/deleted key, or a service account without the BigQuery scope.
Access Denied: … does not have bigquery.jobs.create permission billing_project_id (or project) names a project the account cannot create jobs in.
Not found: Dataset <p>:<d> Wrong dataset, or the dataset is in another location.
BigQuery job did not finish within the poll budget A query slower than five minutes. Narrow it.
bad BigQuery JSON: <e> The endpoint answered something unexpected, usually a proxy or an outage page.

Reference