BigQuery supports querying externally partitioned data in Avro, Parquet, ORC, JSON and CSV formats that is stored on Cloud Storage using a default hive partitioning layout. Hive partitioning support is enabled by setting the appropriate options in the table definition file. Table creation and modification is currently supported via the CLI and REST API.
For instructions on querying managed partitioned tables, see working with partitioned tables.
- Hive partitioning support is built assuming a common source URI prefix for
all URIs that ends immediately before partition encoding, which looks like
- The directory structure of a hive partitioned table is assumed to have the same partitioning keys appear in the same order, with a maximum of ten partition keys per table.
- The data must follow a default hive partitioning layout.
- The hive partitioning keys and the columns in the underlying files cannot overlap.
- Table creation and modification is currently limited to the CLI and REST API.
- All limitations for querying external data sources stored on Cloud Storage apply.
- Support is for Standard SQL only.
Supported data layouts
The data must follow a default hive partitioned layout.
For instance, the following files follow the default layout, with the
key/value pairs laid out as directories with an
= sign as a separator,
and the partition keys are always in the same order.
Unsupported data layouts
If the partition key names are not encoded in the directory path, partition schema detection will fail. For example, consider the following path, which does not encode the partition key names:
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:
Partition schema detection modes
Hive partition keys appear as normal columns when quering data from Cloud Storage. We support three modes of hive partition schema detection:
- AUTO: key names and types are auto detected. The following types can be detected: STRING, INTEGER, DATE and TIMESTAMP.
- STRINGS: key names are automatically inferred with STRING type.
- CUSTOM: partition key schema is encoded in the source URI prefix.
Providing a custom partition key schema
CUSTOM detection requires encoding the schema in the source URI prefix field. Using CUSTOM allows for user-specified types for each partition key. The values must validly parse as the specified type or the query will fail.
For example, setting source_uri_prefix to:
val as a STRING while treating
dt as a DATE, and
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, which allows it to avoid reading unnecessary files. This improves performance and can reduce bytes billed.
Setting HivePartitioningOptions using the CLI
The HivePartitioningOptions are set on the ExternalDataConfiguration object while creating the table definition file.
Requesting string-typed partition key detection
bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \ --hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \ gcs_uri >/tmp/table_def_file
Providing a custom partition key schema encoded via the source_uri_prefix field
bq mkdef --source_format=NEWLINE_DELIMITED_JSON --hive_partitioning_mode=CUSTOM \ --hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix/custom_schema_encoding \ gcs_uris file_schema >/tmp/table_def_file
The partition key schema is encoded immediately following the source URI
prefix. Use the following format to specify
Setting HivePartitioningOptions using the REST API
Note that when
hivePartitioningOptions.mode is set to CUSTOM, you must
encode the partition key schema within the sourceUriPrefix by setting