Amazon Athenaでカラムの値からJSONを作る

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

Amazon Athenaで既存のカラムの値からJSON形式の新しいカラムを作成したかったので、方法を調べてみました。

やりたいこと

既存のカラムの値を要素に持つJSON形式のカラムを新しく定義します。

例えば、温度とタイムスタンプを持つ以下のようなテーブルがあるとします。

このテーブルから、以下のようなjson_colを作成します。

やってみた

まず、Athena エンジンバージョン2が基づいているPresto 0.217のドキュメントに、MAP_FROM_ENTRIES関数を使った方法が記載されているので試してみます。

6.12. JSON Functions and Operators — Presto 0.217 Documentation

以下のようなSQLを作成しました。

WITH json_items AS (
    -- マップを作成し、JSONに変換する。
    SELECT
        CAST(MAP_FROM_ENTRIES(
            ARRAY[
                ('timestamp', timestamp),
                ('temperature', CAST(temperature AS VARCHAR))
            ])AS JSON) AS json_item
    FROM "データベース名"."テーブル名"
)
SELECT json_item AS json_col
FROM json_items

キー・バリューの配列をMAP_FROM_ENTRIES関数に渡し、CAST関数でJSONに変換します。

上記SQLをAthenaから実行します。

以下のようにtemperatureカラムとtimestampカラムの値のJSONを得ることができました。

また、JSONの文字列が欲しい場合はjson_format関数を使います。

WITH json_items AS (
    -- マップを作成し、JSONに変換する。
    SELECT
        CAST(MAP_FROM_ENTRIES(
            ARRAY[
                ('timestamp', timestamp),
                ('temperature', CAST(temperature AS VARCHAR))
            ])AS JSON) AS json_item
    FROM "データベース名"."テーブル名"
),
json_item_strs AS (
    -- さらに結果をjson_formatでVARCHARに変換する。
    SELECT
        json_format(json_item) AS json_item_str
    FROM json_items
)
SELECT
    json_item_str AS json_col
FROM json_item_strs

上記SQLをAthenaから実行します。

ここまでで、MAPを作成してJSONに変換すれば良いことが分かったので、別の方法でも行ってみました。

キーの配列と、バリューの配列からマップを作り、JSONに変換してみます。

WITH json_items AS (
    -- キーの配列と、値の配列のマップを作成し、JSONにキャストする。
    SELECT
        CAST(
            MAP(
                ARRAY['timestamp', 'temperature'],
                ARRAY[timestamp, CAST(temperature AS VARCHAR)]
            ) AS JSON)
        AS json_item
    FROM "データベース名"."テーブル名"
)
SELECT json_item AS json_col
FROM json_items

上記SQLをAthenaから実行します。

こちらの方法でもJSON形式のカラムを作成することができました。

最後に

JSONからキーや値を取り出す例はよくあるのですが、逆のことをしようと思ったときに意外と情報がなかったので、Prestoのドキュメントを参考に試してみました。

同様の変換で困っている方の参考になれば幸いです。