Connect Pigment with Google BigQuery

This article explains how to connect a Google BigQuery dataset with Pigment to retrieve any query result into a Pigment block.

As with all other native Pigment integrations, you’ll first need to configure a Connection in the Integration library page. Once configured, this connection can be used by any Application that is authorized as a data source. These authorized Apps are defined during the ‘Import Data’ configuration step. Now, let’s see how to configure and use this native integration!

Overview

This process starts in Google Cloud Platform, with the creation of a service account. Next, generate a Service Account Key and grant this service account access to the datasets containing the data you want to load in Pigment. Then, in Pigment, create a Connection and add the Key credentials. Once set up, you can input a SQL query and the Google BigQuery connector will load the result into your block. 

Preliminary steps on Google Cloud Platform 

GCP Step 1 - Creating an Service Account 

Pigment uses a Service account to authenticate to your GCP. For security reasons, we recommend a dedicated Service account for Pigment.

Follow these steps to create a new Service Account. 

Name the service account. (ex: Pigment).

Grant the Service Account access to your project with a BigQuery Job User role. 

A service account is created within a single Project, however you can grant access for the service account to multiple projects if you would like to retrieve data from other projects. 

GCP Step 2 - Generate a Service Account Key 

Open the Service account you just created, go to the Keys section and click on Add Key → Create new key and select JSON. This will download a JSON file. You will need this file to configure the Connection on Pigment.

Pigment will read the BigQuery project_id for the project in which the service account was created from the JSON credentials file. If the service account has been granted access to the other projects, BigQuery will manage those credentials as well.

GCP Step 3 - Configure BigQuery access for the Service Account

This step grants the Pigment service account access to the specific BigQuery datasets from which you would like to query data and load it into your Pigment block. 

Follow these steps to grant access for your service account to each dataset containing data you would like to load into Pigment. In the Select a role section, select the BigQuery Data Viewer role.

Remember to grant access for datasets in other projects if needed.

Setting up the BigQuery Connector in Pigment

Navigate to your Integrations page within Pigment (Settings → Integrations). Locate the BigQuery integration and click on the + Add button.

Click on Set up a New Connection.

In the Set up a new BigQuery connection popup, name your connection.

Copy and paste into the Credentials section from the JSON file of the key that you created in GCP Step 2 (Generate a Service Account Key).

Select the specific applications in the Application access section for the applications containing the blocks you would like to connect with BigQuery for your data imports.

Click on Setup to finish the setup! 

Load BigQuery data into Pigment

Within the import panel for your block, select Integrations and select the BigQuery connection you just created in the Choose a connection dropdown. 

Now, you just need to input a Query  and click on Start Import.

The result of the query should load in Pigment within a few seconds!