Exporting Data to Another Vertica Database

EXPORT TO VERTICA exports table data from one Vertica database to another. The following requirements apply:

  • You already opened a connection to the target database with CONNECT TO VERTICA.
  • The source database is no more than one major release behind the target database.
  • The table in the target database must exist.
  • Source and target table columns must have the same or compatible data types.

Each EXPORT TO VERTICA statement exports data from only one table at a time. You can use the same database connection for multiple export operations.

Export Process

Exporting is a three-step process:

  1. Connect to the target database with CONNECT TO VERTICA.

    For example:

    => CONNECT TO VERTICA testdb USER dbadmin PASSWORD '' ON 'VertTest01', 5433;
CONNECT

  2. Export the desired data with EXPORT TO VERTICA. For example, the following statement exports all table data in customer_dimension to a table of the same name in target database testdb:

    => EXPORT TO VERTICA testdb.customer_dimension FROM customer_dimension;
Rows Exported 
---------------
         23416
(1 row)
  3. DISCONNECT disconnects from the target database when all export and import operations are complete: 
    => DISCONNECT testdb;
DISCONNECT

    Closing your session also closes the database connection. However, it is a good practice to explicitly close the connection to the other database, both to free up resources and to prevent issues with other SQL scripts that might be running in your session. Always closing the connection prevents potential errors if you run a script in the same session that attempts to open a connection to the same database, since each session can only have one connection to a given database at a time.

Mapping Between Source and Target Columns

If you export all table data from one database to another as in the previous example, EXPORT TO VERTICA can omit specifying column lists. This is possible only if column definitions in both tables comply with the following conditions:

  • Same number of columns
  • Identical column names
  • Same sequence of columns
  • Matching or compatible column data types

If any of these conditions is not true, the EXPORT TO VERTICA statement must include column lists that explicitly map source and target columns to each other, as follows:

  • Contain the same number of columns.
  • List source and target columns in the same order.
  • Pair columns with the same (or compatible) data types.

For example:

=> EXPORT TO VERTICA testdb.people (name, gender, age) 
   FROM customer_dimension (customer_name, customer_gender, customer_age);

Exporting Subsets of Table Data

In general, you can export a subset of table data in two ways:

  • Export data of specific source table columns.
  • Export the result set of a query (including historical queries) on the source table.

In both cases, the EXPORT TO VERTICA statement typically must specify column lists for the source and target tables.

The following example exports data from three columns in the source table to three columns in the target table. Accordingly, the EXPORT TO VERTICA statement specifies a column list for each table. The order of columns in each list determines how Vertica maps target columns to source columns. In this case, target columns name, gender, and age map to source columns customer_name, customer_gender, and customer_age, respectively:

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

The next example queries source table customer_dimension, and exports the result set to table ma_customers in target database testdb:

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

In this example, the source and target column names are identical, so specifying a columns list for target table ma_customers is optional. If one or more of the queried source columns did not have a match in the target table, the statement would be required to include a columns list for the target table.

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.