Data Integration Connections > Connection properties > Google BigQuery V2 connection properties
  

Google BigQuery V2 connection properties

When you create a Google BigQuery V2 connection, you must configure the connection properties.
The following table describes the Google BigQuery V2 connection properties:
Property
Description
Connection Name
The name of the connection. The name is not case sensitive and must be unique within the domain. You can change this property after you create the connection. The name cannot exceed 128 characters, contain spaces, or contain the following special characters:~ ` ! $ % ^ & * ( ) - + = { [ } ] | \ : ; " ' < , > . ? /
Description
Optional. The description of the connection. The description cannot exceed 4,000 characters.
Type
The Google Big Query V2 connection type.
Runtime Environment
Name of the runtime environment where you want to run the tasks.
Specify a Secure Agent, Hosted Agent, or serverless runtime environment.
Service Account ID
Specifies the client_email value present in the JSON file that you download after you create a service account.
Service Account Key
Specifies the private_key value present in the JSON file that you download after you create a service account.
Project ID
Specifies the project_id value present in the JSON file that you download after you create a service account.
If you have created multiple projects with the same service account, enter the ID of the project that contains the dataset that you want to connect to.
Storage Path
This property applies when you read or write large volumes of data. Required if you read data in staging mode or write data in bulk mode.
Path in Google Cloud Storage where the Secure Agent creates a local stage file to store the data temporarily.
You can either enter the bucket name or the bucket name and folder name.
For example, enter gs://<bucket_name> or gs://<bucket_name>/<folder_name>
Connection mode
The mode that you want to use to read data from or write data to Google BigQuery.
Select one of the following connection modes:
  • - Simple. Flattens each field within the Record data type field as a separate field in the mapping.
  • - Hybrid¹. Displays all the top-level fields in the Google BigQuery table including Record data type fields. Google BigQuery V2 Connector displays the top-level Record data type field as a single field of the String data type in the mapping.
  • - Complex¹. Displays all the columns in the Google BigQuery table as a single field of the String data type in the mapping.
Default is Simple.
Schema Definition File Path¹
Specifies a directory on the Secure Agent machine where the Secure Agent 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 Secure Agent 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.
The schema definition file is required if you configure complex connection mode in the following scenarios:
  • - You add a Hierarchy Builder transformation in a mapping to read data from relational sources and write data to a Google BigQuery target.
  • - You add a Hierarchy Parser transformation in a mapping to read data from a Google BigQuery source and write data to relational targets.
Note: When you use a serverless runtime environment, you must specify a storage path in Google Cloud Storage.
Use Legacy SQL For Custom Query¹
Select this option to use a legacy SQL to define a custom query. If you clear this option, you must use a standard SQL to define a custom query.
Note: Not applicable when you configure the Google BigQuery V2 connection in hybrid or complex mode
Dataset Name for Custom Query¹
When you define a custom query, you must specify a Google BigQuery dataset.
Region Id
Specify the region name where the Google BigQuery dataset that you want to access resides.
Note: You must ensure that you specify a bucket name or the bucket name and folder name in the Storage Path property that resides in the specified region.
For more information about the regions supported by Google BigQuery, see the following Google BigQuery documentation:https://cloud.google.com/bigquery/docs/locations
Optional Properties¹
Specifies whether you can configure certain source and target functionalities through custom properties.
You can select one of the following options:
  • - None. If you do not want to configure any custom properties, select None.
  • - Required. If you want to specify custom properties to configure the source and target functionalities.
Default is None.
Provide Optional Properties¹
Comma-separated key-value pairs of custom properties in the Google BigQuery V2 connection to configure certain source and target functionalities.
Appears only when you select Required in the Optional Properties.
For more information about the list of custom properties that you can specify, see the Informatica Knowledge Base article: https://kb.informatica.com/faq/7/Pages/26/632722.aspx
¹ Does not apply to elastic mappings.
Note: Ensure that you specify valid credentials in the connection properties. The test connection is successful even if you specify incorrect credentials in the connection properties.

Connection modes

You can configure a Google BigQuery V2 connection to use one of the following connection modes:
Simple mode
If you use simple mode, Google BigQuery V2 Connector flattens each field within the Record data type field as a separate field in the field mapping.
Hybrid mode
If you use hybrid mode, Google BigQuery V2 Connector displays all the top-level fields in the Google BigQuery table including Record data type fields. Google BigQuery V2 Connector displays the top-level Record data type field as a single field of the String data type in the field mapping.
Complex mode
If you use complex mode, Google BigQuery displays all the columns in the Google BigQuery table as a single field of the String data type in the field mapping.

Connection mode example

Google BigQuery V2 Connector reads and writes the Google BigQuery data based on the connection mode that you configure for the Google BigQuery V2 connection.
You have a Customers table in Google BigQuery that contains primitive fields and the Address field of the Record data type. The Address field contains two primitive sub-fields, City and State, of the String data type.
The following image shows the schema of the Customers table in Google BigQuery:
The following table shows the Customers table data in Google BigQuery:
ID
Name
Address.City
Address.State
Mobile
Totalpayments
14
John
LOS ANGELES
CALIFORNIA
+1-9744884744
18433.90
+1-8267389993
29
Jane
BOSTON
MANHATTAN
+1-8789390309
28397.33
+1-9876553784
+1-8456437848

Simple mode

If you use simple connection mode, Google BigQuery V2 Connector flattens each field within the Record data type field as a separate field in the Field Mapping tab.
The following table shows two separate fields, Address_City and Address_State, for the respective sub-fields within the Address Record field in the Customers table:
ID
Name
Address_City
Address_State
Mobile
Totalpayments
14
John
LOS ANGELES
CALIFORNIA
+1-9744884744
18433.90
14
John
LOS ANGELES
CALIFORNIA
+1-8267389993
18433.90
29
Jane
BOSTON
MANHATTAN
+1-8789390309
28397.33
29
Jane
BOSTON
MANHATTAN
+1-9876553784
28397.33
29
Jane
BOSTON
MANHATTAN
+1-8456437848
28397.33
The following image shows the fields in the Field Mapping tab of the Target transformation:

Hybrid mode

If you use hybrid connection mode, Google BigQuery V2 Connector displays all the top-level fields in the Google BigQuery table including Record data type fields. Google BigQuery V2 Connector displays the top-level Record data type field as a single field of the String data type in the Field Mapping tab.
The following image shows the Field Mapping tab of the Target transformation:

Complex mode

If you use complex connection mode, Google BigQuery V2 Connector displays all the columns in the Google BigQuery table as a single field of the String data type in the Field Mapping tab.
The following image shows the STRING_DATA field in the Field Mapping tab of the Target transformation:

Rules and guidelines for Google BigQuery V2 connection modes

Simple mode

Consider the following rules and guidelines when you configure a Google BigQuery V2 connection to use simple connection mode:

Hybrid mode

Consider the following rules and guidelines when you configure a Google BigQuery V2 connection to use hybrid connection mode:

Complex mode

Consider the following rules and guidelines when you configure a Google BigQuery V2 connection to use complex connection mode: