4

I have a daily partitioned table, and I want to delete older partitions by API.

The documentation only says that older partitions which are not updated for 3 months are stored with 50% discount. Thanks Google, but I really do not intend to keep those data for half a century.

I thought the whole point of partitioned tables was to allow deleting old data, but all I find is a discount. Is there a way of doing this?

J Doe
  • 41
  • 1
  • 2
  • I am waiting for the feature as well. DML statements that modify partitioned tables are not yet supported. https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language – Pentium10 Dec 11 '16 at 15:28
  • You won't have to wait a half century. :) BigQuery has a limit of 4000 partitions (https://cloud.google.com/bigquery/quotas#partitioned_tables) so you'll max out just under 11 years. – jwadsack Jan 16 '23 at 23:40

1 Answers1

2

You can use Tables: delete API to delete specific partition of the table by specifying that partition as yourTable$YYYYMMDD

And you can use timePartitioning.expirationMs property to set Number of milliseconds for which to keep the storage for a partition. You can set this property either while creating table via Tables: insert API or you can patch existing table via Tables: patch API

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Deleting tables via yourTable$YYYYMMDD does not seem to update the table's partition lists when accessed via select * from [datawarehouse:dev.temps$__PARTITIONS_SUMMARY__] – Piran Dec 13 '16 at 14:56
  • Yes. this might be a bug to be reported to BigQuery Team. Meantime actual table is updated in a sense that no anymore rows in deleted partition and thus no cost for it. you can check it via: SELECT _PARTITIONTIME AS partition_id, COUNT(1) FROM [datawarehouse:dev.temps] – Mikhail Berlyant Dec 13 '16 at 21:58
  • Note also that when deleting partitions, cached queries that include that partition are not invalidated; see https://code.google.com/p/google-bigquery/issues/detail?id=894 – Piran Jan 20 '17 at 14:40