Using COPY and COPY LOCAL

The COPY statement bulk loads data into a Vertica database. You can initiate loading one or more files or pipes on a cluster host. You can also load directly from a client system, using the COPY statement with its FROM LOCAL option.

COPY lets you load parsed or computed data. Parsed data is from a table or schema using one or more columns, and computed data is calculated with a column expression on one or more column values.

COPY invokes different parsers depending on the data format you specify:

  • Delimited text (the default parser format, but never specified)
  • Native binary (NATIVE) (not supported with COPY LOCAL)
  • Native varchar (NATIVE VARCHAR) (not supported with COPY LOCAL)
  • Fixed-width data (FIXEDWIDTH)
  • ORC (Optimized Row Columnar) and Parquet Hadoop files (not supported with COPY LOCAL)

To use one of the several flex table parsers, use the parser parameter, followed by the parser of choice.

COPY has many options, which you can combine to make importing data flexible. For detailed syntax of the various options see the SQL Reference Manual. The Administrator's Guide describes some common operations as follows:

For this option... See this section...

Read uncompressed data, or data in GZIP, BZIP, or LZO compressed forms.

Specifying COPY FROM Options

Insert data into the WOS (memory) or directly into the ROS (disk).

Choosing a Load Method

Set parameters such as data delimiters and quote characters for the entire load operation or, for specific columns.

Loading Delimited Data

Transform data before inserting it into the database.

Transforming Data During Loads

Copying Data from a Vertica Client

Use COPY LOCAL to load files on a client system to the Vertica database. For example, to copy a GZIP file from your local client, use a command such as this:

=> COPY store.store_dimension FROM LOCAL '/usr/files/my_data/input_file' GZIP;

You can use a comma-separated list to load multiple files of the same compression type. COPY LOCAL then concatenates the files into a single file, so you cannot combine files with different compression types in the list. When listing multiple files, be sure to specify the type of every input file, such as BZIP, as shown: 

=>COPY simple_table FROM LOCAL 'input_file.bz' BZIP, 'input_file.bz' BZIP;

You can load data from a local client from STDIN, as follows: 

=> COPY simple_table FROM LOCAL STDIN;

Loading Data from an IDOL CFS Client

The IDOL Connector Framework Server (CFS) VerticaIndexer feature lets CFS clients connect to your Vertica database using ODBC. After it is connected, CFS uses COPY...FROM LOCAL statements to load IDOL document metadata into an existing flex table. For more information, see the Using Flex Tables for IDOL Data section in Using Flex Tables.