EXP-010｜SQL Column Extractor — 巨大SQLを人間が読む前に解析する

00 — WHY

整形した後にも、読む仕事が残る

自分が書いたものではない巨大SQLを調べる。BIツールが自動生成したSQL、何年も改修されてきたSQL、多数のJOINや何段ものCTEを含むSQL。SELECT項目だけで数百あることもある。

SQL Formatterで整形すれば読みやすくなる。それでも、どのテーブルを使っているのか、出力項目は何個あるのか、この項目はどこから来るのか、JOINは何本あるのかを、人間が追う作業は残る。

いきなり最初の行から精読する前に、構造だけを先に取り出せないか。そこでUTILIZABLEに、6つ目のツールとしてSQL Column Extractorを追加した。

最初に欲しかったのは、SELECT項目とテーブル名の一覧だった。そこから式を壊さずに切り出す処理、クエリごとのAlias、CTEの参照元へと、調べる範囲が広がっていった。

人間が精読する前に、
SQLの地図を作る。

サイトを作ったところからの記録はEXP-006｜UTILIZABLE — データエンジニア向け実用ツールサイトを作るに、新旧データの比較へ進んだ記録はEXP-007｜CSV Diff — ブラウザだけでCSVの差分を比較するにまとめている。今回は、データを取り出すSQLそのものを調べる。

01 — EXAMPLE

SELECTの外側と内側を、一度に見る

初期版はOracleとPostgreSQLのSELECT / WITH SELECTを対象にした。AutoはNVL・Oracle q引用・ILIKE・::・PostgreSQL dollar引用などを手がかりにする。共通構文だけならCommon / undeterminedとし、DBを断定しない。必要ならOracle / PostgreSQLを選ぶ。

Exampleボタンには、次のSQLを入れた。これは説明と検証のために作ったサンプルで、実案件のSQLではない。ORDERSを集計するORDER_SUM CTEを、CUSTOMERへLEFT JOINしている。

WITH ORDER_SUM AS (
    SELECT
        CUSTOMER_ID,
        SUM(AMOUNT) AS TOTAL_AMOUNT,
        MAX(ORDER_DATE) AS LAST_ORDER_DATE
    FROM ORDERS
    WHERE ORDER_DATE >= DATE '2026-01-01'
    GROUP BY CUSTOMER_ID
)
SELECT
    C.CUSTOMER_ID,
    C.CUSTOMER_NAME AS NAME,
    COALESCE(O.TOTAL_AMOUNT, 0) AS TOTAL_AMOUNT,
    O.LAST_ORDER_DATE,
    CASE
        WHEN O.TOTAL_AMOUNT >= 100000 THEN 'HIGH'
        ELSE 'NORMAL'
    END AS CUSTOMER_CLASS
FROM CUSTOMER C
LEFT JOIN ORDER_SUM O
    ON C.CUSTOMER_ID = O.CUSTOMER_ID
WHERE C.ACTIVE_FLG = '1'
ORDER BY TOTAL_AMOUNT DESC;

SUMとMAXで集計し、COALESCEで値を補い、CASEで区分を作る。WHEREとORDER BYもある。単純なカラムだけを列挙した例より、構造抽出で扱いたいものが見える。

Example → Analyze

Output Columns: 5
Tables:         3（全スコープ）
Joins:          1
CTEs:           1
Subqueries:     0
Warnings:       0

結果画面ではSummary、Output Columns、Tables / Aliases、Joins、Query Sections、CTEs、Warningsを分けた。WHERE / GROUP BY / HAVING / ORDER BYなどの句は、存在するものをクエリ単位で表示する。

Tablesの3件は、主クエリのCUSTOMERとORDER_SUM、CTE内のORDERSを合わせた数だ。3つの物理テーブルという意味ではない。ORDER_SUMはCTEとして区別する。

02 — OUTPUT COLUMNS

式と名前を、別々に取り出す

SELECT句は出力項目を順番どおり切り出し、Expression、Alias、Source Alias、Source Column、Referenced Columnsへ分ける。例えばC.CUSTOMER_NAME AS NAMEなら、ASの後ろが出力名で、その前が元の式だ。

Expression:         C.CUSTOMER_NAME
Alias:              NAME
Source Alias:       C
Source Column:      CUSTOMER_NAME
Referenced Columns: C.CUSTOMER_NAME

ASなしのAliasも扱う。ただし、計算式の最後の識別子を誤って出力名にしないようにする。単純なカラム参照ならSource Alias / Source Columnを分け、式の場合は中で参照しているカラムを取り出す。

COALESCE(O.TOTAL_AMOUNT, 0) AS TOTAL_AMOUNT

CASE
    WHEN O.TOTAL_AMOUNT >= 100000 THEN 'HIGH'
    ELSE 'NORMAL'
END AS CUSTOMER_CLASS

どちらも一つのSELECT項目として保持する。COALESCEの引数を別の出力項目にしたり、CASEの途中で切ったりしない。表示するExpressionには、できるだけ元SQLの文字列を使う。

03 — SCANNER

カンマがあっても、そこでは切らない

一覧を作るだけなら、SELECTとFROMの間を取り出してカンマでsplitすればよさそうに見える。しかしCOALESCE(C.NAME, 'UNKNOWN')のカンマは、SELECT項目の区切りではない。

SQLには関数の括弧、文字列、引用識別子、行コメント、ブロックコメント、CASE / END、Subqueryがある。文字列中の'--'や'/*'をコメントとして消してしまうのも困る。

そこで、SQL全体を巨大な正規表現一本で解析する設計にはしなかった。文字を走査して引用とコメントを保護し、括弧の対応位置、CASE / END、句の境界を使って構造を分けるScanner的な処理にした。

文字列・引用識別子 → 一まとまりとして扱う
コメント           → 中の区切り記号を無視する
括弧               → 対応位置と深さを追う
CASE / END         → 内側のカンマで切らない
最上位のカンマ     → SELECT項目を分ける
最上位の句境界     → FROM / WHERE等を分ける

コメントは構造判定から除外し、文字列や引用識別子の内容は不用意に分解しない。式を表示する時はトークンの位置から原文を切り出す。引用識別子の大文字・小文字も表示上そのまま残す。

完全なSQLパーサーを作る前に、まず切ってはいけないところを守る。その処理が、関数・CASE・Subqueryを扱う土台になった。

04 — TABLES AND JOINS

Aliasを、テーブルへ戻す

次はFROM / JOINからSchema、Table、Aliasを抽出する。FROM SALES.CUSTOMER Cなら、SchemaはSALES、TableはCUSTOMER、AliasはCになる。

FROM SALES.CUSTOMER C

Alias Mapping:
C → SALES.CUSTOMER

Column Reference:
C.CUSTOMER_ID → SALES.CUSTOMER.CUSTOMER_ID

この対応があれば、出力カラムのCが何を指しているかを一覧で確認できる。public.customer cや引用されたスキーマ・テーブルも扱い、識別子の照合には選んだDBの規則を使う。

ANSI JOINはJOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、RIGHT OUTER JOIN、FULL JOIN、FULL OUTER JOIN、CROSS JOINを対象にした。種類、右側のテーブルとAlias、ON条件を取り出す。

LEFT JOIN ORDERS O
    ON C.COMPANY_CD = O.COMPANY_CD
    AND C.CUSTOMER_ID = O.CUSTOMER_ID

ANDで続く部分も一つのJOIN条件として保持し、C.COMPANY_CD、O.COMPANY_CD、C.CUSTOMER_ID、O.CUSTOMER_IDの参照を取り出す。次のJOINやWHEREに入るところで区切る。

Oracle旧式外部結合(+)は条件として保持するが、Joins一覧の対象はANSI JOINだ。この違いもWarningで知らせる。

05 — QUERY SCOPE

CTEが入ると、一つの対応表では足りない

単純なSELECTなら、テーブルとAliasの対応だけでも参照元を辿れる。ところがExampleのOは、物理テーブルではなくORDER_SUMというCTEを指している。

さらに、内側と外側で同じAliasが使われるかもしれない。SQL全体に一つだけAliasの対応表を置くと、違うクエリのテーブルを混ぜてしまう。

そこでQuery Scopeを導入した。クエリごとに対応表を持ち、実装上はQ番号で区別する。Exampleは概念的には次の形になる。

Q1 / Main Query
  CUSTOMER
  ORDER_SUM (CTE) ← Alias O

Q2 / ORDER_SUM
  ORDERS
  SUM(AMOUNT) → TOTAL_AMOUNT
  MAX(ORDER_DATE) → LAST_ORDER_DATE

主クエリから見えるO.TOTAL_AMOUNTを、CTEの出力TOTAL_AMOUNTへ対応付ける。そこから内側のSUM(AMOUNT)とORDERSを調べる。CTEを物理テーブルと区別するだけで、Aliasの一覧が参照元を追う仕組みに変わってくる。

複数のCTEや、CTE名の後ろに宣言された出力カラム名も扱う。宣言名とSELECT項目は順番で対応させ、一意に辿れない場合はUnknownにする。

06 — SUBQUERY

内側のSELECTも、別のクエリとして読む

CTEだけでなく、SELECT内Subquery、FROM Subquery、条件内Subqueryも別Scopeとして認識する。括弧の中にSELECTがあっても、外側の出力項目を途中で切らない。

SELECT
    C.CUSTOMER_ID,
    (
        SELECT MAX(O.ORDER_DATE)
        FROM ORDERS O
        WHERE O.CUSTOMER_ID = C.CUSTOMER_ID
    ) AS LAST_ORDER_DATE
FROM CUSTOMER C;

この例ではSubquery全体がLAST_ORDER_DATEのExpressionになる。内側のOと外側のCを区別し、Subqueryがあることも結果に表示する。

FROM Subqueryは、内側が公開した出力カラムを外側から可能な範囲で解決する。一方、複雑なSubqueryの絞り込み条件まで含めた完全な依存関係や行数を保証するものではない。追跡範囲が限られる場合はWarningを出す。

07 — BASIC LINEAGE

出力項目から、元のカラムへ

テーブル一覧ができたら、次に欲しくなるのは出力項目とのつながりだった。Basic Column Lineageとして、出力名 ← Expression ← Physical Table.Columnの対応を表示するようにした。

CUSTOMER_ID
← C.CUSTOMER_ID
← CUSTOMER.CUSTOMER_ID

NAME
← C.CUSTOMER_NAME
← CUSTOMER.CUSTOMER_NAME

TOTAL_AMOUNT
← COALESCE(O.TOTAL_AMOUNT, 0)
← ORDERS.AMOUNT

最後の例ではO.TOTAL_AMOUNTで止まらず、ORDER_SUM内のSUM(AMOUNT)を通してORDERS.AMOUNTへ辿る。式に複数の参照があれば、その参照も示す。

これは完全なData Lineage製品ではない。集計や絞り込みの意味をすべて解釈するものでもない。「この出力項目はどこから来ているのか」を、SQL調査の入口として確認するためのものだ。

08 — UNKNOWN AND WARNING

分からない参照元を、推測で埋めない

参照を辿る仕組みを作ると、それらしく埋められてしまう場面も出てくる。例えば次のCUSTOMER_IDは、どちらのテーブルのカラムだろう。

SELECT CUSTOMER_ID
FROM CUSTOMER C
JOIN ORDERS O
    ON C.CUSTOMER_ID = O.CUSTOMER_ID;

DDLやDBのmetadataなしでは、CUSTOMERとORDERSのカラム定義を確認できない。ここでは片方へ勝手に割り当てず、Source TableをUnknownにしてWarningを表示する。

重複Alias、存在しないAlias、追跡できない公開カラムも同じように扱う。確定できた参照とUnknownが混ざる式では、LineageにもUnknownを残す。部分解析や未対応構文を検出した時は、その範囲を知らせる。

SELECT *とSELECT C.*も認識するが、実カラムへは展開しない。Column expansion requires table metadataという考え方で、必要な情報がないことをWarningにする。

調査の道具としては、「それらしい誤答」を出すより、原文を確認すべき場所が分かる方がいい。分からないことが分かる状態を残したかった。

09 — UI PAUSE

1秒未満でも、UIは止まる

構造が取れるようになったところで、説明用の短いSQLだけでは足りなくなった。性能確認のために4,002行のテストSQLを生成した。ここで使ったのも実案件のSQLではない。

参照カラムを大量に含むケースを、CPUを4倍低速にするエミュレーションで測定した。Worker導入前は解析が約686ms、メインスレッドのタイマー間隔は最大約692msだった。

解析が1秒未満で終わっても、その間にUI側の処理が止まる。約700msの停止は、ブラウザで操作している時には気になる長さだ。処理時間だけを見て、十分軽いとは言えなかった。

そこで、大きな入力だけWeb Workerへ解析を移した。目的は数字を大きく縮めることより、解析中もUIの処理を続けられるようにすることだった。

64 Ki UTF-16コード単位未満
  → メインスレッドで直接解析

それ以上
  → ブラウザ内のWeb Workerで解析

短いSQLでは余計なWorkerの読み込みと起動を避ける。大きなSQLではWorkerを使い、Resetや入力変更で中止できるようにする。古い解析が終わってから、消した結果が戻ってこないことも確認した。

この閾値はUTF-16コード単位数で、入力上限のUTF-8バイト数とは別のものだ。Workerへ渡すことは、SQLをサーバへ送ることではない。同じブラウザの中で処理場所を分けている。

10 — PERFORMANCE

4,002行で、処理と操作を確認する

測定環境はWindows / Microsoft Edge、Playwright headless、ローカルHTTP。生成したテストSQLを使い、通常CPUと4倍低速エミュレーションを分けて確認した。

生成SQLによる測定例。解析単独と結果表示までの時間を区別する
入力	条件	測定範囲	結果
4,002行 / 1,525,805 bytes / 4,000 Output Columns	通常CPU	解析コア単独	Node 約68ms Browser 約80ms
4,002行 / 503,815 bytes / 4,000 Output Columns / 40,000 Referenced Columns	CPU 4倍低速、Worker導入前	解析とメインスレッドの停止	解析約686ms タイマー間隔最大約692ms
同じ参照の多いSQL	CPU 4倍低速、Worker導入後	200行の結果DOM生成まで	約659ms 解析中のタイマー更新あり Reset中止確認済み

659msにはWorkerの処理と結果の受け渡し、200行の結果DOM生成が含まれる。通常CPUで測った80msとは、SQLも測定条件も違うので、直接の速度比較には使えない。

これは単一PCでの実測値だ。端末性能やSQLの構造によって所要時間は変わる。大きな結果のコピー・CSV生成・画面描画はメインスレッドで行うため、そこまで無制限に軽くなるわけではない。

11 — LIMITS AND EXPORT

大きく読めても、無制限にはしない

巨大SQLを調べるため、入力上限は2 MiBにした。これはUTF-8バイト数で判定する。括弧・コメントの入れ子は64段、全Queryの出力項目は合計10,000件までとする。

SQL COLUMN EXTRACTOR / LIMITS

INPUT

2 MiB（UTF-8）

NESTING / TOKENS

64段 / 250,000トークン

OUTPUT / TABLES

全Query合計10,000項目 / 1,000参照テーブル

QUERY SCOPES

Query / CTE / Subquery合計200件

WARNINGS

重複除外、最大1,000件

DISPLAY / EXPORT

200項目ずつ表示 / エクスポートは全件

画面へ一度に出すのは200項目までにした。ブラウザ内で処理できることと、大量のDOMを一度に作ることは別だからだ。

持ち出す時は、Copy Output Columnsでカラム一覧、Copy Tablesでテーブル一覧をTSVにする。Copy as Markdown / Copy as TSVはSummary、出力、Tables、Joins、スコープ別の出力・句、CTE、Warningsを含むレポートになる。

Download CSVは全Output ColumnsをUTF-8 BOM付きで保存する。表示中の200件に限定せず、日本語Alias、引用符、タブや改行も保護する。Excel等へ貼り付け、調査メモや資料へ整理するところまでを考えた。

TSV / CSVにはSpreadsheet Formula Injection対策も入れた。=、+、-、@から始まる値は表計算ソフトで数式として評価される可能性があるため、初期状態では該当セルへアポストロフィを付加する。先頭の空白も考慮し、利用者がOFFにした場合は原値を保持する。

12 — PRIVACY

SQLを、外へ送らずに調べる

調査対象のSQLには、業務上のテーブル名や条件が含まれることがある。Browser-firstを基本に、入力SQLと解析結果をブラウザ内で処理する。UTILIZABLEサーバ、Midnight Benchサーバ、外部API、AIサービス、Analyticsへ送信しない。

入力SQLをCookie、localStorage、IndexedDBへ保存しない。大きな入力を扱うWorkerもブラウザ内にある。Resetは入力と結果を消すが、コピー・ダウンロード済みの内容までは消去しない。

サイトではCloudflare Web Analyticsを利用している。ページ閲覧に伴う通常のアクセス情報と、ツールへ入力したSQLは分けて扱い、入力SQLそのものをAnalyticsへ送信しない。詳しくはUTILIZABLEのPrivacyに記載している。

13 — TESTS AND BOUNDARIES

取れるものと、取れないものを確認する

SQL Column Extractor用に37テストを追加した。既存36件と合わせて73 / 73成功。単純なSELECTだけでなく、壊れやすい境界とUnknownになる場面を確認した。

切り出し：ASあり / なしAlias、関数内のカンマ、CASE、引用識別子、文字列、コメント、Oracle q引用、PostgreSQL dollar引用。
構造と参照：Schema、JOINと複数ON条件、各句、複数CTE、SubqueryのScope、Basic Lineage、曖昧なカラムと重複Alias。
制限と持ち出し：wildcard、再帰CTE、集合演算、Table function、未対応構文、不正入力、入力上限、CSV / TSV / Markdown、数式対策。

実ブラウザでもExample、4種類のコピー、BOM付きCSV、エラー時の古い結果消去、ページ送りと全件出力、Reset、Workerの中止を確認した。PCとスマートフォンの表示、既存5ツールの動作、入力を含む通信が追加されていないことも確認している。

ただし、全テスト成功は完全なSQL Parserになったという意味ではない。SQLは実行しない。DB上の構文検証、実カラムの存在確認、DDLとの照合もしない。対象はSELECT / WITH SELECTを一度に1文で、INSERT / UPDATE / DELETE / MERGEは初期対象外だ。

再帰CTE、集合演算、USING / NATURAL JOIN、Table function、LATERAL、SELECT INTO、名前付きWINDOW、独自句、複雑なSubquery、一部のFROM構造はWarning付きの部分解析になる。集合演算のOutput Columns / Lineageは最初のSELECT枝のみを対象とし、別枝は別Scopeで保持する。

Warningがない結果でも、DB上での正しさを保証するものではない。原文を確認する入口として使う。対応していないものを隠さないことも、この道具の役割に含めている。

14 — NEXT

定義とクエリが、少しずつつながってきた

前回のEXP-009｜DDL Analyzer — CREATE TABLEからテーブル定義を読み解くは、DDLからTable / Column metadataを取り出した。今回はSQLからQuery structure / Basic Lineageを取り出している。

将来、両者を組み合わせればSELECT *の展開、Aliasなしカラムの解決、Column metadataとの対応付けを、もっと正確にできる可能性がある。ただし、現時点では連携していない。次の実験候補として考えている段階だ。

UTILIZABLEのツールは6つになった。

01 — SQL Formatter
02 — SQL IN Generator
03 — CSV Inspector
04 — CSV Diff
05 — DDL Analyzer
06 — SQL Column Extractor

単純な変換からCSV比較、DDL解析、SQL構造解析、Basic Lineageへと範囲は広がった。それでも、大規模な統合製品を急いで作るつもりはない。データエンジニアリングの現場で発生する、小さいけれど面倒な作業をブラウザで片付ける。その方針は変えていない。

今回減らしたかったのは、巨大SQLの中で参照元を探し続ける時間だ。構造を取り出した後で、人間が確認すべきところを読む。その順番を作る道具になった。

SQL Column Extractorを使う →
UTILIZABLEを開く →

Make data usable.
読む前に、構造をつかむ。

SQL Column Extractor— 巨大SQLを人間が読む前に解析する