Modifying Partitioned Tables

Copying to partitioned tables

To copy a partitioned table into another partitioned table, the partition specifications for the source and destination tables must match.

If you copy a non-partitioned table into a partitioned table, BigQuery copies the source data into the partition that represents the current date.

If you copy multiple source tables into a partitioned table in the same job, the source tables can't contain a mixture of partitioned and non-partitioned tables. If all of the source tables are partitioned tables, the partition specifications for all source tables must match the destination table's partition specification.

Copying tables into partitions

Use partition decorators to copy tables. For example, the following command copies an unpartitioned table, tableA, into the partition of table1 for the date February 1, 2016 (20160201):

bq cp --append_table mydataset.tableA 'mydataset.table1$20160101'

Restating data in a partition

To update data in a specific partition, append a partition decorator to the name of the partitioned table when loading data into the table. A partition decorator represents a specific date and takes the form:

$YYYYMMDD

For example, the following command replaces the data in the entire partition for the date January 1, 2016 (20160101) in a partitioned table named mydataset.table1 with content loaded from a Cloud Storage bucket:

bq load  --replace --source_format=NEWLINE_DELIMITED_JSON 'mydataset.table1$20160101' gs://[MY_BUCKET]/replacement_json.json

Updating partitions using query results

You can use the output of a query to restate data for a partition.

For example, the following command restates the data for the March 1, 2016 (20160301) partition of table1 by using the output of the query:

bq query --allow_large_results --replace --noflatten_results --destination_table 'mydataset.table1$20160301' 'SELECT field1 + 10, field2 FROM mydataset.table1$20160301'

If you do not specify a suffix, the current partition is used.

The partition decorator separator ($) is a special variable in the unix shell. You might have to escape the decorator when you use the command-line tool. For example, the following commands escape the partition decorator:

bq query 'SELECT * from mydataset.table$20160519'
bq query "SELECT * from mydataset.table\$20160519"

Streaming data into partitions

To stream data to a specific partition, use the partition decorator when specifying the tableId of the table to which you are streaming. For example, the following command streams a single row to a partition for the date January 1, 2017(20170101) in a partitioned table named mydataset.table1:

echo '{"a":1, "b":2}' | bq insert 'mydataset.table1$20170101'

When streaming using a partition decorator, you can stream to partitions within the last 30 days in the past and 5 days in the future relative to the current date, based on current UTC time. To write to partitions for dates outside these allowed bounds, you can use load or query jobs.

For more information on streaming data into partitions, see streaming into partitioned tables.

Deleting partitions of a partitioned table

You can use the partition decorator to delete a specific partition in a partitioned table. For example, the partition for March 1, 2016 in a partitioned table named mydataset.table can be deleted using:

bq rm 'mydataset.table$20160301'

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...