使用 Big Query 将 firebase event_params 键值数据显示到单行中

问题描述

我正在尝试对 Firebase 存储的事件执行 Google BigQuery.我已经执行了以下查询

I am trying to perform a Google BigQuery on the Firebase stored events. I have executed the following query

SELECT * FROM `myTable` LIMIT 6

结果如下:

+-----+----------+--------+------------------+---------------------------------+
| Row |   date   |  name  | event_params.key | event_params.value.string_value |
+-----+----------+--------+------------------+---------------------------------+
|   1 | 20200922 | Event1 | errorName        | BLE_Not_connected               |
|     |          |        | appDetails       | 2.2.2                           |
|     |          |        | errorDetails     | iOS-Error                       |
+-----+----------+--------+------------------+---------------------------------+

因此，第 1 行有多个 event_params.key 条目，它们的值显示在 event_params.value.string_value 列上.现在，我想执行一个 Google Big-Query 来展平 event_params.key 列值并在下面显示结果

So, here row-1 has multiple entries of event_params.key and their value shows on event_params.value.string_value column. Now, I want to perform a Google Big-Query which flattens the event_params.key column value and show a result below

+-----+----------+--------+------------------+---------------------------------+
| Row |   date   |  name  | errorName        | appDetails  | errorDetails      |
+-----+----------+--------+------------------+---------------------------------+
|   1 | 20200922 | Event1 | BLE_Not_connected| 2.2.2       | iOS-Error         |
+-----+----------+--------+------------------+---------------------------------+

有人可以帮我吗?提前致谢.

Could anyone help me? Thanks in advance.

推荐答案

以下是 BigQuery Standard SQL

Below is for BigQuery Standard SQL

EXECUTE IMMEDIATE (
  SELECT """
  SELECT date, name, """ ||
    STRING_AGG("""MAX(IF(key = '""" || key || """', value.string_value, NULL)) AS """ || key, ', ')
  || """
  FROM `project.dataset.table` t, t.event_params
  GROUP BY date, name
  """
  FROM (
    SELECT DISTINCT key
    FROM `project.dataset.table` t, t.event_params
  )
);

如果应用于您问题中的样本数据 - 输出为

If to apply to sample data from your question - output is

Row date        name    errorName           appDetails  errorDetails
1   20200922    Event1  BLE_Not_connected   2.2.2       iOS-Error

这篇关于使用 Big Query 将 firebase event_params 键值数据显示到单行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！