COPY FROM VERTICA

Imports data from another Vertica database. COPY FROM VERTICA is similar to COPY, but accepts only a subset of its parameters.

The source database can be one major release behind the target database.

Syntax

COPY [[database.]schema.]target‑table 
... [( target‑column[,…])]
... FROM VERTICA database.[schema.]source‑table
... [(source‑column[,…])]
... [load-method]
... [STREAM NAME 'stream name']
... [NO COMMIT]

Parameters

[database.]schema

The table in the local database to store the copied data.

target‑column

A target table column to store the copied data. If you specify target columns, COPY FROM VERTICA writes only to those columns. If you omit specifying target columns, Vertica writes to target table columns as described below, in Source and Target Column Mapping.

You cannot use column fillers as part of the column definition.

database The source database of the data to copy. A connection to this database must already exist in the current session.
[schema.]source‑table

The table that is the source of the copied data.

source‑column

A source table column to copy. If you specify source columns, only these columns are copied from the source table. If you omit specifying source columns, Vertica copies columns from the source table, as described below in Source and Target Column Mapping.

load-method

Specifies how to load data into the database, one of the following:

  • AUTO (default): Initially loads data into WOS, suitable for smaller bulk loads.
  • DIRECT: Loads data directly into ROS containers, suitable for large (>100 MB) bulk loads.
  • TRICKLE: Loads data only into WOS, suitable for frequent incremental loads.

This option is invalid for external tables.

For details, see Choosing a Load Method in the Administrator's Guide.

STREAM NAME

Specifies a COPY load stream identifier. Using a stream name helps to quickly identify a particular load. The STREAM NAME value that you specify in the load statement appears in the stream column of the LOAD_STREAMS system table.

NO COMMIT

Prevents COPY from committing its transaction automatically when it finishes copying data. For details, see Overriding COPY Auto Commit in the Administrator's Guide.

Privileges

  • SELECT privileges on the source table
  • USAGE privilege on source table schema
  • INSERT privileges for the destination table in target database
  • USAGE privilege on destination table schema

Connecting to the Source Database

Before you can import data from another database, you must establish a connection to the source database with CONNECT. See Copying Data from Another Vertica Database for details.

By default, COPY FROM VERTICA copies or imports data over the Vertica private network. Connecting to a public network requires some configuration. For information about using this statement to copy data across a public network, see Using Public and Private IP Networks.

If configured with a certificate, Vertica encrypts data during transmission using TLS and attempts to encrypt plan metadata. You can set the ImportExportTLSMode configuration parameter to require encryption for plan metadata. See Security Parameters.

The copy operation fails if either side of the connection is a single-node cluster installed to localhost, or you do not specify a host name or IP address.

Source and Target Column Mapping

You can optionally name a subset of source and target columns to participate in the copy operation. COPY FROM VERTICA attempts to match columns in the source table with corresponding columns in the destination table.

The following table compares the different combinations of naming source and target columns, and the requirements that pertain to each option.

Omit source columns Specify source columns
Omit target columns

Match all columns in source table to columns in target table.

The number of columns in the two tables can differ, but the target table must have at least as many columns as the source table.

Match named source table columns to target table columns.

The number of columns in the two tables can differ, but the target table must have at least as many columns as the number of specified source columns.

Specify target columns

Match source columns to the named target columns.

The number of named target columns must equal the number of columns in the source table.

Match named source columns to named target columns.

The number of source and target columns must be equal.

Node Failure During COPY

See Handling Node Failure During Copy/Export in the Administrator's Guide.

Examples

This example demonstrates connecting to another database, copying the contents of an entire table from the source database to an identically-defined table in the current database directly into ROS, and then closing the connection:

=> CONNECT TO VERTICA vmart USER dbadmin PASSWORD 'myPassword' ON 'VertTest01',5433;
CONNECT
=> COPY customer_dimension FROM  VERTICA vmart.customer_dimension DIRECT;
 Rows Loaded 
-------------
      500000
(1 row)
=> DISCONNECT vmart;
DISCONNECT

For more examples, see Copying Data from Another Vertica Database in the  Administrator's Guide.