'Change FROM Table Name Dynamically based on date in Bigquery Scheduled Query

enter image description here

Please refer the screenshot attached.. i need to set up a bigquery scheduled task to pull info into a permanent table. the from table name will dynamically change each day so i can pull that day info only and append it to the existing table. any help will be highly appreciatedenter image description here

please see the second image...i tried to achieve using this.. but i am unable to convert yester into String.. and add it ..

actually this was the query i am looking to implement this..

SELECT event_date, event_timestamp, event_name, (select value.double_value from unnest(event_params) where key = 'percentage') as percentage, (select value.double_value from unnest(event_params) where key = 'seconds') as seconds FROM xscore-prod.analytics_229726387.events_* WHERE event_name = "spent_time_in_activity"

how can i implement the script into this

sqldatabasefirebasegoogle-bigqueryanalytics


Solution 1:[1]

You can use scripting to generate and execute queries dynamically in BigQuery:

DECLARE yesterday STRING DEFAULT FORMAT_DATE("%F", (DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)));
DECLARE query STRING;
SET query = "SELECT * FROM `xscore-prod.analytics_229726387.events_" || yesterday || "` LIMIT 1000";
EXECUTE IMMEDIATE query;

Solution 2:[2]

If your table comes from Firebase Analytics, try this:

WHERE
  event_name = 'your_event_name'
AND _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY))

Here I'm querying yesterday only as your example.

Solution 3:[3]

You can do this:

select * from <project>.<dataset>.events_* where _TABLE_SUFFIX = '20220525'

See https://cloud.google.com/bigquery/docs/querying-wildcard-tables

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Sergey Geron
Solution 2
Solution 3 user3415717

Related Questions