CODEWITHSUNDEEP
google-bigqueryfirebase-analytics
azekirel555
azekirel555

Reputation: 587

Creating views periodically in BigQuery

I'm currently using Firebase Analytics to export user-related data to BigQuery.

Is there a way to create a view automatically in BigQuery (every 24 hours for example) as exports from Firebase create a new table everyday, or a single view gathering the data from the tables created daily. Is it possible to do such things with the WebUI ?

Upvotes: 1

Views: 492

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33715

You can create a view over a wildcard table so that you don't need to update it each day. Here is an example view definition, using the query from one of your previous questions:

#standardSQL
SELECT
  *,
  PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS date
FROM `com_test_testapp_ANDROID.app_events_*`
CROSS JOIN UNNEST(event_dim) AS event_dim
WHERE event_dim.name IN ("EventGamePlayed", "EventGetUserBasicInfos", "EventGetUserCompleteInfos");

Let's say that you name this view com_test_testapp_ANDROID.event_view (make sure to pick a name that isn't included in the app_events_* expansion). Now you can run a query to select yesterday's events, for instance:

#standardSQL
SELECT event_dim
FROM `com_test_testapp_ANDROID.event_view`
WHERE date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

Or all events in the past seven days:

#standardSQL
SELECT event_dim
FROM `com_test_testapp_ANDROID.event_view`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK);

The important part is having a column in the select list for the view that lets you restrict the _TABLE_SUFFIX to whatever range of time you are interested in.

Upvotes: 3

Related Questions