How to optimize slow external table queries with many files?
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 slow. You want to improve the performance of these queries. What should you do?
Community Votes
100% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
This question tests the ability to optimize external table performance using BigLake features, specifically metadata caching, rather than falling for the trap of changing storage classes or bucket locations.
Upgrading to a BigLake table and enabling metadata caching significantly improves query performance for external tables containing many files. The community consensus identifies this as the most effective solution for optimizing Hive partitioned data in Cloud Storage.
A common mistake is selecting Option A or D, assuming that storage class or bucket region determines query speed, whereas the bottleneck is actually the metadata processing overhead for numerous files.
Community Discussion (6 comments)
- BigLake Table: BigLake allows for more efficient querying of data lakes stored in Cloud Storage. It can handle large datasets more effectively than standard external tables. - Metadata Caching: Enabling metadata caching can significantly improve query performance by reducing the time taken to read and process metadata from a large number of files.
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Upgrading to a BigLake table allows for better integration and performance features compared to standard external tables. Enabling metadata caching stores the table's metadata in BigQuery, eliminating the need to repeatedly list and read the manifest of thousands of files in Cloud Storage for every query, which drastically speeds up execution.Why the Other Options Are Wrong
Changing storage class (Option A) primarily affects data retrieval costs and availability, not the query execution speed related to metadata parsing. Creating individual tables (Option B) introduces administrative complexity and does not inherently speed up metadata scanning. Migrating to a multi-region bucket (Option D) might slightly reduce network latency but fails to address the significant overhead of processing metadata for a large number of files.Community Comment Notes
Commenters explicitly highlight that BigLake handles large datasets more effectively than standard external tables. They emphasize that metadata caching is the specific mechanism required to reduce the time taken to read and process metadata from a large number of files, confirming Option C as the correct choice.Official Reference
Exam Strategy
When encountering questions about slow external table queries with many files, prioritize options involving BigLake tables and metadata caching. Avoid distractors related to storage classes or bucket regions, as these do not solve metadata processing bottlenecks.