GoodData-BigQuery Integration Details
When setting up direct data distribution from your BigQuery data warehouse, pay attention to the considerations and best practices listed in this article.
This article is applicable to all use cases of GoodData and BigQuery integration:
- Integrate a Data Source for a Data Warehouse (a getting-started guide based on sample data)
- Integrate Data Warehouses Directly to GoodData based on an Existing LDM
User Access Rights
To connect your BigQuery data warehouse and GoodData, we recommend that you use Google Cloud Platform service account.
For the sufficient level of access, grant your service account the following user roles:
bigquery.dataViewerbigquery.jobUser
For more information, see https://cloud.google.com/iam/docs/service-accounts and https://cloud.google.com/bigquery/docs/access-control).
Your service account must have the following permissions:
bigquery.jobs.createbigquery.tables.getbigquery.tables.getDatabigquery.tables.list
Custom BigQuery roles are supported. If you create a custom role in BigQuery, then you must also assign the bigquery.datasets.get permission to your users and the custom role or else you will be unable to import any data.
Google Service Account Key File
GoodData supports using Google service account key file to integrate your BigQuery project with the GoodData workspace and create a Data Source. For more information, see Create a Data Source.
The file is used to import most of the settings when you establish the connection between your BigQuery project and GoodData. The following information is extracted:
- client email
- private key
- Google project ID
To learn how to create service account key files, see https://cloud.google.com/iam/docs/creating-managing-service-account-keys#creating_service_account_keys.
Data Types
The BigQuery data warehouse provides a wide range of data types. During mapping the BigQuery schema and the GoodData logical data model (LDM), data types are automatically converted from a BigQuery data type to a GoodData LDM data type. Some columns may be ignored because their data type is not supported within GoodData or their type may lead to performance issues. If you want to prevent automatic changes, update the schema manually.
| BigQuery Data Type | GoodData LDM Data Type |
|---|---|
| ARRAY | Not supported |
| BOOL | VARCHAR (128) |
| BYTES | Not supported |
| DATE | DATE |
| DATETIME | DATE |
| FLOAT64 | DECIMAL (12, 2) |
| GEOGRAPHY | Not supported |
| INT64 | BIGINT |
| NUMERIC | DECIMAL (12, 2) |
| STRING | VARCHAR (128) |
| STRUCT | Not supported |
| TIME | Not supported |
| TIMESTAMP | DATE |
Materialized Views
Materialized views are supported. They appear in the list of available tables in the LDM Modeler alongside regular tables or views and are included in the schema scanning.
Output Stage Prefixes
BigQuery does not support primary key constraints for tables. After generating the Output Stage, manually rename the a__ prefix to either cp__ (connection point) or r__ (reference) for the generated views.
Limitations
BigQuery external data sources are not supported (for more information, see https://cloud.google.com/bigquery/external-data-sources).
Public and commercial datasets are not supported (for more information, see https://cloud.google.com/bigquery/public-data and https://cloud.google.com/commercial-datasets).
If you need to use a public dataset in your GoodData workspace, add the tables from this dataset to the BigQuery project integrated with the workspace (this is the BigQuery project that is specified in the connected Data Source; see Create a Data Source). To do so, create database views of those tables in the BigQuery project. Once this is done, you can load the data from the views to your workspace.Loading data originating in Google Sheets (Connected Sheets) from BigQuery is not supported (for more information, see https://cloud.google.com/bigquery/docs/connected-sheets).
BigQuery applies limits to data load queries (for more information, see https://cloud.google.com/bigquery/quotas#query_jobs).