Amazon Athenaを使ってJSONファイルを検索してみる

eyecatch_athena

はじめに

最近、Amazon Athenaの勉強をしているのですがJSONのファイルはCSVなどと違って階層構造になっているのでどのようにして読み込むのか気になり調べてみました。

JSONファイルの作成

まずは以下のようなJSONファイルを作成しS3にアップロードしました。私が趣味やっているポケモンGOのポケモンのデータになります。最近、第１世代のポケモンは国内コンプできました。

pokemon_go.json

{"name": "カイリュー", "cp": 2666, "hp": 134, "weight": 72.34,  "height": 2.21, "favorite":true,  "attributes": ["ドラゴン" , "ひこう"], "skill1":{"name":"りゅうのいぶき" , "attribute": "ドラゴン"}, "skill2":{"name":"はかいこうせん" , "attribute": "ノーマル"}}
{"name": "カビゴン",   "cp": 1964, "hp": 205, "weight": 501.27, "height": 2.12, "favorite":true,  "attributes": ["ノーマル"],            "skill1":{"name":"したでなめる" , "attribute": "ゴースト"},   "skill2":{"name":"のしかかり" , "attribute": "ノーマル"}}
{"name": "ラプラス",   "cp": 1689, "hp": 162, "weight": 220.58, "height": 2.48, "favorite":false, "attributes": ["みず" , "こおり"],     "skill1":{"name":"こおりのつぶて" , "attribute": "こおり"},   "skill2":{"name":"りゅうのはどう" , "attribute": "ドラゴン"}}

1行目を見やすく改行すると以下のようになります。JSONの文字列(string)、数値(number)、真偽(boolean)、配列(array)、オブジェクト(object)の値が含まれるようにしています。S3のデータは1レコードの途中で改行が入れるとエラーになったので改行を入れないようにして下さい。

{
  "name": "カイリュー",
  "cp": 2666,
  "hp": 134,
  "weight": 72.34,
  "height": 2.21, 
  "favorite":true ,
  "attributes": ["ドラゴン" , "ひこう"],
  "skill1":{"name":"りゅうのいぶき" , "attribute": "ドラゴン"},
  "skill2":{"name":"はかいこうせん" , "attribute": "ノーマル"}
}

テーブルを作成する

Management Consoleからではカラムタイプにstructを選択できないので、CREATE TABLE文を流して作成します。以下のCREATE TABLE文を実行しました。カラム名とJSONの項目名が違うとデータが入らないので注意しましょう。LOCATION の部分は適宜書き換えてください。

CREATE EXTERNAL TABLE IF NOT EXISTS pokemon_go
(
name string,
cp int,
hp int,
favorite boolean, 
weight float,
height float,
attributes array< string >,
skill1 struct <name:string, attribute:string>,
skill2 struct <name:string, attribute:string>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://cm-athena-test/game/';

以下のSQLを実行してデータが表示されたら成功です。

select * from pokemon_go;

データ型に関して

JSONのデータ型に対し、Athena側を以下のデータ型にしたところ参照することができました。

JSON Athena(Hive)
文字列(string) string
数値(number) tinyint, smallint, int , bigint, float, double
真偽(boolean) boolean
配列(array) array
オブジェクト(object) struct

複合型(array, struct)の取得に関して

array, struct型などのJSONな複雑なデータはSQLでどのように取得するのかをやってみました。 まずはarrayのデータを取得してみたいと思います。以下の例ではカイリューという名前のポケモンの属性を取得するSQLです。配列の要素1つが行となっています。UNNEST引数に配列のカラムを指定するようです。

select
  attribute
from
  pokemon_go
    cross join UNNEST(attributes) AS t (attribute)
where
  name='カイリュー';

次はstruct型のデータを条件にデータを取得してみたいと思います。以下の例ではノーマル属性のわざを持つポケモンを検索しています。カラム名.オブジェクトの項目名で指定できました。カイリュー、カビゴンと表示されると思います。

select
  name,
  skill1.name as skill1_name,
  skill2.name as skill2_name
from
  pokemon_go
where
  skill1.attribute='ノーマル' or skill2.attribute='ノーマル';

最後に

JSONを読み込むことができればCloudTrailのログなども検索できるのでかなり便利そうです。CloudTrailのログをAthenaで分析する方法は以下で紹介していますのでぜひやってみて下さい。

AWS AthenaでCloudTrailのS3オブジェクトログを解析をしてみました！