Data format parameters

Data format parameters

FORMAT [AS]

(Optional) Identifies data format keywords. The FORMAT arguments are described following.

CSV [ QUOTE [AS] 'quote_character' ]

Enables use of CSV format in the input data. To automatically escape delimiters, newline characters, and carriage returns, enclose the field in the character specified by the QUOTE parameter. The default quotation mark character is a double quotation mark ( " ). When the quotation mark character is used within a field, escape the character with an additional quotation mark character. For example, if the quotation mark character is a double quotation mark, to insert the string A "quoted" word the input file should include the string "A ""quoted"" word". When the CSV parameter is used, the default delimiter is a comma ( , ). You can specify a different delimiter by using the DELIMITER parameter.

When a field is enclosed in quotation marks, white space between the delimiters and the quotation mark characters is ignored. If the delimiter is a white space character, such as a tab, the delimiter isn't treated as white space.

CSV can't be used with FIXEDWIDTH, REMOVEQUOTES, or ESCAPE.

QUOTE [AS] 'quote_character'

Optional. Specifies the character to be used as the quotation mark character when using the CSV parameter. The default is a double quotation mark ( " ). If you use the QUOTE parameter to define a quotation mark character other than double quotation mark, you don't need to escape double quotation marks within the field. The QUOTE parameter can be used only with the CSV parameter. The AS keyword is optional.

DELIMITER [AS] ['delimiter_char']

Specifies characters that are used to separate fields in the input file, such as a pipe character ( | ), a comma ( , ), a tab ( \t ), or multiple characters such as |~|. Non-printable characters are supported. Characters can also be represented in octal as their UTF-8 code units. For octal, use the format '\ddd', where 'd' is an octal digit (0–7). The default delimiter is a pipe character ( | ), unless the CSV parameter is used, in which case the default delimiter is a comma ( , ). The AS keyword is optional. DELIMITER can't be used with FIXEDWIDTH.

FIXEDWIDTH 'fixedwidth_spec'

Loads the data from a file where each column width is a fixed length, rather than columns being separated by a delimiter. The fixedwidth_spec is a string that specifies a user-defined column label and column width. The column label can be either a text string or an integer, depending on what the user chooses. The column label has no relation to the column name. The order of the label/width pairs must match the order of the table columns exactly. FIXEDWIDTH can't be used with CSV or DELIMITER. In Amazon Redshift, the length of CHAR and VARCHAR columns is expressed in bytes, so be sure that the column width that you specify accommodates the binary length of multibyte characters when preparing the file to be loaded. For more information, see Character types.

The format for fixedwidth_spec is shown following:

'colLabel1:colWidth1,colLabel:colWidth2, ...'

SHAPEFILE [ SIMPLIFY [AUTO] ['tolerance'] ]

Enables use of SHAPEFILE format in the input data. By default, the first column of the shapefile is either a GEOMETRY or IDENTITY column. All subsequent columns follow the order specified in the shapefile.

You can't use SHAPEFILE with FIXEDWIDTH, REMOVEQUOTES, or ESCAPE.

To use GEOGRAPHY objects with COPY FROM SHAPEFILE, first ingest into a GEOMETRY column, and then cast the objects to GEOGRAPHY objects. .

SIMPLIFY [tolerance]

(Optional) Simplifies all geometries during the ingestion process using the Ramer-Douglas-Peucker algorithm and the given tolerance.

SIMPLIFY AUTO [tolerance]

(Optional) Simplifies only geometries that are larger than the maximum geometry size. This simplification uses the Ramer-Douglas-Peucker algorithm and the automatically calculated tolerance if this doesn't exceed the specified tolerance. This algorithm calculates the size to store objects within the tolerance specified. The tolerance value is optional.

For examples of loading shapefiles, see Loading a shapefile into Amazon Redshift.

AVRO [AS] 'avro_option'

Specifies that the source data is in Avro format.

Avro format is supported for COPY from these services and protocols:

• Amazon S3

• Amazon EMR

• Remote hosts (SSH)

Avro isn't supported for COPY from DynamoDB.

Avro is a data serialization protocol. An Avro source file includes a schema that defines the structure of the data. The Avro schema type must be record. COPY accepts Avro files created using the default uncompressed codec as well as the deflate and snappy compression codecs. For more information about Avro, go to Apache Avro.

Valid values for avro_option are as follows:

• 'auto'

• 'auto ignorecase'

• 's3://jsonpaths_file'

The default is 'auto'.

COPY automatically maps the data elements in the Avro source data to the columns in the target table. It does so by matching field names in the Avro schema to column names in the target table. The matching is case-sensitive for 'auto' and isn't case-sensitive for 'auto ignorecase'.

Column names in Amazon Redshift tables are always lowercase, so when you use the 'auto' option, matching field names must also be lowercase. If the field names aren't all lowercase, you can use the 'auto ignorecase' option. With the default 'auto' argument, COPY recognizes only the first level of fields, or outer fields, in the structure.

To explicitly map column names to Avro field names, you can use a JSONPaths file.

By default, COPY attempts to match all columns in the target table to Avro field names. To load a subset of the columns, you can optionally specify a column list. If a column in the target table is omitted from the column list, COPY loads the target column's DEFAULT expression. If the target column doesn't have a default, COPY attempts to load NULL. If a column is included in the column list and COPY doesn't find a matching field in the Avro data, COPY attempts to load NULL to the column.

If COPY attempts to assign NULL to a column that is defined as NOT NULL, the COPY command fails.

Avro Schema

An Avro source data file includes a schema that defines the structure of the data. COPY reads the schema that is part of the Avro source data file to map data elements to target table columns. The following example shows an Avro schema.

{
    "name": "person",
    "type": "record",
    "fields": [
        {"name": "id", "type": "int"},
        {"name": "guid", "type": "string"},
        {"name": "name", "type": "string"},
        {"name": "address", "type": "string"}]
}

The Avro schema is defined using JSON format. The top-level JSON object contains three name-value pairs with the names, or keys, "name", "type", and "fields".

The "fields" key pairs with an array of objects that define the name and data type of each field in the data structure. By default, COPY automatically matches the field names to column names. Column names are always lowercase, so matching field names must also be lowercase, unless you specify the ‘auto ignorecase’ option. Any field names that don't match a column name are ignored. Order doesn't matter. In the previous example, COPY maps to the column names id, guid, name, and address.

With the default 'auto' argument, COPY matches only the first-level objects to columns. To map to deeper levels in the schema, or if field names and column names don't match, use a JSONPaths file to define the mapping. For more information, see JSONPaths file.

If the value associated with a key is a complex Avro data type such as byte, array, record, map, or link, COPY loads the value as a string. Here, the string is the JSON representation of the data. COPY loads Avro enum data types as strings, where the content is the name of the type. For an example, see COPY from JSON format.

The maximum size of the Avro file header, which includes the schema and file metadata, is 1 MB.  

The maximum size of a single Avro data block is 4 MB. This is distinct from the maximum row size. If the maximum size of a single Avro data block is exceeded, even if the resulting row size is less than the 4 MB row-size limit, the COPY command fails.

In calculating row size, Amazon Redshift internally counts pipe characters ( | ) twice. If your input data contains a very large number of pipe characters, it is possible for row size to exceed 4 MB even if the data block is less than 4 MB.

JSON [AS] 'json_option'

The source data is in JSON format.

JSON format is supported for COPY from these services and protocols:

• Amazon S3

• COPY from Amazon EMR

• COPY from SSH

JSON isn't supported for COPY from DynamoDB.

Valid values for json_option are as follows :

• 'auto'

• 'auto ignorecase'

• 's3://jsonpaths_file'

• 'noshred'

The default is 'auto'. Amazon Redshift doesn't shred the attributes of JSON structures into multiple columns while loading a JSON document.

By default, COPY attempts to match all columns in the target table to JSON field name keys. To load a subset of the columns, you can optionally specify a column list. If the JSON field name keys aren't all lowercase, you can use the 'auto ignorecase' option or a JSONPaths file to explicitly map column names to JSON field name keys.

If a column in the target table is omitted from the column list, then COPY loads the target column's DEFAULT expression. If the target column doesn't have a default, COPY attempts to load NULL. If a column is included in the column list and COPY doesn't find a matching field in the JSON data, COPY attempts to load NULL to the column.

If COPY attempts to assign NULL to a column that is defined as NOT NULL, the COPY command fails.

COPY maps the data elements in the JSON source data to the columns in the target table. It does so by matching object keys, or names, in the source name-value pairs to the names of columns in the target table.

Refer to the following details about each json_option value:

'auto'

With this option, matching is case-sensitive. Column names in Amazon Redshift tables are always lowercase, so when you use the 'auto' option, matching JSON field names must also be lowercase.

'auto ignorecase'

With this option, the matching isn't case-sensitive. Column names in Amazon Redshift tables are always lowercase, so when you use the 'auto ignorecase' option, the corresponding JSON field names can be lowercase, uppercase, or mixed case.

's3://jsonpaths_file'

With this option, COPY uses the named JSONPaths file to map the data elements in the JSON source data to the columns in the target table. The s3://jsonpaths_file argument must be an Amazon S3 object key that explicitly references a single file. An example is 's3://amzn-s3-demo-bucket/jsonpaths.txt'. The argument can't be a key prefix. For more information about using a JSONPaths file, see JSONPaths file.

In some cases, the file specified by jsonpaths_file has the same prefix as the path specified by copy_from_s3_objectpath for the data files. If so, COPY reads the JSONPaths file as a data file and returns errors. For example, suppose that your data files use the object path s3://amzn-s3-demo-bucket/my_data.json and your JSONPaths file is s3://amzn-s3-demo-bucket/my_data.jsonpaths. In this case, COPY attempts to load my_data.jsonpaths as a data file.

'noshred'

With this option, Amazon Redshift doesn't shred the attributes of JSON structures into multiple columns while loading a JSON document.

jsonpaths

The name of the JSON object that contains the AvroPath expressions.

[ … ]

Brackets enclose the JSON array that contains the path elements.

$

The dollar sign refers to the root element in the Avro schema, which is the "fields" array.

"$.id",

The target of the AvroPath expression. In this instance, the target is the element in the "fields" array with the name "id". The expressions are separated by commas.

"$.friends[0].id"

Brackets indicate an array index. JSONPath expressions use zero-based indexing, so this expression references the first element in the "friends" array with the name "id".

ORC

Loads the data from a file that uses Optimized Row Columnar (ORC) file format.

PARQUET

Loads the data from a file that uses Parquet file format.