在BigQuery中定期创建视图 [英] Creating views periodically in BigQuery

问题描述

我目前正在使用Firebase Analytics将用户相关数据导出到BigQuery。

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

有没有办法在BigQuery中自动创建视图（每24小时例如），因为Firebase的出口每天都会创建一个新表格，或者是从每天创建的表格收集数据的单个视图。
是否可以使用WebUI执行此类操作？

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 ?

推荐答案

您可以通过通配符表创建视图你不需要每天更新它。这是一个示例视图定义，使用以前问题之一的查询：

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");

假设您命名此视图 com_test_testapp_ANDROID.event_view （确保选择一个未包含在 app_events _ * 扩展中的名称）。现在，您可以运行查询来选择昨天的事件，例如：

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);

重要的部分是在视图的选择列表中有一列，可以让您限制 _TABLE_SUFFIX 到您感兴趣的任何时间范围。

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.

这篇关于在BigQuery中定期创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！