Managing partitioned table data

This document describes how to manage partitioned table data in BigQuery.

Partition decorators

Partition decorators enable you to reference a partition in a table. For example, you can use them to write data to a specific partition.

A partition decorator has the form table_name$partition_id where the format of the partition_id segment depends on the type of partitioning:

Partitioning type Format Example
Hourly yyyymmddhh my_table$2021071205
Daily yyyymmdd my_table$20210712
Monthly yyyymm my_table$202107
Yearly yyyy my_table$20210
Integer range range_start my_table$40

Browse the data in a partition

To browse the data in a specified partition, use the bq head command with a partition decorator.

For example, the following command lists all fields in the first 10 rows of the my_dataset.my_table in the 2018-02-24 partition:

    bq head --max_rows=10 'my_dataset.my_tablee$20180224'

Write data to a specific partition

You can load data to a specific partition by using the bq load command with a partition decorator. The following example writes data into the 20160501 (May 1, 2016) partition of an existing table, assuming the table is already partitioned by date:

bq load --source_format=CSV 'my_dataset.my_table$20160501' data.csv

You can also write the results of a query to a specific partition:

bq query \
  --use_legacy_sql=false  \
  --destination_table='my_table$20160501' \
  --append_table=true \
  'SELECT * FROM my_dataset.another_table'

With ingestion-time partitioning, you can use this technique to load older data into the partition that corresponds to the time when the data was originally created.

You can also use this technique to adjust for time zones. By default, ingestion- time partitions are based on UTC time. If you want the partition time to match a particular time zone, you can use partition decorators to offset the UTC ingestion time. For example, if you are on Pacific Standard Time (PST), you can load data that was generated on May 1, 2016 PST into the partition for that date by using the corresponding partition decorator, $20160501.

For time-unit column and integer-range partitioned tables, the partition ID specified in the decorator must match the data being written. For example, if the table is partitioned on a DATE column, the decorator must match the value in that column. Otherwise, an error occurs. However, if you know beforehand that your data is in a single partition, specifying the partition decorator can improve write performance.

For more information on loading data, see Introduction to loading data into BigQuery.

Stream data into partitioned tables

For information about streaming data into a partitioned table by using the tabledata.insertAll method, see Streaming into partitioned tables.

Export table data

Exporting all data from a partitioned table is the same process as exporting data from a non-partitioned table. For more information, see Exporting table data.

To export data from an individual partition, append the partition decorator to the table name. For example, my_table$20160201.

You can also export data from the __NULL__ and __UNPARTITIONED__ partitions by appending the partition names to the table name. For example, my_table$__NULL__ or my_table$__UNPARTITIONED__.

Partitioned table security

Access control for partitioned tables is the same as access control for standard tables. For more information, see Introduction to table access controls.

Next steps

To learn more about working with partitioned tables, see: