Other: HVR BigQuery ClusteredKeys
AnsweredWe are working on a HVR 5.7.0 with agent plugin to 6.1.0 native connector upgrade. Using the agent plugin worked well as one of my former colleagues wrote a custom method within the python connector script to automatically define clustered columns in BQ.
During this upgrade, I managed to write some other code that gets those clustered columns out of BQ and into the correctly formatted JSON that HVR requires for table properties. Clustered columns is a HUGE savings for us in terms of cost and losing these would have been detrimental to our success moving to 6.1 (and likely a show-stopper)
I'd love to see 2 pieces of functionally brought forward:
- The ability to pull from bigquery the already defined clustered columns and update the hvr table properties accordingly. We can redefine tables within HVR from BQ as a target, this seems like a pretty easy addition to the tool.
- The ability to auto-define clustered columns based on a set of pre-defined logic. If we add a new table today, or a new channel with 1000 tables, each of those would need to have a table property defined with the associated BQClusterKeys. This adds a ton of work in the channel setup phase and off-loading that to be done programmatically within the application is where it should live.
For now, we are going to manage both a 5.7.0 and 6.1.0 environment so we can leverage the auto-genreated clustered columns within the plugin for any new channels or tables we want to replicate, then get that data out of BQ and format the JSON correctly so it can be imported.
-
Official comment
Cody,
For 1 we added BqClusterKeys to the TableProperties action some time ago (https://fivetran.com/docs/hvr6/action-reference/tableproperties#bqclusterkeys).
2 we don't plan to build because we feel there are too many scenarios.
Note we also have PartitionByDate for BigQuery (https://fivetran.com/docs/hvr6/action-reference/tableproperties#partitionbydate).
Mark.
Please sign in to leave a comment.
Comments
1 comment