Stay organized with collections Save and categorize content based on your preferences.
Use externally partitioned dataYou can use BigQuery external tables to query partitioned data in the following data stores:
The external partitioned data must use a default Hive partitioning layout and be in one of the following formats:
To query externally partitioned data, you must create a BigLake table or an external table. We recommend using BigLake tables because they let you enforce fine-grained security at the table level. For information about BigLake and external tables, see Introduction to BigLake tables and Introduction to external tables.
You enable Hive partitioning support by setting the appropriate options in the table definition file. For instructions about querying managed partitioned tables, see Introduction to partitioned tables.
Partition schemaThe following sections explain the default Hive partitioned layout and the schema detection modes that BigQuery supports.
To avoid reading unnecessary files and to improve performance, you can use predicate filters on partition keys in queries.
Supported data layoutsHive partition keys appear as normal columns when you query data from Cloud Storage. The data must follow a default Hive partitioned layout. For example, the following files follow the default layout—the key-value pairs are configured as directories with an equal sign (=) as a separator, and the partition keys are always in the same order:
gs://my_bucket/my_table/dt=2019-10-31/lang=en/my_filename
gs://my_bucket/my_table/dt=2018-10-31/lang=fr/my_filename
The common source URI prefix in this example is gs://my_bucket/my_table.
If the partition key names are not encoded in the directory path, partition schema detection fails. For example, consider the following path, which does not encode the partition key names:
gs://my_bucket/my_table/2019-10-31/en/my_filename
Files where the schema is not in a consistent order also fail detection. For example, consider the following two files with inverted partition key encodings:
gs://my_bucket/my_table/dt=2019-10-31/lang=en/my_filename
gs://my_bucket/my_table/lang=fr/dt=2018-10-31/my_filename
BigQuery supports three modes of Hive partition schema detection:
AUTO: Key names and types are automatically detected. The following types can be detected:
STRINGS: Key names are automatically converted to STRING type.
CUSTOM: Partition key schema is encoded as specified in the source URI prefix.
To use a CUSTOM schema, you must specify the schema in the source URI prefix field. Using a CUSTOM schema lets you specify the type for each partition key. The values must validly parse as the specified type or the query fails.
For example, if you set the source_uri_prefix flag to gs://my_bucket/my_table/{dt:DATE}/{val:STRING}, BigQuery treats val as a STRING, dt as a DATE, and uses gs://my_bucket/my_table as the source URI prefix for the matched files.
BigQuery prunes partitions when possible using query predicates on the partition keys. This lets BigQuery avoid reading unnecessary files, which helps improve performance.
Predicate filters on partition keys in queriesWhen you create an externally partitioned table, you can require the use of predicate filters on partition keys by enabling the requirePartitionFilter option under HivePartitioningOptions.
When this option is enabled, attempts to query the externally partitioned table without specifying a WHERE clause produce the following error: Cannot query over table <table_name> without a filter over column(s) <partition key names> that can be used for partition elimination.
Note: There must be at least one predicate that only references one or more partition keys for the filter to be considered eligible for partition elimination. For example, for a table with partition key val and column f in the file, both of the following WHERE clauses satisfy the requirement:
WHERE val = "key"
WHERE val = "key" AND f = "column"
However, WHERE (val = "key" OR f = "column") is not sufficient.
gs://BUCKET/PATH_TO_TABLE/.Support is for GoogleSQL only.
All limitations for querying external data sources stored on Cloud Storage apply.
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-08-07 UTC.
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["BigQuery enables querying partitioned data from Cloud Storage, Amazon S3, and Azure Blob Storage using external tables or BigLake tables, with the latter recommended for enhanced security."],["Partitioned data must adhere to the default Hive partitioning layout, where key-value pairs are encoded as directories with an equal sign separator, and support file formats such as Avro, CSV, JSON, ORC, and Parquet."],["BigQuery offers three modes for Hive partition schema detection: `AUTO` for automatic key name and type detection, `STRINGS` for string conversion, and `CUSTOM` for user-specified schemas."],["Predicate filters on partition keys can be used to enhance query performance by allowing BigQuery to prune partitions and avoid reading unnecessary files."],["Enabling the `requirePartitionFilter` option ensures that queries on externally partitioned tables include a filter on partition keys, although certain `WHERE` clause requirements need to be respected for partition elimination to occur."]]],[]]
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4