dehio3’s diary

仕事、生活、趣味のメモ

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

f:id:dehio3:20190328174933p:plain

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

  • 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)

f:id:dehio3:20190328175918p:plain

参照

月毎にデータを集計

  • 月毎に、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

f:id:dehio3:20190328181202p:plain

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

  • 上記の参照に、期間指定の条件を追加する
/* 配列データをフラット化*/
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

f:id:dehio3:20190328181654p:plain