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

Question

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 appreciated

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

Limitation

Your query suggests that you are using sharding. I would recommend that you switch to partitioned tables. Even google recommends this: "The recommended best practice is to use date/timestamp/datetime partitioned tables instead of date-sharded tables." – Intromission

Answer 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;

Craddock

Hi, thanks for your reply. 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, (SELECT PARSE_DATE ("%Y%m%d", event_date))As Date FROM xscore-prod.analytics_229726387.events_* WHERE event_name = "spent_time_in_activity" how can i implement the above script into this.. – Limitation

Hmm... Could you add an example of input data you have and an example of output data you need (in text format) to your question like here? – Craddock

actually i was able to get the desired output data using the above sql.. but when i set up scheduled query the datasource for FROM table changes everyday.. so i want my sql also changes that dynamic source.. using some functions.. so i just can run the scheduled query on specific table and append the data to the existing permanent table. – Limitation

The yesterday variable will be changing everyday, thus the query will also be changing it's FROM table everyday. If you add this script to the scheduled query it will select data from different table everyday. – Craddock

but i dont know how to add this script to the existing query..can you help – Limitation

Go to the BigQuery UI. Go to the list of Scheduled queries (choose Scheduled queries in the menu to the left). Click your scheduled query. Click EDIT in the top right corner. Edit as required and press Save query. – Craddock

Answer 2

You can do this:

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

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

Simpleton

Answer 3

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.

Moloch

My query is SELECT * FROM stocks_idx.* where _TABLE_SUFFIX = 'ABBA' I got this error BigQuery error: Syntax error: Expected end of input but got "." at [1:25] – Shalloon

Answer 4

Moreover,

    _TABLE_SUFFIX >= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 day)) ),

is totally legit

Huntingdonshire

Answer 5

Make sure you have an underscore after the table name. The format of the google analytics table is analytics_(propertyid).events_(datestring)

select event_date,sum(case when event_name="session_start" then 1 else 0 end  ) as session,sum(case when event_name="add_to_cart" then 1 else 0 end  ) as atc,traffic_source.name,traffic_source.medium,traffic_source.source from `your_project_name.analytics_(propertyid).events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY))
group by 1,4,5,6

Sideling