Exporting Data to Another Vertica Database

You can export a table, specific columns in a table, or the results of a SELECT statement to another Vertica database. The table in the target database receiving the exported data must already exist and have columns that match (or can be coerced to) the data types of the columns you are exporting.

You can import data from an earlier Vertica release, if the earlier release is a version of the last major release before the target database release.

Exported data is always written in AUTO mode. For details, see Choosing a Load Method.

Export Process

Exporting is a three-step process:

1. CONNECT connects to the target database.

   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.

2. EXPORT TO VERTICA exports the data. You can export only one table at a time. Use multiple EXPORT statements to export multiple tables or the results of multiple SELECT statements. All statements use the same connection to the target database.
3. DISCONNECT disconnects from the target database when the export operation is complete.

Exporting Identity Columns

You can export tables (or columns) that contain identity and auto-increment values, but the sequence values are not incremented automatically at the target table. You must use ALTER SEQUENCE to make updates.

Export identity (and auto-increment) columns as follows:

• If both source and destination tables have an identity column and configuration parameter CopyFromVerticaWithIdentity is set to true (1), you do not need to list them.

• If source table has an identity column, but target table does not, you must explicitly list the source and target columns.

  Failure to list which identity columns to export can cause an error, because the identity column will be interpreted as missing in the destination table.

By default, EXPORT TO VERTICA exports all identity columns . To disable this behavior globally, set the CopyFromVerticaWithIdentity configuration parameter.

Exporting GEOMETRY and GEOGRAPHY Data Types

You can export columns containing Vertica geospatial data types. Vertica supports the following export methods for these data types:

Examples of Exporting Data

The following example demonstrates using the three-step process listed above to export data.

First, open the connection to the other database, then perform a simple export of an entire table to an identical table in the target database.

=> CONNECT TO VERTICA testdb USER dbadmin PASSWORD '' ON 'VertTest01',5433;
CONNECT
=> EXPORT TO VERTICA testdb.customer_dimension FROM customer_dimension;
Rows Exported 
---------------
         23416
(1 row)

The following statement demonstrates exporting a portion of a table using a simple SELECT statement.

=> EXPORT TO VERTICA testdb.ma_customers AS SELECT customer_key, customer_name, annual_income
FROM customer_dimension WHERE customer_state = 'MA';
Rows Exported 
---------------
          3429
(1 row)

This statement exports several columns from one table to several different columns in the target database table using column lists. Remember that when supplying both a source and destination column list, the number of columns must match.

=> EXPORT TO VERTICA testdb.people (name, gender, age) FROM customer_dimension 
(customer_name, customer_gender, customer_age);
Rows Exported 
---------------
         23416
(1 row)

You can also use EXPORT TO VERTICA with a SELECT AT EPOCH LATEST expression to include data from the latest committed DML transaction.

Disconnect from the database when the export is complete:

=> DISCONNECT testdb;
DISCONNECT