Vertica Analytics Platform Version 9.2.x Documentation
Copying Data from Another Vertica Database
You can import a table or specific columns in a table from another Vertica database. The table receiving the copied data must already exist, and have columns that match (or can be coerced into) the data types of the columns you are copying from the other database. 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.
Importing is a three-step process:
CONNECTconnects to the database that contains the data to import.
The import 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.
COPY FROM VERTICAimports table data to the target database. To import data from multiple tables, issue multiple
COPY FROM VERTICAstatements using the same connection to the source database.
DISCONNECTdisconnects from the source database when the copy operation is complete.
Importing Identity Columns
You can import 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.
After importing the columns, the identity column values do not increment automatically. Use
ALTER SEQUENCE to make updates.
The default behavior for this statement is to import Identity (and Auto-increment) columns by specifying them directly in the source table. To disable this behavior globally, set the
CopyFromVerticaWithIdentity configuration parameter, described in Configuration Parameters.
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 '' ON 'VertTest01',5433; CONNECT => COPY customer_dimension FROM VERTICA vmart.customer_dimension DIRECT; Rows Loaded ------------- 500000 (1 row) => DISCONNECT vmart; DISCONNECT
This example demonstrates copying several columns from a table in the source database into a table in the local database:
=> CONNECT TO VERTICA vmart USER dbadmin PASSWORD '' ON 'VertTest01',5433; CONNECT => COPY people (name, gender, age) FROM VERTICA vmart.customer_dimension (customer_name, customer_gender, customer_age); Rows Loaded ------------- 500000 (1 row) => DISCONNECT vmart; DISCONNECT
Was this topic helpful?