COPY Parameters
For details on which COPY
parameters are valid for specific parsers, see COPY Option Parser Dependencies.
[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); |
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 |
path‑to‑data
|
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, path‑to‑data is a URL in the 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 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 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
|
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. This parameter is not valid when creating external tables. |
LOCAL {STDIN | 'path‑to‑data'}
|
Specifies that all paths for the For details, see COPY LOCAL. This parameter is not valid when creating external tables. |
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:
|
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:
|
|
[WITH] 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, This option is not available for ORC or Parquet data. |
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 Delimited Data. This option is not available for ORC or Parquet data. |
SKIP records
|
Indicates the number ( This option is not available for ORC or Parquet data. |
SKIP BYTES total
|
Indicates the total number ( This option is not available for ORC or Parquet data. |
TRIM
|
Trims the number of bytes you specify from a column. This option is only available when loading fixed-width data. You can set This option is not available for ORC or Parquet data. |
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 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 the CopyFaultTolerantExpressions configuration parameter is set. (See General Parameters.) 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:
Do not use path with For more information 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[,…]] 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:
|
ENFORCELENGTH
|
Determines whether You can set Default: |
ERROR TOLERANCE
|
Specifies that This option is not available for ORC or Parquet data. 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: The
This option is invalid for external tables. For details, see Choosing a Load Method in the Administrator's Guide. |
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 ( 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:
For more information, see Overriding COPY Auto Commit. This option is not available for ORC or Parquet data. |