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

2022.10.14

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

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

前提

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

※データは適当です

simple_log

date location details
2022-01-01 JP {"bytes":4252,"requestip":"10.0.0.15","method":"GET","status":200}
2022-01-05 US {"bytes":1128,"requestip":"10.0.0.20","method":"POST","status":200}
2022-01-20 JP {"bytes":3855,"requestip":"10.0.0.25","method":"GET","status":200}

この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_EXTRACTCTASを用いて、先の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

date location bytes requestip method status
2022-01-01 JP 4252 10.0.0.15 GET 200
2022-01-05 US 1128 10.0.0.20 POST 200
2022-01-20 JP 3855 10.0.0.25 GET 200

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

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

  • 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

location avg_bytes max_bytes
JP 4053.5 4252
US 1128.0 1128

最後に

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

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

参考

JSON からのデータの抽出

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