SEARCH関数やHyperLogLogを調べてみた。地味だけど実務で効く

3つのX投稿が目に留まりました。

SEARCH関数の活用法。クエリ効率化のTips

HyperLogLogが数億のカウントを2%精度で12,000ビットで実現。Redis/Elastic/Clickhouse/Hadoop共通のキーアルゴリズム

Snowflakeのプルーニングに関する論文。「LIMIT pruningが巧妙。一度理解すれば驚異的」

派手なAI機能の裏で、こういう地味な機能が進化している。調べてみました。

SEARCH関数

テキストデータの中から特定のキーワードを検索する機能です。

SELECT * FROM inquiries
WHERE SEARCH('内容', 'キャンセル OR 返品');

全文検索的な使い方ができます。使い道としては：

LIKEやCONTAINSでもできる話ですが、SEARCH関数はオプティマイザに最適化されていてパフォーマンスが良い。大量のテキストデータを扱う場合に差が出ます。

これが一番面白かった。

通常、ユニーク数を数えるにはCOUNT(DISTINCT ...)を使います。でもこれは全データを読む必要がある。1億行なら1億行読む。

-- 正確だけど遅い
SELECT COUNT(DISTINCT user_id) FROM events;

-- 精度98%だけど1000倍速い
SELECT APPROX_COUNT_DISTINCT(user_id) FROM events;

HyperLogLogの仕組みを簡単に説明すると：

12,000ビット（約1.5KB）のメモリ で数億のユニークカウントが可能。精度は約98%。

「このデータ、何件のユニークユーザーがいるの？」にすぐ答えられるかどうかが、データ基盤への信頼に直結します。正確に100万件なのか、推定で99万8千件なのかは、多くのビジネス判断では違いにならない。

Redis、Elasticsearch、ClickHouse、Hadoopでも使われている共通技術です。Snowflakeに限った話ではないですが、SQLの中で自然に使えるのが便利。

Snowflakeのプルーニングに関する論文が共有されていて読んでみました。

SELECT * FROM table LIMIT 10のとき、全データを読まずに済む仕組みです。Snowflakeはデータをマイクロパーティションという単位で管理していて、必要なパーティションだけ読む。

プルーニングが効くと、 スキャン量が劇的に減る = コストが下がる 。

Apache DataFusionでもほぼ全てのテクニックが実装されているとのことで、データ基盤全体のトレンドとして「読むデータ量を減らす」方向に進化しているようです。

Snowflakeの技術を学ぶ場として、Frosty Fridayというコミュニティが面白いです。毎週金曜にSnowflakeのチャレンジ問題が出題されて、日本からも参加している方が配信で盛り上がっている。

「こんな関数あったんだ！」という発見が共有される場で、SWT Tokyo（Snowflake User Group Tokyo）も含めて日本のSnowflakeコミュニティは思ったより活発でした。