Connect Pigment with Google BigQuery

  • 8 September 2022
  • 0 replies
  • 89 views
Connect Pigment with Google BigQuery
Userlevel 5
Badge +3

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. 

 

Prerequisites

You must have a Google BigQuery Dataset containing the data you want to load in Pigment.

You must have the permissions to create and manage access for the Google Cloud Service Account.

You must have the permissions to manage the service account’s access to the Google Cloud Projects containing the BigQuery Datasets you want to connect with.

 

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!  

A note on data types

  • ARRAY data types are not supported for the import – data from columns of this type will not be imported into Pigment. A query containing a column of ARRAY data type will contain NULL data in the import.

  • STRUCT data types are supported for the import – a query containing nested data in STRUCT type will be imported into Pigment by flattening each nested field as separate columns.


This topic has been closed for comments