Presto で JSON の ARRAY を集計する

効率よい方法ではないですが、Fluentd 経由で S3 に送り込んだ JSON のログを Presto で集計するときにこんなやり方もできるというメモ。

下記のような構造のログが S3 に置いてあり、Hive の external table として raw_impressions というテーブルを定義して読めるようになっているとします。

{
  "user_id": 12345,
  "data": {
    "imp_ids": [1,2,3,4,5]
  }
}

ここで下記のように view を定義してあげます。view を定義しなくてもできますが、可読性がよくなるので、今回はそうしてます。

CREATE VIEW impressions AS
  SELECT
    cast(json_extract(json, '$.user_id') as bigint) as user_id,
    cast(json_extract(json, '$.data.imp_ids') as array<bigint>) as imp_ids
  FROM raw_impressions
;

ここまで来れば UNNEST 構文を使って、集計できます。

SELECT id, count(id)
FROM impressions
CROSS JOIN UNNEST(imp_ids) AS t (id)
GROUP BY id;

JSON のパースに時間がかかって、ログ量によってはだいぶツラいので、実用上は必要に応じてサンプリングしてみたりするといいのかもしれません。

SELECT
  link_id, count(link_id)
FROM impressions
CROSS JOIN UNNEST(imp_ids) AS t (id)
WHERE user_id % 1000 = 0
GROUP BY id;