CODEWITHSUNDEEP
sqlgoogle-bigqueryfirebase-analytics
SagiLow
SagiLow

Reputation: 6039

BigQuery: How to query firebase daily events table dynamically?

I want to schedule a query which takes data from Firebase daily events table (the day before) and update another table. (I got it working with manually specifying the source full table name)

I thought the events table is just a partitioned table but it doesn't look like it.
Here is what it looks like on UI:

enter image description here
As you can see there are actually 16 tables under events_ which are actually daily tables.
How can I dynamically specify the name of the table I want to query?

Here is what I tried but it's obviously not working: (numbers altered)

SELECT * 
FROM `appname-11111.analytics_11111111.events_`
WHERE _PARTITIONTIME = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Upvotes: 3

Views: 1946

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33765

I think you want this instead:

SELECT * 
FROM `appname-11111.analytics_11111111.events_*`
WHERE _TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

See the wildcard tables documentation for more reading. The short summary is that the events "table" is really a collection of tables with a common prefix (different from a partitioned table, which is a single table with multiple dates), and the * syntax lets you refer to more than one table at once.

Upvotes: 9

Related Questions