Vertica Analytics Platform Version 9.2.x Documentation

COPY

List of semicolon-separated search terms this topic is recommened for:

COPY; Load data;

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

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

For COPY LOCAL:

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

For COPY FROM STDIN:

  • INSERT privilege on table
  • USAGE privilege on schema

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:

COPY Topics