CODEWITHSUNDEEP
firebasegoogle-bigqueryfirebase-analytics
alvaro torrico
alvaro torrico

Reputation: 755

Select several event params in a single row for Firebase events stored in Google 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?

Upvotes: 5

Views: 2683

Answers (2)

Elliott Brossard
Elliott Brossard

Reputation: 33745

Using standard SQL (uncheck "Use Legacy SQL" under "Show Options" in the UI), you can express the query as:

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;

Edit: updated example to include int_value as part of value based on the comment below. Here is a self-contained example that demonstrates the approach as well:

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;

Upvotes: 4

Pentium10
Pentium10

Reputation: 207912

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

Upvotes: 7

Related Questions