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?
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:
1Select Administrator > Runtime Environments.
2On the Runtime Environments page, select the Secure Agent machine that runs the mapping.
3Click Edit.
4In the System Configuration Details section, select Data Integration Server as the Service and DTM as the Type.
5Edit the JVM Option system property and set the value to -DHonorInfaDateFormat=true.
6Click 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.