1

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 ?

azekirel555
  • 577
  • 2
  • 8
  • 25

1 Answers1

3

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.

Elliott Brossard
  • 32,095
  • 2
  • 67
  • 99
  • Thanks for your answer @Elliott Brossard, it's very clear. As the table schema that I'm looking to use is (quite) different from the one used by Firebase, I was wondering if it would be possible to create a new table instead of creating a view to automatically insert data periodically from the tables created daily by Firebase exports. Then it would delete the tables created by Firebase to reduce the amount of data stored. – azekirel555 Apr 27 '17 at 14:47
  • Off the top of my head, one thing you could do is to [set up a cron](https://cloud.google.com/appengine/docs/standard/python/config/cron) that runs a query and writes the result to some other table, then deletes the Firebase table that it read from. – Elliott Brossard Apr 27 '17 at 14:52
  • Is this ok in terms of the billing? I mean if I select from the view single day is the condition correctly propagated into the wildcard tables so as the resulting query touches just the single data table? – Buthrakaur Oct 25 '19 at 11:21
  • Yes, it is. You can confirm by checking the bytes billed estimate for the query in the UI – Elliott Brossard Oct 25 '19 at 15:08