CODEWITHSUNDEEP
sqldatabasefirebasegoogle-bigqueryanalytics
xtract studio
xtract studio

Reputation: 95

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

Upvotes: 8

Views: 13514

Answers (5)

Belegnar
Belegnar

Reputation: 877

Moreover,

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

is totally legit

Upvotes: 1

Simonel Garrad
Simonel Garrad

Reputation: 1

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

Upvotes: 0

user3415717
user3415717

Reputation: 73

You can do this:

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

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

Upvotes: 4

frapeti
frapeti

Reputation: 1161

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.

Upvotes: 2

Sergey Geron
Sergey Geron

Reputation: 10222

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;

Upvotes: 5

Related Questions