Destination Improvement: HVR: Configure Primary Key Columns for BigQuery Target Location
Not planned
While Primary Keys are not enforced in BigQuery, they do offer performance improvements: https://cloud.google.com/blog/products/data-analytics/join-optimizations-with-bigquery-primary-and-foreign-keys
HVR does not set Primary Keys on BigQuery tables when a BigQuery location is the Target. This should be supported.
-
Official comment
Hi Prakhar,
We investigated using the PKs in the BigQuery destination and concluded we won't implement it, as the benefits seem limited to specific queries when the source is HANA.
There are also two primary concerns
- If we introduce duplicates into the target, the primary key constraint is violated, which invalidates using primary/foreign keys in queries.
- We cannot guarantee that the target table will contain a value for every primary key value in the burst table, which invalidates the use of the primary/foreign key in queries.
Best regards,
Edwin
-
Oh this is such a good idea,
would love to see this implemented!! -
We are currently going through exactly same pain!
Looking forward to see this feature added in HVR. -
I don't know any of you guys, but I agree that this is only makes sense.
-
Hi Prakhar,
Thank you for raising this request and for sharing the Google article highlighting the performance benefits of primary keys in BigQuery.
We understand that while primary keys are not enforced in BigQuery, they can still improve performance, especially when data is joined with other tables. While it's currently possible to define primary keys manually in BigQuery, we recognize that this can add significant operational overhead.
At the moment, HVR does not set primary keys on BigQuery targets, but we are actively reviewing the feasibility and impact of supporting this functionality in a future release.
Best regards,
Edwin -
Hi Edwin
I would like to understand better why you feel the benefits are limited to only the HANA source?
Additionally, on the concerns:
- BQ doesn't enforce PK constraint. So, is the concern still valid?
- I guess it's fine if the burst tables don't have the PKs but are you saying the same about the final table? What about Append?
I'd appreciate it if you could share details to answer these.
Please sign in to leave a comment.
Comments
6 comments