Google BigQuery
  • 25 Apr 2022
  • 6 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Google BigQuery

  • Dark
    Light
  • PDF

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; and,
  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

What is a Google Project?

A project organizes all your Google Cloud resources. A project consists of a set of users; a set of APIs; and billing, authentication, and monitoring settings for those APIs. So, for example, all of your Cloud Storage buckets and objects, along with user permissions for accessing them, reside in a project (source).

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.

Reminder

On the Google Cloud Platform Console, ensure that you are logged in to your correct Google Account.

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

To create a project, select Create Project.

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.

Create_Project1@2x

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.

What is a Google Service Account?

A service account is a special type of Google account intended to represent a non-human user that needs to authenticate and be authorized to access data in Google APIs (source).

Create a Service Account

Let's start by selecting + Create Service Account.

Create_Service_Account1

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.

Create_Service_Account2

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

In the Select a role field, please select one of the following options:

  • Editor: Edit access to all service account resources.
  • Owner: Full access to all service account resources.
  • Viewer: View access to all service account resources. If Viewer is selected, then please note that you will be unable to perform a CSV file upload to the BigQuery database.

When ready, select Done.

Select_Role

The last step—granting access to users—is not required, so feel free to complete the process by selecting Done.

Create_Service_Account4

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.

Create_Service_Account5

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

Create_Service_Account6

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.

Create_Service_Account7

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 currently runs on three regions with the IPs listed below, and those need to be allowlisted so Preset can access your data.

Before connecting to Preset, please take a moment to allowlist the IPs below based on the region that you're deployed in.

us-west-2 us-east-1 eu-north-1 ap-northeast-1
35.161.45.11 44.193.153.196 13.48.95.3 35.74.159.67
54.244.23.85 52.70.123.52 13.51.212.165 35.75.171.157
52.32.136.34 54.83.88.93 16.170.49.24 52.193.196.211

Method One: Provide your JSON Code

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

...then select the Google BigQuery icon:

Select_Google_BigQuery

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

BigQuery_Display_Name

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.

BigQuery_Upload_File1

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.

BigQuery_Upload_File2

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.

BigQuery_Copy_Paste_JSON1

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.

BigQuery_Connect

Great work! You can now make additional configurations to your database, if needed. To learn how to do this, please visit the Optional Settings article.

BigQuery_Step3


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.

BigQuery_URI1

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:

BigQuery_URI2

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.

BigQuery_URI3

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

JSON_with_wrapper

If you want to explore other configuration settings, then please see Optional 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!


Was this article helpful?