❄️

Snowflake「検索最適化サービス(SOS)」の機能を実際に検証してみた

2025/11/05に公開

🚀 はじめに

以前の記事では、Snowflakeのパフォーマンスチューニングの全体像として、ウェアハウスのサイジング（スケールアップ/アウト）やクラスタリングキーについて記事にしました。

クラスタリングは、「WHERE date BETWEEN '2024-01-01' AND '2024-01-31'」のような範囲（レンジ）スキャン を高速化する非常に強力な手法です。

しかし、もしあなたのクエリが「WHERE user_id = 'ABC-12345'」や「WHERE email LIKE '%@example.com'」のような、特定の値を「点」で探す（ポイントルックアップ） クエリだったらどうでしょう？
TB（テラバイト）級のテーブルからたった1行のレコードを探すためにテーブル全体をスキャンするのは非効率です。

この記事では、この「ポイントルックアップ」と「サブストリング（部分文字列）検索」を劇的に高速化するために設計された、検索最適化サービス (Search Optimization Service: SOS) について、その仕組み、クラスタリングとの違い、コスト、実践的なデモまでを整理したので以下解説をしていきます。

🔍 検索最適化サービス (SOS) とは？

検索最適化サービス (SOS) は、Snowflakeが提供するEnterprise Edition以上の機能です。
これは、テーブルのデータとは別に、「検索アクセスパス (Search Access Path)」と呼ばれる最適化されたデータ構造をバックグラウンドで構築・維持するマネージドサービスです。

イメージとしては以下のようなイメージ（と私は理解、、、）

アナロジー：「本の索引」 vs 「章立て」

クラスタリングとSOSの違いを理解するために、巨大な技術書を想像してください。

クラスタリング (Clustering):
本（テーブル）のページ自体を章立て順（例: 日付順）に並べ替えることです。「5章（5月）のデータ」を探すのは高速ですが、「"RBAC" という単語が出てくるページ」を探すのは大変です。
検索最適化サービス (SOS):
本の巻末にある「索引（インデックス）」を作成することです。索引（検索アクセスパス）を見れば、「"RBAC" という単語は 150, 302, 512 ページにある」と即座に分かり、該当ページ（マイクロパーティション）だけを直接スキャンできます。

SOSが高速化できるクエリタイプ

SOSは、以下の4種類の検索（Supported Queries）を高速化するために設計されています。

等価検索 (EQUALITY): WHERE col = <値> や WHERE col IN (<値1>, <値2>)
部分文字列検索 (SUBSTRING): LIKE '%term%', ILIKE 'PREFIX%', RLIKE '.*regex.*', CONTAINS, STARTSWITH, ENDSWITH など。
地理空間検索 (GEO): ST_CONTAINS, ST_WITHIN などの地理空間関数
全文検索 (FULL_TEXT): SEARCH / SEARCH_IP 関数により、トークン化（アナライザー）を伴う全文検索を最適化します。

コスト構造

SOSは、Snowflakeの他のサーバレス機能と同様に、2種類のコストが発生します。

ストレージコスト:
「検索アクセスパス」自体を保持するためのストレージ料金が発生します。
メンテナンスコスト（サーバーレス）:
元テーブルに対してDML（INSERT/UPDATE/DELETE）が実行されるたびに、検索アクセスパスを最新の状態に保つための自動メンテナンス（コンピュート） が実行されます。このメンテナンス処理は、ウェアハウスとは別 のサーバーレスクレジットを消費します。

3. 最重要：クラスタリング vs. 検索最適化 (SOS)

この2つの機能は、目的が全く異なるため、競合するものではなく、補完するものです。

比較軸	クラスタリングキー (Clustering Key)	検索最適化サービス (SOS)
主な目的	範囲スキャン (Range Scan) の高速化	ポイントルックアップ / サブストリングの高速化
得意なクエリ例	`WHERE date > '2024-01-01'` `WHERE id BETWEEN 100 AND 200`	`WHERE user_id = 'A123'` `WHERE email LIKE '%@ex.com'`
テキスト検索	(対象外)	SUBSTRING: `LIKE`, `CONTAINS` 等 (文字列パターン) FULL_TEXT: `SEARCH`, `SEARCH_IP` (トークン化)
仕組み	テーブルデータ（MP）自体を並べ替える	検索アクセスパスという別構造を構築
設定対象	テーブル全体	特定の列（複数列・複数タイプ指定可）
コスト	自動クラスタリングのメンテナンスコスト（サーバーレス）	ストレージコスト（パス）＋メンテナンスコスト（サーバーレス）
Edition	Standard Edition から利用可	Enterprise Edition 以上

4. SOSの使い方と監視

必要な権限

SOSを設定・管理するには、以下の権限が必要です。

対象テーブルに対する OWNERSHIP 権限 （設定の追加/変更/削除に必須）
対象スキーマに対する ADD SEARCH OPTIMIZATION 権限

権限の委譲は計画的に行う必要があります。

ステップ1: テーブルで検索最適化を有効にする

ALTER TABLE を使い、対象のテーブルに対して検索最適化を有効にします。実務では、コストとパフォーマンスの観点から、対象の列と検索タイプを明示的に指定することが推奨されます。

-- 権限を付与 (スキーマ所有者またはACCOUNTADMINで実行)
GRANT ADD SEARCH OPTIMIZATION ON SCHEMA SOS_SCHEMA TO ROLE SYSADMIN;
-- (テーブルをSYSADMINで作成した場合、OWNERSHIPは既にSYSADMINが持っています)

-- 構文1: 列と検索タイプを明示的に指定 (推奨)
ALTER TABLE my_large_table
ADD SEARCH OPTIMIZATION ON EQUALITY(user_id, email), SUBSTRING(log_message), FULL_TEXT(document_body, ANALYZER => 'english');

-- 構文2: 列を指定せず有効化 (非推奨)
-- 既定では EQUALITY が該当データ型の全列（semi-structured / GEOGRAPHY を除く）に設定されます。
ALTER TABLE my_large_table ADD SEARCH OPTIMIZATION;

ステップ2: 構築の進捗と設定を監視する

SOSを有効にすると、バックグラウンドで検索アクセスパスの構築が始まります。この進捗は SHOW TABLES で確認できます。

SHOW TABLES LIKE 'my_large_table';
-- 実行結果の以下の列を確認:
-- search_optimization = ON
-- search_optimization_progress = 100.0%

search_optimization_progress が 100.0% になるまで待つ必要があります。（大規模テーブルの場合、数時間かかることもあり、進捗 100% までは効果が漸増します）

現在の設定内容は DESCRIBE コマンドで確認できます。

-- 'DESC' は 'DESCRIBE' のエイリアスです
DESC SEARCH OPTIMIZATION ON my_large_table;

ステップ3: コストの見積もりと監視

SOSは追加コストが発生するため、事前に見積もり、継続的に監視することが重要です。

-- 1. 構築・維持コストの見積もり (有効化する前に実行)
-- 見積もりたい列とタイプを指定します
WITH est AS (
  SELECT PARSE_JSON(
    SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(
      'my_large_table',
      'ON EQUALITY(user_id), SUBSTRING(log_message)'
    )
  ) AS j
)
SELECT
  cp.value:name::string                 AS component,      -- 例: 'storage', 'build', 'maintenance'
  cp.value:costs[0].value::float        AS value,
  cp.value:costs[0].unit::string        AS unit            -- 例: 'CREDITS', 'TB'
FROM est,
LATERAL FLATTEN(input => est.j:costPositions) cp;


-- 2. 実際のコスト監視 (ACCOUNT_USAGE スキーマ)
-- SERVICE_TYPE = 'SEARCH_OPTIMIZATION' でフィルタします
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY
WHERE SERVICE_TYPE = 'SEARCH_OPTIMIZATION'
ORDER BY USAGE_DATE DESC;

-- 3. より詳細な機能専用の履歴ビュー
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.SEARCH_OPTIMIZATION_HISTORY
ORDER BY START_TIME DESC;

-- 4. SOSによるスキャン削減効果の可視化
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.SEARCH_OPTIMIZATION_BENEFITS
ORDER BY USAGE_DATE DESC;

ステップ4: クエリプロファイルで効果を確認する

SOSが構築された後、対象のクエリを実行し、Snowsightのクエリプロファイルを確認します。
EXPLAIN コマンドで事前に確認することも可能です。

クエリプランに「Search Optimization Access」（スペースあり）というノードが表示されていれば、SOSが正しく利用されています。

もしこのノードが表示されず、TableScan のままであれば、SOSが使われていない（構築中、またはクエリがSOSの対象外である）ことを意味します。

ステップ5: 無効化と変更（運用）

検証後や要件変更時にSOSを無効化、または変更する方法です。

-- 1. SOSを無効化する
ALTER TABLE my_large_table DROP SEARCH OPTIMIZATION;

-- 2. SOSの定義を変更する (DROP -> ADD で再定義)
ALTER TABLE my_large_table DROP SEARCH OPTIMIZATION;
ALTER TABLE my_large_table
  ADD SEARCH OPTIMIZATION ON EQUALITY(user_id), SUBSTRING(error_code);
  
-- 3. SOSの定義をアトミックに変更する (MODIFY)
-- (差分変更や再構築コストが発生する場合があります)
ALTER TABLE my_large_table
  MODIFY SEARCH OPTIMIZATION ON EQUALITY(user_id), SUBSTRING(error_code);

5. DEMO：公式ガイド (OPENALEX_WORKS_INDEX) に学ぶSOSの実践

この記事のデモは、Snowflake公式の「Getting Started with Search Optimization」ガイドに沿って進めます。このガイドでは、Snowflake Marketplaceの巨大な無料データセット OPENALEX_WORKS_INDEX（約2.6億行）を使用します。

ステップ1: セットアップとデータ取得

-- 1. ロールとウェアハウスの設定 (ACCOUNTADMIN推奨)
USE ROLE ACCOUNTADMIN;
-- (MY_WH の部分はご自身のWH名に置き換えてください)
USE WAREHOUSE MY_WH;
ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE = 'SMALL' AUTO_SUSPEND = 60;

-- 2. Snowflake Marketplace からデータを取得
-- (UIから "Snowflake Public Data (Free)" を検索し、"Get Data" をクリック)
-- (データベース名は 'SNOWFLAKE_PUBLIC_DATA_FREE' のままにします)

-- 3. デモ用の新しいDBとスキーマを作成
CREATE OR REPLACE DATABASE LLM_TRAINING_SO;
USE DATABASE LLM_TRAINING_SO;
CREATE SCHEMA Public_Data;
USE SCHEMA Public_Data;

ステップ2: テスト用テーブルの作成

共有データ（SNOWFLAKE_PUBLIC_DATA_FREE）にはSOSを設定できないため、テーブルを自分のデータベースにコピーします。

-- ウェアハウスを一時的にスケールアップ
ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE = '4X-LARGE';

-- 巨大テーブルをコピー (実行に時間がかかります)
CREATE OR REPLACE TABLE OPENALEX_WORKS_INDEX AS
SELECT * FROM SNOWFLAKE_PUBLIC_DATA_FREE.PUBLIC_DATA_FREE.OPENALEX_WORKS_INDEX;

-- ウェアハウスをテスト用にスケールダウン
ALTER WAREHOUSE MY_WH SET WAREHOUSE_SIZE = 'X-SMALL';

ステップ3: SOSなしでクエリ実行（キャッシュ無効化）

クエリの純粋なパフォーマンスを測るため、セッションの結果キャッシュを無効にし、WHキャッシュをクリアしてから実行します。

-- キャッシュを無効化
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
ALTER WAREHOUSE MY_WH SUSPEND;
ALTER WAREHOUSE MY_WH RESUME;

-- クエリ1: ポイントルックアップ (NUMBER)
SELECT * from LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX
where mag_work_id = 2240388798;

-- クエリ2: サブストリング (VARCHAR)
SELECT * from LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX
where work_title ilike 'Cross-domain applications of multimodal human-computer interfaces';

-- クエリ3: サブストリング (VARIANT)
SELECT * from LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX
where WORK_PRIMARY_LOCATION:source.display_name ilike 'Eco-forum';

-- クエリ4: ポイントルックアップ (VARIANT)
SELECT * from LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX
where WORK_PRIMARY_LOCATION:source.issn_l = '2615-6946';

X-SmallのWHでは、これらのクエリは数分単位の時間がかかり、クエリプロファイルではほぼ100%のパーティションをスキャン（TableScan）していることがわかります。

ステップ4: コストの見積もりとSOSの有効化

まずコストを見積もり、その後、4つのクエリに対応するSOSを（4つの異なるALTER TABLE文で）有効にします。

-- 1. コスト見積もり
SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS(
  'LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX',
  'EQUALITY(MAG_WORK_ID),EQUALITY(WORK_PRIMARY_LOCATION:source.issn_l),SUBSTRING(WORK_TITLE),SUBSTRING(WORK_PRIMARY_LOCATION:source.display_name)'
) AS estimate_json;

-- 2. 必要な権限を付与 (ACCOUNTADMINまたはスキーマ所有者で実行)
GRANT ADD SEARCH OPTIMIZATION ON SCHEMA LLM_TRAINING_SO.Public_Data TO ROLE ACCOUNTADMIN;
-- (公式ガイドに従いACCOUNTADMINを使用)

-- 3. SOSを列ごとに有効化
ALTER TABLE LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX
  ADD SEARCH OPTIMIZATION ON EQUALITY(MAG_WORK_ID);

ALTER TABLE LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX
  ADD SEARCH OPTIMIZATION ON SUBSTRING(WORK_TITLE);

ALTER TABLE LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX
  ADD SEARCH OPTIMIZATION ON SUBSTRING(WORK_PRIMARY_LOCATION:source.display_name);
  
ALTER TABLE LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX
  ADD SEARCH OPTIMIZATION ON EQUALITY(WORK_PRIMARY_LOCATION:source.issn_l);

ステップ5: 構築の監視とSOSありでの再実行

構築が完了するまで待ちます（公式ガイドでは約2分とありますが、環境により変動します）。

-- 1. 構築の進捗と設定を確認 (active が true になるまで待つ)
DESCRIBE SEARCH OPTIMIZATION ON LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX;

-- 2. キャッシュを再度クリア
ALTER SESSION SET USE_CACHED_RESULT = FALSE;
ALTER WAREHOUSE MY_WH SUSPEND;
ALTER WAREHOUSE MY_WH RESUME;

-- 3. SOSありでクエリを再実行
-- クエリ1: ポイントルックアップ (NUMBER)
SELECT * from LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX where mag_work_id = 2240388798;

-- クエリ2: サブストリング (VARCHAR)
SELECT * from LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX where work_title ilike 'Cross-domain applications of multimodal human-computer interfaces';

-- クエリ3: サブストリング (VARIANT)
SELECT * from LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX where WORK_PRIMARY_LOCATION:source.display_name ilike 'Eco-forum';

-- クエリ4: ポイントルックアップ (VARIANT)
SELECT * from LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX where WORK_PRIMARY_LOCATION:source.issn_l = '2615-6946';

数分かかっていたクエリが、桁違いに短縮されるはずです（公式ガイドの例では、数分 $\rightarrow$ 数秒）。
クエリプロファイルを確認すると、TableScan の代わりに Search Optimization Access ノードが使われ、スキャンしたパーティション数が劇的に（例: 15953 $\rightarrow$ 1）減少していることが確認できます。

実際の検証時の結果は以下の通りでした

ステップ6: （補足）SOSが効かないクエリ

SOSは選択率（Selectivity）が低い（＝結果セットが小さい）クエリに最適です。逆に、検索語が一般的すぎて大量の行が返るクエリでは、SOSが使われない（または効果が薄い）場合があります。

-- 例: 'Reactions Weekly' は非常に多くの行にヒットするため、SOSが使われにくい
select * from LLM_TRAINING_SO.Public_Data.OPENALEX_WORKS_INDEX  
  where 
    WORK_PRIMARY_LOCATION:source.display_name ilike 'Reactions Weekly';

6. （応用編）SOSの高度な機能

SOSは進化を続けており、以下のような高度なケースにも対応しています。利用条件の詳細は公式ドキュメントを確認してください。

JOINの高速化: 等価結合キー（例: Fact.user_id = Dim.user_id）の Dim.user_id 側にSOSを設定しておくと、JOIN時のプルーニングが効き、パフォーマンスが向上する場合があります。
スカラー関数越しの最適化: 一部の決定的なスカラー関数（例: SHA2(email) = '...'）越しの等価比較でも、SOSが効くように設定可能です。
半構造化データ: VARIANT 型の特定のキー（例: json_data:user_id）に対しても EQUALITY や SUBSTRING を設定できます。

😌 おわりに

検索最適化サービス（SOS）は、Snowflakeのパフォーマンスチューニングにおいて非常に強力な武器です。しかし、その特性を正しく理解することが重要です。

SOSは「ポイントルックアップ」と「部分文字列検索」のためのものです。
クラスタリングは「範囲検索」のためのものです。

WHERE date > ... のようなクエリが遅い場合は、SOSを有効にしても効果はありません。その場合はクラスタリングキーを見直す必要があります。

逆に、WHERE user_id = '...' のようなクエリが遅い場合、クラスタリングキーを user_id に設定しても（カーディナリティが高すぎるため）効果は限定的です。この場合、SOSが劇的な効果を発揮します。

Enterprise Edition以上を利用していて、TB級のテーブルに対するポイントルックアップに悩んでいる場合は、ぜひSOSの導入を検討してみるのが良いかもしれないです。

📚 参考出典

Snowflake公式: 検索最適化サービス (SOS)
Snowflake公式: 最適化の恩恵を受けるクエリ (SOS対象クエリ詳細)
Snowflake公式デモ: Getting Started with Search Optimization
Snowflakeドキュメント: ALTER TABLE (SEARCH OPTIMIZATION) (構文、FULL_TEXT, MODIFY)
Snowflakeドキュメント: 検索最適化の権限 (ADD SEARCH OPTIMIZATION権限)
Snowflakeドキュメント: 検索最適化のコストの見積もりと管理 (SYSTEM$ESTIMATE..., ACCOUNT_USAGE)
Snowflakeドキュメント: ACCOUNT_USAGE.METERING_DAILY_HISTORY (SERVICE_TYPE = SEARCH_OPTIMIZATION)
Snowflakeドキュメント: ACCOUNT_USAGE.SEARCH_OPTIMIZATION_HISTORY
Snowflakeドキュメント: ACCOUNT_USAGE.SEARCH_OPTIMIZATION_BENEFITS
Snowflakeドキュメント: クエリプロファイル (Search Optimization Accessノード)
Snowflakeドキュメント: 検索最適化の監視 (SHOW TABLESの進捗, DESCRIBE)
Snowflakeドキュメント: パラメータ USE_CACHED_RESULT (キャッシュ無効化)
Snowflakeドキュメント: 高度な機能 (JOIN, スカラー関数, 半構造化データ)
Snowflakeドキュメント: テキスト検索 (FULL_TEXT) (SEARCH/SEARCH_IP関数)
Snowflakeドキュメント: SUBSTRING検索の制約 (短い文字列の制約)
Zenn: Snowflake「クエリが遅い」を撲滅！パフォーマンスチューニングの体系的アプローチ (クラスタリングとの比較)