Writing Scheduled Queries using the run_date vs current_date

user_19404825 0
Viewed 50

I have created a scheduled query that returns a count of users, and transactions on each day. Here is the code:

SELECT 
event_date, 
COUNT(DISTINCT user_id) users, 
COUNT(DISTINCT transaction_id) transactions, 

FROM `xyz.events` 
WHERE 
event_date = current_date

GROUP BY event_date 
ORDER BY event_date

The query shown above works when I execute it manually. But when I use it as a scheduled query it doesn't update the destination table as it should even though if I check the runs, it shows that the query has run successfully for that particular day.

The query shown below however does the trick and runs exactly as intended. It updates the daily count of users and transactions in the destination table.

SELECT 
DATE_SUB(@run_date, INTERVAL 1 DAY) event_date, 
COUNT(DISTINCT user_id) users, 
COUNT(DISTINCT transaction_id) transactions, 

FROM `xyz.events` 
WHERE 
event_date = DATE_SUB(@run_date, INTERVAL 1 DAY) 

GROUP BY event_date 
ORDER BY event_date

So I wanted to understand why this is happening? Because when run manually both the queries give the same output.

google-bigquery sql
1 Answers

Welcome Anxiety,

When you call the CURRENT_DATE() function you must add the opening and closing parenthesis at the end (). Having this missing from the end of your function call is why this query is failing when set to run as a scheduled query.

As to why it runs when you run it in a regular BigQuery query window, I am not certain, but assume the UI must have some inbuilt logic to work around the missing parenthesis , which is not available to scheduled queries.

user_78130050
Related