Streaming data to a specific BigQuery Time Partition

9
votes

I would like to know if there is any way to stream data to a specific time partition of a BigQuery table. The documentation says that you must use table decorators:

Loading data using partition decorators

Partition decorators enable you to load data into a specific partition. To adjust for timezones, use a partition decorator to load data into a partition based on your preferred timezone. For example, if you are on Pacific Standard Time (PST), load all data generated on May 1, 2016 PST into the partition for that date by using the corresponding partition decorator:

[TABLE_NAME]$20160501

Source: https://cloud.google.com/bigquery/docs/partitioned-tables#dealing_with_timezone_issues

And:

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

Source: https://cloud.google.com/bigquery/docs/creating-partitioned-tables#creating_a_partitioned_table

But if I try to use them when streaming data i got the following error: Table decorators cannot be used with streaming insert.

Thanks in advance!

2
google-bigquery

2 Answers

5
votes

Sorry for the inconvenience. We are considering providing support for this in the near future. Please stay tuned for more updates.

Possible workarounds that might work in many cases:

  1. If you have most of the data available(which is sometimes the case when restating data for an old partition), you can use a load job with the partition as the destination.

  2. Another option is to stream to a temporary table and after the data has been flushed from the streaming buffer, use bq cp

1
votes

This feature was recently released and you can now stream directly into a decorated date partition within the last 30 days historically and 5 days into the future.

https://cloud.google.com/bigquery/streaming-data-into-bigquery