athenaでS3のJSONデータを参照する

はじめに

athenaにてJSON形式のデータを参照する時に理解に苦しんだので、覚えてるうちに流れを整理しておく。

JSONデータ

S3に保存しているJSONのデータは以下

• 日毎のJSONファイルが生成される
• その日のorganization毎のビルド数を集計している

cat 2019-01-14.json | jq
{
  "date": "2019-01-14",
  "counts": [
    {
      "name": "organization_A",
      "count": 81
    },
    {
      "name": "organization_B",
      "count": 76
    },
    {
      "name": "organization_C",
      "count": 48
    },
    {
      "name": "organization_D",
      "count": 48
    },
  ]
}

データ参照するまでの流れ

テーブル作成

• athenaでテーブルを作成する
• 本当はテーブル作成時点で、dateカラムについてはdate型にしたかったが、athenaのバグ！？でJSONのファイルが増えるとデータを参照できないので、string型で作成する

CREATE EXTERNAL TABLE daily_build_count (
-- date date,
date string,
  counts array<struct<name:string,count:int>>
 )
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://<S3バケット名>/'

テーブル作成段階でのデータの参照

select * from daily_build_count

配列をフラット化したデータの参照

• countsの値が配列になっているので、name毎に参照できるようにする
• dateカラムを日付として扱えるように、TRY_CAST関数を使ってdate型に変更する

SELECT
  TRY_CAST(date AS date) as date,
  count.name,
  count.count
FROM daily_build_count
  cross join UNNEST(counts) as t (count)

参照

• ネストされた配列のフラット化 - Amazon Athena
• 6.4. Conversion Functions — Presto 0.172 Documentation

月毎にデータを集計

• 月毎に、name単位のcountの合計値を集計する
• クエリをわかりやすくする為に、WITH句で一旦フラット化したテーブルを参照する

/* 配列データをフラット化*/
with daily_build_count as(
SELECT
  TRY_CAST(date AS date) as date,
  count.name,
  count.count
FROM daily_build_count
  cross join UNNEST(counts) as t (count)
)
/* フラット化したテーブルに対して参照*/
SELECT
 to_char(date, 'yyyy/mm') as month,
  name,
  sum(count) as total
FROM daily_build_count
  group by name, to_char(date, 'yyyy/mm')
  order by month, total desc

期間を指定してデータを集計

• 上記の参照に、期間指定の条件を追加する

/* 配列データをフラット化*/
with daily_build_count as(
SELECT
  TRY_CAST(date AS date) as date,
  count.name,
  count.count
FROM daily_build_count
  cross join UNNEST(counts) as t (count)
)
/* フラット化したテーブルに対して参照*/
SELECT
 to_char(date, 'yyyy/mm') as month,
  name,
  sum(count) as total
FROM daily_build_count
WHERE date BETWEEN date '2019-02-01' AND date '2019-02-28'
  group by name, to_char(date, 'yyyy/mm')
  order by month, total desc