Athenaの前方一致検索、LIKEよりstarts_with()の方がいい

  • AWS
  • Athena
  • SQL

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

やりたかったこと

AthenaでログテーブルのURIパスカラムに対して前方一致検索をしたかった。example_uri_pathカラムが/example/pathで始まるレコードだけ抽出する、みたいな話。

LIKEでやる方法

一番シンプルなのはLIKE演算子。

SELECT *
FROM example_table
WHERE example_uri_path LIKE '/example/path%';

%は0文字以上の任意文字列にマッチするから、末尾に付ければ前方一致になる。これはPresto/Trinoに限らず標準SQLの話。

starts_with()でやる方法

AthenaのエンジンはPresto/Trinoベースで、starts_with()という関数がある。

SELECT *
FROM example_table
WHERE starts_with(example_uri_path, '/example/path');

第一引数の文字列が第二引数で始まっていればtrueを返す、それだけの関数。

どっちを使うか

結論、単純な前方一致ならstarts_with()の方がいい。理由は2つ。

  • 意図が明確。「前方一致してるかどうか」を見たいだけなのに、LIKEだとパターンマッチの記法を読み解く手間が発生する
  • ワイルドカード文字のエスケープを気にしなくていい。パス文字列に%や_が含まれてる場合、LIKEだとエスケープしないと誤爆する（LIKE '/ex_ample%' ESCAPE '\'みたいに書く必要がある）。starts_with()は単純な文字列比較なので関係ない

逆にLIKEが必要になるのは、前方一致じゃなくて中間一致や複雑なパターンマッチをしたい時ぐらい。

パフォーマンスについて

どっちを使ってもAthenaはスキャンしたデータ量に対して課金されるのは変わらない。前方一致検索自体はインデックスが効くものではなく、基本フルスキャンになる。大量データを扱うならパーティショニングで事前にスキャン範囲を絞る方が効く。実機でスキャン量やレイテンシの差を比較検証したわけではないので、starts_with()とLIKEでそのあたりに差が出るかどうかは未確認。