BigQuery Setup Guide Betalink

Updated November 16, 2023

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

Prerequisiteslink

To connect BigQuery to Fivetran, you need a BigQuery account with the permissions to assign Job User, Data Viewer, and Admin roles at the dataset level.

Setup instructionslink

(Optional) Create service account keylink

You can use your own service account to process the data if you'd like. If you prefer to use Fivetran's service account, proceed to the next step.

Follow Google's Create a service account key instructions to set up service account key authentication.
Make a note of the entire service account key file. You will need it to configure Fivetran. It should have the following format:

{
  "type": "",
  "project_id": "",
  "private_key_id": "",
  "private_key": "",
  "client_email": "",
  "client_id": "",
  "auth_uri": "",
  "token_uri": "",
  "auth_provider_x509_cert_url": "",
  "client_x509_cert_url": ""
}
content_copy

Set service account permissionslink

Grant the service account the following permissions on the dataset:

IMPORTANT: If you're using Fivetran's service account, use the auto-generated email address that's displayed in the connector setup form (for example, g-xxxxxxxxxxxxx@fivetran-production.iam.gserviceaccount.com).

Role	Reason
BigQuery Job User	To create queries
BigQuery Data Viewer	To view the data
BigQuery Admin	To create temporary tables (required by Teleport)

TIP: To learn how to set permissions in BigQuery, see Google's Introduction to IAM documentation.

Configure query results tablelink

Query results that exceed 10GB of compressed data require the use of a query results table in order to import data.

To use a query results table, enable the Use working dataset for queries toggle. By default, we use the same dataset to sync source tables and source views to create the query results table.

To specify a separate dataset, disable the Use same dataset as source table toggle, and specify the Query Results Dataset Name.

A copy of the entire source table or view will be created before importing for each source table or view selected in the schema, and the tables will have the prefix _FIVETRAN_QUERY_RESULTS.

Once the import is complete the query results table will be dropped, and these tables will always be excluded from syncs.

IMPORTANT: To use a query results table, the service account must have permission to add and drop tables in that dataset, and these query results must be in the same region as the source table dataset (e.g. "us-west-1").

Choose connection methodlink

First, decide whether to connect Fivetran to your database directly or using Private Google Access.

Connect directlylink

To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your BigQuery database from Fivetran's IPs for your database's region.

Connect using Private Google Accesslink

NOTE: You must have an Enterprise or Business Critical plan to use Private Google Access.

Private Google Access allows VM instances with only internal IP addresses to reach the external IP addresses of Google APIs and services. Learn more in Google's Private Google Access Overview.

Follow the Private Google Access configuration instructions to enable Private Google Access for your dataset.

Complete Fivetran configurationlink

In the connector setup form, enter your Destination Schema name.
IMPORTANT: Do not use this destination schema for any other purpose.
Enter the Project ID.
Enter the Dataset name.
(Optional) If you chose to use your own service account, set the Use own service account toggle to ON.
If you selected your own service account, enter the Service Account Private key that you found in Step 1.
Choose a connection method.
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 testslink

Fivetran performs the following BigQuery connection tests:

The BigQuery Connection test checks the accessibility of the BigQuery project and validates the database credentials you provided in the setup form.
The Permission test checks that we can connect to the database and create a temporary table.
NOTE: The tests may take a couple of minutes to finish running.

Documentation