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

T君

2017.01.25

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

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

JSONファイルの作成

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

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
文字列(string)	string
数値(number)	tinyint, smallint, int , bigint, float, double
真偽(boolean)	boolean
配列(array)	array
オブジェクト(object)	struct, map

今回の例ではオブジェクト(object)のデータを入れるカラムのタイプをstructにしていますが、mapでも定義できます。 mapの場合は値の型をすべて同じにする必要がありますが、項目が増えたときに定義し直す必要がありません。

skill1 struct < name:string, attribute:string >,
↓
skill1 map < string, string >,

複雑なデータ型(array, struct, map)の検索に関して

array、struct、map型などの複雑なデータはSQLでどのように検索するのかを試してみました。

array

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

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

struct

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

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

map

オブジェクトに対しmap型を使った場合は以下のようにして検索します。

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