what is the best way to copy data from one vertica cluster and another one which is having different
Hi, I am newbie to Vertica. what is the best way to copy data from one vertica cluster and another one which is having different number of nodes. I am on Vertica 7. Thanks. Sarayu
0
Comments
If you wanted to copy a particular table/column you can use export to vertica which exports an entire table, columns from a table, or the results of a select statement to another HP Vertica database. Exported data is written to the target database using AUTO mode. Exporting data to another database requires first establishing a connection to the target database using the connect statement or copy from Vertica function which imports a table or specific columns in a table from another HP Vertica database. The table receiving the copied data must already exist, and have columns that match (or can be coerced into) the data types of the columns you are copying from the other database. You can import data from an earlier HP Vertica release, as long as the earlier release is a version of the last major release. For instance, for Version 6.x, you can import data from any version of 5.x, but not from 4.x.
Kindly find the below documented link for detailed information on these functionality:
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/EXPORTTOVER...
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/COPYFROMVER...
Also in case you want to copy the entire database it is not possible to achieve the same having different no. of nodes in the target cluster.For that you must have same no. of nodes & can use copy cluster function to achieve the same.
Kindly use below Vertica document to check the same:
https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/BackupRestore/Copying...
Regards
Rahul
Thanks Rahul for quick response. I think this is a severe limitation from the product as we cannot expect source and target clusters to have same configuration all the time. I am wondering how other customers are managing now to overcome this situation when they want to do complete database copy
Technically its problematic !
Vertica break the data into segmentations base on the segmentation keys you define on the projection creations , each cluster node include partial set of the data , full backup take full set of the entire database data files that exists on each one of the nodes (incremental take only partial set) . To do that the , the backup need to know what is your target cluster architecture to be able to generate different data files set that fit your target cluster needs , I am not saying that it’s not doable , however I did not see such ability on other database . Looks like your only options is set of EXPORT FROM VERTICA command per each table .
Thanks Eli Revach for detailed explanation. Being a newbie, it helps me to understand. I dont know the internals on how it works. As end user, I have a very normal and most helpful usecase. Two systems may not be always be of same number of nodes. And hence I expected HP Vertica to support that operation. It might be expensive, time consuming, etc. But that feature should exist in my opinion.
Sorry if I am too basic and if my requirement is not feasible at all.
Thx again