How to prune partitions with joins in BigQuery?

user_14694383 0
Viewed 347

I have two tables with two different type of events, both tables are partitioned by the event's timestamp and I would like to create a view of the join between them with the events with the same id and in the same quarter.

CREATE TABLE `proj1.test.table1` ( event_timestamp TIMESTAMP, id INT64 )
PARTITION BY DATE(event_timestamp);

CREATE TABLE `proj1.test.table2` ( event_timestamp TIMESTAMP, id INT64 )
PARTITION BY DATE(event_timestamp);

CREATE VIEW `proj1.test.view1` AS
  SELECT t1.event_timestamp,t1.id
  FROM `proj1.test.table1` t1
  INNER JOIN `proj1.test.table2` t2
  USING (id)
  WHERE TIMESTAMP_TRUNC(t1.event_timestamp, quarter) = TIMESTAMP_TRUNC(t2.event_timestamp, quarter)
;

The problem is that this view only is pruning the partitions in the first table.

SELECT * 
FROM test.view1
WHERE event_timestamp BETWEEN '2020-01-01' AND '2020-04-01'

To be able to prune the partitions of both tables I have to use a literal expression instead of a computed value:

CREATE VIEW `proj1.test.view1` AS
  SELECT t1.event_timestamp,t1.id
  FROM `proj1.test.table1` t1
  INNER JOIN `proj1.test.table2` t2
  USING (id)
  WHERE TIMESTAMP_TRUNC(t2.event_timestamp, quarter) = '2020-01-01'
google-bigquery
1 Answers

You have described the intended behaviour. I would try either:

  1. Get rid of the view and double up the filter in the WHERE clause. I do this often as it is easy:
    SELECT t1.event_timestamp,t1.id
    FROM test.table1 t1
    INNER JOIN test.table2 t2
    USING (id)
    WHERE t1.event_timestamp BETWEEN '2020-01-01' AND '2020-04-01'
    AND t2.event_timestamp BETWEEN '2020-01-01' AND '2020-04-01'
  1. If the query is always going to be based on the quarter, add another column and partition and join on it:
CREATE TABLE test.table1 ( event_timestamp TIMESTAMP, event_quarter DATE, id INT64 )
PARTITION BY event_quarter;

CREATE TABLE test.table2 ( event_timestamp TIMESTAMP, event_quarter DATE, id INT64 )
PARTITION BY event_quarter;

CREATE VIEW test.view1 AS
  SELECT t1.*
  FROM test.table1 t1
  INNER JOIN test.table2 t2
  USING (id, event_quarter);

SELECT * 
FROM test.view1
WHERE event_quarter = '2020-01-01'
user_73595020
Related