Google BigQuery is a fully managed, cloud-based big data analytics web service for processing very large read-only data sets. BigQuery was designed for analyzing data on the order of billions of rows, using a SQL-like syntax.
For detailed info on how Stitch works with BigQuery, check out the Stitch Google BigQuery Destination Overview.
BigQuery Setup Requirements
To set up BigQuery in Stitch, you need:
A user with full access to an existing Google Cloud Platform (GCP) project within BigQuery. Stitch won’t be able to create one for you.
Admin permissions for BigQuery and Google Cloud Storage (GCS). This includes the BigQuery Admin and Storage Admin permissions. Stitch requires these permissions to create and use a GCS bucket to load replicated data into BigQuery.
Access to a project where billing is enabled and a credit card is attached. Even if you’re using BigQuery’s free trial, billing must still be enabled for Stitch to load data.
Step 1: Create a GCP account
This one’s easy. Simply sign up here and you’ll receive a $300 credit.
Step 2: Create a GCP project and enable billing
Next, create a new GCP project to house your BigQuery data warehouse by following these instructions.
Be sure to enable billing for the account and attach a credit card, even if you’re using the free trial option. If billing isn’t enabled, Stitch will encounter issues when loading data into your data warehouse.
Step 3: Grant user permissions
After the project has been created, open the project in the GCP console. You can do this by either:
Clicking Manage Project Settings in the Project Info box on the dashboard page, as seen to the right.
Toggling between Projects by clicking the drop-down menu next to the Google Cloud Platform logo in the upper-left corner.
Then, follow the instructions in the tab below. Note: Even if the user has Owner permissions, the permissions outlined below must still be granted to the user. Stitch will encounter loading errors otherwise.
In the page that displays, click the IAM option in the menu on the left side of the page. This will display a page of all the members that have access to the project.
In the list, locate the user you want to use to connect BigQuery to Stitch.
Click the Role(s) drop-down in the row for that user.
Select the BigQuery option and click BigQuery Admin.
Next, select the Storage option and click Storage Admin.
In the table below are the database user privileges Stitch requires to connect to and load data into BigQuery.
|Privilege name||Reason for requirement|
|BigQuery Admin (roles/bigquery.admin)||
Required to create the necessary objects in a BigQuery project to load and store your data.
|Storage Admin (roles/storage.admin)||
Required to access and create objects in a Google Cloud Storage (GCS) bucket. Stitch uses a GCS bucket to temporarily store data before loading it into a dataset in your BigQuery project.
Step 4: Authenticate with Google
The last step is to complete Google’s authorization process and grant Stitch access to the BigQuery project you created in Step 2.
- Sign into your Stitch account, if you haven’t already.
- Click the Destination tab, then the BigQuery icon.
- Click Sign in with Google.
- If you aren’t already signed into your Google account, you’ll be prompted for your credentials. Sign in as the same user you granted BigQuery and Storage Admin permissions to in Step 3.
- After you sign in, you’ll see a list of the permissions requested by Stitch:
- Read/Write Access to Google Cloud Storage - Stitch requires Read/Write access to create and use a GCS bucket to load replicated data into BigQuery.
- Full Access to BigQuery - Stitch requires full access to be able to create datasets and load data into BigQuery.
- Read-Only Access to Projects - Stitch requires read-only access to projects to allow you to select a project to use during the BigQuery setup process.
- Basic Profile Information - Stitch uses your basic profile info to retrieve your user ID.
- Offline Access - To continuously load data, Stitch requires offline access. This allows the authorization token generated during setup process to be used for more than an hour after the initial authentication takes place.
- To grant access, click the Authorize button.
- After you sign into Google and grant Stitch access, you’ll be redirected back to Stitch.
Fill in the fields that display:
- Google Cloud Project: From the dropdown, select the project you created in Step 2.
- Google Cloud Storage Location: From the dropdown, select the location where data should be stored:
- US: Data will be stored in the United States
- EU: Data will be stored in Europe
- Click Finish Setup.
Troubleshooting connection errors
If you receive an error message, we recommend trying these troubleshooting steps for common destination connection problems before reaching out to support.
We’ve found that the majority of the time, these resources can resolve most issues.
Learning about Stitch and BigQuery
Now that your BigQuery data warehouse is up and running, we recommend learning about how Stitch will load your data into it.
BigQuery Destination Overview: This overview will give you the high-level basics for working with your BigQuery data warehouse.
Destination Data Loading Guide: This article covers many of the common scenarios Stitch will encounter when loading data into your BigQuery destination and the expected behavior for each scenario.