Available Options

• ImportRelationalDataToRedshift: Imports relational data to Amazon Redhshift using OLEDB/ADO.net/ODBC driver. You can create OLEDB connection or ADO.net connection for connecting to various Relational/Non-relational databases such as ORACLE, MySQL, DB2, MS Access, Excel. There are many 3rd party ADO.net drivers available to connect to Cloud Services such as Salesforce, Microsoft Dynamics too. If you have ODBC driver then you can always use ADO.net Connection with Odbc Data Provider.
  
  
• ImportLocalFilesToRedshift: Imports local flat file(s) (compressed or non compressed) to Amazon Redhshift. Flat file(s) have to be CSV format (using any single character column delimiter and new line as row delimiter (\r\n)). If your files are in *.gz format then you must specify IsSourceFilesCompressed=True
  
  

Available Options

• None (Default): No action performed on source file(s) after they are uploaded to S3 Staging area
  
  
• Delete: Delete local source file(s) after they are uploaded to S3 Staging area
  
  
• Move: Move local source file(s) to some archive folder after they are uploaded to S3 Staging area. Archive folder is specified by SourceArchiveFolder property
  
  

Available Options

• None: No action performed on source stage file(s) after they are uploaded to S3 Staging area
  
  
• Delete (Default): Delete local source stage file(s) after they are uploaded to S3 Staging area
  
  
• Move: Move local source stage file(s) to some archive folder after they are uploaded to S3 Staging area. Archive folder is specified by SourceArchiveFolder property
  
  

• {{DATAROOT}} = Path of source folder
• {{YYYY}} = Year (e.g. 2014)
• {{MM}} = Month number (e.g. 03)
• {{MON}} = Month name (e.g. March)
• {{DD}} = Day (e.g. 31)
• {{HH}} or {{HH24}} = Hour in 24 hrs format (e.g. 23)
• {{HH12}} = Hour in 12 hrs format (e.g. 12)
• {{MI}} = Minute (e.g. 59)
• {{SS}} = Second (e.g. 45)
• {{MS}} = Milisecond (e.g. 999)
• {{US}} = Microsecond (e.g. 999999)

• {{DATAROOT}} = Path of source stage folder
• {{YYYY}} = Year (e.g. 2014)
• {{MM}} = Month number (e.g. 03)
• {{MON}} = Month name (e.g. March)
• {{DD}} = Day (e.g. 31)
• {{HH}} or {{HH24}} = Hour in 24 hrs format (e.g. 23)
• {{HH12}} = Hour in 12 hrs format (e.g. 12)
• {{MI}} = Minute (e.g. 59)
• {{SS}} = Second (e.g. 45)
• {{MS}} = Milisecond (e.g. 999)
• {{US}} = Microsecond (e.g. 999999)

When this option is specified, the backslash character (\) in input data is treated as an escape character. The character that immediately follows the backslash character is loaded into the table as part of the current column value, even if it is a character that normally serves a special purpose. For example, you can use this option to escape the delimiter character, a quote, an embedded newline, or the escape character itself when any of these characters is a legitimate part of a column value.

If you specify the ESCAPE option in combination with the REMOVEQUOTES option, you can escape and retain quotes (' or ") that might otherwise be removed. The default null string, \N, works as is, but can also be escaped in the input data as \\N. As long as you do not specify an alternative null string with the NULL AS option, \N and \\N produce the same results.

You cannot use the ESCAPE option for FIXEDWIDTH loads, and you cannot specify the escape character itself; the escape character is always the backslash character. Also, you must ensure that the input data contains the escape character in the appropriate places.

Here are some examples of input data and the resulting loaded data when the ESCAPE option is specified. The result for row 4 assumes that the REMOVEQUOTES option is also specified. The input data consists of two pipe-delimited fields:

1|The quick brown fox\[newline]
jumped over the lazy dog.
2| A\\B\\C
3| A \| B \| C
4| 'A Midsummer Night\'s Dream'

The data loaded into column 2 looks like this:

The quick brown fox
jumped over the lazy dog.
A\B\C
A|B|C
A Midsummer Night's Dream

The ESCAPE option does not interpret octal, hex, unicode, or other escape sequence notation. For example, if your source data contains the octal linefeed value (\012) and you try to load this data with the ESCAPE option, Amazon Redshift loads the value 012 into the table and does not interpret this value as a linefeed that is being escaped.

In order to escape newlines in data that originates from Windows platforms, you might need to use two escape characters: one for the carriage return and one for the linefeed. Alternatively, you can remove the carriage returns before loading the file (for example, by using the dos2unix utility).

Available Options

• None: No action performed on target stage file(s) after they are loaded to Redshift Database
  
  
• Delete (Default): Delete target stage file(s) after they are loaded to Redshift Database
  
  
• Move: Move target stage file(s) to some archive folder after they are loaded to Redshift Database. Archive folder is specified by TargetStageArchiveFolder property
  
  

• {{DATAROOT}} = Path of target stage folder
• {{YYYY}} = Year (e.g. 2014)
• {{MM}} = Month number (e.g. 03)
• {{MON}} = Month name (e.g. March)
• {{DD}} = Day (e.g. 31)
• {{HH}} or {{HH24}} = Hour in 24 hrs format (e.g. 23)
• {{HH12}} = Hour in 12 hrs format (e.g. 12)
• {{MI}} = Minute (e.g. 59)
• {{SS}} = Second (e.g. 45)
• {{MS}} = Milisecond (e.g. 999)
• {{US}} = Microsecond (e.g. 999999)

Surrounding quotation marks are removed from strings in the incoming data. All characters within the quotes, including delimiters, are retained. If a string has a beginning single or double quotation mark but no corresponding ending mark, the COPY command fails to load that row and returns an error. The following table shows some simple examples of strings that contain quotes and the resulting loaded values.

If no DATEFORMAT is specified, the default format is 'YYYY-MM-DD'. For example, an alternative valid format is 'MM-DD-YYYY'.

If you want Amazon Redshift to automatically recognize and convert the date format in your source data, specify 'auto'. The 'auto'' keyword is case sensitive. If the COPY command does not recognize the format of your date or time values, or if your date or time values use different formats, use the 'auto' option with the DATEFORMAT or TIMEFORMAT parameter. The 'auto' option recognizes several formats that are not supported when using a DATEFORMAT and TIMEFORMAT string. For more information, see Using Automatic Recognition with DATEFORMAT and TIMEFORMAT.

The date format can include time information (hour, minutes, seconds), but this information is ignored. The AS keyword is optional. For more information, see DATEFORMAT and TIMEFORMAT Strings .

Enables loading of data into VARCHAR columns even if the data contains invalid UTF-8 characters. When AllowInvalidCharacters is specified, COPY replaces each invalid UTF-8 character with a string of equal length consisting of the character specified by replacement_char. (See ReplaceInvalidCharacterWith) For example, if the replacement character is '^', an invalid three-byte character will be replaced with '^^^'.

The replacement character can be any ASCII character except NULL. The default is a question mark ( ? ). For information about invalid UTF-8 characters, see Multibyte Character Load Errors.

COPY returns the number of rows that contained invalid UTF-8 characters, and it adds an entry to the STL_REPLACEMENTS system table for each affected row, up to a maximum of 100 rows per node slice. Additional invalid UTF-8 characters are also replaced, but those replacement events are not recorded.

If ACCEPTINVCHARS is not specified, COPY returns an error whenever it encounters an invalid UTF-8 character.

ACCEPTINVCHARS is valid only for VARCHAR columns.

If the load returns the error_count number of errors or greater, the load fails. If the load returns fewer errors, it continues and returns an INFO message that states the number of rows that could not be loaded. Use this option to allow loads to continue when certain rows fail to load into the table because of formatting errors or other inconsistencies in the data. Set this value to 0 or 1 if you want the load to fail as soon as the first error occurs. The AS keyword is optional.

The actual number of errors reported might be greater than the specified MaxAllowedErrorsBeforeFail because of the parallel nature of Amazon Redshift. If any node in the Amazon Redshift cluster detects that MaxAllowedErrorsBeforeFail has been exceeded, each node reports all of the errors it has encountered.

Governs automatic computation and refresh of optimizer statistics at the end of a successful COPY command. By default, if the STATUPDATE option is not used, statistics are updated automatically if the table is initially empty. See also Analyzing Tables.

Whenever ingesting data into a nonempty table significantly changes the size of the table, we recommend updating statistics either by running an ANALYZE command or by using the STATUPDATE ON option.

With STATUPDATE ON (or TRUE), statistics are updated automatically regardless of whether the table is initially empty. If STATUPDATE is used, the current user must be either the table owner or a superuser. If STATUPDATE is not specified, only INSERT permission is required.

With STATUPDATE OFF (or FALSE), statistics are never updated.

Allows data files to be loaded when contiguous columns are missing at the end of some of the records. The missing columns are filled with either zero-length strings or NULLs, as appropriate for the data types of the columns in question. If the EMPTYASNULL option is present in the COPY command and the missing column is a VARCHAR column, NULLs are loaded; if EMPTYASNULL is not present and the column is a VARCHAR, zero-length strings are loaded. NULL substitution only works if the column definition allows NULLs.

For example, if the table definition contains four nullable CHAR columns, and a record contains the values apple, orange, banana, mango, the COPY command could load and fill in a record that contains onlythe values <apple, orange. The missing CHAR values would be loaded as NULL values.