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.

Connection Methods

• Method One: Connect by providing a JSON code for authentication; or,
• Method Two: Connect by providing a SQLAlchemy URI and JSON authentication code.

Below we will discuss both methods. For simplicity and ease-of-use, we recommend that users follow Method One.

Setting up the Google Backend

In order to connect to Google BigQuery, regardless of the preferred method, you'll need to:

1. Select an existing Google Project or create a new one;
2. Create and configure a Google Service Account;
3. Create and save a JSON key.

We'll walk you through all of the steps above so that you can get started using your Google BigQuery database connection!

Pre-connection Steps with Google

Step One: Associate a Google Project

The first step is to select an existing Google Project or create a new Google Project. You can do this by navigating to the Google Cloud Platform Console .

If this is your first time visiting the Platform Console, be sure to select your country and accept the Terms of Service.

The Platform Console will display any existing projects — feel free to select an existing project if desired.

To create a project, select Create Project.

In the Project name field, enter a name for your project — this will become your project ID.

Take note of the correctly formatted "Project ID" below the Project name field — you will need this if you decide to connect via a SQLAlchemy URI. In the graphic below, the project ID is preset-connections-317005. Select Edit if you want to change this.

If the Google account you are logged in under belongs to an organization (i.e., non-gmail.com), then an Organization field appears and the domain of your organization appears by default.

If needed, select a location in the Location field. When done, select Create.

Post-creation, the Service Accounts screen appears.

Step Two: Create & Configure a Google Service Account

In this step we will create and configure a new Google Service Account.

Create a Service Account

Let's start by selecting + Create Service Account.

In the Service account name field, enter a memorable name for the service account. The Service account ID field will automatically populate based on the defined account name.

In the Service account description field, enter a brief description of the service account.

When done, select Create and Continue.

In this step, you need to grant service account access.

In the Select a role field, 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 perform DML operation within Preset, grant the BigQuery Data Editor role as well.

When ready, select Done (the last step—granting access to users—is not required).

Step Three: Create & Save a JSON Key

A table displaying your new Google Service Account—which we've called Preset Connections—appears.

Our next step is to create a private key in JSON that will be downloaded to your local system. This will be used later on when we configure the database connection in Preset.

In the table, under the Actions column header, select the vertical ellipsis icon and then choose Manage keys.

The Service Account details screen appears with the Keys tab selected. In the Add Key drop-down menu, select Create new key.

The Create private key window appears. By default, the JSON key type is selected. Ensure that this is indeed the case and then select Create.

The key, in JSON file format, will download to your local system. Be sure to save this file.

Alright! So we've created a project, a service account, and now have a JSON key! Time to connect your BigQuery database to Preset.

Allowlist Preset IPs

Preset Cloud runs on four regions. For Preset to access your data, first thing you need to do is to add region based Preset IP addresses to your Inbound and Outbound firewall rules.

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.

Method One: Provide your JSON Code

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

...then select the Google BigQuery icon:

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

Option One: Upload JSON File

In the How do you want to enter service account credentials? field, select Upload JSON file and then select Choose File.

Navigate to the JSON file you downloaded earlier and select it for upload to Preset. The JSON filename will appear — select the trash bin icon to remove the file, if needed.

Option Two: Copy & Paste JSON Code

Alternatively, you can directly paste your JSON code.

In the How do you want to enter service account credentials? field, select Copy and Paste JSON credentials. The Service Account text-entry field appears.

Copy your JSON code in its entirety and then paste the contents into the Service Account field.

Making the Connection

If you have any additional parameters, then enter them in the Additional Parameters field.

If not, then go ahead and select 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.

Method Two: Connect via a SQLAlchemy URI

Enter SQLAlchemy URI

Like Method One above, start by selecting the Google BigQuery icon, then scroll to the bottom of the Connect a Database panel.

Select Connect this database with a SQLAlchemy URI string instead.

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

In the SQLAlchemy URI field, enter your URI using the following syntax:

bigquery://<project_id>

Given the example used earlier in this article, the URI would look as follows:

Provide JSON Authentication Code

Select the Advanced tab and then expand the Security panel.

Copy the JSON code from the file you downloaded earlier and then paste it into the Secure Extra field.

To ensure that the code works correctly, please wrap the JSON with a "credentials_info" element. This looks as follows:

If you want to explore other configuration settings, then please see Advanced Connection Settings.

Make the Connection

Next, navigate back to the Basic tab and then select Test Connection.

A notification message will appear indicating success or failure. If the test worked, then go ahead and select Connect to finalize the database connection.

Great work!

Connecting with OAuth2 (Optional)

If you'd like to enable OAuth2 to apply individual user permissions from BigQuery to the queries that users run in Preset, expand the "OAuth2 client information" section in the database connection form:

Fill out each of the fields in this section with your OAuth2 client information. The Project id and JSON file or credentials are optional when connecting using OAuth2.

Connect your database once the OAuth2 client information has been filled out.

In the next step, scroll down to expand the "Security" section of the form and make sure that the "Impersonate logged in user" checkbox is checked.

Once that's done, click Finish to save your database connection.