Vertica Analytics Platform Version 9.2.x Documentation

EXPORT TO VERTICA

Exports an entire table, columns from a table, or query results to another Vertica database. Exported data is always written in AUTO mode.

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

Syntax

EXPORT TO VERTICA database.[schema.]target‑table 
... [ ( target‑column[,…] ) ] 
... { AS SELECT query‑expression | FROM [schema.]source‑table[ ( source‑column[,…] ) ] }; 

Parameters

database

A string containing the name of the database to receive the exported data. There must be an active connection to this database for the export to succeed.

[schema.]target‑table

The table to store the exported data (schema specification is optional). This table must already exist.

target‑column

A list of columns in the target table to store the exported data.

query‑expression

Specifies the data to be exported. See SELECT for the syntax.

[schema.]source‑table

The table that contains the data to export.

source‑column

A list of the columns in the source table to export. If present, only these columns are exported.

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 Target Database

Before you can export data to another database, you must establish a connection to the target database with CONNECT. See Exporting Data to Another Vertica Database for details.

By default, EXPORT TO VERTICA exports data to another database over the Vertica private network. Connecting to a public network requires some configuration. For details about exporting data across a public network, see Using Public and Private IP Networks.

The export 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 export operation. EXPORT TO VERTICA tries to match columns in the source table with corresponding columns in the destination table. If you do not supply a list of source and destination columns, EXPORT TO VERTICA tries to match columns in the source table with corresponding columns in the destination table. Auto-projections for the target table are similar to the projections for the source 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 EXPORT

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

Examples

See Exporting Data to Another Vertica Database in the  Administrator's Guide.