Spark SQL完全ガイド｜Databricks試験対策に必須のSQL構文

Databricks認定試験（DEA・DEP・Spark Developer）では、Spark SQLの正確な構文知識が合否を分けます。特にData Engineer AssociateのELT with Spark SQL & Python（29%）ドメインでは、SQLクエリの読み書きスキルが直接問われます。この記事では、CREATE TABLE・MERGE INTO・Window関数・CTE・Delta Lake操作・高階関数・PIVOT/UNPIVOTまで、試験に出る構文を実行可能なSQLコード付きで体系的に整理しています。

試験頻出度マップ

以下は、Databricks認定試験における各Spark SQL構文の出題頻度を整理した表です。学習の優先順位付けに活用してください。

テーブル操作

Delta LakeテーブルのCREATE文は3つの主要パターンがあります。マネージドテーブル・外部テーブルの違いと、DROP TABLE時のデータ削除の有無を正確に把握しましょう。

CREATE TABLE USING DELTA（マネージドテーブル）

LOCATIONを指定しないCREATE TABLE文はマネージドテーブルを作成します。Unity Catalogがデータの格納場所を管理し、DROP TABLE実行時にメタデータ・データファイルの両方が削除されます。

-- マネージドテーブル: スキーマ定義で作成
CREATE TABLE catalog_name.schema_name.orders (
  order_id   BIGINT,
  customer_id BIGINT,
  order_date DATE,
  amount     DECIMAL(10, 2),
  status     STRING
) USING DELTA
COMMENT 'Customer order transactions'
TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact'   = 'true'
);

CREATE TABLE ... LOCATION（外部テーブル）

LOCATION句で外部ストレージのパスを指定すると外部テーブルになります。DROP TABLEを実行してもメタデータのみ削除され、データファイルは外部ストレージに残ります。

-- 外部テーブル: データは指定パスに格納
CREATE TABLE catalog_name.schema_name.events (
  event_id   BIGINT,
  event_type STRING,
  event_ts   TIMESTAMP,
  payload    STRING
) USING DELTA
LOCATION 's3://my-bucket/bronze/events';

CREATE OR REPLACE TABLE & CTAS

CREATE OR REPLACE TABLE（CORT）は既存テーブルを上書きで再作成します。CTASはSELECTの結果からスキーマを自動推論してテーブルを作成するため、カラム定義が不要です。

-- CTAS: SELECTの結果をテーブル化（スキーマ自動推論）
CREATE OR REPLACE TABLE silver.daily_summary AS
SELECT
  order_date,
  COUNT(*)          AS order_count,
  SUM(amount)       AS total_amount,
  AVG(amount)       AS avg_amount
FROM bronze.orders
WHERE status = 'completed'
GROUP BY order_date;

一時ビュー vs グローバル一時ビュー

-- セッションスコープ（セッション終了で消滅）
CREATE OR REPLACE TEMP VIEW v_active_users AS
SELECT * FROM users WHERE is_active = true;

-- クラスタースコープ（global_temp.で参照）
CREATE OR REPLACE GLOBAL TEMP VIEW v_summary AS
SELECT region, COUNT(*) AS cnt FROM sales GROUP BY region;

SELECT * FROM global_temp.v_summary;

データ操作（INSERT / COPY INTO）

INSERT INTO vs INSERT OVERWRITE

INSERT INTOは既存データに追記します。INSERT OVERWRITEはテーブル（またはパーティション）の既存データを削除してから書き込みます。Delta Lakeではパーティション指定の有無で動作が変わる点に注意してください。

-- 追記: 既存データは保持される
INSERT INTO silver.orders
SELECT * FROM bronze.orders_raw
WHERE process_date = current_date();

-- 上書き: テーブル全体を置換
INSERT OVERWRITE silver.orders
SELECT * FROM bronze.orders_raw;

-- パーティション単位の上書き（動的パーティション上書き）
INSERT OVERWRITE silver.orders
PARTITION (order_date)
SELECT order_id, customer_id, amount, status, order_date
FROM bronze.orders_raw;

COPY INTO

外部ファイル（CSV/JSON/Parquet等）からDelta Lakeテーブルにデータをロードするコマンドです。処理済みファイルを自動追跡し、同じファイルの重複取り込みを防ぐ冪等性を持ちます。ファイル数が数千件程度の小規模バッチ向けで、大規模・継続的な取り込みにはAuto Loaderが推奨されます。

COPY INTO silver.transactions
FROM 's3://data-lake/landing/transactions/'
FILEFORMAT = JSON
FORMAT_OPTIONS (
  'inferSchema'  = 'true',
  'mergeSchema'  = 'true',
  'dateFormat'   = 'yyyy-MM-dd'
)
COPY_OPTIONS (
  'mergeSchema' = 'true',
  'force'       = 'false'   -- true にすると重複取り込み防止を無効化
);

MERGE INTO（試験最頻出）

MERGE INTOはソーステーブルとターゲットテーブルをON条件でマッチングし、WHEN MATCHED / WHEN NOT MATCHED / WHEN NOT MATCHED BY SOURCEの3分岐でINSERT・UPDATE・DELETEを一括実行するコマンドです。Databricks試験全体で最も出題頻度の高いSQL構文です。

基本Upsertパターン

MERGE INTO gold.customers AS target
USING staging.customer_updates AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
  UPDATE SET
    target.name       = source.name,
    target.email      = source.email,
    target.updated_at = current_timestamp()
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email, created_at, updated_at)
  VALUES (source.customer_id, source.name, source.email,
          current_timestamp(), current_timestamp());

条件付きMERGE & DELETE分岐

MERGE INTO gold.products AS t
USING staging.product_feed AS s
ON t.product_id = s.product_id
-- 条件付きUPDATE: 更新日が新しい場合のみ
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET *
-- ソースに存在しない行を論理削除
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET t.is_active = false, t.deleted_at = current_timestamp()
-- 新規行はINSERT
WHEN NOT MATCHED BY TARGET THEN
  INSERT *;

SCD Type 2の実装パターン

SCD Type 2は変更履歴を保持するディメンション管理手法です。既存レコードのend_dateを設定して無効化し、新バージョンの行をINSERTします。

MERGE INTO dim.customers AS t
USING (
  SELECT s.*, true AS is_new
  FROM staging.customers s
  JOIN dim.customers d
    ON s.customer_id = d.customer_id
   AND d.is_current = true
   AND (s.name <> d.name OR s.email <> d.email)
  UNION ALL
  SELECT s.*, true AS is_new
  FROM staging.customers s
  WHERE NOT EXISTS (
    SELECT 1 FROM dim.customers d
    WHERE d.customer_id = s.customer_id
  )
) AS source
ON t.customer_id = source.customer_id AND t.is_current = true
WHEN MATCHED THEN
  UPDATE SET t.is_current = false, t.end_date = current_date()
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email, start_date, end_date, is_current)
  VALUES (source.customer_id, source.name, source.email,
          current_date(), NULL, true);

Delta Lake操作コマンド

OPTIMIZE & ZORDER

OPTIMIZEは小さなファイルを統合して読み取り性能を向上させるCompactionコマンドです。ZORDER BYを併用すると、指定カラムの値でデータをコロケーション（同じカラム値のデータを物理的に近くに配置）し、フィルタクエリのスキップ効率を高めます。

-- 基本Compaction
OPTIMIZE gold.sales;

-- ZORDERでデータをコロケーション
OPTIMIZE gold.sales ZORDER BY (region, product_id);

-- WHERE句で対象パーティションを限定
OPTIMIZE gold.sales
WHERE order_date >= '2026-01-01'
ZORDER BY (customer_id);

VACUUM

現在のテーブルバージョンから参照されていない古いデータファイルを物理削除し、ストレージコストを削減します。削除されたバージョンのTime Travelは使えなくなります。

-- デフォルト: 168時間（7日）より古い不要ファイルを削除
VACUUM gold.sales;

-- 保持期間を明示的に指定
VACUUM gold.sales RETAIN 720 HOURS;  -- 30日間

-- DRY RUN: 削除対象ファイルの一覧のみ表示（削除しない）
VACUUM gold.sales DRY RUN;

DESCRIBE HISTORY & DESCRIBE DETAIL

-- トランザクション履歴（バージョン・操作・タイムスタンプ）
DESCRIBE HISTORY gold.sales;
DESCRIBE HISTORY gold.sales LIMIT 5;

-- テーブル詳細情報（ファイル数・サイズ・パーティション等）
DESCRIBE DETAIL gold.sales;

RESTORE TABLE & Time Travel

-- 特定バージョンに復元（実際にデータを戻す）
RESTORE TABLE gold.sales TO VERSION AS OF 12;

-- タイムスタンプ指定で復元
RESTORE TABLE gold.sales TO TIMESTAMP AS OF '2026-03-20 10:00:00';

-- Time Travel: 過去バージョンを参照のみ（復元しない）
SELECT * FROM gold.sales VERSION AS OF 12;
SELECT * FROM gold.sales TIMESTAMP AS OF '2026-03-20';

-- Time Travel構文でのテーブル指定（@記法）
SELECT * FROM gold.sales@v12;

Window関数

Window関数はGROUP BYのように行を集約せず、各行を保持したまま集計・順位付け・前後行参照を行います。OVER句でパーティションと並び順を指定します。

ランキング関数の比較

以下の表は、同じデータセット（department内のsalary降順）で各関数の出力がどう異なるかを示しています。

BobとCarolが同額85000の場合: ROW_NUMBERは任意の順で2,3を振ります。RANKは両者に2を付け、次を4（3をスキップ）にします。DENSE_RANKは両者に2を付け、次を3（スキップしない）にします。

ROW_NUMBERによる重複排除

-- 各顧客の最新注文のみ取得（CTE + ROW_NUMBERパターン）
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC
    ) AS rn
  FROM silver.orders
)
SELECT order_id, customer_id, order_date, amount
FROM ranked
WHERE rn = 1;

LAG / LEAD（前後行参照）

-- 前月比の売上差分を計算
SELECT
  month,
  product_id,
  revenue,
  LAG(revenue, 1)  OVER (PARTITION BY product_id ORDER BY month) AS prev_month,
  LEAD(revenue, 1) OVER (PARTITION BY product_id ORDER BY month) AS next_month,
  revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month) AS mom_diff
FROM gold.monthly_sales;

累積集計（SUM / AVG OVER）

-- 累積売上と移動平均
SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue,
  AVG(daily_revenue) OVER (ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM gold.daily_sales;

CTE（Common Table Expressions）

CTEはWITH句で名前付き結果セットを定義し、後続のSELECTで参照する構文です。サブクエリと異なり、複数箇所から同じCTEを参照でき、クエリの可読性が大幅に向上します。

複数CTEの連結

WITH active_customers AS (
  SELECT customer_id, name, region
  FROM gold.customers
  WHERE is_active = true
),
recent_orders AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM silver.orders
  WHERE order_date >= add_months(current_date(), -3)
  GROUP BY customer_id
),
customer_summary AS (
  SELECT
    ac.customer_id,
    ac.name,
    ac.region,
    COALESCE(ro.total_spent, 0) AS total_spent_3m
  FROM active_customers ac
  LEFT JOIN recent_orders ro ON ac.customer_id = ro.customer_id
)
SELECT region, COUNT(*) AS customer_count, SUM(total_spent_3m) AS region_revenue
FROM customer_summary
GROUP BY region
ORDER BY region_revenue DESC;

Window関数 + CTEの試験頻出パターン

Window関数のエイリアスはWHERE句で直接フィルタできません（SQLの評価順序でWHEREはSELECTより先に評価）。CTEでWindow関数を計算した後、外側のクエリでフィルタするのが定石です。

-- 各部門のトップ3給与者を取得
WITH salary_rank AS (
  SELECT
    employee_id, name, department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department ORDER BY salary DESC
    ) AS dr
  FROM hr.employees
)
SELECT employee_id, name, department, salary
FROM salary_rank
WHERE dr <= 3
ORDER BY department, dr;

高階関数（TRANSFORM / FILTER / REDUCE）

Spark SQLの高階関数は、配列型カラムに対してラムダ式で要素単位の操作を行います。EXPLODE→集計→COLLECT_LISTの多段処理を1行で表現でき、ネスト構造のETL処理に有効です。

TRANSFORM

配列の各要素に関数を適用して新しい配列を返します。

-- 各要素を2倍に変換
SELECT TRANSFORM(ARRAY(1, 2, 3, 4), x -> x * 2);
-- 結果: [2, 4, 6, 8]

-- 注文の各商品に税率10%を加算
SELECT
  order_id,
  TRANSFORM(item_prices, price -> price * 1.10) AS prices_with_tax
FROM orders;

FILTER

条件に合致する要素のみを抽出した新しい配列を返します。

-- 偶数のみ抽出
SELECT FILTER(ARRAY(1, 2, 3, 4, 5, 6), x -> x % 2 = 0);
-- 結果: [2, 4, 6]

-- 1000円以上の商品のみ残す
SELECT
  order_id,
  FILTER(item_prices, p -> p >= 1000) AS high_value_items
FROM orders;

REDUCE

配列の要素をアキュムレータで畳み込み、単一の値にします。

-- 配列要素の合計を計算
SELECT REDUCE(ARRAY(1, 2, 3, 4), 0, (acc, x) -> acc + x);
-- 結果: 10

-- 最大値を取得
SELECT REDUCE(
  ARRAY(35, 12, 88, 47), 0,
  (acc, x) -> CASE WHEN x > acc THEN x ELSE acc END
);
-- 結果: 88

PIVOT / UNPIVOT

PIVOTは行データを列に変換（行→列）、UNPIVOTは列データを行に変換（列→行）する構文です。クロス集計レポートの生成やELTのデータ整形で使われます。

PIVOT

-- 四半期別の売上を列方向に展開
SELECT *
FROM (
  SELECT region, quarter, revenue
  FROM gold.quarterly_sales
)
PIVOT (
  SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
);
-- 結果: region | Q1 | Q2 | Q3 | Q4

UNPIVOT

-- 列方向のデータを行方向に変換
SELECT *
FROM gold.quarterly_sales_wide
UNPIVOT (
  revenue FOR quarter IN (Q1, Q2, Q3, Q4)
);
-- 結果: region | quarter | revenue（各四半期が1行ずつ）

試験対策の実践ポイント

• MERGE INTOの構文: WHEN MATCHED / WHEN NOT MATCHED / WHEN NOT MATCHED BY SOURCEの3分岐を丸暗記してください。UPDATE SET *とINSERT *のワイルドカード構文も頻出です。
• Window関数のWHERE問題: 「ROW_NUMBER()のエイリアスをWHERE句でフィルタするにはCTEが必要」というパターンは繰り返し出題されます。直接WHEREでrn = 1と書けない理由（SQL評価順序）を理解しましょう。
• OPTIMIZE vs VACUUM: OPTIMIZEは「ファイル統合→読み取り性能向上」、VACUUMは「不要ファイル削除→ストレージ節約」。VACUUM後はTime Travelが使えなくなる副作用があります。
• INSERT OVERWRITE vs INSERT INTO: OVERWRITEは既存データを置換、INTOは追記。パーティション指定の有無で影響範囲が変わります。
• COPY INTOの冪等性: 同じファイルを2回実行しても重複取り込みしない特性。ただし force = true にすると冪等性が無効化される点が問われます。
• RESTORE vs Time Travel: RESTOREはテーブルを過去バージョンに実際に書き戻し、Time Travel（VERSION AS OF）は過去バージョンを参照するのみです。

サンプル問題

よくある質問（FAQ）