Athenaに思いのほかいろんな関数が実装されていた

自己紹介

ブログ初投稿の新卒です。
普段はサーバーサイドのプログラムを書いたりしています。

はじめに

DBに格納されたデータに対しては、クエリを投げることで効率よく検索したり集計したりできます。しかし、サービスを運営し続けることで溜まっていく膨大なログについては全てDBに入れたりするわけにはいかず、適宜圧縮ファイルにして蓄え続けることになります。

このようにファイルとして書き出してしまうと今度は過去ログを漁りたい時に不便なのですが、Amazon Athenaのようなサービスを使うことで、ファイル群に対してあたかもDBを扱う時のようにクエリを投げることができます。

このサービスについて、S3上のファイルをクエリ実行の度に都度読み込む形式だと聞いて、簡単なSELECT文を投げることができる程度だと勝手に思っていました。
実際にはそんな低機能なサービスではなく、一見すると手元でスクリプトを組んで集計する必要がありそうなデータ形式でも関数の力でなんとかできるケースが多かったので、そういったケースの例をいくつか挙げます。

ケース1 : JSONのパース

とあるところに以下のような形式の行がたくさん格納されたログがありました。

日付時刻|json形式のデータ|その他

このような行データからjson部分のデータを取り出して検索・集計するとき、地道に作業するなら "|" 区切りで2番目の文字列をjsonデコードするスクリプトを書くのですが、このような形式でもAthenaでクエリを投げることが可能です。

そのためには、以下のような方法でjsonデータの値をカラムとして読み込ませます。

• "|" 区切りのcsvとしてテーブルを作成する
• json部分をVARCHAR型とし、jsonデコードして特定のキーを取り出す

json部分のカラム名をjson_strとすると、テーブル作成後に投げるクエリはざっくり以下のような感じになります。

WITH tmp_table_name AS (
  SELECT
    CAST(JSON_EXTRACT(JSON_PARSE(json_str), '$.id') AS INTEGER) AS id,
    CAST(JSON_EXTRACT(JSON_PARSE(json_str), '$.str') AS VARCHAR) AS str
  FROM
    table_name
)
SELECT
  *
FROM
  tmp_table_name
WHERE
  id = 125

JSON_PARSEでパースしてjson型の値を得たあと、JSON_EXTRACTでjqのような記法で中の値を取り出します。
取り出した値を好きな型に変換して別名をつけたテーブルを用意し、以降のクエリでは名前と型のついたテーブルとして扱います。

WITH句を使わなくても同じことはできますが、カラムの情報が必要になる度にCAST(...)を書くことになるのと、別名でGROUP_BY等が使えないので、素直に一度WITHでテーブル変換を噛ませるのが良いと思います。

---

補足1

MySQL5.7以降でも同様の関数が使えるようです。知らなかった...。

補足2

データ全体がjson形式になっているときはSTRUCT型を用いてネストしたデータを取得できるようにする例がいくつも見つかったのですが、この例のようにcsvの中にjsonが含まれる場合はうまくSTRUCT型として認識させられませんでした。
( STRUCT<col1:str,col2:int,...>のように定義したところ、json文字列全体がcol1に入って他のカラムがNULLになった)

---

ケース2 : コンマ区切りのカラムの行展開

とあるところに以下のように複数の値がコンマ区切りで入っているログがありました。

{..., "ids": "1,2,5"}

このような行データからコンマ区切り部分を取り出して検索・集計するとき、地道に作業するならjsonデコードしてidsの中身をsplitするスクリプトを書くのですが、この形式でもAthenaでクエリを投げることが可能です。

以下のようなクエリを書くことで行データとして展開できます。

SELECT
  *
FROM
  table_name
CROSS JOIN UNNEST (
  SPLIT(ids, ',')
) AS tmp_table_name (id)

区切り文字による分割はSPLITというそのまんまな関数があります。返り値は配列です。
分割によってできた配列をCROSS JOIN UNNESTで結合することで、元の行に分割した配列の値 n 個がそれぞれJOINされた n 行の結果が得られます。

CROSS JOINといえばレコード数が膨れ上がるイメージがありますが、このクエリではUNNESTの効果で長さ n の配列に対応する 1 行ごとに結合しています。

ケース3: コンマ区切りの複数カラムの行展開

ケース2と似た内容ですが、以下のようにコンマ区切りの同じ長さの配列が複数個所にあるログがありました。

{..., "item_ids": "1,2,5", "item_amounts": "12,25,125"}

このような場合も、配列の結合部分を以下のようなクエリにすることで、複数配列の対応するインデックスを同じ行に展開できます。

CROSS JOIN UNNEST (
  MAP(
    TRANSFORM(SPLIT(item_ids, ","), v -> CAST(v AS INTEGER)),
    TRANSFORM(SPLIT(item_amounts, ","), v -> CAST(v AS INTEGER))
  )
) AS tmp_table_name (item_id, item_amount)

元の行にcsvの値 n 個がそれぞれJOINされた n 行の結果が得られます。

このクエリで出てきたMAPやTRANSFORM関数は、Pythonでいうところのzip mapにあたる関数です。
ケース2では触れませんでしたが、SPLITの結果は文字列の配列なので、数字として扱いたい場合などはTRANSFORM関数とラムダ関数の組み合わせで各値をCASTさせることができます。

まとめ・感想

思いのほか普通のプログラミング言語のような関数が使えることにびっくりしました。
記事の後ろのほうに関数の一覧ページへのリンクを貼っていますが、数値,文字列,配列などに関する基本的な関数は実装されているようです。

普段からデータ分析などをやっている人は知っているようなのですが、自分のようにアプリのサーバーロジックで MySQL を使って生きている普通のサーバーマンだとあまり知らない内容なのではないかと思います。

調査のタスクなどをやる際には簡単にスクリプトを書いたりして作業を進めていたのですが、こういった関数を使えばクエリを投げるだけで完結するタスクが増えそうです。
今後、自分に集計などのタスクが振られたときは「それってクエリ投げるだけで完結できないかな？」と考えるようにするつもりです。

補足

Athena に焦点を当てた記事になっていますが、同様の関数が実装されているDBやサービスでもできるはずなので、ログの保存先がS3ではない場合でも同じようなことができるサービスがあるか調べるのが良さそうです。

参考

Amazon Athena の Presto 関数
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/functions-operators-reference-section.html

おまけ

「集計処理をクエリだけで終わらせたいけど、大きなテーブルどうしのJOINみたいな重そうなのを投げるのは無理かな？」と思っていましたが、そういった重いクエリが投げられるのもこういったサービスの利点だという話をちらっと聞いたので、やってみました。
→ Athenaで億単位のレコード数のテーブル同士をJOINしてみる