sqlgoogle-cloud-platformgoogle-bigquery

BigQuery Date-Partitioned Views

BigQuery allows you to create date-partitioned tables: https://cloud.google.com/bigquery/docs/creating-partitioned-tables

I'd like to be able to create views on top of date-partitioned tables and ideally still reap the performance benefits. Everything I've read suggests that this isn't possible?

Has anyone had any luck otherwise?

Solution

  • Define your view to expose the partitioning pseudocolumn, like this:

    SELECT *, EXTRACT(DATE FROM _PARTITIONTIME) AS date
FROM Date partitioned table;

    Now if you query the view using a filter on date, it will restrict the partitions that are read.