Query over multiple partitions bigquery

user_1059860 0
Viewed 952

My bigquery table is partitioned by a column named: business_day

I'm trying to get all rows within certain business_days. For example,

select *
from table
where
    business_day >= "2018-12-01" and business_day <= "2019-01-01"
    or business_day >= "2019-12-01" and business_day <= "2010-01-01"
    or business_day >= "2020-12-01" and business_day <= "2021-01-01"

I get this error:

Cannot query over table 'table' without a filter over column(s) 'business_day' that can be used for partition elimination

Is there a way to do this without using UNION ALL?

Here is the table info:

Table expiry: Never
Data location: EU
Table type: Partitioned
Partitioned by: Day
Partitioned on column: business_day
Partition filter: Required

What am I missing?

google-bigquery
1 Answers

Maybe add additional pseudo filter? But this may be more costly compared to UNION ALL because it could still read non necessary partitions:

select *
from table
where 
business_day >= "2018-12-01"
AND (business_day >= "2018-12-01" AND business_day <= "2019-01-01"
     OR business_day >= "2019-12-01" AND business_day <= "2010-01-01"
     OR business_day >= "2020-12-01" AND business_day <= "2021-01-01"
    )
user_134735250
Related