たそらぼ

日頃思ったこととかメモとか。

Athenaでjson文字列から値を取り出したい

クラウド

入れ子のjsonになっていると見せかけて、json文字列が入っている場合に、どのようにすればAthenaでデータを取り出すことができるか確認した。

データを見てみる

以下のようなデータを考える。

{"key1": 1, "key2": "{\"key21\":2, \"key22\":3}"}
{"key1": 4, "key2": "{\"key21\":5, \"key22\":6}"}

key2のバリューが文字列になっている。
似たようなパターンで、json文字列ではなくjsonが入れ子になっているものがある。

{"key1": 1, "key2": {"key21":2, "key22":3}}
{"key1": 4, "key2": {"key21":5, "key22":6}}

パッと見るとおんなじように見えるが、当然格納されているデータが全然違うので、DDLには大きな影響がある。

例えば、入れ子は以下のようにテーブルを定義すればよい。

CREATE EXTERNAL TABLE test_table ( 
    key1 int, 
    key2 struct<
        key21:int, 
        key22:int
    >
) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://test_bucket/'

SELECTしてみると以下のようになる。
f:id:tasotasoso:20210522232917j:plain

後は、SELECTするときに"."演算子を使えば、jsonの要素も取得できる。

一方、json文字列の中身はこの方法では当然とれない。SELECTしてみると、「JSONObjectちゃうやん、文字列やんけ」と、至極ごもっともなご指摘を受ける。
f:id:tasotasoso:20210522232418j:plain

Presto関数を使ってデータを取り出す

取り出す一つの方法として、Presto関数を使うことができる。
テーブルは以下のように定義する。

CREATE EXTERNAL TABLE test_table ( key1 int, key2 string ) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://test_bucket/'

DML側でPresto関数のcastとjson_extractおよびjson_parseを使い、json文字列の中身にアクセスして期待した型にキャストする。

SELECT
 cast(json_extract(k2, '$.key21') as int) as key21,
 cast(json_extract(k2, '$.key22') as int) as key22
FROM
(SELECT key1 AS k1,
       json_parse(key2) AS k2
 FROM "test_db"."test_table")


castは、

json_extractとjson_parseは、

を参照してほしい。

クエリの結果は以下のようになる。
f:id:tasotasoso:20210522225809j:plain

ちゃんと値が取れていることが分かる。

その他、Athenaから使えるPresto関数についてはこちらを参照いただきたい。
docs.aws.amazon.com

感想

jsonのバリューにjsonが入っているか、json文字列が入っているかは、よく考えると大きな違いなのですが、私はぱっと見同じような印象を受けるので、今までその違いをどうやってAthenaで吸収するのか深く考えていませんでした。Athenaの話よりかは、ベースになっているPrestoの話なので、検索してもズバリな情報も少なく、かなり面食らってしまいました。上手くパースする方法が分かったので良かったです。