PROFESSIONAL-DATA-ENGINEER · Question #365
You have created an external table for Apache Hive partitioned data that resides in a Cloud Storage bucket, which contains a large number of files. You notice that queries against this table are…
Creating individual external tables per Hive partition and using wildcard queries reduces metadata overhead, enabling BigQuery to prune partitions efficiently and improve query speed.
Question
Options
- AChange the storage class of the Hive partitioned data objects from Coldline to Standard.
- BCreate an individual external table for each Hive partition by using a common table name prefix. Use wildcard table queries to reference the partitioned data.
Why each option
Creating individual external tables per Hive partition and using wildcard queries reduces metadata overhead, enabling BigQuery to prune partitions efficiently and improve query speed.
Changing the storage class from Coldline to Standard reduces retrieval latency for infrequently accessed data, but for actively queried tables Standard class should already be used; this change alone does not address the structural performance issue caused by a large number of files.
Concept tested: BigQuery external table performance optimization for Hive partitioned data
Source: https://cloud.google.com/bigquery/docs/hive-partitioned-queries-gcs
Topics
Community Discussion
No community discussion yet for this question.