为存储在 Google BigQuery 中的 Firebase 事件在一行中选择多个事件参数 [英] Select several event params in a single row for Firebase events stored in Google BigQuery

问题描述

我正在尝试对存储在 Google BigQuery 中的 Firebase 事件执行一个非常简单的查询，但我找不到方法.

I'm trying to perform a very simple query for Firebase events stored in Google BigQuery but I´m not able to find a way to do it.

在 Android 应用中，我正在记录这样的事件:

In the Android app, I´m logging an event like this:

Bundle params = new Bundle();
params.putInt("productID", productId);
params.putInt(FirebaseAnalytics.Param.VALUE, value);
firebaseAnalytics.logEvent("productEvent", params);

所以，在 BigQuery 中，我有这样的事情:

So, in BigQuery I have something like this:

 ___________________ _______________________ ____________________________ 
| event_dim.name    | event_dim.params.key  | event_dim.params.int_value | 
|___________________|_______________________|____________________________|
| productEvent      | productID             | 25                         | 
|                   |_______________________|____________________________| 
|                   | value                 | 1253                       |
|___________________|_______________________|____________________________| 

当我从这个表中获取数据时，我得到两行:

When I get the data from this table I get two rows:

 ___________________ _______________________ ____________________________
|event_dim.name     | event_dim.params.key  | event_dim.params.int_value |
|___________________|_______________________|____________________________|
| productEvent      | productID             | 25                         |
| productEvent      | value                 | 12353                      |

但我真正需要的是从该表中使用 SELECT 子句来获取如下数据:

But what I really need is a SELECT clause from this table to get the data as below:

 ___________________ _____________ _________
|   name            | productID   | value   |
|___________________|_____________|_________|
| productEvent      | 25          | 12353   |

有什么想法或建议吗?

推荐答案

您可以像这样将值透视到列中

You can pivot the values into columns like this

SELECT 
  event_dim.name as name,
  MAX(IF(event_dim.params.key = "productID", event_dim.params.int_value, NULL)) WITHIN RECORD productID,
  MAX(IF(event_dim.params.key = "value", event_dim.params.int_value, NULL)) WITHIN RECORD value,
FROM [events] 

如果您想使用 SQL 生成此命令，请参阅此解决方案:Pivot RepeatedBigQuery 中的字段

In case you want to generate this command using SQL, see this solution: Pivot Repeated fields in BigQuery

这篇关于为存储在 Google BigQuery 中的 Firebase 事件在一行中选择多个事件参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！