# Connect to BigQuery

## Choose an authentication method

ABsmartly can authenticate to BigQuery in one of two ways. Pick one before you start, because the Google Cloud setup differs:

| Authentication                 | How it works                                                                                                                                                                          | Setup                                                     |
| ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------- |
| **Service account key (JSON)** | You create a service account in your Google Cloud project and give ABsmartly its JSON key.                                                                                            | [Google Cloud prerequisites](#google-cloud-prerequisites) |
| **Trust relationship**         | ABsmartly connects as its own Google service account, dedicated to your deployment. You grant that service account access to your projects and datasets; no key is created or shared. | [Trust relationship](#trust-relationship)                 |

You select the method in the **Authentication** dropdown when you [add the data source](#add-the-data-source-in-absmartly).

## Google Cloud prerequisites

> **note**
>
> These steps apply to **Service account key (JSON)** only. If you're using **Trust relationship**, skip to [Trust relationship](#trust-relationship).

Before connecting ABsmartly to BigQuery, you need a Google Cloud service account with the right permissions. The steps below walk through creating one from scratch.

### 1. Enable the BigQuery API

In the [Google Cloud Console](https://console.cloud.google.com), open the project that contains your BigQuery dataset and navigate to **APIs & Services → Library**. Search for **BigQuery API** and enable it if it isn't already.

### 2. Create a service account

1. Go to **IAM & Admin → Service Accounts** and click **Create service account**.
2. Give it a name (e.g., `absmartly-warehouse`) and an optional description, then click **Create and continue**.
3. Skip the optional step of granting users access to the service account, then click **Done**.

### 3. Grant permissions

Grant your service account the following roles:

| Role                                                                   | Where to grant it                                                                              | Why                                                                                                                                                                                                                                                                                                                                               |
| ---------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **BigQuery Data Viewer** (`roles/bigquery.dataViewer`)                 | On the dataset                                                                                 | Allows reading your experiment tables                                                                                                                                                                                                                                                                                                             |
| **BigQuery Job User** (`roles/bigquery.jobUser`)                       | On the job project (the **Job / billing project ID**, or the **Project ID** when that's empty) | Allows running queries                                                                                                                                                                                                                                                                                                                            |
| **BigQuery Read Session User** (`roles/bigquery.readSessionUser`)      | On the job project                                                                             | Allows reading large query results                                                                                                                                                                                                                                                                                                                |
| **Service Usage Consumer** (`roles/serviceusage.serviceUsageConsumer`) | On the job project                                                                             | Allows billing queries and inserts to the job project                                                                                                                                                                                                                                                                                             |
| **Storage Object Viewer** (`roles/storage.objectViewer`)               | On the exposures staging bucket *(Hybrid only)*                                                | Allows BigQuery to read staged exposures with your service account's credentials, regardless of the **Exposures staging** authentication choice. If **Exposures staging** uses **Trust relationship**, also grant ABsmartly's service account **Storage Object User** (`roles/storage.objectUser`) on the bucket so it can write the staged files |

For Hybrid exposures import, you must create a [staging dataset](#4-create-the-staging-dataset) even with a service account key. Grant this service account **BigQuery Data Editor** (`roles/bigquery.dataEditor`) on the staging dataset whenever you configure one.

Without a staging dataset (allowed only when this data source has no Hybrid exposures import), JSON layouts use the ABsmartly dataset, so grant **BigQuery Data Editor** on it instead of Data Viewer if you want ABsmartly to create and maintain the `json_layouts` table.

To grant dataset-level access, open **BigQuery Studio**, select the dataset, click **Sharing → Permissions**, and add the service account email with the appropriate role. To grant the project-level roles, go to **IAM & Admin → IAM** in the job project, click **Grant access**, paste the service account email, and assign **BigQuery Job User**, **BigQuery Read Session User** and **Service Usage Consumer**. For Hybrid, open the staging bucket in **Cloud Storage → Buckets**, go to **Permissions → Grant access**, and grant your service account **Storage Object Viewer**.

### 4. Create and download the service account key

1. In **IAM & Admin → Service Accounts**, click the service account you just created.
2. Go to the **Keys** tab and click **Add key → Create new key**.
3. Select **JSON** and click **Create**. A `.json` file will download automatically.

This is the file you'll paste into the **Service Account Credentials** field in ABsmartly.

***

## Trust relationship

With **Trust relationship**, ABsmartly connects to BigQuery as its own Google service account, created for your ABsmartly deployment. You don't create a service account or upload a key: you grant ABsmartly's service account access to your projects, datasets and, in Hybrid mode, your exposures staging bucket.

ABsmartly only reads your own datasets. The only dataset it writes to is a separate staging dataset that you create for it (see [Create the staging dataset](#4-create-the-staging-dataset)).

### 1. Copy the ABsmartly service account

In the data source form, select **Trust relationship** in the **Authentication** dropdown. The **ABsmartly identity** block shows **Service account email** and **Unique ID**, each with a **Copy** button. The email looks like this:

```text
abs-acme@absmartly-customer-identities.iam.gserviceaccount.com
```

Use this email as the principal in every grant below.

> **note**
>
> If your organization restricts sharing to specific domains (`iam.allowedPolicyMemberDomains`) or uses VPC Service Controls, allow ABsmartly's service account there first, or the grants and queries fail.

> **note**
>
> If **Trust relationship** is disabled and shows "Not available on this deployment. Contact ABsmartly support.", your deployment hasn't been set up for it yet. Contact ABsmartly support, or use **Service account key (JSON)** in the meantime.

### 2. Enable the BigQuery APIs

Enable the **BigQuery API** (`bigquery.googleapis.com`) and the **BigQuery Storage API** (`bigquerystorage.googleapis.com`) in your job project (the **Job / billing project ID**, or the **Project ID** if you leave that empty), the project that holds your ABsmartly dataset, and the staging project if you configure a staging dataset. In the [Google Cloud Console](https://console.cloud.google.com), open each project, navigate to **APIs & Services → Library**, search for each API and enable it if it isn't already.

### 3. Grant the required permissions

| Role                                                                   | Where to grant it                                                  | Why                                                                                                                                                                                                                                                                                                           |
| ---------------------------------------------------------------------- | ------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **BigQuery Job User** (`roles/bigquery.jobUser`)                       | On the job project                                                 | Allows running queries                                                                                                                                                                                                                                                                                        |
| **BigQuery Read Session User** (`roles/bigquery.readSessionUser`)      | On the job project                                                 | Allows reading large query results                                                                                                                                                                                                                                                                            |
| **Service Usage Consumer** (`roles/serviceusage.serviceUsageConsumer`) | On the job project                                                 | Allows billing queries and inserts to the job project                                                                                                                                                                                                                                                         |
| **BigQuery Data Viewer** (dataset role `READER`)                       | On the ABsmartly dataset (the **Dataset ID** in the form)          | Allows reading your experiment tables                                                                                                                                                                                                                                                                         |
| **BigQuery Data Viewer** (dataset role `READER`)                       | On each other dataset your table mappings or SQL queries read from | Allows reading your source data                                                                                                                                                                                                                                                                               |
| **BigQuery Data Editor** (dataset role `WRITER`)                       | On the staging dataset (the **Staging dataset** in the form)       | Allows ABsmartly to create and maintain its own tables there. See [Create the staging dataset](#4-create-the-staging-dataset).                                                                                                                                                                                |
| **Storage Object User** (`roles/storage.objectUser`)                   | On the exposures staging bucket *(Hybrid only)*                    | Allows BigQuery to read staged exposures even when **Exposures staging** uses **Access key (HMAC)**; also allows ABsmartly to write them when exposures staging uses **Trust relationship**. See [Grant access to the exposures staging bucket](#5-grant-access-to-the-exposures-staging-bucket-hybrid-only). |

The job project is the **Job / billing project ID** in the form, or the **Project ID** when that's empty. This step covers the job project and read-only dataset roles; steps 4 and 5 cover the staging dataset and the bucket.

#### Using the Google Cloud Console

* **Job project roles:** go to **IAM & Admin → IAM** in the job project (the **Job / billing project ID**, or the **Project ID** when that's empty), click **Grant access**, paste the service account email as the new principal, and assign **BigQuery Job User**, **BigQuery Read Session User** and **Service Usage Consumer**.
* **Dataset roles:** open **BigQuery Studio**, select the dataset, click **Sharing → Permissions**, then **Add principal**. Paste the service account email and assign **BigQuery Data Viewer**. Repeat for the ABsmartly dataset and each source dataset.

#### Using gcloud and bq

Run these as an administrator of your Google Cloud projects, with `gcloud`, `bq` and `jq` installed. Set `SA_EMAIL` to the **Service account email** from the **ABsmartly identity** block, and replace the other placeholders in `<>` with your own values. Run the commands in steps 4 and 5 in the same shell, since they reuse `$SA_EMAIL`.

```bash
SA_EMAIL=<ABSMARTLY_SA_EMAIL>   # the Service account email from the ABsmartly identity block

# Prerequisite APIs, in the job, data and staging projects (if staging is configured)
gcloud services enable bigquery.googleapis.com bigquerystorage.googleapis.com --project=<JOB_PROJECT>
gcloud services enable bigquery.googleapis.com bigquerystorage.googleapis.com --project=<DATA_PROJECT>
gcloud services enable bigquery.googleapis.com bigquerystorage.googleapis.com --project=<STAGING_PROJECT>

# Run and bill queries in the job project
gcloud projects add-iam-policy-binding <JOB_PROJECT> --member="serviceAccount:$SA_EMAIL" --role=roles/bigquery.jobUser
gcloud projects add-iam-policy-binding <JOB_PROJECT> --member="serviceAccount:$SA_EMAIL" --role=roles/bigquery.readSessionUser
gcloud projects add-iam-policy-binding <JOB_PROJECT> --member="serviceAccount:$SA_EMAIL" --role=roles/serviceusage.serviceUsageConsumer

# Read the ABsmartly dataset and each source dataset: append a READER access entry, keeping existing entries.
# List one PROJECT:DATASET per source dataset after the ABsmartly dataset (or remove the second entry if there are none).
for DS in <DATA_PROJECT>:<DATASET> <SOURCE_PROJECT>:<SOURCE_DATASET>; do
  bq show --format=prettyjson "$DS" > ds.json
  jq --arg sa "$SA_EMAIL" '.access += [{"role": "READER", "userByEmail": $sa}]' ds.json > ds.new.json && mv ds.new.json ds.json
  bq update --source ds.json "$DS"
done
```

> **tip**
>
> BigQuery datasets don't support `bq add-iam-policy-binding`, which is why the dataset grants append an access entry instead. The `jq` step keeps every existing entry and authorized view on the dataset.

### 4. Create the staging dataset

The staging dataset holds only objects ABsmartly owns: the `json_layouts` table and the short-lived external tables that the Hybrid exposures import creates, which expire after 1 day. Under **Trust relationship**, it is the only dataset ABsmartly writes to; ABsmartly never writes to your own datasets.

The staging dataset requirements depend on the feature:

* **JSON layouts** (auto-complete for JSON properties in the metric editor) require a staging dataset under **Trust relationship**. They stay turned off until you set a **Staging dataset**. With **Service account key (JSON)**, a staging dataset is optional for JSON layouts: without one, they use the ABsmartly dataset. See [JSON layouts table](https://docs.absmartly.com/docs/web-console-docs/warehouse-native/get-started.md#json-layouts-table).
* **Exposures import** *(Hybrid only)* requires a staging dataset with either **Service account key (JSON)** or **Trust relationship** authentication. The import creates a temporary external table, so it needs a staging dataset to create it in. ABsmartly won't save a data source with an exposures import, or run **Test exposures import**, until you set a **Staging dataset**. A data source saved earlier without a staging dataset keeps failing Hybrid metric queries until you add one, and its next save requires it. See [Exposures import](https://docs.absmartly.com/docs/web-console-docs/warehouse-native/get-started.md#step-4-configure-exposures-import-hybrid-only).

We recommend a staging dataset for every BigQuery data source, including Warehouse Native deployments without exposures import. It is where ABsmartly creates and maintains the tables it owns, so it keeps them out of your ABsmartly dataset and lets JSON layouts work under either authentication method.

Create the staging dataset **in the same location as your ABsmartly and source datasets**, because a single query reads from them. It must be a different dataset from the **Dataset ID** in the **Project ID** project. Then enter it in the form's **Staging dataset** field, and its project in **Staging project ID** if it isn't the **Project ID**. Enable both BigQuery APIs in the staging project as described in [Enable the BigQuery APIs](#2-enable-the-bigquery-apis).

With **Service account key (JSON)**, grant **BigQuery Data Editor** on the staging dataset to your own service account instead: set `SA_EMAIL` to its email before running the commands below.

**Using the Google Cloud Console:** in **BigQuery Studio**, open the staging project, click the **⋮** menu next to the project and choose **Create dataset**. Enter the dataset ID, set **Location type** and the location to match your source datasets, and click **Create dataset**. Then select the new dataset, click **Sharing → Permissions**, then **Add principal**, paste the service account email and assign **BigQuery Data Editor**.

**Using bq:**

```bash
# Grant staging access to the data source's service account (WRITER = BigQuery Data Editor).
# <LOCATION> must be your source datasets' location.
bq --location=<LOCATION> mk --dataset <STAGING_PROJECT>:<STAGING_DATASET>
bq show --format=prettyjson <STAGING_PROJECT>:<STAGING_DATASET> > ds.json
jq --arg sa "$SA_EMAIL" '.access += [{"role": "WRITER", "userByEmail": $sa}]' ds.json > ds.new.json && mv ds.new.json ds.json
bq update --source ds.json <STAGING_PROJECT>:<STAGING_DATASET>
```

### 5. Grant access to the exposures staging bucket *(Hybrid only)*

Skip this step unless you use [Exposures import](https://docs.absmartly.com/docs/web-console-docs/warehouse-native/get-started.md#step-4-configure-exposures-import-hybrid-only). With a **Trust relationship** data source, BigQuery reads the staged files as ABsmartly's service account, so grant this role even if the **Exposures staging** section uses **Access key (HMAC)**. If **Exposures staging** also uses **Trust relationship**, the same role lets ABsmartly write the files.

Put the bucket in the same location as your staging and source datasets. If those datasets are in the `US` or `EU` multi-region, the bucket can also be in a single region inside it (for example `europe-west1` for `EU`). BigQuery reads the staged files through an external table, which can't read a bucket outside the dataset's location.

**Using the Google Cloud Console:** go to **Cloud Storage → Buckets**, open the bucket, go to the **Permissions** tab, click **Grant access**, paste the service account email and assign **Storage Object User**.

**Using gcloud:**

```bash
gcloud storage buckets add-iam-policy-binding gs://<BUCKET> --member="serviceAccount:$SA_EMAIL" --role=roles/storage.objectUser
```

> **note**
>
> Switching an existing data source from **Service account key (JSON)** to **Trust relationship** deletes the stored key when you save. Revoke or delete the key in Google Cloud afterwards if you no longer need it.

***

## Add the data source in ABsmartly

Navigate to **Settings → Data Sources** and click **Add data source**. Give your data source a **name** and optional **description**, then select **BigQuery** from the **Database type** dropdown.

![BigQuery data source configuration](/assets/images/datasource-bigquery-cd8fba5391cb0e780c6683112832abc9.png)

## Connection credentials

| Field                                  | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| -------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Project ID**                         | Your GCP project ID                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| **Job / billing project ID**           | *Optional.* Project that runs and is billed for queries and inserts. Leave empty to use the Project ID. The service account needs BigQuery Job User, BigQuery Read Session User and Service Usage Consumer on it.                                                                                                                                                                                                                                             |
| **Dataset ID**                         | The BigQuery dataset containing your experiment tables                                                                                                                                                                                                                                                                                                                                                                                                        |
| **Staging dataset**                    | Required for Hybrid exposures import with either authentication method, and for JSON layouts under **Trust relationship**; otherwise optional. A dataset you create for ABsmartly's own tables (`json_layouts` and the Hybrid exposures import's temporary external tables). It must be different from the ABsmartly dataset and in the same location as your ABsmartly and source datasets. See [Create the staging dataset](#4-create-the-staging-dataset). |
| **Staging project ID**                 | *Optional.* The project that holds the staging dataset. Leave empty to use the **Project ID** (not the Job / billing project ID).                                                                                                                                                                                                                                                                                                                             |
| **Authentication**                     | How ABsmartly authenticates to BigQuery: **Service account key (JSON)** (the default) or **Trust relationship**. See [Choose an authentication method](#choose-an-authentication-method).                                                                                                                                                                                                                                                                     |
| **Service Account Credentials (JSON)** | *Service account key (JSON) only.* A GCP service account key in JSON format. You can paste the JSON directly into the editor or use the **Upload JSON file** button. This field is required.                                                                                                                                                                                                                                                                  |
| **ABsmartly identity**                 | *Trust relationship only.* The **Service account email** and **Unique ID** of the service account ABsmartly connects as, each with a **Copy** button, and a **Required permissions** link to [Trust relationship](#trust-relationship). There's nothing to enter here.                                                                                                                                                                                        |

> **tip**
>
> With **Service account key (JSON)**, create a dedicated service account with only the roles listed in [step 3](#3-grant-permissions). It reads your source and ABsmartly datasets, and writes only to the staging dataset (or to the ABsmartly dataset when you have no staging dataset and want ABsmartly to maintain `json_layouts`). Don't reuse a service account that has broader permissions than necessary.

## Test the connection

Click **Test connection** at the bottom of the form. If everything is configured correctly, you'll see a green **"Connection successful"** confirmation.

![Successful BigQuery connection test](/assets/images/datasource-bigquery-connected-cc1fa4561c93836b6b2294093453d7b6.png)

> **note**
>
> If the test fails, double-check your credentials and make sure ABsmartly's IP addresses are allowed through your BigQuery project's network policies.
>
> With **Trust relationship**, a failed test shows the missing role and resource (for example "Missing BigQuery Job User on `<project>`") with a **See permissions** link. **Test connection** doesn't exercise inserts, so a missing **Service Usage Consumer** grant on a separate job project shows up later as failed writes, not as a failed test.

Once connected, save the data source and proceed to [map your tables](https://docs.absmartly.com/docs/web-console-docs/warehouse-native/get-started.md#step-2-map-your-tables).
