Objective

The steps below will walk you through the process of connecting BigQuery to Segment as a source, along with the prerequisites. 

Product

Twilio Segment

Environment

Segment Console

Procedure 

The steps to setup your BigQuery source with Reverse ETL are: 

1. Construct a BigQuery role and service account.
2. Create a BigQuery source in your Segment workspace. 

Construct a BigQuery role and service account.

You can provide Segment access to your BigQuery resources in two ways: 

Grant Full Access 

1. In BigQuery, navigate to IAM & Admin > Service Accounts.
2. Click + Create Service Account to create a new service account.
3. Enter your Service account name and a description of what the service account will do.
4. Click Create and Continue.
5. Click + Add another role and add the BigQuery User role.
6. Click + Add another role and add the BigQuery Data Editor role.
7. Click Continue, then click Done.

Grant Limited Access

1. In BigQuery, navigate to IAM & Admin > Roles.
2. Click + CREATE ROLE to create a new role.
3. Add Title and Description as you like.
4. Click ADD PERMISSIONS and add the permission listed in the above tables. Repeat this step until you’ve added all required permissions.
5. Click CREATE.
6. Navigate to IAM & Admin > Service Accounts.
7. Click + Create Service Account to create a new service account.
8. Enter your Service account name and a description of what the account will do.
9. Click Create and Continue.
10. In the Grant this service account access to project section, select the role you just created.
11. Click Continue.
12. Click Done. Copy and keep the Service Account email handy for the next steps.

13. Navigate to the BigQuery SQL editor and create a dataset that will be used by Segment:

    CREATE SCHEMA IF NOT EXISTS `__segment_reverse_etl`;

14. Grant limited access to the Segment Reverse ETL dataset

    GRANT `roles/bigquery.dataEditor` ON SCHEMA `__segment_reverse_etl` TO "serviceAccount:<YOUR SERVICE ACCOUNT EMAIL>";

Create a BigQuery source in your Segment workspace 

1. In the BigQuery console, search for the service account you created.
2. When your service account pulls up, click the 3 dots under Actions and select Manage keys.
3. Click Add Key > Create new key.
4. In the pop-up window, select JSON for the key type and click Create. The file will be downloaded.
5. Copy all the content in the JSON file you created in the previous step.
6. Open the Segment app and navigate to Connections > Sources.
7. On the My sources page, click + Add source.
8. Search for “BigQuery” and select the BigQuery source from the sources catalog. On the BigQuery overview page, click Add Source.
9. On the Set up BigQuery page, enter a name for your source and paste all the credentials you copied from previous step into the Enter your credentials section.
10. Enter the location of your BigQuery warehouse in the Data Location field.
11. Click Test Connection to test to see if the connection works. If the connection fails, make sure you have the right permissions and credentials and try again.
12. If the test connection completes successfully, click Add source to complete the setup process.

Additional Information 

More information on setting up BigQuery as your Reverse ETL source can be found here.