BigQuery asks for a partition filter while a filter exists

Question

We have a BigQuery query that joins two tables, one of those tables is partitioned on DATE. As a first step i added a date literal into the query, which works perfect.

SELECT a.hello,b.ciao FROM table a INNER JOIN table b ON a.time = b.time AND b.date = '2021-10-03'

Now it is time to add multiple days this to prune only the partitions that are relevant.

Attempt one: using a subquery, this does not work, subquery not allowed

SELECT a.hello,b.ciao FROM table a INNER JOIN table b ON a.time = b.time AND b.date IN (SELECT mytimes FROM table)

Attempt two: use ARRAY and UNNEST, does not work (i receive the same error when you do not have a filter at all: error: cannot query over table without a filter)

Querying a Partitioned table in BigQuery using a reference from a joined table

DECLARE date_filter ARRAY DEFAULT (SELECT ARRAY_AGG(day) FROM table);

SELECT a.hello,b.ciao FROM table a INNER JOIN table b ON a.time = b.time AND b.date IN UNNEST(data_filter)

Something I thought was going to be easy seems not so easy after all, can someone guide me to a solution? Many thx.

Sergey Geron · Accepted Answer · 2021-10-04 15:50:21Z

0

Try to move b.date IN UNNEST(data_filter) from ON to WHERE:

SELECT a.hello,b.ciao FROM table a INNER JOIN table b ON a.time = b.time WHERE b.date IN UNNEST(data_filter)

answered Oct 4, 2021 at 15:50

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SLDX Over a year ago

Hello Sergey, the can work however with the first attempt we learned that there is no partition pruning at the join when you use the filter in the where clause. I will give it another try just to sure that there is no partition elimination when you use the filter in the where clause.

Mikhail Berlyant · Accepted Answer · 2021-10-04 16:36:53Z

0

cannot query over table without a filter

You can just "remove" that requirement, which was set when table was created

 ALTER TABLE mydataset.mytable SET OPTIONS ( require_partition_filter=false )

answered Oct 4, 2021 at 16:36

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

2 Comments

SLDX Over a year ago

Hello Mikhail, thx for the anwser. We want to really use the partitioning so skipping the partition pruning is not an option.

Mikhail Berlyant Over a year ago

not requiring filter - has nothing to do with partitioning pruning! it still happenning!

user17618916 · Accepted Answer · 2021-12-07 20:36:42Z

0

It has to be in the ON clause. Store the result of the select in a variable. Prepare the query dynamically and then execute.

answered Dec 7, 2021 at 20:36

user17618916

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

BigQuery asks for a partition filter while a filter exists

3 Answers 3

1 Comment

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Linked

Related