Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Keeping Track of Data Exports and Imports

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->[email protected]=> CONNECT TO VERTICA test_db USER dbadmin PASSWORD 'password' ON 'XXX.XX.XX.XXX', 5433;
CONNECT

dbadmin->[email protected]=> COPY biggie FROM VERTICA test_db.biggie;
 Rows Loaded
-------------
    36000000
(1 row)

dbadmin->[email protected]=>* EXPORT TO VERTICA test_db.biggie AS SELECT * FROM biggie LIMIT 10;
 Rows Exported
---------------
            10
(1 row)

dbadmin->[email protected]=>* \x
Expanded display is on.

dbadmin->[email protected]=>* 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->[email protected]=>* DISCONNECT test_db;
DISCONNECT

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

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.