How to Move/Copy Partition across cluster?

opper20

Hi,
Vertica has the great utility on Move/copy partition and same as copy table but they work in the same cluster.Is there any way i can move/Copy the partitions across cluster with the same DDL of table.

thanks

LenoyJ

If the target cluster is identically configured (i.e. same number of nodes), you could try and see if the following works:

• In the source cluster, use COPY_PARTITIONS_TO_TABLE() to copy the paritions to a new (temporary) table.
• Use VBR Replicate to replicate the object (in this case, the new table with copied partitions) across to an identically configured alternate cluster.
• In the target cluster, use MOVE_PARTITIONS_TO_TABLE() to move copied partitions from the temporary table to the target table.

opper20

Hi Lenoy,Thanks for replying.I want to use the COPY_PARTITIONS_TO_TABLE() and MOVE_PARTITIONS_TO_TABLE() and now i don't want to use the vbr.but the problem is i have to copy the partitions across cluster.let's assume if i have a development cluster and i have created an empty table with same structure in test cluster.now i want to copy partitions from dev cluster to test cluster. The answer you gave will work if the source and target in same cluster.

LenoyJ

VBR Replicate is intended exactly for the example you've mentioned. If your dev and test clusters have the same number of nodes, you can follow my previous post step by step. Any reason why you think it won't work? Quoting from our docs here:

One reason you might use object replication is to copy tables and schemas between test, staging, and production clusters.

To reiterate - In your dev cluster, copy partitions you want to a table. Then perform vbr replicate of this table to the test cluster. Then in your test cluster, move the partitions in the replicated table to your target table. Done! Script it out if needed. The reason why we're doing this is because vbr can replicate tables and schemas but not partitions (AFAIK). :-)
 
Now, one caveat with vbr replicate is that you need your dev cluster to have the same number of nodes as your test cluster. If this is not the case, you can use COPY FROM VERTICA or EXPORT TO VERTICA to copy data between dissimilar clusters. This is slower than using VBR Replicate (if you're moving really large tables, that is). See the following docs & blog post on how:

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/CopyExportData/CopyingAndExportingData.htm
https://www.vertica.com/kb/Copying-Data-Between-Dissimilar-Vertica-Clusters/Content/BestPractices/Copying-Data-Between-Dissimilar-Vertica-Clusters.htm

opper20

Hi Lenoy,This is the reason i don't want vbr as the no of nodes in both cluster is not same.And also if we go with Export utility it will take a lot of time, so thought if we could do with moving partitions.Anyway i got answer that it can not be done

mosheg

Did you try to see how long it takes to copy one dose of records at a time?
For example:
CONNECT TO VERTICA my_db USER dbadmin PASSWORD 'mypass' ON 'myhost', 5433;
COPY my_small_dimension FROM VERTICA mydb.my_small_dimension DIRECT;
DISCONNECT my_db;