Load data into partitioned tables

This document describes how to load data into partitioned tables.

Write data to a specific partition

You can load data to a specific partition by using thebq load command with apartition decorator. The following example appends data into the20160501 (May1, 2016) partition of an existing table, assuming the table is alreadypartitioned by date:

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

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

bqquery\--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 datainto the partition that corresponds to the time when the data was originallycreated.

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 aparticular time zone, you can use partition decorators to offset the UTCingestion time. For example, if you are on Pacific Standard Time (PST), you canload data that was generated on May 1, 2016 23:30 PST into the partition forthat date by using the corresponding explicit partition decorator,$2016050123. If you didn't use this explicit decorator, it would instead loadinto$2016050207 (May 2nd 07:00 UTC).

For time-unit column and integer-range partitioned tables, the partition IDspecified in the decorator must match the data being written. For example, ifthe table is partitioned on aDATE column, the decorator must match the valuein that column. Otherwise, an error occurs. However, if you know beforehand thatyour data is in a single partition, specifying the partition decorator canimprove write performance.

The preceding example appends data to a partition. To overwrite data in apartition instead, you must include different flags for each command, namelybq load --replace=true ... andbq query --append_table=false ....For more information about the flags in these commands, seebq loadandbq query.

For more information on loading data, seeIntroduction to loading data into BigQuery.

Stream data into partitioned tables

For information about streaming data into a partitioned table with theBigQuery Storage Write API, seeStream into partitioned tables.

What's next

To learn more about working with partitioned tables, see:

• Creating partitioned tables
• Managing partitioned tables
• Querying partitioned tables