I have a customer attempting to create a new projection for a very large (I think >100 TB) fact table to replace the existing one. They realize this will take a long time and are wondering whether the following approach will work:
Create new table and create new projection on the table.
Copy data over while still loading and querying old table.
During a maintenance window, rename old table, then rename new table to old table and resume load.
Catch up by copying remaining epochs / batches from old to new.
Their question was how to estimate run time (presumably they could copy one partition at a time and estimate timing) and would like to know also whether table rename is atomic - that is, will it take effect instantaneously so they can stop and start loading for just a minute or so during rename?
Create new projection for very large table
+2
+1Certainly just making a new projection is the least complicated option, but at least they recognize that could take some time. 100 TB is not an insignificant amount of data, however, and I would be concerned about it running into a problem somewhere, and forcing an internal rollback, which might waste several days.
copy_table and copy_partitions_to_table both require the same projection sort order. So those might not be valid options, either.
They can do an atomic table rename to swap table names:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/Tables/ModifyTableDefinition/RenamingTables.htm?zoom_highlight=atomic table rename
+2Not even sure copy functions will work because I think they are changing partitions to hierarchical, which I think means changing to a datetime type, so the partitions will probably change. Unless someone has a better suggestion or reason it might not work, I was goign to go with copy one partition at a time with a direct INSERT-SELECT one old partition key at a time to active partitions, then swap tables/projections by name, then copy over the remaining active partition data from the old table.
+1I think that's a reasonable approach. If you're changing the sort order, and changing the partitions, you can't get around fully resorted all that data. All you can really achieve is blocking it into chunks so you can recover it easier if it fails for some reason.
If the segmentation isn't changing, you could do each node independently as well, which might make it less work overall.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
