Google BigQuery Connectors

Back Next

Optimize lookup performance in staging mode

You can configure Data Integration to optimize the staging performance of a lookup operation.

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

INFA_DTM_LKP_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 processes this data read.

Consider the following rules when you enable the staging property:

You can optimize the staging performance when you use connected and cached lookup operation.

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.

Enabling Google BigQuery V2 Connector to optimize the lookup performance

Perform the following steps to set the staging property 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

Data Integration Server

and the type as

Tomcat

Set the value of the Tomcat property INFA_DTM_LKP_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>/downloads/<GoogleBigQueryV2 package>/CCIManifest

Click

Save

Restart the Data Integration Service.

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 lookup mode as staging and set the Data Format of the staging file property to CSV.

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

LKPDP_1:READER_1_1> SDKS_38636 [2023-04-21 15:07:23.020] Plug-in #601601: DTM Staging is enabled for connector for Source Instance [Source].

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

LKPDP_1:READER_1_1> SDKS_38637 [2023-04-21 15:42:45.538] Plug-in #601601: DTM Staging is disabled for connector for Source Instance [Source].

Rename Saved Search

Table of Contents

Google BigQuery Connectors

Google BigQuery Connectors

Optimize lookup performance in staging mode

Optimize lookup performance in staging mode

Enabling Google BigQuery V2 Connector to optimize the lookup performance