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

You select the method in the Authentication dropdown when you add the data source.

Google Cloud prerequisites

note

These steps apply to Service account key (JSON) only. If you're using Trust relationship, skip to 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, 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 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).

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:

[email protected]

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, 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.
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.

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.

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.
• 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.

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.

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:

# 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. 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:

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.

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

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.