Google BigQuery

Overview

In this article we will discuss how to connect a Google BigQuery database to Preset. There are two methods that we support to do this:

• Connect using a Service Account; or

• Connect via an OAuth2 integration.

Allowlist Preset IPs

Access to your BigQuery instance might be limited via VPC Service Controls with IP restrictions. In this case, you need to allow Preset IPs to interact with BigQuery for a successful connection.

Preset Cloud runs on four regions. Below IPs should be allowed (according to the workspace region):

If you are not sure where your Preset workspace is located, you can refer to the URL on your browser when accessing Preset. It should look like this: https://xxxxxxxx.us2a.app.preset.io/superset..., where us2a means it is in us-east-1.

Connect using a Service Account

It's possible to use a Service Account to connect BigQuery to Preset. Let's go through this process:

Create & Configure a Google Service Account

Access Google Cloud Platform Console, and select the Project that contains the BigQuery instance you're looking to connect to Preset. Copy the Project ID, as you will need this information to set up the connection in Preset.

You'll be redirected to the Service Accounts menu.

1. Click on the + CREATE SERVICE ACCOUNT button.
    

2. Provide a name for the service account, and optionally a description.
    

3. Click on CREATE AND CONTINUE.

4. In the Select a role dropdown, grant the desired permissions to the service account, according to your needs:

   • If you plan to use Preset for read-only access, granting BigQuery Data Viewer, BigQuery Metadata Viewer, BigQuery Read Session User and BigQuery Job User roles should be sufficient.

   • If you also want to execute DML queries in Preset, grant the BigQuery Data Editor role as well.

5. Click on DONE.

You'll be redirected to the Service Accounts list.

Create & Save a JSON Key

Our next step is to create and download a private JSON key. This will be used later on when we configure the database connection in Preset.

1. In the list of accounts, click on the service account email.

2. Navigate to the KEYS tab.

3. Click on the ADD KEY dropdown and select Create new key.
    

4. The Create private key dialog appears. By default, the JSON key type is selected. Click on CREATE to download the file to your computer.

Alright! It's time to connect your BigQuery database to Preset.

Setting up the connection In Preset

Let's start by selecting + Database — have a look at Connecting your Data if you need help wth this step.

Select Google BigQuery.

There are two ways to provide the Service Account credentials:

Method One: Using the dynamic form

1. In the Display Name field, enter a nickname for your database for display in the Preset environment.

2. Provide the Project ID previously copied from Google:
   

3. In the How do you want to enter service account credentials? field, select:

   1. Upload JSON file and then click on Choose File to upload the JSON key downloaded earlier; or

   2. Copy and Paste JSON credentials and then paste the JSON key content in the Service Account text field.

4. (optional) Additional connection parameters can be provided in the Additional Parameters field.

5. Click on Connect.

Great work! You can now make additional configurations to your database, if needed. To learn how to do this, please visit the Advanced Connection Settings article. When ready, click on Finish.

Method Two: Connect via a SQLAlchemy URI

1. Like the previous method, start by selecting the Google BigQuery icon, then scroll to the bottom of the Connect a Database panel.

2. Click on Connect this database with a SQLAlchemy URI string instead.
   

3. The dialog is now organized into two tabs: Basic and Advanced.

4. In the SQLAlchemy URI field, enter your URI using the following syntax (replace ${Project_ID} with the actual Project ID):

bigquery://${Project_ID}

5. Navigate to the Advanced tab and expand the Security section.

6. Paste below content in the Secure Extra field:

{
  "credentials_info":  ${JSON_KEY}
}

7. Open the JSON key downloaded earlier with a text editor, and copy its entire content.

8. Replace ${JSON_KEY} in the Secure Extra field with the copied key.
   

9. Other configurations can be applied in the Advanced tab. If you want to explore them, refer to Advanced Connection Settings.

10. Navigate back to the Basic tab and then select Test Connection.

Great work! The connection is now created.

Connect using an OAuth2 integration

It's possible to connect to BigQuery via OAuth2 to apply individual user permissions from BigQuery to the queries that users run in Preset. This approach requires setting up the integration in the Google Cloud Console.

Let's go through this process.

Select the Google Project

Access Google Cloud Platform Console, and select the Project that contains the BigQuery instance you're looking to connect to Preset. Copy the Project ID, as you will need this information to set up the connection in Preset.

Setting up API & Services

Navigate to the API & Services menu. Then follow these steps:

Creating an OAuth consent screen

1. Select OAuth consent screen in the sidebar menu.

2. Set User Type as Internal and click on CREATE.

3. Provide an App name and User support email.

4. Click on ADD DOMAIN and add preset.io as an Authorized domain.

5. Provide Developer email address.

6. Click on SAVE AND CONTINUE.

Note that the User support email and Developer email address can be internal to your Organization.

Setting up an OAuth Client ID

1. Select Credentials in the sidebar menu.

2. Click on + CREATE CREDENTIALS > OAuth Client ID.

3. Set Web application in the Application type field.

4. Provide a Name to the client.

5. Click on + ADD URI under the Authorized redirect URIs section.

6. Replace ${WORKSPACE_DOMAIN} in the link below and use it as the URI:

https://${WORKSPACE_DOMAIN}/api/v1/database/oauth2/

7. Click on CREATE.

8. In the confirmation dialog, click on DOWNLOAD JSON to retrieve the OAuth2 credentials.

It's time to set up the connection in Preset.

Setting up the connection In Preset

Let's start by selecting + Database — have a look at Connecting your Data if you need help wth this step.

Select Google BigQuery.

1. In the Display Name field, enter a nickname for your database for display in the Preset environment.

2. Provide the Project ID previously copied from Google.

3. Expand the OAuth2 client information section in the database connection form:
    

4. Open the OAuth2 credentials JSON file downloaded earlier in a text editor to fill the OAuth2 form. Fill the:

   1. Client ID field with the "client_id" value.

   2. Client Secret field with the "client_secret" value.

   3. Authorization Request URI field with the "auth_uri" value.

   4. Token Request URI field with the "token_uri" value.

   5. Scope field with https://www.googleapis.com/auth/bigquery.

5. Click on Connect.

6. You'll be redirected to the Advanced tab.

7. Expand the Security section and enable the Impersonate logged in user checkbox.

8. Other configurations can be applied in the Advanced tab. If you want to explore them, refer to Advanced Connection Settings.

9. Click Finish to save your database connection.

To test the integration:

1. Navigate to SQL > SQL Lab.

2. Select the BigQuery connection in the Database dropdown.

3. You should see an Authorization needed error. Click on See more.

4. Click on the provide authorization hyperlink to be redirected to Google. Authenticate to your Google account and grant consent for the integration.

5. After authentication, you're redirected back to Preset. Refresh the page.

Queries should now be properly authenticated with your OAuth2 token. Note that this Authorization needed prompt will be displayed to all users the first time they interact with this database (either in SQL Lab or via charts).