Amazon Redshift Connectors > Part III: Data Integration with Amazon Redshift Connector > Mappings and mapping tasks with Amazon Redshift > Amazon Redshift objects in mapping tasks
  

Amazon Redshift objects in mapping tasks

When you configure a mapping task based on an integration template, you can configure advanced properties for Amazon Redshift sources and targets.

Amazon Redshift sources in mapping tasks

For Amazon Redshift source connections used in template-based mapping tasks, you can configure advanced properties in the Sources page of the Mapping Task wizard.
You can configure the following advanced properties:
Advanced Property
Description
S3 Bucket Name
Amazon S3 bucket name for the Amazon Redshift target data.
Use an S3 bucket in the same region as your Amazon Redshift cluster.
Enable Compression
Compresses staging files before writing the files to Amazon Redshift.
Task performance improves when the runtime environment compresses the staging files.
Default is selected.
Staging Directory Location
Amazon Redshift staging directory.
When you run a task in Secure Agent runtime environment, specify a directory path that is available on each Secure Agent machine in the runtime environment.
When you run a task in Hosted Agent runtime environment, leave the staging directory blank. The Hosted Agent creates a directory at a temporary location.
UnloadOptions Property File
Unload command options.
Add options to the unload command to write data from an Amazon Redshift object to an S3 bucket. You can add the following options:
  • - DELIMITER
  • - PARALLEL
  • - ESCAPE
  • - AWS_IAM_ROLE
When you run a task in the Secure Agent runtime environment, either specify the path of the property file that contains the unload options or specify the unload options directly in the UnloadOptions Property File field.
When you run a task in the Hosted Agent runtime environment, specify options directly in the UnloadOptions Property File field.
Turn on S3 Client Side Encryption
Indicates that the Secure Agent encrypts data by using a private encryption key.
Encryption Type
Select the source encryption type. You can select from the following encryption types:
  • - SSE-S3
  • - SSE-KMS
Default is SSE-S3 For more information, see Data encryption in Amazon Redshift sources .
Enable Downloading S3 Files in Multiple Parts
Downloads large Amazon S3 objects in multiple parts.
When the file size of an Amazon S3 object is greater than 5 MB, you can choose to download the object in multiple parts in parallel.
Part Size
Specifies the part size of an object. Default is 5 MB.
Infa Advanced Filter
Not applicable for Amazon Redshift Connector.
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.
SQL Query
Overrides the default query. Enclose column names in double quotes. The SQL query is case sensitive. Specify an SQL statement supported by the Amazon Redshift database.
Number of Sorted Ports
Number of columns used when sorting rows queried from the source. The agent adds an ORDER BY clause to the default query when it reads source rows. The ORDER BY clause includes the number of ports specified, starting from the top of the transformation. When you specify the number of sorted ports, the database sort order must match the session sort order.
Default is 0.
Select Distinct
Selects unique values. The agent includes a SELECT DISTINCT statement if you choose this option. Amazon Redshift ignores trailing spaces. Therefore, the agent might extract fewer rows than expected.
Source Table Name
You can override the default source table name.
Tracing Level
Sets the amount of detail that appears in the log file.
You can choose terse, normal, verbose initialization, or verbose data. Default is normal.

Amazon Redshift targets in mapping tasks

For Amazon Redshift target connections used in template-based mapping tasks, you can configure advanced properties in the Targets page of the Mapping Task wizard.
You can configure the following advanced properties:
Property
Description
S3 Bucket Name
Amazon S3 bucket name for the Amazon Redshift target data.
Use an S3 bucket in the same region as your Amazon Redshift cluster.
Enable Compression
Compresses staging files before writing the files to Amazon Redshift.
Task performance improves when the runtime environment compresses the staging files.
Default is selected.
Staging Directory Location
Amazon Redshift staging directory.
Specify a directory on the machine that hosts the runtime environment.
Batch Size
Minimum number of rows in a batch. Enter a number greater than 0.
Default is 2000000.
Max Redshift Errors per Upload Batch for INSERT
Number of errors within a batch that causes a batch to fail. Enter a positive integer.
If the number of errors is equal to or greater than the property value, the runtime environment writes the entire batch to the error rows file.
Default is 1.
Truncate Target Table Before Data Load
Truncates an Amazon Redshift target before writing data to the target.
Null value for CHAR and VARCHAR data types
String value used to represent null values in CHAR and VARCHAR fields in Amazon Redshift targets, such as NULL or a space character.
Default is an empty string.
Wait time in seconds for file consistency on S3
Number of seconds to wait for the runtime environment to make the staging files available.
Default is 5.
CopyOptions Property File
Path to the property file.
Enables you to add options to the copy command to write data from Amazon S3 to an Amazon Redshift target. You can add the following options:
  • - DELIMITER
  • - ACCEPTINVCHARS
  • - QUOTE
  • - COMPUPDATE
When you run a task in the Secure Agent runtime environment, either specify the path of the property file that contains the copy options or specify the copy options directly in the CopyOptions Property File field.
When you run a task in the Hosted Agent runtime environment, you must specify options directly in the CopyOptions Property File field.
Turn on S3 Server Side Encryption
Indicates that Amazon S3 encrypts data during upload and decrypts data at the time of access.
Turn on S3 Client Side Encryption
Indicates that the runtime environment encrypts data by using a private encryption key.
If you enable both server side and client side encryption, the runtime environment ignores the server side encryption.
Vacuum Target Table
Recovers disk space and sorts rows 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.
Analyze Target Table
Improve the efficiency of the read and write operations.
The query planner on Amazon Redshift updates the statistical metadata to build and choose optimal plans to improve the efficiency of queries.
Prefix for Retaining 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.
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.
Target Table Name
You can override the default target table name.
Part Size
Specifies the part size of an object.
Default is 5 MB.
TransferManager Thread Pool Size
Specifies the number of the threads to write data in parallel.
Default is 10.
Number of Files per Batch
Provide the number of files to calculate the number of the target staging file per batch.
If you do not provide a value, the number of the target staging file is calculated internally.
Note: Specify a minimum value based on the cluster type and number of nodes in the Amazon Redshift cluster. Do not specify a large value. Else, the task fails with an OutOfMemoryError error message.
Success File Directory
Directory for the Amazon Redshift success rows file. Specify a directory path that is available on each Secure Agent machine in the runtime environment. By default, Data Integration writes the success file to the following directory: <Secure Agent installation directory>/apps/Data_Integration_Server/data/success
The Hosted Agent does not create a success rows file. Leave the Success File Directory field blank when you run a task in the Hosted Agent runtime environment.
Error File Directory
Directory for the Amazon Redshift error rows file.
Directory for error rows file. Specify a directory path that is available on each Secure Agent machine in the runtime environment. By default, Data Integration writes the error rows file to the following directory: <Secure Agent installation directory>/apps/Data_Integration_Server/data/error
When you specify the default error file directory you can download the error file from the schedule tab.
When the Error File Directory is other than the default error directory, you cannot download the error file from the Schedule tab. You must go to the specified directory to retrieve the error file.
The Hosted Agent does not create an error rows file. Leave the Error File Directory field blank when you run a task in the Hosted Agent runtime environment.