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

Amazon Redshift V2 targets in mapping tasks

Amazon Redshift V2 targets in
mapping
tasks

For Amazon Redshift V2 target connections used in
mapping
tasks, you can configure advanced target properties in the
Targets
page of the
Mapping
Task wizard.
You can configure the following Amazon Redshift V2 advanced target properties:
Property
Description
S3 Bucket Name
Amazon S3 bucket name for writing the files to Amazon Redshift target.
You can also specify the bucket name with the folder path. If you provide an Amazon S3 bucket name that is in a different region than the Amazon Redshift cluster, you must configure the
REGION
attribute in the Copy command options.
Enable Compression
Compresses the staging files before writing the files to Amazon Redshift.
The task performance improves when the Secure Agent compresses the staged files. Default is selected.
Staging Directory Location
Location of the local staging directory.
When you run a task in Secure Agent runtime environment, specify a directory path that is available on the corresponding Secure Agent machine in the runtime environment.
Specify the directory path in the following manner:
<staging directory>
For example,
C:\Temp
. Ensure that you have the write permissions on the directory.
Does not apply to
elastic mappings
.
Batch Size
Minimum number of rows in a batch.
Enter a number greater than 0. Default is 2000000.
Does not apply to
elastic mappings
.
Max Errors per Upload Batch for INSERT
Number of error rows that causes an upload insert batch to fail.
Enter a positive integer. Default is 1.
If the number of errors is equal to or greater than the property value, the Secure Agent writes the entire batch to the error file.
Truncate Target Table Before Data Load
Deletes all the existing data in the Amazon Redshift target table before loading new data.
Require Null Value For Char and Varchar
Replaces the string value with NULL when you write data to Amazon Redshift columns of Char and Varchar data types.
Default is an empty string.
When you run a mapping to write null values to a table that contains a single column of the Int, Bigint, numeric, real, or double data type, the mapping fails. You must provide a value other than the default value in the
Require Null Value For Char And Varchar
property.
WaitTime In Seconds For S3 File Consistency
Number of seconds to wait for the Secure Agent to make the staged files consistent with the list of files available on Amazon S3.
Default is 0.
Does not apply to
elastic mappings
.
Copy Options
Copy command options.
Add options to the Copy command to write data from Amazon S3 to the Amazon Redshift target when the default delimiter comma (,) or double-quote (") is used in the data. Provide the Amazon Redshift Role Amazon Resource Name (ARN).
You can add the following options:
  • DELIMITER
  • ACCEPTINVCHARS
  • QUOTE
  • COMPUPDATE
  • AWS_IAM_ROLE
  • REGION
For example:
DELIMITER = \036;ACCEPTINVCHARS = #;QUOTE = \037 COMPUPDATE = ON;AWS_IAM_ROLE=arn:aws:iam::<account ID>:role/<role-name>;REGION = ap-south-1
Specify a directory on the machine that hosts the Secure Agent.
If you do not add the options to the Copy command manually, the Secure Agent uses the default values.
S3 Server Side Encryption
Indicates that Amazon S3 encrypts data during upload.
Provide a customer master key ID in the connection property to enable this property. Default is not selected.
S3 Client Side Encryption
Indicates that the Secure Agent encrypts data using a private key.
Provide a master symmetric key ID in the connection property to enable this property. If you enable both server-side and client-side encryptions, the Secure Agent ignores the server-side encryption.
When you use a serverless runtime environment, you cannot configure client-side encryption for Amazon Redshift V2 targets.
Client-side encryption does not apply to
elastic mappings
.
Analyze Target Table
Runs an ANALYZE command on the target table.
The query planner on Amazon Redshift updates the statistical metadata to build and choose optimal plans to improve the efficiency of queries.
Vacuum Target Table
Recovers disk space and sorts the row in a specified table or all tables in the database.
You can select the following recovery options:
  • None
  • Full
  • Sort Only
  • Delete Only
  • Reindex
Default is None. For more information about the vacuum tables, see Vacuum tables.
Prefix to retain staging files on S3
Retains staging files on Amazon S3.
Provide both a directory prefix and a file prefix separated by a slash (/) or only a file prefix to retain staging files on Amazon S3. For example,
backup_dir/backup_file
or
backup_file
.
Success File Directory
Directory for the Amazon Redshift success file.
Specify a directory on the machine that hosts the Secure Agent.
Does not apply to
elastic mappings
.
Error File Directory
Directory for the Amazon Redshift error file.
Specify a directory on the machine that hosts the Secure Agent.
Does not apply to
elastic mappings
.
Treat Source Rows As
Overrides the default target operation.
Default is
INSERT
.
Select one of the following override options:
NONE
By default, none is enabled. The Secure Agent considers the task operation that you select in the
Operation
target property.
INSERT
Performs insert operation. If enabled, the Secure Agent inserts all rows flagged for insert. If disabled, the Secure Agent rejects the rows flagged for insert.
DELETE
Performs delete operation. If enabled, the Secure Agent deletes all rows flagged for delete. If disabled, the Secure Agent rejects all rows flagged for delete.
UPDATE and UPSERT
Performs update and upsert operations. To perform an update operation, you must map the primary key column and at least one column other than primary key column. You can select the following data object operation attributes:
  • Update as Update: The Secure Agent updates all rows as updates.
  • Update else Insert: The Secure Agent updates existing rows and inserts other rows as if marked for insert.
Amazon Redshift V2 Connector does not support the Upsert operation in the Upgrade Strategy transformation. To use an Update Strategy transformation to write data to an Amazon Redshift target, you must select
Treat Source Rows As
as
None
.
By default, the Secure Agent performs the task operation based on the value that you specify in the
Operation
target property. However, if you specify an option in the
Treat Source Rows As
property, the Secure Agent ignores the value of that you specify in the
Operation
target property or in the Update Strategy transformation.
Override Target Query
Overrides the default update query that the Secure Agent generates for the update operation with the update query that you specify.
Transfer Manager Thread Pool Size
Number of threads to write data in parallel.
Default is 10.
Does not apply to
elastic mappings
.
Pre-SQL
The pre-SQL commands to run a query before you read data from Amazon Redshift. You can also use the UNLOAD or COPY command. The command you specify here is processed as a plain text.
Post-SQL
The post-SQL commands to run a query after you write data to Amazon Redshift. You can also use the UNLOAD or COPY command. The command you specify here is processed as a plain text.
Preserve record order on write
Retains the order of the records when you read data from a CDC source and write data to an Amazon Redshift target.
Use this property when you create a mapping to capture the changed record from a CDC source. This property enables you to avoid inconsistencies between the CDC source and target.
Does not apply to
elastic mappings
.
Minimum Upload Part Size
Minimum size of the Amazon Redshift object to upload an object.
Default is 5 MB.
Does not apply to
elastic mappings
.
Number of files per batch
Calculates the number of the staging files per batch.
If you do not provide the number of files, Amazon Redshift V2 Connector calculates the number of the staging files.
Does not apply to
elastic mappings
.
Schema Name
Overrides the default schema name.
Target table name
Overwrites the default target table name.
Recovery Schema Name
Schema that contains recovery information stored in the
infa_recovery_table
table on the target system to resume the extraction of the changed data from the last checkpoint.
Does not apply to
elastic mappings
.
Temporary Credential Duration
The time duration during which an IAM user can use the dynamically generated temporarily credentials to access the AWS resource. Enter the time duration in seconds.
Default is 900 seconds.
If you require more than 900 seconds, you can set the time duration maximum up to 12 hours in the AWS console and then enter the same time duration in this property.
Forward Rejected Rows
This property is not applicable for Amazon Redshift V2 Connector.