CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
tomwoodruff
tomwoodruff

Reputation: 413

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?

Upvotes: 38

Views: 20554

Answers (3)

vilozio
vilozio

Reputation: 322

For anyone who needs to union several tables but can't use a wildcard table. You can add a fixed value column to each table. Filtering by this column reduces the scanned bytes.

CREATE VIEW some.view as ( 
  SELECT *, DATE('2023-01-01') as `date`, 
  FROM `my_project.my_dataset.some_table`
  UNION ALL
  SELECT *, DATE('2023-01-02') as `date`, 
  FROM `my_project.my_dataset.another_table`
  UNION ALL
  SELECT *, DATE('2023-01-03') as `date`, 
  FROM `my_project.my_dataset.again_another_table`
);


SELECT * FROM some.view WHERE `date` = '2023-01-03'

Upvotes: 0

ernesto
ernesto

Reputation: 341

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'

Upvotes: 11

Elliott Brossard
Elliott Brossard

Reputation: 33745

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.

Upvotes: 31

Related Questions