Synchronize table across different clusters.

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 ?

Comments

  • Jim_KnicelyJim_Knicely Administrator
    edited October 2017

    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;"
    
  • @Jim_Knicely awesome . thank you so much !

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file