databricks-dbsql

Databricks SQL（DBSQL）- 高度な機能

クイックリファレンス

機能	主な構文	対応開始	リファレンス
SQL スクリプティング	BEGIN...END、DECLARE、IF/WHILE/FOR	DBR 16.3+	references/sql-scripting.md
ストアドプロシージャ	CREATE PROCEDURE、CALL	DBR 17.0+	references/sql-scripting.md
再帰 CTE	WITH RECURSIVE	DBR 17.0+	references/sql-scripting.md
トランザクション	BEGIN ATOMIC...END	GA	references/sql-scripting.md
マテリアライズドビュー	CREATE MATERIALIZED VIEW	Pro/Serverless	references/materialized-views-pipes.md
一時テーブル	CREATE TEMPORARY TABLE	すべて	references/materialized-views-pipes.md
パイプ構文	|> 演算子	DBR 16.1+	references/materialized-views-pipes.md
地理空間（H3）	h3_longlatash3()、h3_polyfillash3()	DBR 11.2+	references/geospatial-collations.md
地理空間（ST）	ST_Point()、ST_Contains()、80+ 個の関数	DBR 16.0+	references/geospatial-collations.md
照合順序	COLLATE、UTF8_LCASE、ロケール対応	DBR 16.1+	references/geospatial-collations.md
AI 関数	ai_query()、ai_classify()、11+ 個の関数	DBR 15.1+	references/ai-functions.md
http_request	http_request(conn, ...)	Pro/Serverless	references/ai-functions.md
remote_query	SELECT * FROM remote_query(...)	Pro/Serverless	references/ai-functions.md
read_files	SELECT * FROM read_files(...)	すべて	references/ai-functions.md
データモデリング	スタースキーマ、リキッドクラスタリング	すべて	references/best-practices.md

---

よく使われるパターン

SQL スクリプティング - 手続き型 ETL

BEGIN
  DECLARE v_count INT;
  DECLARE v_status STRING DEFAULT 'pending';

  SET v_count = (SELECT COUNT(*) FROM catalog.schema.raw_orders WHERE status = 'new');

  IF v_count > 0 THEN
    INSERT INTO catalog.schema.processed_orders
    SELECT *, current_timestamp() AS processed_at
    FROM catalog.schema.raw_orders
    WHERE status = 'new';

    SET v_status = 'completed';
  ELSE
    SET v_status = 'skipped';
  END IF;

  SELECT v_status AS result, v_count AS rows_processed;
END

エラー処理付きストアドプロシージャ

CREATE OR REPLACE PROCEDURE catalog.schema.upsert_customers(
  IN p_source STRING,
  OUT p_rows_affected INT
)
LANGUAGE SQL
SQL SECURITY INVOKER
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET p_rows_affected = -1;
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = concat('Upsert failed for source: ', p_source);
  END;

  MERGE INTO catalog.schema.dim_customer AS t
  USING (SELECT * FROM identifier(p_source)) AS s
  ON t.customer_id = s.customer_id
  WHEN MATCHED THEN UPDATE SET *
  WHEN NOT MATCHED THEN INSERT *;

  SET p_rows_affected = (SELECT COUNT(*) FROM identifier(p_source));
END;

-- Invoke:
CALL catalog.schema.upsert_customers('catalog.schema.staging_customers', ?);

スケジュール更新付きマテリアライズドビュー

CREATE OR REPLACE MATERIALIZED VIEW catalog.schema.daily_revenue
  CLUSTER BY (order_date)
  SCHEDULE EVERY 1 HOUR
  COMMENT 'Hourly-refreshed daily revenue by region'
AS SELECT
    order_date,
    region,
    SUM(amount) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM catalog.schema.fact_orders
JOIN catalog.schema.dim_store USING (store_id)
GROUP BY order_date, region;

パイプ構文 - 読みやすい変換

-- Traditional SQL rewritten with pipe syntax
FROM catalog.schema.fact_orders
  |> WHERE order_date >= current_date() - INTERVAL 30 DAYS
  |> AGGREGATE SUM(amount) AS total, COUNT(*) AS cnt GROUP BY region, product_category
  |> WHERE total > 10000
  |> ORDER BY total DESC
  |> LIMIT 20;

AI 関数 - LLM でデータを拡充

-- Classify support tickets
SELECT
  ticket_id,
  description,
  ai_classify(description, ARRAY('billing', 'technical', 'account', 'feature_request')) AS category,
  ai_analyze_sentiment(description) AS sentiment
FROM catalog.schema.support_tickets
LIMIT 100;

-- Extract entities from text
SELECT
  doc_id,
  ai_extract(content, ARRAY('person_name', 'company', 'dollar_amount')) AS entities
FROM catalog.schema.contracts;

-- General-purpose AI query with structured output
SELECT ai_query(
  'databricks-meta-llama-3-3-70b-instruct',
  concat('Summarize this customer feedback in JSON with keys: topic, sentiment, action_items. Feedback: ', feedback),
  returnType => 'STRUCT<topic STRING, sentiment STRING, action_items ARRAY<STRING>>'
) AS analysis
FROM catalog.schema.customer_feedback
LIMIT 50;

地理空間 - H3 による近接検索

-- Find stores within 5km of each customer using H3 indexing
WITH customer_h3 AS (
  SELECT *, h3_longlatash3(longitude, latitude, 7) AS h3_cell
  FROM catalog.schema.customers
),
store_h3 AS (
  SELECT *, h3_longlatash3(longitude, latitude, 7) AS h3_cell
  FROM catalog.schema.stores
)
SELECT
  c.customer_id,
  s.store_id,
  ST_Distance(
    ST_Point(c.longitude, c.latitude),
    ST_Point(s.longitude, s.latitude)
  ) AS distance_m
FROM customer_h3 c
JOIN store_h3 s ON h3_ischildof(c.h3_cell, h3_toparent(s.h3_cell, 5))
WHERE ST_Distance(
  ST_Point(c.longitude, c.latitude),
  ST_Point(s.longitude, s.latitude)
) < 5000;

照合順序 - 大文字と小文字を区別しない検索

-- Create table with case-insensitive collation
CREATE TABLE catalog.schema.products (
  product_id BIGINT GENERATED ALWAYS AS IDENTITY,
  name STRING COLLATE UTF8_LCASE,
  category STRING COLLATE UTF8_LCASE,
  price DECIMAL(10, 2)
);

-- Queries automatically case-insensitive (no LOWER() needed)
SELECT * FROM catalog.schema.products
WHERE name = 'MacBook Pro';  -- matches 'macbook pro', 'MACBOOK PRO', etc.

http_request - 外部 API の呼び出し

-- Set up connection first (one-time)
CREATE CONNECTION my_api_conn
  TYPE HTTP
  OPTIONS (host 'https://api.example.com', bearer_token secret('scope', 'token'));

-- Call API from SQL
SELECT
  order_id,
  http_request(
    conn => 'my_api_conn',
    method => 'POST',
    path => '/v1/validate',
    json => to_json(named_struct('order_id', order_id, 'amount', amount))
  ).text AS api_response
FROM catalog.schema.orders
WHERE needs_validation = true;

read_files - 未加工ファイルの取り込み

-- Read JSON files from a Volume with schema hints
SELECT *
FROM read_files(
  '/Volumes/catalog/schema/raw/events/',
  format => 'json',
  schemaHints => 'event_id STRING, timestamp TIMESTAMP, payload MAP<STRING, STRING>',
  pathGlobFilter => '*.json',
  recursiveFileLookup => true
);

-- Read CSV with options
SELECT *
FROM read_files(
  '/Volumes/catalog/schema/raw/sales/',
  format => 'csv',
  header => true,
  delimiter => '|',
  dateFormat => 'yyyy-MM-dd',
  schema => 'sale_id INT, sale_date DATE, amount DECIMAL(10,2), store STRING'
);

再帰 CTE - 階層の走査

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level managers
  SELECT employee_id, name, manager_id, 0 AS depth, ARRAY(name) AS path
  FROM catalog.schema.employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: direct reports
  SELECT e.employee_id, e.name, e.manager_id, o.depth + 1, array_append(o.path, e.name)
  FROM catalog.schema.employees e
  JOIN org_chart o ON e.manager_id = o.employee_id
  WHERE o.depth < 10  -- safety limit
)
SELECT * FROM org_chart ORDER BY depth, name;

remote_query - フェデレーションクエリ

-- Query PostgreSQL via Lakehouse Federation
SELECT *
FROM remote_query(
  'my_postgres_connection',
  database => 'my_database',
  query    => 'SELECT customer_id, email, created_at FROM customers WHERE active = true'
);

---

リファレンスファイル

詳細な構文、すべてのパラメーター一覧、高度なパターンについては、以下のファイルを参照してください。

ファイル	内容	参照するタイミング
references/sql-scripting.md	SQL スクリプティング、ストアドプロシージャ、再帰 CTE、トランザクション	手続き型 SQL、エラー処理、ループ、動的 SQL が必要な場合
references/materialized-views-pipes.md	マテリアライズドビュー、一時テーブル／ビュー、パイプ構文	MV、更新スケジュール、一時オブジェクト、パイプ演算子が必要な場合
references/geospatial-collations.md	39 個の H3 関数、80+ 個の ST 関数、照合順序の種類と階層	空間分析、H3 インデックス、大文字・小文字やアクセントの処理が必要な場合
references/ai-functions.md	13 個の AI 関数、http_request、remote_query、read_files（すべてのオプション）	AI によるデータ拡充、API 呼び出し、フェデレーション、ファイル取り込みが必要な場合
references/best-practices.md	データモデリング、パフォーマンス、リキッドクラスタリング、アンチパターン	アーキテクチャの指針、最適化、またはモデリングのアドバイスが必要な場合

---

主なガイドライン

• AI 関数、MV、http_request には必ず Serverless SQL ウェアハウスを使用します
• AI 関数を使用する開発時は、コストを抑えるためにLIMITを使用します
• 新しいテーブルでは、パーティション分割よりもリキッドクラスタリングを優先します（キーは最大1～4個）
• クラスタリングキーが不明な場合は、CLUSTER BY AUTOを使用します
• BI には Gold レイヤーでスター・スキーマを使用します。Silver では OBT も使用できます
• クエリを最適化するため、ディメンショナルモデルに PK/FK 制約を定義します
• 大文字と小文字を区別しない検索が必要なユーザー向け文字列列には、COLLATE UTF8_LCASEを使用します
• デプロイ前に、CLI（databricks experimental aitools tools query）またはノートブックで SQL をテストします。お使いの CLI バージョンで--warehouseが拒否される場合は、代わりに環境変数DATABRICKS_WAREHOUSE_IDを設定します。