COPY Parameters
Note: For details on which COPY
parameters are valid for specific parsers, see COPY Option Parser Dependencies.
schema
|
Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example: myschema.thisDbObject
|
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 do not specify any columns, Table columns that you do not specify in the column list are assigned their default values. If a column had no defined default value, If you leave the The data file must contain the same number of columns as the => COPY T1 (C1, C6, C9); |
FILLER datatype
|
Specifies not to load the column and its fields into the destination table. Use this option to omit columns that you do not want to transfer into a table. 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). Note: 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 |
pathToData
|
Specifies the absolute path of the file (or files) containing the data, which can be from multiple input sources. If the file is stored in HDFS, pathToData is a URL in the pathToData 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 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 Note: 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 of the nodes, so Caution: 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
|
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. |
LOCAL {STDIN | 'pathToData'}
|
Specifies that all paths for the For details, see COPY LOCAL. |
input‑file‑format
|
Specifies the input file 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:
|
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 Note: 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:
|
|
[WITH] UDL‑clause[...]
|
Alone, improve readability of the statement. Using Followed by a UDL-clause, specifies one or more user-defined load functions—one source, one or more filters, and one parser, as follows:
To use a flex table parser for column tables, use the |
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 |
COLSIZES (integer[,...])
|
Specifies column widths when loading fixed-width data. |
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 ( |
TRAILING NULLCOLS
|
Specifies that if Vertica encounters a record with insufficient data to match the columns in the table column list, |
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
|
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. |
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 |
NULL
|
The string representing a null value. The default is an empty string ( |
RECORD TERMINATOR
|
Specifies the literal character string indicating the end of a data file record. For more information about using this parameter, see Loading UTF-8 Format Data. |
SKIP records
|
Indicates the number ( |
SKIP BYTES total
|
Indicates the total number ( |
TRIM
|
Trims the number of bytes you specify from a column. This option is only available when loading fixed-width data. You can set |
REJECTMAX
|
Specifies a maximum number of logical records that can be rejected before a load fails. For details, see Capturing Load Rejections and Exceptions.
|
REJECTED DATA
|
Specifies to write each row that failed to load due to a parsing error to the specified target, as follows: REJECTED DATA { 'path' [ ON nodename ] [, ...] | AS TABLE reject-table } Vertica can write rejected data to the specified path or to a table:
When this parameter is used with For details about both options, see Capturing Load Rejections and Exceptions in the Administrator's Guide. |
EXCEPTIONS
|
Specifies the file name or absolute path of the file in which to write exceptions, as follows: EXCEPTIONS 'path' [ ON nodename ] [,...] Files are written on the node or nodes executing the load Exceptions describe why each rejected row was rejected. Each exception describes the corresponding record in the file specified by the If path resolves to a storage location, the following privileges apply to non-superusers:
The When this parameter is used with Specifying an exceptions file name is incompatible with the |
ENFORCELENGTH
|
Determines whether You can set Default: |
ERROR TOLERANCE
|
Specifies that This parameter is disabled for ORC and Parquet files; specifying it has no effect. Using this parameter disables apportioned load. |
ABORT ON ERROR
|
Specifies that |
[STORAGE] load‑method
|
Specifies how to load data into the database, one of the following:
This option is invalid for external tables. For details, see Choosing a Load Method in the Administrator's Guide. |
STREAM NAME
|
[Optional] 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 ( To name a stream: => COPY mytable FROM myfile DELIMITER '|' DIRECT STREAM NAME 'My stream name';
|
NO COMMIT
|
Prevents the The following requirements and restrictions apply:
|
Note: The RETURNREJECTED
parameter is supported only for internal use by the JDBC and ODBC drivers. Be aware that the Vertica internal-use options can change without notice.