---
title: "Connect Google BigQuery"
description: "Connect Datarelix to Google BigQuery: OAuth, service-account key and impersonation auth, dataset scoping, location matching, and partition-filter rules."
canonical: https://docs.datarelix.ai/guides/connections/bigquery/
---

# 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.*`](https://cloud.google.com/bigquery/public-data) datasets.
- For service-account auth: a service account with [`BigQuery Data Viewer` and `BigQuery Job User`](https://cloud.google.com/bigquery/docs/access-control) roles (Data Viewer on the data project, 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). 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

```ini
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](https://cloud.google.com/bigquery/docs/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**

1. In the Google Cloud Console, [create a service account](https://cloud.google.com/iam/docs/service-accounts-create) (**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](https://cloud.google.com/bigquery/docs/access-control):
   - `BigQuery Data Viewer` (on the data project, if different from billing)
   - `BigQuery Job User` (on the billing project)
4. After creation, [create a JSON key](https://cloud.google.com/iam/docs/keys-create-delete) 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**

```ini
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. That is the scope BigQuery requires to *run a query*, because a query creates a job. 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**

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**

```ini
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

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 generated queries automatically include the required partition filter.

## 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 allowed dataset.
- Partition-required tables: the partition filter is added automatically when table metadata is available. 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 so the partition column is picked up 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 `{ }`. |