Keeping Track of Data Exports and Imports

Jim_Knicely · June 2019

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.

The V_MONITOR.IMPORT_EXPORT_REQUESTS system table keeps a history of your export and imports!

Example:

dbadmin->vmart@sandbox1=> CONNECT TO VERTICA test_db USER dbadmin PASSWORD 'password' ON 'XXX.XX.XX.XXX', 5433;
CONNECT

dbadmin->vmart@sandbox1=> COPY biggie FROM VERTICA test_db.biggie;
 Rows Loaded
-------------
    36000000
(1 row)

dbadmin->vmart@sandbox1=>* EXPORT TO VERTICA test_db.biggie AS SELECT * FROM biggie LIMIT 10;
 Rows Exported
---------------
            10
(1 row)

dbadmin->vmart@sandbox1=>* \x
Expanded display is on.

dbadmin->vmart@sandbox1=>* SELECT * FROM v_monitor.import_export_requests;
-[ RECORD 1 ]--------------+---------------------------------
start_time                 | 2019-06-19 14:39:55.780413-04
node_name                  | v_vmart_node0001
session_id                 | v_vmart_node0001-506647:0x147fd1
user_id                    | 45035996273704962
user_name                  | dbadmin
transaction_id             | 45035996280952887
statement_id               | 1
request_id                 | 7
request_type               | Local IMPORT
metadata_connection_secure | f
data_connection_secure     | f
is_executing               | f
success                    | t
-[ RECORD 2 ]--------------+---------------------------------
start_time                 | 2019-06-19 14:42:29.858981-04
node_name                  | v_vmart_node0001
session_id                 | v_vmart_node0001-506647:0x147fd1
user_id                    | 45035996273704962
user_name                  | dbadmin
transaction_id             | 45035996280952892
statement_id               | 3
request_id                 | 9
request_type               | Local EXPORT
metadata_connection_secure | f
data_connection_secure     | f
is_executing               | f
success                    | t

dbadmin=> \x
Expanded display is off.

dbadmin->vmart@sandbox1=>* DISCONNECT test_db;
DISCONNECT

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/CopyExportData/CopyingData.htm

Have fun!