COPY LOCAL

Using the COPY statement with its LOCAL option lets you load data files (up to 65,535 files) on a client system, rather than on a cluster host. COPY LOCAL supports the STDIN and 'pathToData' parameters, but not the [ON nodename] clause. COPY LOCAL does not support multiple file batches in NATIVE or NATIVE VARCHAR formats. COPY LOCAL does not support reading ORC or Parquet files; use ON NODE instead. COPY LOCAL does not support CURRENT_LOAD_SOURCE().

The COPY LOCAL option is platform-independent. The statement works in the same way across all supported Vertica platforms and drivers. For more details about supported drivers, see Client drivers.

COPY LOCAL must be the first statement in any multi-statement query you make with the ODBC client library. Using it as the second or later statement results in an error. When using other client libraries, such as JDBC, COPY LOCAL should always be the first statement in a multi-statement query. Also, do not use it multiple times in the same query.

COPY LOCAL does not automatically create exceptions and rejections files, even if exceptions occur.

Privileges

User must have INSERT privilege on the table and USAGE privilege on the schema.

How COPY LOCAL works

COPY LOCAL loads data in a platform-neutral way. The COPY LOCAL statement loads all files from a local client system to the Vertica host, where the server processes the files. You can copy files in various formats: uncompressed, compressed, fixed-width format, in bzip or gzip format, or specified as a bash glob. Files of a single format (such as all bzip, or gzip) can be comma-separated in the list of input files. You can also use any of the applicable COPY statement options (as long as the data format supports the option). For instance, you can define a specific delimiter character, or how to handle NULLs, and so forth.

For more information about using the COPY LOCAL option to load data, see COPY for syntactical descriptions, and Specifying where to load data from for detailed examples.

The Vertica host uncompresses and processes the files as necessary, regardless of file format or the client platform from which you load the files. Once the server has the copied files, Vertica maintains performance by distributing file parsing tasks, such as encoding, compressing, uncompressing, across nodes.

Viewing copy local operations in a query plan

When you use the COPY LOCAL option, the GraphViz query plan includes a label for Load-Client-File, rather than Load-File. Following is a section from a sample query plan:

-----------------------------------------------
  PLAN:  BASE BULKLOAD PLAN  (GraphViz Format)
-----------------------------------------------
 digraph G {
 graph [rankdir=BT, label = " BASE BULKLOAD PLAN \nAll Nodes Vector:
 \n\n  node[0]=initiator (initiator) Up\n", labelloc=t, labeljust=l ordering=out]
.
.
.
10[label = "Load-Client-File(/tmp/diff) \nOutBlk=[UncTuple]",
color = "green", shape = "ellipse"];

Examples

The following example shows a load from a local file.

$ cat > t.dat
12
17
9
^C

=> CREATE TABLE numbers (value INT);
CREATE TABLE

=> COPY numbers FROM LOCAL 't.dat';
 Rows Loaded
-------------
           3
(1 row)

=> SELECT * FROM numbers;
 value
-------
    12
    17
     9
(3 rows)