POST POST

MAY
8
2021

Query BigTable Events

ORIGINALLY POSTED TO: https://blog.simontimms.com/2021/05/08/query-collections-in-big-table

Firebase can feed its data to bigtable and then you can run queries there. The syntax is SQL like but not quite because they have internal record types. So for the data that is fed across from firebase you get a structure that looks like

)

You can see that event_params and user_properties are these kind of collection things. The easiest way to deal with them is to flatten the structure and internally join the table against itself

1
SELECT r.event_name, p.key, p.value FROM `pocketgeek-auto.analytics_258213689.events_intraday_20210305` r cross join unnest(r.event_params) as p where key = 'DealerName'

This gets you a dataset like

)

1
SELECT r.event_name, p.key, p.value FROM `pocketgeek-auto.analytics_258213689.events_intraday_20210305` r cross join unnest(r.event_params) as p where key = 'DealerName' and p.value.string_value <> 'none'

is probably even better with the filter


Simon Timms

Email Email
Web Web
Twitter Twitter
GitHub GitHub
RSS

Looking for someone else?

You can find the rest of the Western Devs Crew here.

© 2015 Western Devs. All Rights Reserved. Design by Karen Chudobiak, Graphic Designer