BigQuery Setup Guide

Follow our setup guide to replicate your BigQuery dataset to your destination using Fivetran.

Prerequisites

To connect BigQuery to Fivetran, you need a BigQuery account with the permissions to assign Admin role at the project level.

Setup instructions

Find project ID

1. Go to your Google Cloud Console's projects list.

   

2. Find your project on the list and make a note of the project ID in the ID column. You will need it to configure Fivetran.

Find Fivetran service account

In the connector setup form, find the auto-generated email address of the Fivetran service account and make a note of it. You will need it to grant permissions in BigQuery.

(Optional) Create service account key

IMPORTANT: Only perform this step if you want to use your own service account instead of Fivetran's service account to configure your connector, otherwise skip to the next step.

1. Follow Google's Create a service account key documentation to set up service account key authentication.

2. Make a note of the entire service account key file. You will need it to configure Fivetran. The key must be in the following JSON format:

{
  "type": "service_account",
  "project_id": "my-project-12345",
  "private_key_id": "abcdefg",
  "private_key": "*****",
  "client_email": "name@project.iam.gserviceaccount.com",
  "client_id": "12345678",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/name%40project.iam.gserviceaccount.com"
}

Set service account permissions

Refer to the one of the following tables to grant the necessary permissions to the service account:

With the BigQuery Admin role

TIP: For instructions to set permissions in BigQuery at the Project level, see Google's Manage access to projects documentation.

Without the BigQuery Admin role

If you do not want to grant BigQuery Admin role to the service account, grant the following permissions to the service account:

TIP: For instruction to create custom IAM role and set permissions in BigQuery at the Dataset level, see Google's Create a custom role and Grant access to a dataset documents, respectively.

Configure query results table

You must specify a dataset for Fivetran to create a query results table to write the SQL import query results that exceed 10GB of compressed data. We then create a copy of the entire source table or view in the query results table before performing the initial sync. These tables are prefixed with _FIVETRAN_QUERY_RESULTS.

You can either use the same dataset as the source table or specify another dataset to store the query results table. If you want to use another dataset, do the following:

• Ensure that the dataset is in the same region as the source dataset. For example us-east-1.
• Assign the custom IAM role you created to the dataset, if you have not assigned the BigQuery Admin role to the service account.

NOTE:

• If you have granted the service account with BigQuery Admin role or bigquery.tables.delete permission in Step 4, we drop the query results tables after the initial sync is completed.
• We always exclude the query results tables from syncs.

(Optional) Configure quota project

A quota project allows you to separate your resource project (where data resides) from the project against which your quota and billing are counted. To use a quota project, you need to grant the service account the Service Usage Consumer IAM role on the chosen quota project.

NOTE: If you use a quota project with insufficient permissions, BigQuery API uses the resource project for quota and billing without throwing any error.

Finish Fivetran configuration

1. In the connector setup form, enter your chosen Destination Schema name.

2. Enter the Project ID you found.

3. Enter the Dataset name.

4. (Optional) If you want to use your own service account:

   i. Set the Use own service account toggle to ON.

   ii. In the Service account key field, paste all the contents of the private key JSON file you created.

5. If you want to use a query results table, set the Use working dataset for queries toggle to ON.

   • If you want to use the same dataset as the source table, set the Use same dataset as source table toggle to ON.
   • If you want to specify another dataset, set the Use same dataset as source table toggle to OFF, and specify the Query Results Dataset Name you configured.

6. (Optional) If you want to use a quota project, set the Use quota project toggle to ON and enter the Quota project ID.

7. Click Save & Test. Fivetran tests and validates the BigQuery connection. On successful completion of the setup tests, you can sync your data using your new BigQuery connector.

Setup tests

Fivetran performs the following BigQuery connection tests:

• The BigQuery Connection test:
  • Checks the accessibility of the BigQuery project and dataset.
  • Validates if we can access the metadata of at least one table.
  • Checks if we can store query results in a destination table (query results table) by running a simple query. We perform this test only if you set the Use working dataset for queries toggle to ON.
• The Permission test checks if we can create a temporary table.

NOTE:

• The tests may take a couple of minutes to complete.
• We do not verify all the permissions mentioned in Step 4. If any permissions are missing during the sync, we display an error.

Related articles

description Connector Overview

assignment Schema Information

assignment Release Notes

settings API Configuration

home Documentation Home