CODEWITHSUNDEEP
google-bigquerymaterialized-views
Scott Schaen
Scott Schaen

Reputation: 111

How to add a partition expiration to a BigQuery Materialized View

I have a materialized view that's aggregated by timestamp-minute and a few other columns. I use it for various time series monitoring. I'd only like to keep the most recent 7 days of data in this MV since it's a lot of aggregate data that I don't need long term. Right now there's just a hardcoded date filter.

I've tried a few things to limit the MV and none worked.

OPTIONS ( partition_expiration_days = 7) --> Doesn't work. The base table has no expiration.

where _partitiondate >= date_sub(current_date(),interval 7 day) --> Doesn't work. "Materialized view query cannot use functions that depend on current time"

order by partitiondate desc limit 1000000 doesn't work either.

The only idea I have to keep this table reasonably sized is an airflow/composer job that replaces the MV every day or so with a hard-coded date filter.

Any better ideas?

Upvotes: 0

Views: 1506

Answers (1)

Pentium10
Pentium10

Reputation: 208002

Partition expiration must not be set on materialized views.

A materialized view implicitly inherits the partition expiration time from the base table. Materialized view partitions are aligned with the base table partitions, so they expire synchronously. Reference.

Upvotes: 2

Related Questions