Connect to BigQuery
You can connect to BigQuery and make it available as a resource in Retool. Once complete, you can write queries that interact with your BigQuery data.
Requirements
All users for Retool organizations on Free or Team plans have global Edit permissions and can add, edit, and remove resources. If your organization manages user permissions for resources, you must be a member of a group with Edit all permissions.
To create a BigQuery resource, you need:
- A Google Cloud project that is configured with service account or OAuth authentication.
- Authentication credentials for the Google Cloud project.
Google Cloud project configuration
Configure your Google Cloud project and manage authentication credentials using the Google Cloud console.
Retool authenticates with BigQuery using service account or OAuth credentials. The authentication method you use determines the type of access granted to Retool and the configuration settings required.
Service account
A service account is a Google Account that is associated with your Google Cloud project. This method allows Retool to access your BigQuery data by authenticating with a service account's credentials rather than user credentials.
You must configure the service account with BigQuery Data Viewer and BigQuery User roles. These roles grant the necessary permissions for Retool to access your data. Once complete, you must generate a private key in JSON format.
OAuth
OAuth enables you to quickly authenticate with Google. You have some control over the scope of permissions that Retool has with your BigQuery data.
You must have access to a BigQuery project with an OAuth client ID that identifies Retool with Google's OAuth servers. If you need to create a OAuth client ID, use the OAuth callback URL provided by Retool as an authorized redirect URI when creating the client in the Google Cloud console.
1. Create a new resource
Sign in to your Retool organization and click the Resources tab. Click Create new > Resource to start configuring your BigQuery resource.
2. Select the resource type
Retool can connect to almost any API or database, and has built-in integrations for popular data sources. Select BigQuery to start configuring the resource.
3. Configure the resource
Specify a name and location for the BigQuery resource. Retool displays the resource name and type in query editors to help users identify them.
Authentication
Retool can authenticate to BigQuery using either service account or OAuth credentials. If you don't already have existing credentials, you can use the provided information to configure authentication in the Google Cloud console.
Select an authentication method to display relevant configuration settings.
Service account
Select Service Account as the authentication method, then provide the entire JSON key. If you downloaded the JSON key, open the file in a suitable plain text editor to avoid incorrect input.
OAuth
Select OAuth as the authentication method, then provide the required OAuth client information. If you have not yet created an OAuth client ID, create one using the OAuth callback URL.
| Setting | Required | Description |
|---|---|---|
| Project ID | Yes | The BigQuery project ID. |
| Client ID | Yes | The OAuth client ID. |
| Client secret | Yes | The Oauth client secret. |
Click Test OAuth integration with your own account to verify that OAuth authentication is configured correctly.
Dataset region
Regardless of which authentication method you use, you must set the Region qualifier to the dataset's location if it's located outside the United States. This allows Retool to fetch relevant metadata for query autocomplete. Refer to Google Cloud dataset documentation to learn more about data locations.
Set Authentication to either Service Account or OAuth, then provide the necessary settings for the chosen method.
Advanced options
Retool organization administrators can configure the following settings for BigQuery resources.
| Setting | Description |
|---|---|
| Disable converting queries to prepared statements | This disables SQL injection protection to allow dynamically generated SQL using JavaScript. |
| Show write GUI mode only | This restricts queries to GUI mode only and prevents users from writing raw SQL queries. |
Click Test Connection to verify that Retool can connect to BigQuery. If the test fails, check the resource settings and try again. Testing a connection only checks whether Retool can successfully connect to the resource. It cannot check whether the provided credentials have sufficient privileges or can perform every supported action.
4. Save the resource
Click Create resource to complete the setup. You can then click either Create app to immediately start building a Retool app or Back to resources to return to the list of resources.
Wrap up
Your BigQuery resource is now ready to use. To interact with BigQuery data, select the resource in the query editor. You can read data using SQL queries, write data using GUI queries (if enabled), and use the schema browser to search tables or columns.