What is the benefit of having WOS? Why do not we always directly load into ROS? I understand that WOS is for trickle feed and ROS is for bulk load. Why can not Vertica have just ROS and save time on any spills?
Hi Mrao, I'm not sure what you mean "spilling"? WOS is much faster for trickle loading. Many users who bulk-load data don't use the WOS, so it doesn't generally hurt them. You seem to have lots of interesting technical questions about Vertica. Building anything interesting? Feel free to come chat with us at our User Conference, if you're attending. Adam
Just trying to learn Vertica and attempt vertica certification. I may soon need to implement a trickle feed application, so trying to get ready for it.
Do you have any best practices for trickle feed implementations? The source data may be generated every 10 min. I am looking at different design options. Is there a Queue table in Vertica? In Teradata, the queue table will automatically be deleted once you consume the data. I am thinking of having a stage table(queue table if available), which will be loaded with source data at every 10 min interval. Then will load the target table with the stage table.
I would like to join them. Pls send me the details.
Hi Rao, Ah, sounds like a good reason for asking questions. I'm not familiar with Teradata's Queue tables personally, but I would recommend LOCAL TEMP tables for that use case. WOS is useful as a buffer for ROS. ROS containers are most effective when they are large (many hundreds of MB to GB at least). If your loads are small, keep them in WOS so we can move them to ROS in batches; if they are large, you can go directly to ROS. Adam
I suggest you are on right path when considering trickle loads every 10 mins.
As you said, you can just use the stage tables for every new load and then depending on the join condition you can use the merge statement to update your master table if any old records are updated in the stage table or insert new records which are present in stage table but not in target table.
But, before this, you need to take care of transfer/Merge time between two tables which should not take more then 10 mins, and i fit exceeds 10 mins. you need to manage your new load and let it wait for the previous process to complete.
Make sure after every merge process you truncate the stage table.
Comments
Thanks for the useful info.
Just trying to learn Vertica and attempt vertica certification. I may soon need to implement a trickle feed application, so trying to get ready for it.
Do you have any best practices for trickle feed implementations? The source data may be generated every 10 min. I am looking at different design options. Is there a Queue table in Vertica? In Teradata, the queue table will automatically be deleted once you consume the data. I am thinking of having a stage table(queue table if available), which will be loaded with source data at every 10 min interval. Then will load the target table with the stage table.
I would like to join them. Pls send me the details.
Thanks
Rao
I suggest you are on right path when considering trickle loads every 10 mins.
As you said, you can just use the stage tables for every new load and then depending on the join condition you can use the merge statement to update your master table if any old records are updated in the stage table or insert new records which are present in stage table but not in target table.
But, before this, you need to take care of transfer/Merge time between two tables which should not take more then 10 mins, and i fit exceeds 10 mins. you need to manage your new load and let it wait for the previous process to complete.
Make sure after every merge process you truncate the stage table.
Hope this helps.