Google BigQuery Connectors

Back Next

Optimize read performance in staging mode

You can configure Data Integration to create a flat file for staging when you read data from a Google BigQuery source to optimize the staging performance.

You can enhance the read operation performance by setting a staging property,

INFA_DTM_RDR_STAGING_ENABLED_CONNECTORS

, for the Secure Agent. Data Integration first copies the data from Google BigQuery source into a flat file located in the local staging file directory. When the staging file contains all the data, Data Integration reads the data.

You can optimize the staging performance when you read data from single or multiple Google BigQuery objects.

Enabling Google BigQuery V2 Connector to optimize the read performance

Perform the following tasks to set the staging property:

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

Data Integration Server

and the type as

Tomcat

Set the value of the Tomcat property

INFA_DTM_RDR_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:

<Secure Agent installation directory>/<GoogleBigQueryV2 package>/CCIManifest

Click

Save

Restart the Secure Agent.

In the Google BigQuery V2 connection, set the

UseRFC4180CSVParser:true

custom property in the

Provide Optional Properties

connection property.

You can check the session logs. If the flat file is created successfully, Data Integration logs the following message in the session log:

The reader is configured to run in [DTM_STAGING_CSV] mode.

In the Google BigQuery advanced source properties, set the read mode as staging and set the Data Format of the staging file property to CSV.

When you enable the staging mode to read source data, you can see the following message in the logs:

READER_1_1_1> SDKS_38636 [2022-07-26 14:59:29.056] Plug-in #601601: DTM Staging is enabled for connector for Source Instance [Source].

When you disable the staging mode to read source data, you can see the following message in the logs:

READER_1_1_1> SDKS_38637 [2022-07-26 16:46:04.312] Plug-in #601601: DTM Staging is disabled for connector for Source Instance [Source].

Rules and guidelines when you optimize the read performance

Consider the following rules when you enable the staging property:

If you run a mapping enabled for

SQL ELT optimization

, the mapping does not consider the staging property and runs without staging optimization.

When you read data of the byte data type from the Google BigQuery source, ensure that the size or precision of the binary data does not exceed 62,914,560 bytes.

Ensure that the total size or precision of all the columns in the Google BigQuery source does not exceed 125,829,120 bytes.

If the format of the staging file is CSV and you read from a single Google BigQuery table with multiple objects as the source type, the mapping runs without staging optimization.

If you do not specify a valid path for the local staging file directory, the mapping fails and the session logs do not display a meaningful error message.

When you parameterize both the Google BigQuery object type and the advanced fields, and select the

Allow Parameter to be overridden at run time

option while configuring the input parameters, the mapping does not consider the staging property and runs without staging optimization.

When you configure staging optimization to process source data with the Numeric data type with a scale greater than 9 and a precision greater than 28, the mapping truncates the data to a scale of 9 while writing to the target. To preserve the same scale and precision of the Numeric data type in the target, perform the following tasks:

Map the BigNumeric data type in the source to the Decimal data type in the target.

Create a table in the backend database with a scale of 9.

Specify the rounding-off mode for the data.

When you configure staging optimization to process data and the source contains data of the Numeric or BigNumeric data types with a precision greater than 28 digits, the mapping fails with the following error:

[ERROR] Error occurred for Transformation - Source while writing the data to DTM Buffer - Data Conversion Failed

Rename Saved Search

Table of Contents

Google BigQuery Connectors

Google BigQuery Connectors

Optimize read performance in staging mode

Optimize read performance in staging mode

Enabling Google BigQuery V2 Connector to optimize the read performance

Rules and guidelines when you optimize the read performance