BigQuery Setup Guide

Follow our setup guide to connect your BigQuery data warehouse to Fivetran.

Prerequisiteslink

To connect BigQuery to Fivetran, you need the following:

• A BigQuery account or a Google Apps account
• Fivetran role with the Create Destinations or Manage Destinations permissions

NOTE: Fivetran doesn’t support BigQuery sandbox accounts.

Setup instructionslink

Find Project ID link

You need to grant Fivetran access to your BigQuery cluster so we can create and manage tables for your data, and periodically load data into those tables.

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

2. Find your Project ID and make a note of it. You will need it to configure Fivetran.

   

Find Fivetran service account link

1. In another tab, go to your Fivetran destination setup form.

2. Select I already have a destination and select BigQuery.

3. Make a note of the Fivetran service account. You will need to grant it permissions in BigQuery.

   

(Optional) Create service account link

If you want to use your service account to control access to BigQuery, instead of the Fivetran-managed service account, you must create a service account. For more information, see Google’s Creating a service account documentation.

Follow Google’s service key instructions to create a private key in JSON format for your service account. Make a note of the private key. You will need it to configure Fivetran. The private key should have the following format:

{
  "type": "service_account",
  "project_id": "random-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"
}

Configure service account link

1. Go back to the IAM & admin tab, and go to the project principals list.

2. Select + Add.

   

3. In the New Principals field, enter the Fivetran service account you found in Step 2 or the service account you created in Step 3. The service account is the entire email address.

   

4. Click Select a role > BigQuery > BigQuery User.

   

   NOTE: For more information about roles, see our documentation.

(Optional) VPC service perimeter configuration link

If you use a service perimeter to control access to BigQuery, you must set up a GCP bucket to ingest data from Fivetran.

NOTE: The bucket must be present in the same location as the dataset location.

Complete Fivetran configuration link

1. Log in to your Fivetran account.
2. Go to the Manage Account page.
3. In the Destinations tab, click +Destination.
4. On the Add Destination To Fivetran page, enter a Destination Name of your choice.
5. Click Add Destination.
6. Select BigQuery as the destination type.
7. In the destination setup form, enter the Project ID you found in Step 1.
8. (Optional) If you want to use your service account, set the Use own Service Account toggle to ON, and then paste the whole contents of the private key JSON file that you created in Step 3 into the Service Account Private Key field.
9. Enter the Data Location.
10. (Optional) If you want to use your GCS bucket to process the data instead of a Fivetran-managed bucket, set the Use GCP Service Parameter toggle to ON, and then enter the Customer Bucket name you configured in Step 5.

    NOTE: Make sure that the bucket is present in the same location as the datasets and the service account has Storage Object Admin permission for the bucket.

11. Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and AWS region as described in our Destinations documentation.
12. Choose your Timezone.
13. Click Save & Test.

Fivetran tests and validates the BigQuery connection. On successful completion of the setup tests, you can sync your data using Fivetran connectors to the BigQuery destination.

Setup testslink

Fivetran performs the following BigQuery connection tests:

• The Connection test checks if we can connect to your BigQuery data warehouse and retrieve a list of the datasets.

• The Check Permissions test validates if we have the required permissions on your data warehouse. The test also checks if billing has been enabled on your account and is not a sandbox account. As part of the test we:

  • create a dataset to check if we have bigquery.datasets.create permissions on your data warehouse.
  • create a table in the dataset (bigquery.tables.create permissions) and insert a row in the table (bigquery.tables.updateData permissions) to check if billing has been enabled.
  • create a job to check if we have bigquery.jobs.create permissions.

• The Bucket Configuration Test verifies if we have the Storage Object Admin permission on your data bucket if you are using your own data bucket to process the data. The test also checks if the bucket is located in the same dataset. We skip this test if you are using a Fivetran-managed bucket.

  NOTE: The tests may take a couple of minutes to finish running.

Related Contentlink

description Destination Overview

assignment Release Notes

settings API Destination Configuration

home Documentation Home