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 {
   [ LOCAL ] STDIN [ input‑format ] 
   | 'path‑to‑data' [ ON { nodename | (nodeset) | ANY NODE } ] [ input-format ] }[,...]
   | LOCAL 'path‑to‑data' [ input-format ] [,...]
   | VERTICA source‑database.[source‑schema.]source‑table[( source‑column[,...] ) ]
  } 
  | [ WITH ] UDL-clause[...]
}
   [ ABORT ON ERROR ]
   [ DELIMITER [ AS ] 'char' ]
   [ ENCLOSED [ BY ] 'char' 
   [ ENFORCELENGTH ]
   [ ERROR TOLERANCE ]
   [ ESCAPE [ AS ] 'char' | NO ESCAPE ]
   [ EXCEPTIONS 'path' [ ON nodename ] [,...]
   [ NULL [ AS ] 'string' ]
   [ RECORD TERMINATOR 'string' ]
   [ REJECTED DATA {'path' [ ON nodename ] [,...] | AS TABLE reject-table} ]
   [ REJECTMAX integer ]
   [ SKIP integer ]
   [ SKIP BYTES integer ]
   [ STREAM NAME  'streamName']
   [ TRAILING NULLCOLS ]
   [ TRIM 'byte' ]
   [ [  WITH ] PARSER parser ([ arg=value[,...] ]) ] ]
   [ NO COMMIT ]

Parameters

See COPY Parameters

Privileges

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

• INSERT privilege on table
• USAGE privilege on schema
• USER-accessible storage location
• Applicable READ or WRITE privileges granted to the storage location where files are read or written

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)