Vertica provides a way to load data(without creating a file) directly and quickly from table in 1 Vertica cluster to table in other Vertica cluster. This can be accomplished using Vertica's 'CONNECT TO VERTICA'.
VSQL client is needed to load data from one Vertica to other Vertica cluster. This can be downloaded from vertica site at https://www.vertica.com/download/vertica/client-drivers/
Syntax:
Path_to_vsql_bin/vsql -h cluster_host_name -U username_for_vertica_user -w password_for_vertica_user -d vertica_cluster_name
Example:
/Users/user/Desktop/opt/vertica/bin/vsql -h verticaprod.dbmstutorials.com -U tutorial_user -w tutorial_user -d verticaProd
Syntax:
CONNECT TO VERTICA other_vertica_cluster_name USER username_in_other_vertica_cluster PASSWORD 'password_for_other_vertica_cluster_user' ON 'Other_cluster_host_name' , PORT_NUMBER;
Example:
CONNECT TO VERTICA verticaTest USER tutorial_user PASSWORD 'tutorial_user' ON 'verticatest.dbmstutorials.com' , 5433 ;
Syntax: EXPORT TO VERTICA other_vertica_cluster_name.schema_name.table_name AS SELECT * FROM vertica_cluster_name.schema_name.table_name DIRECT; COMMIT;
Example:
EXPORT TO VERTICA verticaTest.tutorial_app.vertica_import_table AS SELECT * FROM verticaProd.tutorial_app.vertica_export_table DIRECT ; commit ;
DISCONNECT other_vertica_cluster_name;
\q
COPY vertica_cluster_name.schema_name.table_name FROM VERTICA other_vertica_cluster_name.schema_name.table_name DIRECT;