CODEWITHSUNDEEP
google-bigquery
Mim
Mim

Reputation: 1069

Bigquery Does not cache subqueries?

I have two tables, TODAYDUID which changes a lot, and UNITARCHIVE changes only once a day.

i tried this simple query

SELECT duid FROM `test-187010.aemodataset.TODAYDUID` where date(settlementdate)=current_date('+10:00')
UNION ALL
SELECT duid FROM `test-187010.ReportingDataset.UNITARCHIVE`

once I run the query, and tried it second time the cache is not used at all ?

I understand the first sub query from the table TODAYDUID can not be cached as it is using current_date and it did change anyway, but the second one I expected to be cached.

my workaround currently, is to import the queries separately and append them at the BI tool level, is there a better approach ?

Upvotes: 0

Views: 443

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33705

As a workaround for the lack of caching with CURRENT_DATE, you can declare a variable and use it in the query. As long as the variable's value is the same, the query will be cached:

DECLARE target_date DATE DEFAULT CURRENT_DATE('+10:00');

SELECT duid FROM `test-187010.aemodataset.TODAYDUID` where date(settlementdate)=target_date
UNION ALL
SELECT duid FROM `test-187010.ReportingDataset.UNITARCHIVE`

Upvotes: 2

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

There's only one query here, and as you note it contains a current_date(), which makes it non-cacheable.

If you're optimizing tables for BI Engine, it might be better to:

CREATE OR REPLACE TABLE `p.d.t_for_bi`
AS

SELECT duid FROM `test-187010.aemodataset.TODAYDUID` where date(settlementdate)=current_date('+10:00')
UNION ALL
SELECT duid FROM `test-187010.ReportingDataset.UNITARCHIVE` 
#or whatever query

And point your dashboards to the p.d.t_for_bi table.

Upvotes: 0

Related Questions