# Google BigQuery Reverse ETL Source

[Google BigQuery](https://cloud.google.com/bigquery) is an industry-leading, fully-managed cloud data warehouse that lets you store and analyze petabytes of data in no time.

RudderStack supports Google BigQuery as a source from which you can ingest data and route it to your desired downstream destinations.

## Grant permissions {#granting-permissions}

Before you set up BigQuery as a source, you must grant certain permissions on your BigQuery warehouse for RudderStack to access data from it.

Follow the steps below in the **exact order** to grant these permissions:

### Step 1: Create role and grant permissions {#step-1-creating-a-role-and-granting-permissions}

1. Go to the [Roles](https://console.cloud.google.com/iam-admin/roles) section of Google Cloud Platform dashboard and click **CREATE ROLE**.

{{< image src="images/warehouse-actions-sources/GCP-create-role.webp" alt="Google Cloud Platform dashboard create role" >}}

2. Fill in the details as shown:

{{< image src="images/warehouse-actions-sources/gcp-role-details.webp" alt="GCP role details" >}}

3. Click **ADD PERMISSIONS** and add the following permissions individually:

**Read-only:**
```
bigquery.datasets.get
bigquery.jobs.list
bigquery.tables.get
bigquery.tables.getData
bigquery.tables.list
bigquery.routines.get
bigquery.routines.list
```

**Read-write:**
```
bigquery.jobs.create
bigquery.tables.create
bigquery.tables.update
bigquery.tables.updateData
bigquery.tables.delete
```

4. Click **CREATE** after adding the permissions.

{{< details "**Click here to see how the above options are seen in the Google Cloud Console**." >}}
<br />

{{< figure src="images/warehouse-actions-sources/gcp-role-permissions.webp" alt="BigQuery role permissions" >}}
{{< /details >}}

### Step 2: Create service account and attach role {#step-2-creating-a-service-account-and-attaching-the-role-to-it}

1. Go to [Service Accounts](https://console.cloud.google.com/iam-admin/serviceaccounts) and select the project which has the dataset or the table that you want to use.
2. Click **CREATE SERVICE ACCOUNT**.

{{< image src="images/warehouse-actions-sources/gcp-create-service-account.webp" alt="Create service account in GCP" >}}

3. Fill in the **Service Account details** as shown below, and click **CREATE AND CONTINUE**:

{{< image src="images/warehouse-actions-sources/gcp-service-account-details.webp" alt="Service account role details" >}}

4. Under **Grant this service account access to project**, select the role you created in [Step 1: Creating a role and granting permissions](#step-1-creating-a-role-and-granting-permissions) section above.

{{< image src="images/warehouse-actions-sources/gcp-service-account-connect-role.webp" alt="Service account role connection" >}}

5. Click **DONE** to move to the list of service accounts.

{{< info >}}
Note down the service account ID. You will need this ID while creating the RudderStack schema and granting the required permissions to it.

{{< figure src="images/warehouse-actions-sources/gcp-service-account-id.webp" alt="Service account ID" >}}
{{< /info >}}

### Step 3: Create and download JSON key {#step-3-creating-and-downloading-the-json-key}

1. Click the three dots icon under **Actions** in the service account that you just created and select **Manage keys**:

{{< image src="images/warehouse-actions-sources/manage-keys.webp" alt="Managing keys in GCP" >}}

2. Click **ADD KEY**, followed by **Create new key**:

{{< image src="images/warehouse-actions-sources/gcp-add-key.webp" alt="GCP Adding a new key" >}}

3. Select **JSON** and click **CREATE**.

{{< image src="images/warehouse-actions-sources/create-new-key.webp" alt="Select Reverse ETL source in RudderStack" >}}

A JSON file will be downloaded on your system. This file is required while [setting up the BigQuery source](#setting-up-the-bigquery-source-in-rudderstack) in RudderStack.

### Step 4: Create RudderStack schema and granting permissions {#creating-the-rudderstack-schema-and-granting-permissions}

1. From your [BigQuery SQL workspace](https://console.cloud.google.com/bigquery), run the following command to create a dedicated schema `rudderstack_`.

{{< danger >}}
The `rudderstack_` schema stores Reverse ETL sync state, snapshots, and related tables. **Do not change this name**.

See [`_rudderstack` Schema Reference]({{< ref "data-pipelines/reverse-etl/developer-guides/rudderstack-schema.md" >}}) for more details.
{{< /danger >}}

```sql
create schema rudderstack_;
```

{{< warning >}}
The `rudderstack_` schema is created in the default region of your BigQuery instance. 

If the GCP cloud storage bucket used as a staging bucket by RudderStack is in a different region, you will need to adjust the above SQL statement to match that region. For example, to create the schema in the [`europe-west-3`](https://cloud.google.com/bigquery/docs/locations) region, run the following statement: <br />

```sql
create schema rudderstack_ OPTIONS (location = "europe-west3");
```
{{< /warning >}}

2. Grant full access to the `rudderstack_` schema for the RudderStack service account you created above. Replace `<SERVICE_ACCOUNT_ID>` with the service account ID you specified in [Step 2: Creating a service account and attaching role to it]({{< ref "#step-2-creating-a-service-account-and-attaching-the-role-to-it" >}}).

{{< info >}}
The `<SERVICE_ACCOUNT_ID>` takes the form of `name@your-gcp-project.iam.gserviceaccount.com`. You can also find it in the `client_email` key of the service account credentials JSON file downloaded in [Step 3: Creating and downloading the JSON key](#step-3-creating-and-downloading-the-json-key).
{{< /info >}}

```sql
GRANT `roles/bigquery.dataOwner`
     ON SCHEMA rudderstack_
     TO "serviceAccount:<SERVICE_ACCOUNT_ID>";
```

## Set up BigQuery source in RudderStack {#setting-up-the-bigquery-source-in-rudderstack}

1. Log in to your [RudderStack dashboard](https://app.rudderstack.com/).
2. On the **Connections** page, click **Add source**.
3. Under **Sources**, click **Reverse ETL** and select **BigQuery**.

### Configure warehouse credentials {#configuring-the-connection-credentials}

You can choose to proceed with your existing warehouse credentials if you have configured them in the RudderStack dashboard previously. Otherwise, click **Add new credentials** to add new credentials for your warehouse.

- **Credentials**: Add the contents of the GCP service account credentials JSON downloaded [above](#step-3-creating-and-downloading-the-json-key).
- **Project ID**: Specify your GCP project ID where your BigQuery database is located.
- **Service account**: Specify your GCP service account in this field.

Click the **Verify** button on the top right. RudderStack will then [verify and validate your credentials](#faq). Once verified, click **Continue** to proceed.

### Specify name and source type

{{< customreadfile "/includes/retl/retl-name-sourcetype.md" >}}

#### **Use warehouse table as source**

{{< customreadfile "/includes/retl/table-as-source.md" >}}

#### **Use model as source**

{{< customreadfile "/includes/retl/model-as-source.md" >}}

#### **Use audience as source**

{{< customreadfile "/includes/retl/audience-as-source.md" >}}

### Review and complete setup

{{< customreadfile "/includes/retl/review-retl-setup.md" >}}

## Connect destination

{{< customreadfile "/includes/retl/connect-retl-destination.md" >}}

## Update source configuration and settings

{{< customreadfile "/includes/retl/update-retl-source-configuration.md" >}}

## FAQ 

{{< customreadfile "/includes/retl/retl-faq.md" >}}