> ## Documentation Index
> Fetch the complete documentation index at: https://docs.hightouch.com/llms.txt
> Use this file to discover all available pages before exploring further.

# BigQuery

> Power internal tools, in-app experiences, and more

## Overview

Hightouch lets you send data from your warehouse into your Google BigQuery.

Connecting Hightouch to BigQuery requires some setup in both platforms. It's recommended to set up a service account with the correct permissions in BigQuery before configuring the connection in Hightouch.

## Supported syncing

| Sync Type | Description | Supported Sync Modes |
| - | - | - |
| Any data set | Sync data from any source to a Big Query table | Insert, Upsert |

## BigQuery credential setup

### 1. Create a project and enable BigQuery API

1. Login to the [Google Developers Console](https://console.developers.google.com/).
2. Configure the [Cloud Platform](https://console.cloud.google.com/):
   * If you don't have a project already, [create one](https://support.google.com/cloud/answer/6251787?hl=en\&ref_topic=6158848).
   * Once you have a project, [enable the BigQuery API](https://console.cloud.google.com/flows/enableapi?apiid=bigquery&_ga=2.71379221.724057513.1673650275-1611021579.1664923822&_gac=1.213641504.1673650813.EAIaIQobChMIt9GagtPF_AIVkgB9Ch331QRREAAYASAAEgJfrfD_BwE) for it.
3. Copy your **Project ID** for later use.
4. Find the **location** of your BigQuery dataset or sets. You can find this by querying the `INFORMATION_SCHEMA.SCHEMATA` view or by visiting the [Google Cloud web console](https://console.cloud.google.com/) and clicking on a BigQuery dataset in the Explorer panel. You need both **Project ID** and **Data location** when connecting Hightouch to BigQuery.

   ![Dataset Location in the Google Cloud Console](https://mintcdn.com/hightouch-3c037897/L_vQfA0JQEpeGUUo/images/sources/source-bigquery-data-location.png?fit=max\&auto=format\&n=L_vQfA0JQEpeGUUo\&q=85\&s=a93a644b9d78af92dfaa4084374733d2)

> [!NOTE]
>
> Make sure [billing is
> enabled](https://support.google.com/cloud/answer/6293499#enable-billing) on
> your project, otherwise Hightouch can't write into the cluster.

### 2. Create a service account

To create a service account, follow the setup instructions in our [Google Cloud Platform (GCP) documentation](https://docs.hightouch.com/security/gcp.md).

### 3. Grant access

Your GCP service account will need [permission](https://cloud.google.com/bigquery/docs/access-control#permissions-predefined-roles) to read and write data to BigQuery.
You can set up your service account to have [full access](#grant-full-access) to your project using a [predefined role](https://cloud.google.com/iam/docs/understanding-roles#predefined_roles).
Otherwise, you can create a [custom role](https://cloud.google.com/iam/docs/roles-overview#custom) and provide [limited access](#grant-limited-access) according to a user-specified list of permissions.

#### Grant full access

You can grant full access by assigning the `bigquery.user`, `bigquery.dataViewer`, and `bigquery.dataEditor` roles to your service account.
You can do this in the [Google Cloud web console](https://cloud.google.com/iam/docs/granting-changing-revoking-access#grant-single-role) or by running these snippets in the [Cloud Shell](https://cloud.google.com/shell/docs/run-gcloud-commands).

Grant permission to **read metadata and list tables**:

```bash theme={null}
gcloud projects add-iam-policy-binding <YOUR_PROJECT_NAME> \
--member serviceAccount:<YOUR_SERVICE_ACCOUNT> \
--role roles/bigquery.user
```

Grant permission to **read data from tables and views**:

```bash theme={null}
gcloud projects add-iam-policy-binding <YOUR_PROJECT_NAME> \
--member serviceAccount:<YOUR_SERVICE_ACCOUNT> \
--role roles/bigquery.dataViewer
```

Grant permission to **write data to tables and views**:

```bash theme={null}
gcloud projects add-iam-policy-binding <YOUR_PROJECT_NAME> \
--member serviceAccount:<YOUR_SERVICE_ACCOUNT> \
--role roles/bigquery.dataEditor
```

#### Grant limited access

If you don't want to grant full access to your BigQuery service account, you can opt to grant limited access instead.
You can do this by assigning the `bigquery.dataViewer` and `bigquery.dataEditor` roles only to the specific datasets, tables, or views you want to use in Hightouch.

Since you are assigning these roles only to specific resources, you need to assign the `bigquery.user` role **and** grant the `bigquery.tables.get` permission at the project level.
For this, you can create a [custom role](https://cloud.google.com/iam/docs/creating-custom-roles#creating) in the Google Cloud web console based on an [existing predefined role](https://cloud.google.com/bigquery/docs/access-control#bigquery.user) (`bigquery.user`), which you can name `custom.bigquery.user`.
When setting up the custom role, click **Add permissions** to add the `bigquery.tables.get` permission to this custom role.

Then assign this role to your service account at the project level.
You can do this in the [Google Cloud web console](https://cloud.google.com/iam/docs/granting-changing-revoking-access#grant-single-role) or by running this snippet in the [Cloud Shell](https://cloud.google.com/shell/docs/run-gcloud-commands):

```bash theme={null}
gcloud projects add-iam-policy-binding <YOUR_PROJECT_NAME> \
--member serviceAccount:<YOUR_SERVICE_ACCOUNT> \
--role roles/custom.bigquery.user
```

> [!TIP]
>
> If this `custom.bigquery.user` role still isn't limited enough, you can try assigning the [`bigquery.jobUser`](https://cloud.google.com/bigquery/docs/access-control#bigquery.jobUser) role **and** granting the `bigquery.dataset.get`, `bigquery.tables.get`, and `bigquery.tables.list` permissions at the project level.

> [!NOTE]
>
> Hightouch needs to be able to list the schemas and tables in your BigQuery
> project when setting up your sync configuration.

## Connect to Google BigQuery

Go to the [**Destinations** overview page](https://app.hightouch.com/destinations?from=docs) and click the **Add destination** button.
Select **Google Big Query** and click **Continue**.

### Configure your service account

Select the **GCP credentials** you [previously created](#create-a-service-account) or click **Create new**. To learn more about these credentials, see the [Google Cloud Provider (GCP) documentation](https://docs.hightouch.com/security/gcp.md).

### Configure your destination

Enter the **Project ID** for the project you [enabled the BigQuery API](#create-a-project-and-enable-bigquery-api) for and the **Dataset location**.

## Sync configuration

Once you've set up your Google BigQuery destination and have a [model](https://docs.hightouch.com/getting-started/concepts.md#models) to pull data from,
you can set up your sync configuration to begin syncing data.
Go to the [**Syncs** overview page](https://app.hightouch.com/syncs?from=docs) and click the **Add sync** button to begin.
Then, select the relevant model and the Google BigQuery destination you want to sync to.

### Sync mode

Hightouch supports **Upsert** mode using the [`MERGE`](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement) statement, with the option to [delete removed rows](#delete-behavior),
and **Insert** mode using the [`insertAll`](https://cloud.google.com/nodejs/docs/reference/bigquery/latest/bigquery/table#_google_cloud_bigquery_Table_insert_member_1_) endpoint.

### Record matching

To match rows from your model to rows in BigQuery when upserting, Hightouch requires you to select a unique identifier in the table you are syncing to.
This statement will fail if there are duplicate identifier values in your table.

## Tips and troubleshooting

To see the exact API requests and responses from each sync run, use the [live debugger](https://docs.hightouch.com/syncs/debugger.md). To get notified by Slack, PagerDuty, SMS, or email when a sync fails, set up [sync alerts](https://docs.hightouch.com/syncs/alerting.md). If you run into an issue this page doesn't cover, [contact support](#pylon-chat).