COPY LOCAL

Using the COPY statement with its LOCAL option lets you load a data file 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.

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 using COPY LOCAL with supported drivers, see the Connecting to Vertica section for your platform.

Note: On Windows clients, the path you supply for the COPY LOCAL file is limited to 216 characters due to limitations in the Windows API.

Invoking COPY LOCAL does not automatically create exceptions and rejections files, even if exceptions occur. You cannot save exceptions and rejections to a table with the rejected data as table parameter. For information about saving such files, see Capturing Load Rejections and Exceptions in the Administrator's Guide.

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.

Note: The Linux glob command returns files that match the pattern you enter, as specified in the Linux Manual Page for Glob (7). For ADO.net platforms, specify patterns and wildcards as described in the .NET Directory.getFiles Method.

For more information about using the COPY LOCAL option to load data, see COPY for syntactical descriptions, and Using COPY and COPY LOCAL 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"];