How to use partition pruning in google big query without hardcoding the filter condition?

Question

I have a big table in GBQ which is partitioned by date. and I want to use partition pruning to optimize my query. the problem is that filter condition is a value that is read from another table and I can't hardcode the value. I wonder if there is any way to use partition pruning in this case and also I can't use declare statement because scheduling engine that I am working with doesn't allow that. here is my code:

WITH CTE AS
(
  SELECT tmp.partitionStartDate
  FROM tmp_table tmp
)
SELECT *      
FROM table1  t
WHERE sbcc.partitionDate = (select partitionStartDate from cte)

you should use scripting with `execute immediate` to at a run time partition date will be defined — Mikhail Berlyant, May 19 '21 at 20:18
Thanks @MikhailBerlyant, how would you write the above query to use ```execute immediate ``` ? — sia, May 19 '21 at 21:55

Yun Zhang · Answer 1 · 2021-05-20T15:54:46.970

2

Script below will work:

DECLARE partitionDate DEFAULT (
  SELECT tmp.partitionStartDate
  FROM tmp_table tmp
);

SELECT *      
FROM table1  t
WHERE sbcc.partitionDate = partitionDate;

Or

EXECUTE IMMEDIATE
"""
SELECT *      
FROM table1  t
WHERE sbcc.partitionDate = @partitionDate;
""" USING (
  SELECT tmp.partitionStartDate
  FROM tmp_table tmp
) AS partitionDate;

edited May 20 '21 at 15:54

answered May 20 '21 at 00:43

Yun Zhang

5,185
2
10
29

unfortunately I can't use Declare because of scheduler's constraint that i am using – sia May 20 '21 at 00:50
understood, added the `execute immediate` approach – Yun Zhang May 20 '21 at 15:55

score 2 · Accepted Answer · answered May 20 '21 at 03:40

2

Try this:

execute immediate "SELECT * FROM table1 WHERE partitionDate = ?" using (SELECT partitionStartDate FROM tmp_table);

answered May 20 '21 at 03:40

Sergey Geron

9,098
2
22
29

How to use partition pruning in google big query without hardcoding the filter condition?

2 Answers2