Why are streamed data left unpartitioned when inserting into a time partitioned BigQuery table?

Viewed 445

When inserting streamed data into a time partitioned table in BigQuery (e.g. by day), not all expected partitions are shown when querying them (although all data are actually available).

For instance, although there are data available for the dates 2021-09-13 to 2021-09-15, it is not possible to see the corresponding partitions when querying them using legacy SQL.

bq query --use_legacy_sql '
    SELECT partition_id
    FROM [database.product$__PARTITIONS_SUMMARY__]
    ORDER BY partition_id DESC '

Waiting on bqjob_id_1 ... (0s) Current status: DONE
+-------------------+
|   partition_id    |
+-------------------+
| __UNPARTITIONED__ |
| 20210912          |
| 20210911          |
| 20210910          |
+-------------------+

A closer look using the information contained within the table metadata INFORMATION_SCHEMA.PARTITIONS reveals a partition called __STREAMING_UNPARTITIONED__ containing all rows which have not been correctly partitioned.

bq query --nouse_legacy_sql '
    SELECT table_name, partition_id, total_rows
    FROM database.INFORMATION_SCHEMA.PARTITIONS
    WHERE table_name="product"
    ORDER BY partition_id DESC '

Waiting on bqjob_id_2 ... (0s) Current status: DONE
+------------+-----------------------------+------------+
| table_name |        partition_id         | total_rows |
+------------+-----------------------------+------------+
| product    | __UNPARTITIONED__           |          0 |
| product    | __STREAMING_UNPARTITIONED__ |       9519 |
| product    | 20210912                    |       3014 |
| product    | 20210911                    |       3152 |
| product    | 20210910                    |       3369 |
+------------+-----------------------------+------------+ 

So the question is, why are all these data left unpartitioned? Note that this behavior has business impact, since querying repetitively unpartitioned data might incur high expenses.

1 Answers

According to the official Google Cloud Support, "the behavior reported is expected behavior as streaming data is only re-partitioned when there is enough unpartitioned data. BigQuery's current internal limit for repartitioning hot data is 5GiB (although this could change during the current month)".

Additional information can be found in the corresponding Google Cloud documentation site.

Related