Synchronize table across different clusters.

vishnu777

I have a table (t1) on cluster A and this needs to available as t1 on cluster B.
I am exploring multiple options to perform this activity.
Option#1 : Use "COPY FROM VERTICA"
Option#2: Load data parallel to t1 on cluster A and t1 on cluster B.
note: cluster A and cluster b has different vertica versions and different no of nodes.

do you see other options ?

Jim_Knicely

Hi,

Option #2 might be easier, unless you have to make a lot of modifications to your ETL process or applications.

Do you plan on copying the entire table every time, or just the new records? If it's the new records, I wonder if the "EXPORT TO VERTICA" command is better as you can specify a query. That is, on cluster A you could have a little script run periodically to update the remote table. Something like this:

CONNECT TO VERTICA clusterB_db USER dbadmin PASSWORD '' ON 'clusterB', 5433;

EXPORT TO VERTICA clusterB.t1 AS SELECT * FROM t1 WHERE create_date > (select last_exported date from my_export_config);

DISCONNECT lusterB_db;

UPDATE my_export_config SET last_exported date = sysdate;

COMMIT;

That's assuming you have a "create_date" column on the t1 table. You could also use a sequence, increment or the row epoch as indicators of which rows were already exported.

See:
https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/EXPORTTOVERTICA.htm

You can also script a copy using vsql... Say you want to truncate the table t1 on cluster B then copy all the records from t1 on cluster A to B:

On cluster B you could run in a shell script periodically:

vsql -h clusterB -c "truncate table t1;"
vsql -h clusterA -Atc "select * from t1;" | vsql -h clusterB -Atc "copy t1 from stdin;"

vishnu777

@Jim_Knicely awesome . thank you so much !