Specifying a COPY Parser
By default, COPY
uses the DELIMITER
parser to load raw data into the database. Raw input data must be in UTF-8, delimited text format. Data is compressed and encoded for efficient storage.
COPY
cannot explicitly specify the DELIMITER
parser.
If the raw data to load does not consist primarily of delimited text, specify the parser that is most appropriate, one of the following:
NATIVE
(binary)NATIVE VARCHAR
FIXEDWIDTH
ORC
andPARQUET
(see Reading ORC and Parquet Formats)
Using a different parser for your data can improve load performance. If delimited input data includes binary data types, COPY
translates the data on input. See Using Load Scripts and Loading Binary (Native) Data for examples. You can also load binary data, but only if it adheres to the COPY
format requirements, described in Appendix: Creating Native Binary Format Files.
The same bulk load COPY
statement cannot mix raw data types that require different parsers, such as NATIVE
and FIXEDWIDTH
. For information about verifying input data formats, see Checking Data Format Before or After Loading.
Flex Table Parsers
You can use flex parsers to load data into standard, columnar tables. Loading data with the flex parsers makes loading data flexible. For example, you can load JSON data into a columnar table in one load with the FJSONPARSER
, and delimited data into the same table in another with the default COPY
parser.
Use the flex parser that best matches your needs, as described in the Using Flex Table Parsers:
Specifying Load Metadata
In addition to choosing a parser option, COPY
supports other options to determine how to handle raw data. These options are considered load metadata, and you can specify metadata options in different parts of the COPY
statement as follows:
Qualifies: | |||
---|---|---|---|
Metadata option | Column expression | COLUMN OPTION | FROM options |
DELIMITER
|
Y | Y | Y |
ENCLOSED BY
|
Y | Y | Y |
ESCAPE AS
|
Y | Y | Y |
NULL
|
Y | Y | Y |
TRIM
|
Y | Y | |
RECORD TERMINATOR
|
Y | ||
SKIP
|
Y | ||
SKIP BYTES
|
Y (fixed-width only) |
||
TRAILING NULLCOLS
|
Y |
The following precedence rules apply to all data loads:
- All column-level parameters override statement-level parameters.
COPY
uses the statement-level parameter if you do not specify a column-level parameter.COPY
uses the default metadata values for theDELIMITER
,ENCLOSED BY
,ESCAPE AS
, andNULL
options if you do not specify them at either the statement or column level.
When you specify any metadata options, COPY
uses the parser to produce the best results and stores the raw data and its corresponding metadata in the following formats:
Raw data format | Metadata format | Parser |
---|---|---|
UTF-8 |
UTF-8 |
|
Binary |
Binary |
|
UTF-8 |
Binary |
|
UTF-8 |
UTF-8 |
|