This document describes how to manage partitioned table data in BigQuery.
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
partition_id segment depends on the type of partitioning:
Browse the data in a partition
To browse the data in a specified partition, use the
bq head command with a
For example, the following command lists all fields in the first 10 rows of
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
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,
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
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,
You can also export data from the
partitions by appending the partition names to the table name. For example,
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.
To learn more about working with partitioned tables, see: