CODEWITHSUNDEEP
google-bigquerypartitioningdatabase-partitioningpruning
sia
sia

Reputation: 577

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

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)

Upvotes: 1

Views: 664

Answers (2)

Yun Zhang
Yun Zhang

Reputation: 5518

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;

Upvotes: 2

Sergey Geron
Sergey Geron

Reputation: 10212

Try this:

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

Upvotes: 2

Related Questions