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

问题描述

我试图对存储在Google BigQuery中的Firebase事件执行一个非常简单的查询，但是我无法找到一种方法来处理它。

Android应用程序，我正在记录一个这样的事件：
$ b $ pre $ Bundle params = new Bundle（）;
params.putInt（productID，productId）;
params.putInt（FirebaseAnalytics.Param.VALUE，value）;
firebaseAnalytics.logEvent（productEvent，params）;


所以，在BigQuery中我有这样的内容：

 ___________________ _______________________ ____________________________ 
 | event_dim.name | event_dim.params.key | event_dim.params.int_value | 
 | ___________________ | _______________________ | ____________________________ | 
 | productEvent | productID | 25 | 
 | | _______________________ | ____________________________ | 
 | |值| 1253 | 
 | ___________________ | _______________________ | ____________________________ | 
 
 
 
当我从这个表中获取数据时，我得到两行： 
 
 
 ___________________ _______________________ ____________________________ 
 | event_dim.name | event_dim.params.key | event_dim.params.int_value | 
 | ___________________ | _______________________ | ____________________________ | 
 | productEvent | productID | 25 | 
 | productEvent |值| 12353 | 
但是我真正需要的是从这个表中获取数据的SELECT子句：
 
 
 ___________________ _____________ 
 |名字| productID |值| 
 | ___________________ | _____________ | _________ | 
 | productEvent | 25 | 12353 | 
任何想法或建议？  
 
解决方案
您可以将这些值转换为像这样的列。
 $ b $ pre $ SELECT 
 event_dim.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值，
 FROM [events] 
  

如果您想要生成命令使用SQL，请参阅此解决方案：在BigQuery中重复字段

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.

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

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                      |

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

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

Any idea or suggestion?

解决方案

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] 

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

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