PowerExchange for Amazon Redshift User Guide

10.4.1
- 10.5.4
- 10.5.3
- 10.5.2
- 10.5
- 10.4.0

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. PowerExchange for Amazon Redshift only supports DELIMITER, ACCEPTINVCHARS, QUOTE, REGION, COMPUPDATE, and AWS_IAM_ROLE copy command options.

To add options to the Copy command, use the

Copy Options

option. You can set the following options:

DELIMITER: A single ASCII character to separate fields in the input file. Default is \036, the octal representation of the non-printable character and the record separator.; You can use characters such as pipe (|), tilde (~), or a tab (\t). The delimiter must not be a part of the data.
ACCEPTINVCHARS: Loads data into VARCHAR columns even if the data contains UTF-8 characters that are not valid. Default is question mark (?).; When you specify ACCEPTINCHARS, the Data Integration Service 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 Data Integration Service 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.
QUOTE: Specifies the quote character to use with comma separated values. Default is \037, the octal representation of the non-printable character and the 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. Default is OFF.; Use the COMPUPDATE option in an insert task 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.
AWS_IAM_ROLE: Specify the Amazon Redshift Role Resource Name (ARN) to run the session on Data Integration Service 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

Rename Saved Search

Table of Contents

PowerExchange for Amazon Redshift User Guide

PowerExchange for Amazon Redshift User Guide

Copy Command Options

Copy Command Options