You can configure Data Integration to create a flat file for staging when you write data to a Google BigQuery target in bulk mode. You can set Data Integration to optimize the staging performance.
Data Integration first writes the data to a flat file located in the local staging file directory. When the staging file contains all the data, Data Integration loads the data from the staging file to the Google BigQuery target.
In the Google BigQuery advanced target properties, set the
Local Stage File Directory
property to a directory on your local machine where you want to create the flat file and set the
Data Format of the staging file
property to
CSV
.
When you run the mapping, the flat file is created in the local stage file directory that you specified.
If you do not specify a local stage file directory, the flat file is created in the temp directory in the Linux or Windows machine where the Secure Agent runs.
When the mapping run is completed, the Secure Agent deletes the local staging file.
Enabling Google BigQuery V2 Connector to optimize the staging performance
Perform the following tasks to set the staging property,
INFA_DTM_STAGING_ENABLED_CONNECTORS
, for the Tomcat in the Secure Agent properties:
In Administrator, click
Runtime Environments
.
The Runtime Environments page appears.
Select the Secure Agent for which you want to set the custom configuration property.
Click
Edit Secure Agent
icon corresponding to the Secure Agent you want to edit in Actions .The Edit Secure Agent page appears.
In the
System Configuration Details
section, select the
Service
as
Data Integration Server
and the type as
Tomcat
.
Set the value of the Tomcat property
INFA_DTM_STAGING_ENABLED_CONNECTORS
to the plugin ID of the Google BigQuery V2 Connector.
You can find the plugin ID in the manifest file located in the following directory:
The following image shows the INFA_DTM_STAGING_ENABLED_CONNECTORS property set for the Secure Agent:
Click
Save
.
Restart the Secure Agent.
You can check the session logs. If the flat file is created successfully, Data Integration logs the following message in the session log:
INFA_DTM_STAGING mode is enabled for the write operation.
If you do not set the staging property, Data Integration performs staging without the optimized settings, which might impact the performance of the task.
Rules and guidelines when you optimize the write performance
Consider the following rules when you enable the staging property:
If you run a mapping enabled for
SQL ELT optimization
, the mapping runs without
SQL ELT optimization
.
When the mapping writes a column of the String data type that contains null values to a column of the String data type set to Required constraint in the Google BigQuery target table, the job fails and does not write the data to any of the target columns.
When you write Numeric data to the Google BigQuery target, the Numeric data in the local staging flat file contains trailing zeroes. However, the Secure Agent writes the Numeric data correctly in the Google BigQuery target table.
When you write data of the Binary data type to the Google BigQuery target, ensure that size or precision of the Binary data does not exceed more than 78643200 bytes.
When you write data with a precision of more than 15 digits in the float data type, the data becomes corrupted.