You can use an Amazon Redshift object as a single target in a synchronization task, mapping, or mapping task. You can also create an Amazon Redshift target based on the input source. When you use Amazon Redshift target objects, you can select a standard object as the primary source.
You can insert, update, upsert, and delete data from Amazon Redshift targets. An update or insert task writes an entire batch to an Amazon Redshift target if no errors occur within the batch. If an error occurs within a batch, the Secure Agent writes the entire batch to the error rows file.
When you configure the advanced target properties, you configure properties specific to Amazon Redshift. You can encrypt data, update statistical metadata of the database tables to improve the efficiency of queries, load data into Amazon Redshift from flat files in an Amazon S3 bucket, and use vacuum tables to recover disk space and sort rows in tables.
If a mapping includes a flat file or an Amazon Redshift target, you can choose to use an existing target or create a new target at run time. You must specify Amazon Redshift target object names in lowercase letters.
Note: If the distribution key column in a target table contains null values and you configure a task with an upsert operation for the same target table, the task might create duplicate rows. To avoid creating duplicate rows, you must perform one of the following tasks:
•Replace the null value with a non-null value when you load data.
•Do not configure the column as a distribution key if you expect null values in the distribution key column.
•Remove the distribution key column from the target table temporarily when you load data. You can use the Pre-SQL and Post-SQL properties to remove and then add the distribution key column in the target table.
Amazon Redshift staging directory for Amazon Redshift targets
The agent creates a staging file in the directory that you specify in the target properties. The synchronization tasks, mappings, and mapping tasks stage data in a staging directory before writing data to Amazon Redshift. You can configure the task to retain or delete staging files.
You cannot configure a directory on Hosted Agent. The Hosted Agent creates a directory to stage data at a temporary location and deletes the staging files from the temporary location when the task completes.
To improve task performance, enable compression for staging files. Specify a staging directory with an appropriate amount of disk space for the volume of data that you want to process. Specify a directory path that is available on each Secure Agent machine in the runtime environment.
The applications creates subdirectories in the staging directory based on the time that the task runs. Subdirectories use the following naming convention:
To optimize query performance, you can configure a task to analyze the target table. Target table analysis updates statistical metadata of the database tables.
You can use the Analyze Target Table option to extract sample rows from the table, analyze the samples, and save the column statistics. Amazon Redshift then updates the query planner with the statistical metadata. The query planner uses the statistical metadata to build and choose optimal plans to improve the efficiency of queries.
You can run the Analyze Target Table option after you load data to an existing table by using the Copy command. If you load data to a new table, the Copy command performs an analysis by default.
Data encryption in Amazon Redshift targets
To protect data, you can enable server-side encryption or client-side encryption to encrypt the data that you insert in Amazon Redshift.
If you enable both server-side and client-side encryption for an Amazon Redshift target, then the client-side encryption is used for data load.
Server-side encryption for Amazon Redshift targets
If you want Amazon Redshift to encrypt data while uploading the .csv files to Amazon Redshift, you must enable server-side encryption. To enable server-side encryption, select Server Side Encryption as the encryption type in the advanced target properties on the Schedule page.
You can configure the customer master key ID generated by AWS Key Management Service (AWS KMS) in the connection properties for server-side encryption. You must add IAM EC2 role and IAM Redshift role to the customer master key when you use IAM authentication and server-side encryption using customer master key. If you select the server-side encryption in the advanced target properties and do not specify the customer master key ID in the connection properties, Amazon S3-managed encryption keys are used to encrypt data.
Client-side encryption for Amazon Redshift targets
Client-side encryption is a technique to encrypt data before transmitting the data to the Amazon Redshift server.
When you enable client-side encryption for Amazon Redshift targets, the Secure Agent fetches the data from the source, writes the data to the staging directory, encrypts the data, and then writes the data to an Amazon S3 bucket. The Amazon S3 bucket then writes the data to Amazon Redshift.
To enable client-side encryption, you must provide a master symmetric key in the connection properties and select the Turn on S3 Client Side Encryption option in the advanced target properties.
The Secure Agent encrypts the data by using the master symmetric key. The master symmetric key is a 256-bit AES encryption key in the Base64 format. Amazon Redshift Connector uploads the data to the Amazon S3 server by using the master symmetric key and then loads the data to Amazon Redshift by using the copy command with the Encrypted option and a private encryption key for additional security. To enable client-side encryption, perform the following tasks:
Retain staging files
You can retain staging files on Amazon S3 after the writes data to the target. You can retain files to create a data lake of your organizational data on Amazon S3. The files you retain can also serve as a backup of your data.
When you create a target connection, you can configure a file prefix or directory prefix to save the staging files. After you provide the prefixes, the creates files within the directories at Amazon S3 location specified in the target connection. Configure one of the following options for the Prefix for Retaining Staging Files on S3 property:
•Provide a directory prefix and a file prefix. For example, backup_dir/backup_file. The creates the following directories and files:
•Do not provide a prefix. The does not save the staging files.
Copy command
You can use the Copy command to append data in a table. The Copy command uses a secure connection to load data from source to Amazon Redshift.
You can specify the Copy command options directly in the CopyOptions Property File field. Enter the options in uppercase and delimit the options by using a The Copy command has the following options and default values:
The property file contains the Copy command options. Include the property file path in the CopyOptions Property File field. For example:
C:\Temp\Redshift\copyoptions.txt
It is recommended to use octal representation of non-printable characters as DELIMITER and QUOTE.
Copy command options
The Copy command options read data from Amazon S3 and write data to Amazon Redshift in a particular format. You can apply compression to data in the tables or delimit the data with a particular character.
To add options to the Copy command, use the CopyOptions Property File option. You can set the following options:
DELIMITER
A single ASCII character to separate fields in the input file. You can use characters such as pipe (|), tilde (~), or a tab (\t). The delimiter must not be a part of the data. Default is \036, the octal representation of the non-printable character, record separator.
ACCEPTINVCHARS
Loads data into VARCHAR columns even if the data contains UTF-8 characters that are not valid. When you specify ACCEPTINCHARS, the replaces UTF-8 character that is not valid with an equal length string consisting of the character specified in ACCEPTINVCHARS. If you have specified '|' in ACCEPTINVCHARS, the replaces the three-byte UTF-8 character with '|||'.
If you do not specify ACCEPTINVCHARS, the COPY command returns an error when it encounters an UTF-8 character that is not valid. You can use the ACCEPTINVCHARS option on VARCHAR columns. Default is question mark (?).
QUOTE
Specifies the quote character to use with comma separated values. Default is \037, the octal representation of the non-printable character, unit separator.
COMPUPDATE
Overrides current compression encoding and applies compression to an empty table. Use the COMPUPDATE option in an insert operation when the rows in a table are more than 100,000. The behavior of COMPUPDATE depends on how it is configured:
- If you do not specify COMPUPDATE, the COPY command applies compression if the target table is empty and all columns in the table have either RAW or no encoding.
- If you specify COMPUPDATE ON, the COPY command replaces the existing encodings if the target table is empty and the columns in the table have encodings other than RAW.
- If you specify COMPUPDATE OFF, the COPY command does not apply compression.
Default is OFF.
AWS_IAM_ROLE
Specify the Amazon Redshift Role Resource Name (ARN) to run the on installed on an Amazon EC2 system in the following format: AWS_IAM_ROLE=arn:aws:iam::<account ID>:role/<role-name>
For example: arn:aws:iam::123123456789:role/redshift_write
STATUPDATE
Optional. Governs computation and refresh of optimizer statistics at the end of a successful COPY command.
The behavior of STATUPDATE depends on how it is configured:
- If you specify STATUPDATE ON, the COPY command updates the statistics even if the table is empty or not. Ensure that the current user is either the table owner or a superuser for the COPY command to work.
- If you specify STATUPDATE OFF, the COPY command does not update the statistics.
- If you do not specify STATUPDATE, the COPY command requires the insert permissions.
Default is OFF.
Field mappings
The field mapping page displays key icons for primary key fields. When you configure field mappings, map all key fields and NOT NULL fields to successfully insert or upsert data to Amazon Redshift targets. Though Amazon Redshift enforces NOT NULL fields, it does not enforce key constraints.
The field mapping page displays key icons for primary key fields. Other Amazon Redshift key types are not marked. You must map a non-key field for update operation. If you use Amazon Redshift Identity fields in field mappings, map all available Identity fields or none. The Identity fields contain data that is automatically generated by Amazon Redshift.
You cannot map identity columns in a field map, if the identity column is not part of a key. If an identity column is part of a key, you must map the identity column in field map. However, you cannot set a value on the identity column from source.
Vacuum tables
You can use vacuum tables to recover disk space and sorts rows in a specified table or all tables in the database.
After you run bulk operations, such as delete or load, or after you run incremental updates, you must clean the database tables to recover disk space and to improve query performance on Amazon Redshift. Amazon Redshift does not reclaim and reuse free space when you delete and update rows.
Vacuum databases or tables often to maintain consistent query performance. You can recover disk space for the entire database or for individual tables in a database. You must run vacuum when you expect minimal activity on the database or during designated database administration schedules. Long durations of vacuum might impact database operations. Run vacuum often because large unsorted regions result in longer vacuum times.
You can enable the vacuum tables option when you configure the advanced target properties. You can select the following recovery options:
None
Does not sort rows or recover disk space.
Full
Sorts the specified table or all tables in the database and recovers disk space occupied by rows marked for deletion by previous update and delete operations.
Sort Only
Sorts the specified table or all tables in the database without recovering space freed by deleted rows.
Delete Only
Recovers disk space occupied by rows marked for deletion by previous update and delete operations, and compresses the table to free up used space.
Reindex
Analyzes the distribution of the values in the interleaved sort key columns to configure the entire Vacuum table operations for a better performance.