SnowflakeのSEARCH関数とHyperLogLogでDWHコストを下げる

最適化は速度ではなくコストの問題です

私がクラウドDWHの相談を受けるとき、最初に伝えるのは「最適化は速度の問題ではなく、コストの問題です」という視点です。クラウドDWHには、オンプレミス時代と決定的に異なる性質があります。 すべてのクエリに値札がついている という事実です。

Snowflakeのコンピュートコストは、クエリが消費するウェアハウスの稼働時間で決まります。つまり、不要なデータを読めば読むほど課金額が上がります。「動けばいい」で書いたSQLが、月末の請求書に直結するわけです。そもそもデータウェアハウスに何を期待すべきかはデータウェアハウスとは何かで整理していますが、その価値を削るのも、日々の何気ないクエリです。

この構造を理解すると、Snowflakeが地味に進化させている機能群の意味が見えてきます。SEARCH関数、HyperLogLog、LIMIT Pruning。どれもプレスリリースの見出しにはなりませんが、 実務の請求額を変える機能 です。

よくある失敗：コストを意識しないクエリ

私が現場でよく見るのは、次の3つのパターンです。

1. 数十億行に対する COUNT(DISTINCT)

-- 全データを読む。正確だが、コストも正確に高い
SELECT COUNT(DISTINCT user_id) FROM events;

ユニークユーザー数を出すだけで、数十億行のフルスキャンが走ります。ダッシュボードの更新が毎時回っていれば、月間のコストは無視できない金額になります。

2. LIKE ‘%keyword%’ の安易な使用

-- インデックスが効かない。全行スキャン確定
SELECT * FROM logs WHERE message LIKE '%timeout%';

先頭ワイルドカードを使った瞬間、Snowflakeのプルーニングは無力化されます。数百GBのログテーブルを毎回フルスキャンする羽目になります。

3. クエリプロファイルを見ない

「結果が返ってくるから問題ない」。私の経験では、この判断が最も高くつきます。Snowflakeのクエリプロファイルには、スキャンしたバイト数、パーティションのプルーニング率、スピルの有無が記録されています。 見ないということは、請求書を見ないのと同じ です。

コスト視点で見るSnowflakeの3機能

SEARCH関数：LIKEを置き換えるコスト削減策

SEARCH関数は、テキスト検索を オプティマイザが最適化できる形 に変換します。

-- コストが高い：プルーニング無効
SELECT * FROM inquiries
WHERE content LIKE '%キャンセル%' OR content LIKE '%返品%';

-- コストが低い：オプティマイザが最適化
SELECT * FROM inquiries
WHERE SEARCH(content, 'キャンセル OR 返品');

構文の違いは小さいものです。しかしLIKEの先頭ワイルドカードがプルーニングを無効にするのに対し、SEARCH関数はSnowflakeの内部インデックスを活用できます。問い合わせログの分析、自由記述欄の分類、大量テキストの検索。こうしたユースケースで スキャン量が桁違いに変わります 。

HyperLogLog：98%の精度で十分なら、コストは1/1000

「このデータセットに何種類のユーザーがいるか」。この質問に正確に答えるには、全行を読む必要があります。しかし多くのビジネス判断において、私は「100万人」と「99万8千人」の差が意味を持たない場面によく遭遇します。

-- 正確だが高コスト：全行スキャン
SELECT COUNT(DISTINCT user_id) FROM events;

-- 98%精度で1/1000のコスト
SELECT APPROX_COUNT_DISTINCT(user_id) FROM events;

HyperLogLogは、ハッシュ値の統計的性質を利用してユニーク数を推定するアルゴリズムです¹。 約1.5KBのメモリ で数億件のユニークカウントを実現します。Redis、Elasticsearch、ClickHouseでも採用されている、実績ある技術です。

ポイントは「精度を落とす」のではなく、 「必要十分な精度を選ぶ」 という判断にあります。毎時更新のダッシュボードに、小数点以下の正確さは要りません。

LIMIT Pruning：読まないデータには課金されない

Snowflakeはデータをマイクロパーティション単位で管理しています。LIMIT句がある場合、必要なパーティションだけを読んで結果を返す仕組みがあります²。

-- プルーニングが効けば、数パーティションで完了
SELECT * FROM large_table
ORDER BY created_at DESC
LIMIT 100;

プルーニングが効くと スキャン量が劇的に減り、課金も比例して下がります 。Apache DataFusionでも同様の最適化が実装されており、「読むデータ量を最小化する」方向はデータ基盤全体のトレンドです。

80/20ルール：コストの源泉を特定する

DWHのコスト最適化で最も効果が高いのは、新機能の導入ではありません。 コストの80%を生んでいる20%のクエリを特定すること です。

Snowflakeのクエリ履歴には、各クエリのスキャンバイト数、実行時間、ウェアハウスの消費クレジットが記録されています。私はこれを週次でレビューするだけで、最適化すべきクエリが見えてくると考えています。

-- コスト上位のクエリを特定する
SELECT query_id, query_text,
       bytes_scanned,
       total_elapsed_time,
       partitions_scanned,
       partitions_total
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY bytes_scanned DESC
LIMIT 20;

この20件を改善するだけで、月間コストが数十パーセント下がることは珍しくありません。最新のAI機能を検討する前に、私はまずこのクエリを実行することをおすすめしています。上位クエリの特定と継続的な改善の進め方は遅いクエリがコストを膨らませる仕組みとFinOps で詳しく扱っています。

見えない最適化がROIを決める

クラウドDWHの本当のROIは、導入時の機能比較表では測れません。日々のクエリが生むコストと、それを抑制する地味な最適化の積み重ねで決まります。

SEARCH関数でLIKEを置き換える。APPROX_COUNT_DISTINCTで十分な場面を見極める。クエリプロファイルを定期的に確認する。どれも派手さはありませんが、 請求書に反映される改善 です。同じコスト効率を別のDWHと比べたいときは BigQueryとSnowflakeの違いも参考になります。

データ基盤の価値は、導入した瞬間ではなく、運用の中で可視化されます。その可視化を支えるのは、プレスリリースに載る機能ではなく、こうした地味な最適化技術だと私は考えています。

Philippe Flajolet, Éric Fusy, Olivier Gandouet, Frédéric Meunier. “HyperLogLog: the analysis of a near-optimal cardinality estimation algorithm”. DMTCS Proceedings, 2007. ↩
Snowflake Documentation. “Understanding & Using Query Profile”. Pruning and partition elimination. ↩