Google BigQuery
Visier can retrieve data through this connector if the following requirements are met.
If table sources are enabled, you can select the source type during connector credential creation. Select one of:
- File sources: File-based containers that use regex and override behavior to manage data. For more information about acceptable files, see Data File Guidelines.
- Table sources: Row-based containers that support incremental updates and SQL transformations during sync. For more information, see Use SQL with Table Sources.
The following table describes the differences between the behavior of each source type.
|
File sources |
Table sources |
|---|---|
|
Each data transfer or extraction outputs to a unique file. |
Each data transfer or extraction directly modifies the same table. |
|
Use Visier Extraction Language to query records in a source's Records tab or SQL to query sources in the Data > Query room. For more information, see Query Source Data. |
Use Spark SQL to interact with the table data. For more information, see Use SQL with Table Sources. |
|
To delete an individual's data, such as for GDPR compliance, open a Visier Technical Support ticket to remove the information one-by-one from every extracted file. |
To delete an individual's data, such as for GDPR compliance, use a DELETE SQL query on the table without contacting Visier Technical Support. Note: The snapshot retention period specifies how long to keep previous snapshots of tables before permanently deleting them. You can set the retention period between 1 and 180 days. The default is 3 days. |
|
The data is available in the Data Transfers room. For more information, see Navigate Studio. |
The data is available in the associated table source in the Sources room. For more information, see Sources. |
|
Exclude or delete uploaded files individually. Files load in order by snapshot time. |
Use the Reset action to roll the table back to a particular timestamp. This rolls back all changes since the timestamp. You can't easily undo actions or apply changes out of order. Tables do not keep a complete history of every action. |
|
Optionally enable Snap to midnight setting to move the snapshot time to the start of the day. |
Actions are applied strictly in order. |
|
Files uploaded manually or by SFTP connect to the source using a file regex. |
Because you upload files directly into a table source, files uploaded manually in the Data Transfers room or by SFTP cannot connect to the source. |
You cannot change the source type after creating a credential. To use a different source type, create a new credential.
Prerequisites
- Create an integration user or a Google Cloud Platform (GCP) service account in Google BigQuery.
- Allow network and data traffic between Visier and your source system. For more information, see Allow Visier Connections.
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:
|
|
Grant additional permissions on myproject.mydataset.mytable |
GRANT SELECT ON myproject.mydataset.mytable TO 'user@example.com' |
Create a connector credential in Visier to authenticate and authorize access to your source system. Connector credentials can be created in Studio or through an API. For more information, see Set Up Data Connectors and "Create a connector credential" in API Reference. You will need to provide the following credentials in your connector credential configuration.
|
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 Google Cloud Platform (GCP) 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 integration user:
|
|
Dataset |
|
Create a connector credential in Visier to authenticate and authorize access to your source system. Connector credentials can be created in Studio or through an API. For more information, see Set Up Data Connectors and "Create a connector credential" in API Reference. You will need to provide the following credentials in your connector credential configuration.
|
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. For more information about sending data to Visier, see File Upload.
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.