Amazon Redshift Connectors

Back Next

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.; The default value is \036 which represents the octal representation of the non-printable known as the 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 Secure Agent 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 Secure Agent 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.
REGION: You can use the REGION attribute when the Amazon S3 staging bucket is not in the same region as the cluster region. If Amazon Redshift resides in the US East (N. Virginia) region, you can use an Amazon S3 bucket residing in the Asia Pacific (Mumbai) region to create staging files. For example,
REGION = ap-south-1
.
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.
TRUNCATECOLUMN: Truncates the data of the VARCHAR and CHAR data types column before writing the data to the target. If the size of the data that you want to write to the target is larger than size of the target column, the Secure Agent truncates the data before writing data to the target column.

By default, the TRUNCATECOLUMNS option is OFF. To enable the TRUNCATECOLUMNS option, specify ON as the value of the TRUNCATECOLUMNS option. For example, TRUNCATECOLUMNS=ON.
AWS_IAM_ROLE: Specify the Amazon Redshift Role Resource Name (ARN) to run the task on Secure Agent 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
IGNOREBLANKLINES: If the input rows contain NULL values, set the
IGNOREBLANKLINES
property to
ON
in the
Copy Options
to ignores blank lines while inserting the data to the target. Specify the property in the following format:
IGNOREBLANKLINES=ON
The property helps ignores blank lines that only contain a line feed in a data file and does not try to load them.

Rename Saved Search

Table of Contents

Amazon Redshift Connectors

Amazon Redshift Connectors

Copy command options

Copy command options