Connector

Google BigQuery

Last updated: June 01, 2023

Visier can retrieve data through this connector if the following requirements are met.

Prerequisites

Create an integration user or service account in Google BigQuery.

Integration user

Create an integration user in your source system and assign it the appropriate security access so that Visier can retrieve your source data through this user.

Access	Description
IAM roles	Assign the following roles to the integration user: BigQuery Data Viewer BigQuery Job User BigQuery User BigQuery Admin (Optional)
Grant additional permissions on myproject.mydataset.mytable	GRANT SELECT ON myproject.mydataset.mytable TO 'user@example.com'

Access

Description

IAM roles

Assign the following roles to the integration user:

BigQuery Data Viewer

BigQuery Job User

BigQuery User

BigQuery Admin (Optional)

Grant additional permissions on myproject.mydataset.mytable

GRANT SELECT ON myproject.mydataset.mytable TO 'user@example.com'

Connector credentials

You need to provide the following credentials to Visier so that we can retrieve data from your source system.

Credential	Description	Example
Project ID	The unique identifier of the project to retrieve data from.	ID123
Dataset Region	The region in which your BigQuery data is stored. For more information, see BigQuery locations.	us-west1
OAuth Refresh Token	The integration user’s refresh token. For more information, see Token types.	1//98jX00-AmKJdMCgYIARAAGAQSNwF-L9IrlUQlXl_Q-zIcluswxSJtJsz-sPKsv6j7X6vjRheKusfqPk9rGOtzhVM8kE6pZw55-PW
Client ID	The integration user’s client ID.	IntegrationUser
Client Secret	The integration user’s client secret.	RINKPX-7LwauKZg83WvY_98_OEZV2-Lill9
Default Dataset	The default schema for unqualified tables; for example, if a table does not have an assigned schema, use this dataset as the schema.	data_h

Service account

Create a service account in Google Cloud console > IAM & Admin > Service Accounts so Visier can retrieve your source data through the service account instead of using a refresh token. Add a key and assign the appropriate security access to the service account.

Access	Description
IAM roles	Assign the following roles to the service account: BigQuery Job User
Dataset	BigQuery Data Viewer BigQuery User

Access

Description

IAM roles

Assign the following roles to the service account:

BigQuery Job User

Dataset

BigQuery Data Viewer

BigQuery User

You need to provide the following service account credentials to Visier so that we can retrieve data from your source system.

Credential	Description	Example
Project ID	The unique identifier of the project to retrieve data from.	ID123
Dataset Region	The region in which your BigQuery data is stored. For more information, see BigQuery locations.	US
Default Dataset	The default schema for unqualified tables; for example, if a table does not have an assigned schema, use this dataset as the schema.	Dataset1
Service Account Email	The service account email.	example@bigquery-ID123.iam.gserviceaccount.com
Service Account Key	The service account key.	{ "type": "service_account", "project_id": "psychic-outcome-397412", "private_key_id": "*", "private_key": "-----BEGIN PRIVATE KEY----------END PRIVATE KEY-----\n", "client_email": "readonlyserviceaccount@psychic-", "client_id": "104218", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/readonlyserviceaccount%40psychic-outcom***", "universe_domain": "googleapis.com" }

Data files

The Google BigQuery data connector only retrieves data stored in BigQuery for use in Visier.

For other data, you must provide that data through alternative methods such as SFTP or Visier’s other data connectors.

How the connector loads data

Initially, data connectors retrieve a full history for each subject to generate an initial history of events. In each subsequent data retrieval, each connector handles the data load differently.

Full load: All data from the source system is loaded into Visier.
Delta load: Only changed data is loaded into Visier. A delta load may take either the entire history or a specific number of years of history for each changed record.
Snapshot load: All data at a particular point in time. A snapshot load does not contain historical records or show changes over time.
Partial load: A subset of data is loaded into Visier.

This connector retrieves snapshot data for a particular date and time.

However, the data type and key/updated fields can be configured so the connector only retrieves data for changed records.