在一行中为存储在Google BigQuery中的Firebase事件选择几个事件参数

Question

我试图执行一个非常简单的查询Firebase事件存储在Google BigQuery，但我无法找到一种方法来做到这一点。

在Android应用程序中，我像这样记录一个事件:

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。关键字| event_dim.params.int_value ||___________________|_______________________|____________________________|| producteevent | productID | 25 || |_______________________|____________________________|| | value | 1253 ||___________________|_______________________|____________________________|

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

<前> ___________________ _______________________ ____________________________event_dim.name | event_dim.params。关键字| event_dim.params.int_value ||___________________|_______________________|____________________________|| producteevent | productID | 25 || productEvent | value | 12353 |

但是我真正需要的是一个SELECT子句从这个表中获取数据，如下所示:

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

有什么想法或建议吗?

Answer 1

您可以像这样将值pivot到列中

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生成此命令，请参阅以下解决方案:透视BigQuery

Answer 2

使用标准SQL(取消勾选UI中"Show Options"下的"Use Legacy SQL")，您可以将查询表示为:

SELECT 
  event_dim.name as name,
  (SELECT value.int_value FROM UNNEST(event_dim.params)
   WHERE key = "productID") AS productID,
  (SELECT value.int_value FROM UNNEST(event_dim.params)
   WHERE key = "value") AS value
FROM `dataset.mytable` AS t,
  t.event_dim AS event_dim;

编辑:根据下面的评论，更新了示例，将int_value作为value的一部分。下面是一个演示该方法的自包含示例:

WITH T AS (
  SELECT ARRAY_AGG(event_dim) AS event_dim
  FROM (
    SELECT STRUCT(
        "foo" AS name,
        ARRAY<STRUCT<key STRING, value STRUCT<int_value INT64, string_value STRING>>>[
          ("productID", (10, NULL)), ("value", (5, NULL))
        ] AS params) AS event_dim
    UNION ALL
    SELECT STRUCT(
        "bar" AS name,
        ARRAY<STRUCT<key STRING, value STRUCT<int_value INT64, string_value STRING>>>[
          ("productID", (13, NULL)), ("value", (42, NULL))
        ] AS params) AS event_dim
  )
)
SELECT 
  event_dim.name as name,
  (SELECT value.int_value FROM UNNEST(event_dim.params)
   WHERE key = "productID") AS productID,
  (SELECT value.int_value FROM UNNEST(event_dim.params)
   WHERE key = "value") AS value
FROM T AS t,
  t.event_dim AS event_dim;