Snowflakeバッチクエリ向け決定論的SQL最適化ツールの設計考察とQueryBrewの示唆

Snowflakeバッチクエリ向け決定論的SQL最適化ツールの設計考察とQueryBrewの示唆.md

Snowflakeバッチクエリ向け決定論的SQL最適化ツールの設計考察とQueryBrewの示唆

1. はじめに（背景と目的）

データウェアハウス（Snowflake）における夜間バッチやETL/ELTパイプラインでは、長期の開発・運用に伴い、重複したCTE、冗長なサブクエリ、プルーニングを阻害するWHERE句、非効率な相関サブクエリなどを含む「乱雑で巨大なSQL」が蓄積しやすい。これらはクエリ実行時間の長期化やウェアハウスコンピュートコストの増大を引き起こす。

昨今の大規模言語モデル（LLM）の進化により、クエリと実行計画（Execution Plan / Query Profile）をLLMに入力して最適化案を生成させるアプローチが模索されている。しかし、LLM単体のアプローチには以下の重大な課題がある：

1. 非決定論性（Non-deterministic）: 実行ごとに生成されるSQLが異なり、CI/CDや自動バッチパイプラインに組み込めない。
2. セマンティクス破壊のリスク: 数百〜数千行の複雑なバッチSQLにおいて、微細な結合条件やNULL処理の解釈ミス（ハルシネーション）により出力結果が変わり、データ整合性を破壊する。
3. 再現性・監査性の欠如: なぜその書き換えが行われたのか、ルールベースでの説明が困難。

本稿では、VLDB 2026の最新研究 QueryBrew（UmbraベースのSQL-to-SQLクエリ最適化サービス）のコードベースおよびアーキテクチャの調査結果を踏まえ、Snowflake環境において**「決定論的（Deterministic）な書き換え」と「安全な等価性検証（Equivalence Verification）」を中核に据えた自前クエリ最適化ツール**の設計アーキテクチャを体系化する。

---

2. QueryBrew（VLDB 2026）のアーキテクチャ調査と示唆

2.1 QueryBrewの基本コンセプト

• 論文: QueryBrew: System-Agnostic SQL-to-SQL Query Optimization (Tobias Schmidt, Thomas Neumann et al. / TUM)
• 内部ノート: [[PVLDB_v19_QueryBrew_System_Agnostic_SQL_Optimization]]
• アプローチ: データベースエンジンと密結合しているクエリオプティマイザをデカップリングし、任意のSQLを入力として受け取り、最先端オプティマイザで最適化された関係代数DAGを構築した後、ターゲットDB（PostgreSQL, ClickHouse, DuckDB, SQL Server等）の方言に合わせた**演算子指向の共通テーブル式（CTE: Common Table Expressions）のSQLとして逆翻訳・出力（Brew）**する。

2.2 ソースコード調査（umbra-db/QueryBrew）から判明した実態

QueryBrewの公開リポジトリ（https://github.com/umbra-db/QueryBrew）のコード解析により、以下の実装実態が確認された：

1. Umbraの外部呼び出し構造: QueryBrew自体（Python/FlaskバックエンドおよびReactフロントエンド）はオーケストレーション層であり、クエリ最適化のコア処理はDockerコンテナ上の Umbraバイナリ（umbradb/umbra:HEAD） へPostgreSQLプロトコル経由で委譲されている。
2. ネイティブ構文 explain (sql, dialect ...): dbms/umbra.py 内で、Umbraネイティブの以下の構文を呼び出すことで、ターゲットDB用の方言CTEクエリを一発生成している：

   res = self._execute(f"explain (sql, dialect {dialect}) {query}", True)

3. Umbraのプロプライエタリ性: Umbraはミュンヘン工科大学（TUM）Thomas Neumann教授の研究プロジェクトであり、オープンソースではない（クローズドソース / Dockerイメージのみ配布）。商用版は CedarDB としてスピンオフされている。

2.3 自前ツール開発への示唆

• Umbra Docker利用の道: 個人利用やPoCとしては最速かつ最強の最適化性能を得られるが、商用プロダクトや社内サービスへの組み込みにはライセンス上の境界が存在する。
• OSSオプティマイザの選択肢: 完全オープンソースで構築する場合、Apache Calcite（Java製、実績多数、RelToSql対応）、Apache Arrow DataFusion（Rust製）、またはAST操作ライブラリ（Python sqlglot）の採用が必要となる。

---

3. Snowflake向け決定論的SQL最適化ツールの設計

3.1 推奨アーキテクチャ: ハイブリッドパイプライン

本ツールでは、完全なブラックボックスLLM化を避け、「静的プロファイリング ➔ 決定論的ASTリライター ➔ LLM補助（複雑箇所のみ） ➔ 決定論的等価性検証」 の4層パイプラインを採用する。

flowchart TD
    RawSQL["乱雑なSnowflake SQL"] --> Profiler["【Layer 1】クエリプロファイル解析<br/>(Spill / フルスキャン / プルーニング阻害検知)"]
    Profiler --> AST["【Layer 2】決定論的ASTルールエンジン (sqlglot)<br/>(構文木・代数レベルの決定論的リライト)"]
    AST --> Router{"複雑な相関副クエリや<br/>再編が必要か？"}
    Router -- No (ルールのみで完結) --> Verifier["【Layer 4】決定論的等価性検証<br/>(EXCEPT検証 / HASH_AGG突合)"]
    Router -- Yes --> LLM["【Layer 3】LLM提案エンジン<br/>(局所的な非ネスト化・CTE再編の提案)"]
    LLM --> Verifier
    Verifier -->|"検証OK (差分0)"| OptimizedSQL["最適化済みSQL (本番反映 / PR作成)"]
    Verifier -->|"検証NG (差分あり)"| Rollback["警告ログ出力 / Layer 2出力へフォールバック"]

Loading

---

3.2 各レイヤーの詳細設計

【Layer 1】クエリプロファイル・ボトルネック静的解析

Snowflakeのクエリ履歴およびプロファイル情報（SYSTEM$EXPLAIN_PLAN_JSON や INFORMATION_SCHEMA.QUERY_HISTORY）を取得し、書き換えのターゲットを特定する。

• Bytes spilled to local/remote storage: メモリ溢れを起こしている巨大JOINや集約ノードの特定。
• Partitions scanned vs total partitions: マイクロパーティション・プルーニングが効いていないフルスキャンの特定。
• Join Explosion: 入力行数に対して出力行数が爆発している直積に近い結合箇所の特定。

【Layer 2】決定論的ASTルールエンジン（Python sqlglot 基盤）

Pythonの sqlglot を用いてSnowflakeの構文木（AST）を走査し、副作用のない純粋な書き換えルールを決定論的に適用する。

1. プルーニング阻害（Non-sargable）WHERE句の平坦化:
   • Bad: WHERE DATE(created_at) = '2026-09-01'
   • Good: WHERE created_at >= '2026-09-01 00:00:00' AND created_at < '2026-09-02 00:00:00'
   • 効果: Snowflakeのメタデータによるマイクロパーティションスキップが正常に動作する。
2. 重複CTE / サブクエリの共通化とインライン化:
   • 同一テーブル・同一フィルタを複数箇所でスキャンしている無駄なCTEを1つに統合。
3. UNION から UNION ALL への安全な置換:
   • 各ブランチの出力が排他的（Disjoint）であることが明らかな場合、高コストな重複排除ソート・ハッシュ処理を排除。
4. サブクエリ・インラインVIEW内の冗長句除去:
   • 集約やウィンドウ関数を伴わないサブクエリ内の ORDER BY や重複した DISTINCT の機械的除去。
5. Snowflake独自構文の最適化:
   • QUALIFY ROW_NUMBER() OVER (...) = 1 形式への集約整理。

【Layer 3】LLM補助エンジン（複雑な局所リライト）

Layer 2の決定論的ルールでは対応が難しい「深くネストした相関副クエリの非相関化（Unnesting）」や「長大なビジネスロジックのCTE分離」についてのみ、プロンプトにクエリ・スキーマ・該当ボトルネック箇所を与えてリライト案を生成させる。

【Layer 4】決定論的等価性検証エンジン（Equivalence Verification）

リライトされたクエリが、元のクエリと1行・1列の狂いもなく完全一致する結果を返すことを決定論的に証明・テストするセーフティネット。

1. EXCEPT 差分ゼロ検証（完全一致テスト）: テスト環境または本番環境のサンプリングデータに対し、以下のクエリを実行：

   WITH original AS (
       -- 元クエリ (または検証用パラメータ適用版)
       <ORIGINAL_QUERY>
   ),
   rewritten AS (
       -- 最適化後クエリ
       <REWRITTEN_QUERY>
   )
   SELECT 'orig_minus_rewritten' AS diff_type, COUNT(*) AS diff_count 
   FROM (SELECT * FROM original EXCEPT SELECT * FROM rewritten)
   UNION ALL
   SELECT 'rewritten_minus_orig' AS diff_type, COUNT(*) AS diff_count 
   FROM (SELECT * FROM rewritten EXCEPT SELECT * FROM original);

   両方の diff_count が 0 の場合のみ、セマンティクスが100%同一であると認定する。
2. HASH_AGG / チェックサム突合（巨大データ向け）: 全量 EXCEPT がコスト高な場合は、全カラムを文字列結合したハッシュ集約値（HASH_AGG）を比較し、高速に同一性を担保する。

---

4. 段階的実装ロードマップ

フェーズ	実装内容	成果物・検証方法
Phase 1 (PoC)	sqlglot を用いたアンチパターン検知＆決定論的リライトCLIの構築（Sargable化、UNION ALL置換、冗長ソート削除）。	過去のバッチクエリ10本に対して適用し、構文エラーゼロを確認。
Phase 2 (検証自動化)	Snowflake Connectorと接続し、EXCEPT 検証スクリプト（Layer 4）を自動実行するテストフレームワークを実装。	元クエリとリライトクエリの実行結果差分が完全0件であることを決定論的に自動検証。
Phase 3 (プロファイル連携)	SYSTEM$EXPLAIN_PLAN_JSON を取得し、リライト前後で推定バイト数やパーティションプルーニング率の改善を定量レポート化。	ウェアハウスでの実行時間・Spill低減率を測定。
Phase 4 (LLMハイブリッド)	複雑な相関副クエリに対してLLMリライトを挟み、Phase 2の検証をパスしたものだけを採用するフォールバック機能の実装。	人手によるクエリチューニング業務の自動化完了。

---

5. まとめと今後の展望

• 決定論の重要性: LLMを全面に押し出すのではなく、「AST解析（sqlglot）による決定論的ルール」を主軸にし、「EXCEPTによる決定論的等価性検証」を安全弁とすることで、本番パイプラインに安心して投入できるクエリオプティマイザが実現できる。
• QueryBrewからの学び: Umbraのようにオプティマイザをサービス化（QOaaS）し、複雑な関係代数プランを「演算子指向CTE」として逆出力する手法は、今後の高度な自動チューニングの究極的なゴールとして極めて示唆に富んでいる。

---

関連資料・リンク

• 内部ノート: [[PVLDB_v19_QueryBrew_System_Agnostic_SQL_Optimization]]
• QueryBrew 公式リポジトリ: github.com/umbra-db/QueryBrew
• Umbra 公式サイト: umbra-db.com
• sqlglot リポジトリ: github.com/tobymao/sqlglot
• Apache Calcite 公式: calcite.apache.org\n