Vertica to Vertica Load

Vertica to Vertica Load

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/


Data can be loaded from Vertica to Vertica table following below 4 steps

• Step 1: Connect to Vertica cluster using VSQL from where data need to be exported

  
  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 
  

  
  
• Step 2: Connect to other Vertica cluster (where data need to be imported) in the same window using CONNECT Command

  
  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 ;
  

  
  
• Step 3: Load data from vertica to vertica using EXPORT command specifying vertica cluster name before table name.

  
  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 ;
  

  
  
• Step 4: Once the data in loaded in required vertica cluster, connection can be terminated from Vertica clusters using below command

  
  DISCONNECT other_vertica_cluster_name;
  \q
  

Other approach to load data
Below command works similar to EXPORT TO VERTICA but it can be used to copy data to current vertica cluster from other instead of export to other.

COPY vertica_cluster_name.schema_name.table_name FROM  VERTICA other_vertica_cluster_name.schema_name.table_name DIRECT;