COPY Parameters
Parameters and their descriptions are divided into the following sections:
For details on which COPY
parameters are valid for specific parsers, see COPY Option Parser Dependencies.
Target Options
The following parameters apply to the target tables and their columns:
[database.]schema |
Specifies a schema, by default myschema.thisDbObject If you specify a database, it must be the current database.
|
target‑table |
The target columnar or flexible table for loading new data. Vertica loads the data into all projections that include columns from the schema table. |
column‑as‑expression |
Specifies the expression used to compute values for the target column. For example: COPY t(year AS TO_CHAR(k, 'YYYY')) FROM 'myfile.dat' Use this option to transform data when it is loaded into the target database. For details about:
|
column |
Restricts the load to one or more specified columns in the table. If you omit specifying columns, Table columns that you omit from the column list are assigned their DEFAULT or SET USING values, if any; otherwise, If you leave the The data file must contain the same number of columns as the => COPY T1 (C1, C6, C9); |
COLUMN OPTION |
Specifies load metadata for one or more columns declared in the table column list. For example, you can specify that a column has its own |
Column Options
Depending on how they are specified, the following COPY options can qualify specific columns or all columns.
DELIMITER |
Indicates the single ASCII character used to separate columns within each record of a file.You can use any ASCII value in the range Default: Vertical bar ( |
ENCLOSED [BY] |
Sets the quote character within which to enclose data, allowing delimiter characters to be embedded in string values. You can choose any ASCII value in the range |
ENFORCELENGTH |
If included, specifies to rejects data rows of type Default: |
ESCAPE [AS] |
Sets the escape character. Once set, the character following the escape character is interpreted literally, rather than as a special character. You can define an escape character using any ASCII value in the range The
|
FILLER datatype |
Indicates that COPY should not load the identified column into the destination table. Use this option to omit columns that you do not want to transfer into a table. The name of the column being filtered must not match the name of a column in the table definition. The following is an error: => CREATE TABLE names (firstname VARCHAR, lastname VARCHAR); --- error: => COPY names (firstname FILLER VARCHAR) FROM ... This parameter also transforms data from a source column and loads the transformed data to the destination table, rather than loading the original, untransformed source column (parsed column). If datatype is For more information, see Manipulating Source Data Columns in the Administrator's Guide |
FORMAT 'format' |
Specifies the input formats to use when loading date/time and binary columns, where format can be one of the following: These are the valid input formats when loading binary columns:
See Loading Binary (Native) Data to learn more about these formats. When loading date/time columns, using FORMAT significantly improves load performance. See the following topics for additional information: If you specify invalid format strings, the |
NULL [AS] |
The string representing a null value. The default is an empty string ( |
TRIM |
Trims the number of bytes you specify from a column. This option is only available when loading fixed-width data. You can set Restrictions: Invalid for ORC or Parquet data |
Input Options
The following options are available for specifying source data:
LOCAL |
Specifies to load a data file on a client system, rather than on a cluster host. Restrictions: Invalid for CREATE EXTERNAL TABLE AS COPY |
STDIN |
Reads from the client a standard input instead of a file. User must have INSERT privileges on the table and USAGE privileges on its schema. Restrictions: Invalid for CREATE EXTERNAL TABLE AS COPY |
path‑to‑data
|
Specifies the absolute path of the file (or files) containing the data, which can be from multiple input sources.
path‑to‑data can optionally contain wildcards to match more than one file. The file or files must be accessible to the local client or the host on which the You can use variables to construct the pathname as described in Using Load Scripts. If path‑to‑data resolves to a storage location on a local file system, and the user invoking
Further, if a user has privileges but is not a superuser, and invokes |
ON nodename |
Specifies the node on which the data to copy resides and the node that should parse the load file. If you omit nodename, the location of the input file defaults to the initiator node. Use nodename to copy and parse a load file from a node other than the nodename is invalid with |
ON (nodeset) |
Specifies a set of nodes on which to perform the load. The same data must be available for load on all named nodes. nodeset is a comma-separated list of node names in parentheses. For example: => COPY t FROM 'file1.txt' ON (v_vmart_node0001, v_vmart_node0002); Vertica apportions the load among all of the specified nodes. If you also specify If the data is available on all nodes, you usually use |
ON ANY NODE |
Specifies that the source file to load is available on all nodes, so The file must be the same on all nodes. If the file differs on two nodes, an incorrect or incomplete result is returned, with no error or warning. Vertica attempts to apportion the load among several nodes if the file is large enough to benefit from apportioning. It chooses a single node if You can use a wildcard or glob (such as
|
input‑format |
Specifies the input format, one of the following:
Input files can be of any format. If you use wildcards, all qualifying input files must be in the same format. To load different file formats, specify the format types specifically. The following requirements and restrictions apply:
|
VERTICA | See COPY FROM VERTICA. |
[WITH] UDL‑clause[…] |
Specifies one or more user-defined load functions—one source, and optionally one or more filters and one parser, as follows: SOURCE source( [arg=value[,…] ] ) [ FILTER filter( [arg=value[,…] ] ) ]… [ PARSER parser( [arg=value[,…] ] ) ] To use a flex table parser for column tables, use the |
Handling Options
The following parameters provide various options for controlling how COPY handles different contingencies:
ABORT ON ERROR |
Specifies that |
COLSIZES (integer[,…]) |
Specifies column widths when loading fixed-width data. |
ERROR TOLERANCE |
Specifies that Using this parameter disables apportioned load. Restrictions: Invalid for ORC or Parquet data |
EXCEPTIONS |
Specifies the file name or absolute path of the file in which to write exceptions, as follows: EXCEPTIONS 'path' [ ON nodename[,…]] Exceptions describe why each rejected row was rejected. Each exception describes the corresponding record in the file specified by the Files are written on the node or nodes executing the load. If the file already exists, it is overwritten. To collect all exceptions in one place, use the
The If you use this parameter with EXCEPTIONS '/home/ex01.txt' on v_db_node0001,'/home/ex02.txt' on v_db_node0002,'/home/ex03.txt' on v_db_node0003 If path resolves to a storage location, the following privileges apply to non-superusers:
|
NO ESCAPE |
Eliminates escape character handling. Use this option if you do not need any escape character and you want to prevent characters in your data from being interpreted as escape sequences. |
RECORD TERMINATOR |
Specifies the literal character string indicating the end of a data file record. For more information about using this parameter, see Loading Delimited Data. Restrictions: Invalid for ORC or Parquet data |
REJECTED DATA |
Specifies where to write each row that failed to load. If this parameter is specified, records that failed due to parsing errors are always written. Records that failed due to an error during a transformation are written only if configuration parameter CopyFaultTolerantExpressions is set. The syntax for this parameter is: REJECTED DATA { 'path' [ ON nodename ] [,…] | AS TABLE reject-table } Vertica can write rejected data to the specified path or to a table:
For more information about both options, see Capturing Load Rejections and Exceptions. |
REJECTMAX integer |
The maximum number of logical records that can be rejected before a load fails. For details, see Capturing Load Rejections and Exceptions.
|
SKIP integer |
The number of records to skip in a load file. For example, you can use the Restrictions: Invalid for ORC or Parquet data |
SKIP BYTES integer |
The total number of bytes in a record to skip, valid only for loading fixed-width data. Restrictions: Invalid for ORC or Parquet data |
[STORAGE] load‑method |
For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect. Specifies how to load data into the database, one of the following:
For details, see Choosing a Load Method in the Administrator's Guide. Restrictions: Invalid for CREATE EXTERNAL TABLE AS COPY |
STREAM NAME |
Supplies a A valid stream name can contain any combination of alphanumeric or special characters up to 128 bytes in length. By default, Vertica names streams by table and file name. For example, if you are loading two files ( For example: => COPY mytable FROM myfile DELIMITER '|' DIRECT STREAM NAME 'My stream name'; |
TRAILING NULLCOLS |
Specifies that if Vertica encounters a record with insufficient data to match the columns in the table column list, Restrictions: Invalid for ORC or Parquet data |
WITH parser |
Specifies the parser to use when bulk loading columnar tables, one of the following: By default, To use a flex table parser for column tables, use the You do not specify the DELIMITER parser directly; absence of a specific parser indicates the default. For more information, see Specifying a COPY Parser in the Administrator's Guide. The following restrictions apply:
|
NO COMMIT
|
Prevents the For more information, see Overriding COPY Auto Commit. Restrictions: Invalid for ORC or Parquet data , ignored by CREATE EXTERNAL TABLE AS COPY |