COPY

COPY bulk-loads data into a Vertica database. By default, COPY automatically commits itself and any current transaction except when loading temporary tables. If COPY is terminated or interrupted Vertica rolls it back.

COPY reads data as UTF-8 encoding.

For information on loading one or more files or pipes on a cluster host or on a client system, see COPY LOCAL.

Syntax

COPY [[database.]schema-name.]target-table 
... [ ( { column-as-expression | column }
...... [ DELIMITER [ AS ] 'char' ] 
...... [ ENCLOSED [ BY ] 'char' ]
...... [ ENFORCELENGTH ]
...... [ ESCAPE [ AS ] 'char' | NO ESCAPE ]
...... [ FILLER datatype]
...... [ FORMAT 'format' ] 
...... [ NULL [ AS ] 'string' ]
...... [ TRIM 'byte' ]
... [,…] ) ]
... [ COLUMN OPTION ( column 
...... [ DELIMITER [ AS ] 'char' ]
...... [ ENCLOSED [ BY ] 'char' ]
...... [ ENFORCELENGTH ]
...... [ ESCAPE [ AS ] 'char' | NO ESCAPE ]
...... [ FORMAT 'format' ]
...... [ NULL [ AS ] 'string' ]
...... [ TRIM 'byte' ]
... [,…] ) ]
[ FROM { 
...STDIN [ input‑format ] 
...| { 'path‑to‑data' [ ON { nodename | (nodeset) | ANY NODE } ] [ input-format ] } [,…]
...| LOCAL {STDIN | 'path‑to‑data'} [ input-format ] [,…]
...| VERTICA source‑database.[source‑schema.]source‑table[(source‑column [,…]) ]
} ]

...[ [ WITH ] PARSER parser ([ arg=value[,…] ]) ] ]
...[ [ WITH ] UDL-clause[...] ]
...[ DELIMITER [ AS ] 'char' ]
...[ TRAILING NULLCOLS ]
...[ NULL [ AS ] 'string' ]
...[ ESCAPE [ AS ] 'char' | NO ESCAPE ]
...[ ENCLOSED [ BY ] 'char' ]
...[ RECORD TERMINATOR 'string' ]
...[ SKIP records ]
...[ SKIP BYTES integer ]
...[ TRIM 'byte' ]
...[ REJECTMAX integer ]
...[ REJECTED DATA {'path' [ ON nodename ] [,…] | AS TABLE reject-table} ]
...[ EXCEPTIONS 'path' [ ON nodename ] [,…] ]
...[ ENFORCELENGTH ]
...[ ERROR TOLERANCE ]
...[ ABORT ON ERROR ]
...[ [ STORAGE ] load-method ]
...[ STREAM NAME  'streamName']
...[ NO COMMIT ]

Parameters

See COPY Parameters

Privileges

General

Superusers have full COPY privileges. The following requirements apply to non-superusers:

• USER-accessible storage location
• Applicable READ or WRITE privileges granted to the storage location where files are read or written

COPY LOCAL

• INSERT privileges to copy data from the STDIN pipe
• USAGE privileges on the schema

COPY FROM STDIN

• INSERT privilege on table
• USAGE privilege on schema

File Paths for Rejected Data and Exceptions

COPY can specify a path to store rejected data and exceptions. If the path resolves to a storage location, the following privileges apply to non-superusers:

• The storage location was created with the USER option (see CREATE LOCATION).
• The user must has READ access to the storage location, as described in GRANT (Storage Location)

COPY Topics

COPY Option Parser Dependencies

COPY Restrictions

COPY Examples

See Also