BigQuery Date-Partitioned Views

Question

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?

Answer 1

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.

Answer 2

For anyone trying to do this with a wildcard partition date table such as Firebase or Google Analytics:

create view some.view as ( 
    select *, _TABLE_SUFFIX as suffix from 
    `firebase-public-project.analytics_153293282.events_*` 
)


 select * from some.view WHERE suffix = '20180814'