Athenaで文字列として格納されたJSON形式のカラムを別テーブルに展開する

データアナリティクス事業本部のueharaです。

今回はAthenaについて、文字列として格納されたJSON形式のカラムを別テーブルに展開する操作をしてみたいと思います。

前提

ここでは、以下のような「simple_log」テーブルがあり、detailsカラムがJSON形式の文字列となっていることを想定します。

※データは適当です

simple_log

このdetails カラムを展開した別テーブルを作成するのが今回のゴールです。

まずは結論

いくつか方法がありますが、本記事ではJSON_EXTRACT関数とCTASクエリを用います。

JSON_EXTRACT関数について

JSON_EXTRAXT関数は、JSON形式の文字列のカラムを対象に、JSONPathを使用して文字列を検索する関数です。

まずJSONPathが何か分からないよ、というかたはJSONPath 使い方まとめなどが参考になるかもしれません。要は、JSONオブジェクトから値を取得する（クエリする）ために標準化された方法です。

JSON_EXTRACT("カラム", "JSONPath")という形で利用することができます。

CTASクエリについて

CTASとは、CREATE TABLE AS SELECTの頭文字です。

文字通り、あるテーブルからSELECTした結果で別のテーブルを作成することができるクエリになります。

AthenaにおけるCTAS構文については、「CREATE TABLE AS」に記載がありますが、構文の概要は次の通りです。

CREATE TABLE table_name
[ WITH ( property_name = expression [, ...] ) ]
AS query
[ WITH [ NO ] DATA ]

JSON形式のカラムを別テーブルに展開

JSON_EXTRACTとCTASを用いて、先のdetailsを展開した「simple_log_mart」というテーブルを作成します。

SQLは次の通りです。

CREATE TABLE simple_log_mart 
WITH (
  external_location = 's3://ctas-test-mart/'
)
AS SELECT
　　date,
　　location,
　　CAST(JSON_EXTRACT(details, '$.bytes') AS INT) AS bytes,
　　CAST(JSON_EXTRACT(details, '$.requestip') AS VARCHAR) AS requestip, 
　　CAST(JSON_EXTRACT(details, '$.method') AS VARCHAR) AS method,
　　CAST(JSON_EXTRACT(details, '$.status') AS INT) AS status
FROM simple_log;

external_locationでは新たに作成するテーブルのデータを保存するS3バケットを指定します。

JSON_EXTRACT関数で取得したデータは、明示的にCAST関数で型を設定します。

成功すると、次のような「simple_log_mart」というテーブルが作成されているかと思います。

simple_log_mart

（補足）集約関数を用いて別テーブルに展開

先の例で出てきたデータについて、

• location毎のbytesの平均と最大値で別テーブルを作成したい
• ただしsimple_log_martのような中間テーブルは保持したくない

といった要求がある場合でもCTASを利用することができます。

具体的には、先のsimple_log_martの内容をWITH句で一時テーブルとしてしまうことで可能です。

CREATE TABLE simple_log_bytes
WITH (
  external_location = 's3://cm-da-uehara/ctas-test-mart2/'
)
AS　WITH tmp AS(
  SELECT
    location,
    CAST(JSON_EXTRACT(details, '$.bytes') AS INT) AS bytes
  FROM simple_log
)
SELECT
  location,
  AVG(bytes) AS avg_bytes,
  MAX(bytes) AS max_bytes
FROM tmp
GROUP BY location;

simple_log_bytes

最後に

今回はAthenaについて、JSON_EXTRACTとCTASを用いてJSON形式のカラムを別テーブルに展開するといった操作を行いました。

同様の操作をしたい方の参考になると幸いです。

参考

JSON からのデータの抽出

クエリ結果からのテーブルの作成 (CTAS)