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 account owner permission to add destinations
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.
-
Go to your Google Cloud Console’s projects list.
-
Find your Project ID and make a note of it. You will need it to configure Fivetran.
Find Fivetran service account link
-
In another tab, go to your Fivetran destination setup form.
-
Select I already have a destination and select BigQuery.
-
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.
Create a Private key (JSON) for your service account. Make a note of it. You will need it to configure Fivetran.
Configure service account link
-
Go back to the IAM & admin tab, and go to the project members list.
-
Select + Add.
-
In the New Members 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.
-
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.
Set bucket access control
In the GCP console, set the access control for the bucket to Fine-grained.
IMPORTANT: If you set the access control for the bucket to Uniform bucket-level, the setup tests will fail with the
Cannot insert legacy ACL for an object when uniform bucket-level access is enablederror.
Assign permissions to service account
You must give the service account (in the setup form) Storage Object Admin permission for the bucket, so that it can read and write the data from the bucket.
-
In your Google Cloud Console, go Storage > Browser to see the list of buckets in your current project.
-
Select the bucket you want to use.
-
Go to Permissions and then click Add Members.
-
In the Add members window, enter the Fivetran service account you found in Step 2 or the service account you created in Step 3.
-
From the Select a role dropdown, select Storage Object Admin.
Set the life cycle of objects in the bucket
You must set a lifecycle rule so that data older than one day is deleted from your bucket.
-
In your Google Cloud Console, go Storage > Browser to see the list of buckets in your current project.
-
In the list, find the bucket you are using for Fivetran, and in the Lifecycle rules column, select its rules.
-
Click Add rule. A detail view will open.
-
In Select object conditions, select Age and enter 1.
-
Click Continue.
-
In Select action, select Delete.
-
Click Continue and then click Save.
Complete Fivetran configuration link
- Log in to your Fivetran account.
- Go to the Manage Account page.
- In the Destinations tab, click +Destination.
- On the Add Destination To Fivetran page, enter a Destination Name of your choice.
- Click Add Destination.
- Select BigQuery as the destination type.
- In the destination setup form, enter the Project ID you found in Step 1.
- (Optional) If you want to use your service account, set the Use own Service Account toggle to ON, and then enter the Private key you created in Step 3.
- Enter the Data Location.
- (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.
- 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.
- Choose your Timezone.
- 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.createpermissions on your data warehouse. - create a table in the dataset (
bigquery.tables.createpermissions) and insert a row in the table (bigquery.tables.updateDatapermissions) to check if billing has been enabled. - create a job to check if we have
bigquery.jobs.createpermissions.
- create a dataset to check if we have
-
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
settings API Destination Configuration