Set An Existing Table’s Partition Expiration In Big Query

Why Might I need A Partition Expiration?

Partition expirations can be leveraged for several use cases. Some countries have laws pertaining to how long PII can be held. Storage is very expensive, maybe your company wants to save some money? The list can and will go on. But it gets a little harder when the table has already been created. So, this article will show you how to set an existing table’s partition expiration in Big Query.

So, how do I set an existing table’s partition expiration In Big Query then?

There are several different options. My favorite two are the CLI and using Big Query DML.

The biggest thing to note here is that these changes can’t be made using Big Query’s UI.

The CLI Option

First, you’ll need to get the BQ CLI installed. Visit this Google page to get the gcloud CLI installed. That’ll come with the bq CLI. Don’t forget to execute:

gcloud init

This will get you authed and ready to update your table. Then you can go ahead and do what you came here for, edit the table. That’ll look like:

bq update \
--time_partitioning_expiration 126144000 \
--time_partitioning_type DAY \
project_id:dataset.table

The SQL Option

This one might be a bit easier, but I personally prefer the CLI option for whatever reason. First, visit your Big Query console. And then you’ll run the following:

ALTER TABLE project_id:dataset.table
  SET OPTIONS (
    -- Sets partition expiration to 4 years
    partition_expiration_days = 1461);

Conclusion

You hitting permissions errors? Reach out to your company’s core data teams or finnagle with your IAM perms if you have the access. Want more Big Query? Check out this article.