Frequently Asked Questions for Google BigQuery Connector

Frequently Asked Questions for Google BigQuery Connector

General Questions

General Questions

What is Google Cloud Platform?
Google Cloud Platform is a set of public cloud computing services offered by Google. It provides a range of hosted services for compute, storage, and application development that run on Google hardware. Google Cloud Platform services can be accessed by software developers, cloud administrators, and other enterprise IT professionals over the public internet or through a dedicated network connection.
Google Cloud Platform provides Google BigQuery to perform data analytics on large datasets.
How can I access Google Cloud Platform?
You must create a Google service account to access Google Cloud Platform. To create a Google service account, click the following URL: https://cloud.google.com/
What are the permissions required for the Google service account to read data from or write data into a table in a Google BigQuery dataset?
You must verify that you have read and write access to the Google BigQuery dataset that contains the source table and target table.
When you read data from or write data to a Google BigQuery table, you must have the following permissions:
  • bigquery.datasets.create
  • bigquery.datasets.get
  • bigquery.datasets.getIamPolicy
  • bigquery.datasets.updateTag
  • bigquery.models.*
  • bigquery.routines.*
  • bigquery.tables.create
  • bigquery.tables.delete
  • bigquery.tables.export
  • bigquery.tables.get
  • bigquery.tables.getData
  • bigquery.tables.list
  • bigquery.tables.update
  • bigquery.tables.updateData
  • bigquery.tables.updateTag
  • resourcemanager.projects.get
  • resourcemanager.projects.list
  • bigquery.jobs.create
When you only read data from a Google BigQuery table, you must have the following permissions:
  • bigquery.datasets.get
  • bigquery.datasets.getIamPolicy
  • bigquery.models.getData
  • bigquery.models.getMetadata
  • bigquery.models.list
  • bigquery.routines.get
  • bigquery.routines.list
  • bigquery.tables.export
  • bigquery.tables.get
  • bigquery.tables.getData
  • bigquery.tables.list
  • resourcemanager.projects.get
  • resourcemanager.projects.list
  • bigquery.jobs.create
  • bigquery.tables.create
What are the operations that
Data Integration
supports for Google BigQuery Connector?
You can use Google BigQuery Connector to read from and write data to Google BigQuery. You can perform the following operations when you write data to a Google BigQuery target:
  • Insert
  • Update
  • Upsert
  • Delete
What are the operations that I can perform on partitioned tables using Google BigQuery Connector?
You can perform the insert operation on partitioned tables using Google BigQuery Connector. For more information about partition tables, click the following URL: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language
Does Google BigQuery Connector support both the
Data Integration
Hosted Agent and the Secure Agent?
Yes. Google BigQuery Connector supports both the
Data Integration
Hosted Agent and the Secure Agent.
What information do I need to create a Google BigQuery connection?
Before you create a Google BigQuery connection, you must have the following information:
  • Runtime Environment. Name of the runtime environment where you want to run the tasks.
  • Service Account ID. The client_email value present in the JSON file that you download after you create a Google service account.
  • Service Account Key. The private_key value present in the JSON file that you download after you create a Google service account.
  • Project ID. The project_id value present in the JSON file that you download after you create a Google service account.
  • Dataset ID. Name of the dataset that contains the source table and target table that you want to connect to.
  • Storage Path. Path in Google Cloud Storage where the Google BigQuery Connector creates a local stage file to store data temporarily.
  • Connection mode. The mode that you want to use to read data from or write data to Google BigQuery. You can select Simple, Hybrid, or Complex connection modes.
  • Schema Definition File Path. Specifies a directory on the client machine where the Data Integration Service must create a JSON file with the sample schema of the Google BigQuery table. The JSON file name is the same as the Google BigQuery table name. Alternatively, you can specify a storage path in Google Cloud Storage where the Data Integration Service must create a JSON file with the sample schema of the Google BigQuery table. You can download the JSON file from the specified storage path in Google Cloud Storage to a local machine.
What modes can I use to read data from a Google BigQuery source using Google BigQuery Connector?
You can use staging or direct mode to read data from a Google BigQuery source using Google BigQuery Connector.
What modes can I use to write data to a Google BigQuery target using Google BigQuery Connector?
You can use bulk or streaming mode to write data to a Google BigQuery target using Google BigQuery Connector.
While writing data, does Google BigQuery Connector persist the staging file in Google Cloud Storage?
By default, the Google BigQuery Connector deletes the staging file in Google Cloud Storage. However, you can configure the task or mapping to persist the staging file after writing data to the Google BigQuery target. To persist the staging file, select the
Persist Staging File After Loading
option in the advanced target properties.
What are the staging file data formats that Google BigQuery Connector supports?
Google BigQuery Connector supports CSV and JSON (Newline Delimited) data formats for the staging files created in Google Cloud Storage.
You can use CSV data format to for staging files that contain primitive data and non-repeated columns. You cannot use CSV data format for staging files that contain Record data and repeated columns.
What are the data types that
Data Integration
supports for Google BigQuery sources and targets?
Data Integration
supports the following data types for Google BigQuery sources and targets:
  • BOOLEAN
  • BYTE
  • DATE
  • DATETIME
  • FLOAT
  • INTEGER
  • RECORD
  • STRING
  • TIME
  • TIMESTAMP
Does Google BigQuery Connector support partitioning?
Yes. Google BigQuery Connector supports key range partitioning for Google BigQuery sources.
You can configure a partition key for fields of the following data types:
  • String
  • Integer
  • Timestamp. Use the following format: YYYY-MM-DD HH24:MI:SS
You cannot configure a partition key for Record data type columns and repeated columns.
Can I use PowerExchange for Cloud Applications to read data from or write data to Google BigQuery?
Yes. You can use PowerExchange for Cloud Applications to read data from or write data to Google BigQuery.
Does Google BigQuery Connector support federated queries?
Yes. You can use Google BigQuery Connector or PowerExchange for Cloud Applications to read data from federated data sources such as Google Cloud Storage and Google Cloud Bigtable. Ensure that the data location of the Google BigQuery dataset, Google Cloud Bigtable, and Google Cloud Storage bucket is the same.
How do I access the sample schema of a Google BigQuery table?
Google BigQuery Connector creates a JSON file with the sample schema of the Google BigQuery table. In the
Schema Definition File Path
field of the Google BigQuery connection, you can specify a directory where the Google BigQuery Connector must create the JSON file.
Alternatively, you can specify a storage path in Google Cloud Storage where the Google BigQuery Connector must create a JSON file with the sample schema of the Google BigQuery table. You can download the JSON file from the specified storage path in Google Cloud Storage to a local machine.


Updated August 06, 2020