Hi, I'm Ask INFA!
What would you like to know?
ASK INFAPreview
Please to access Ask INFA.

Table of Contents

Search

  1. Preface
  2. Part 1: Introduction to Amazon Redshift connectors
  3. Part 2: Data Integration with Amazon Redshift V2 Connector
  4. Part 3: Data Integration with Amazon Redshift Connector

Amazon Redshift Connectors

Amazon Redshift Connectors

Troubleshooting for Amazon Redshift V2 Connector

Troubleshooting for Amazon Redshift V2 Connector

Using the JVM option for upsert operation

For an upsert operation, you can set the JVM option
-DuseDeleteForUpsert=true
and connect all the fields of the target table. Use the following rules when you set the JVM option:
  • When you set the JVM option, the Secure Agent deletes the records of the target table based on the primary key match between the source table and the target table. All the records of the source table are inserted into the target table. When you do not set the JVM option, the Secure Agent updates existing rows and inserts other rows as if marked for insert.
  • For mappings in advanced mode, configure the JVM option in the
    Spark Session Properties
    when you create a mapping task.
  • When you use a target table that has all the columns forming a composite key and you do not set an update column in the target, the mapping might fail with a warning. When you set the JVM option, the mapping task runs successfully.
  • When you use a target table that has duplicate values in an update column, the target table has lesser number rows after you run the mapping. When you do not set the JVM option, the target table does not have lesser number of rows.
  • When you use a source table that has duplicate values in a column and select the column as an update column on the target, an additional row is added to the target. When you do not set the JVM option, an additional row is not added to the target.
  • For an upsert operation, when you use a table with column names that contain special characters and do not set the JVM option, the mapping fails.

Amazon S3 bucket does not exist or the user does not have permission to access the bucket

Do not modify the time on the machine that hosts the Secure Agent. The time on the Secure Agent must be correct as per the time zone. Otherwise, the mapping fails with an exception.

NOT NULL columns with default values

Even when you do not map the NOT NULL columns that have default values in an Amazon Redshift target table, the insert, update, or upsert operation is successful and the default values for NOT NULL columns are used.
If you set the JVM option
-DRetainUnmappedNotNullColumnValidation
value to
true
in the Secure Agent properties, the operation is not successful and the default values for NOT NULL columns are not used.

How to implement the upsert operation using Amazon Redshift V2 Connector?

For information about implementing the upsert operation, see Implementing the upsert operation using Amazon Redshift V2 Connector.

How to configure AWS IAM authentication for Amazon Redshift V2 Connector?

For information about configuring AWS IAM authentication, see Configuring AWS IAM Authentication for Amazon Redshift and Amazon Redshift V2 Connectors.

How to connect to Amazon Redshift Serverless offered by Amazon Web Services (AWS) using the Amazon Redshift V2 connector?

For information about connecting to Amazon Redshift Serverless, see Using Amazon Redshift Serverless with Cloud Data Integration.

Invalid timestamp error occurs when a string data type is mapped to a time data type in a mapping

When you run a mapping enabled for
SQL ELT optimization
to write data from a string column that contains date, timestamp and time information and you want to process it with default date/time format to write to Redshift, we can make use of JVM property
-DHonorInfaDateFormat=true
for the Secure Agent.
To configure the JVM option in
Administrator
, perform the following steps:
  1. Select
    Administrator
    >
    Runtime Environments
    .
  2. On the
    Runtime Environments
    page, select the Secure Agent machine that runs the mapping.
  3. Click
    Edit
    .
  4. In the
    System Configuration Details
    section, select
    Data Integration Server
    as the Service and DTM as the
    Type
    .
  5. Edit the JVM Option system property and set the value to
    -DHonorInfaDateFormat=true
    .
  6. Click
    Ok
    to save the changes.
We also need to set
DateTime Format String
session property as per the input string format under advanced session properties in a mapping task.

Connection timeout error while downloading data from Amazon S3 staging to the agent machine

Network performance and reliability issues when connecting to Amazon S3 can cause slow response times, connection failures, or request errors.
These issues might be due to suboptimal configuration of client-side connection management parameters such as maximum concurrent connections, connection timeout, socket timeout, acquisition timeout, and retry settings.
To resolve these issues, configure the following properties for the Secure Agent:
  • -DAWSS3MaxConnectionPool. The maximum number of simultaneous HTTP connections that the Amazon S3 client can maintain. Default value is 500.
  • -DAWSS3ConnectionTimeOut. The timeout duration in milliseconds to establish a connection to Amazon S3. Default value is 1200000 milliseconds.
  • -DAWSS3SocketTimeOut. The timeout duration in milliseconds to read data from an established connection. Default value is 600000 milliseconds.
  • -DAWSS3AcquisitionTimeOut. The timeout duration in milliseconds to acquire a connection from the connection pool. Default value is 180000 milliseconds.
  • -DAWSMaxRetries. The maximum number of retry attempts for a connection.
Perform the following tasks to configure the JVM options in the Secure Agent:
  1. Click
    Administrator
    Runtime Environments
    .
  2. On the
    Runtime Environments
    page, select the Secure Agent.
  3. Click
    Edit
    .
  4. In the
    System Configuration Details
    section, select
    Service
    as
    Data Integration Server
    and
    Type
    as
    DTM
    .
  5. Edit and configure the following JVM options:
    JVM option
    Value
    JVMOption1
    -DAWSS3MaxConnectionPool=<number of connections>
    JVMOption2
    -DAWSS3ConnectionTimeOut=<duration in milliseconds>
    JVMOption3
    -DAWSS3SocketTimeOut=<duration in milliseconds>
    JVMOption4
    -DAWSS3AcquisitionTimeOut=<duration in milliseconds>
    JVMOption5
    -DAWSMaxRetries=<number of retries>
  6. Click
    Save
    .

0 COMMENTS

We’d like to hear from you!