この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部の鈴木です。
今回は配列をネストしたJSONLファイルをJSON SerDeライブラリで読み込み、フラット化して分析に使用したいことがあったので、試してみました。
やりたいこと
以下のようなJSONLファイルを、
{"col1":"a", "col2":{"col_21":["a1","a2","a3","a4"], "col_22":["a5","a6","a7","a8"]}}
{"col1":"b", "col2":{"col_21":["b1","b2","b3","b4"], "col_22":["b5","b6","b7","b8"]}}
{"col1":"c", "col2":{"col_21":["c1","c2","c3","c4"], "col_22":["c5","c6","c7","c8"]}}
以下のようにAthenaでフラット化して検索することが目標です。
このようにすることで、JSON内にネストしている配列のデータを使って、ほかのテーブルと結合し、分析を進められるケースがあります。
ポイント
今回、やりたいことを実現するために、以下の2つの技術要素がポイントになったので、ご紹介します。
1. JSON SerDe ライブラリ
Athenaでは、JSONを読み出すために、現状以下の2種類のSerDeライブラリがあります。
- Hive JSON SerDe
- OpenX JSON SerDe
今回はOpenX JSON SerDeを使ってJSONLファイルに検索を実行します。
sample1.jsonl
{"id": 1, "profile": {"name": "sasaki", "age": 24}}
{"id": 2, "profile": {"name": "tanaka", "age": 36}}
{"id": 3, "profile": {"name": "suzuki", "age": 27}}
このJSONLファイルは、例えばs3://cm-nayuts-sample/unnest_sample1/
にアップロードしておくとします。
続いて、以下のDDLを実行し、sample_json_table1
テーブルを作成します。
オブジェクトのデータに対応するカラムは、以下のようにstruct
もしくはmap
で定義する必要があります。
CREATE EXTERNAL TABLE `sample_json_table1`(
id int
, profile struct<name:string, age:int>
)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
LOCATION
's3://cm-nayuts-sample/unnest_sample1/'
これでJSONLをAthenaで読み出す準備が完了です。.
でJSONのバリューにアクセスすることができるので、これを使ってファイルを検索してみます。
SELECT
id
profile.name
profile.age
FROM sample_json_table1
JSONLファイルからデータを読み出してを構造化することができました。
2. UNNEST
UNNESTを使うことで、ARRAYやMAPを構造化したデータに展開することができます。 ARRAYだと1列、MAPだと2列に展開されます。
Athena エンジンバージョン2が基づいているPresto 0.217のドキュメントだと以下に記載があります。
まず簡単な例を確認します。
-- 以下のドキュメントの例を引用しました。
-- https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html
WITH dataset AS (
SELECT
'engineering' as department,
ARRAY['Sharon', 'John', 'Bob', 'Sally'] as users
)
SELECT department, names FROM dataset
CROSS JOIN UNNEST(users) as t(names)
複数の配列を持たせた場合は以下のようになりました。
-- 以下のドキュメントの例を改変しました。
-- https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html
WITH dataset AS (
SELECT
'engineering' as department,
ARRAY['Sharon', 'John', 'Bob', 'Sally'] as users,
ARRAY[28, 25, 36] as ages
)
SELECT department, names , age FROM dataset
CROSS JOIN UNNEST(users, ages) as t(names, age)
ages
カラムはわざと長さを短くしていていますが、足りない分はnullになることが分かります。
やってみる
ようやく本題ですが、先ほど見た2つのポイントを使って以下のJSONLファイルを読み込んでフラット化してみます。
sample2.jsonl
{"col1":"a", "col2":{"col_21":["a1","a2","a3","a4"], "col_22":["a5","a6","a7","a8"]}}
{"col1":"b", "col2":{"col_21":["b1","b2","b3","b4"], "col_22":["b5","b6","b7","b8"]}}
{"col1":"c", "col2":{"col_21":["c1","c2","c3","c4"], "col_22":["c5","c6","c7","c8"]}}
まず検索用のsample_json_table2
テーブルを定義します。JSONLファイルはあらかじめS3にアップされているとします。
CREATE EXTERNAL TABLE `sample_json_table2`(
col1 string,
col2 struct<col_21:array<string>, col_22:array<string>>
)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
LOCATION
's3://cm-nayuts-sample/unnest_sample2/'
.
を使って、以下のように配列を1カラムに読み出すことができます。
SELECT
col1,
col2.col_21,
col2.col_22
FROM sample_json_table2
この操作でUNNEST
の解説で見た状態にすることができました。
続いて、col_21
とcol_22
をUNNEST
を使うことでフラット化してみましょう。
SELECT
col1,
col_21,
col_22
FROM sample_json_table2
CROSS JOIN UNNEST(col2.col_21, col2.col_22) as t(col_21, col_22)
期待通りに構造化することができました。
最後に
今回はネストしたJSONLファイルをJSON SerDeライブラリで読み込んでフラット化する例をご紹介しました。
どこかで誰かのお役に立てば幸いです。