DatabricksのSQLスクリプティング

Posted at 2025-05-24

以前ご紹介したSQLスクリプティング

こちらのリリースでパブリックプレビューになっています。

マニュアルに従って、一通り動かしてみます。

注意
こちらはDatabricksランタイム17.0 Betaで動かしています。

CASE文

-- シンプルなCASE文
BEGIN
    DECLARE choice INT DEFAULT 3;
    DECLARE result STRING;
    CASE choice
      WHEN 1 THEN
        VALUES ('one fish');
      WHEN 2 THEN
        VALUES ('two fish');
      WHEN 3 THEN
        VALUES ('red fish');
      WHEN 4 THEN
        VALUES ('blue fish');
      ELSE
        VALUES ('no fish');
    END CASE;
  END;

-- 検索されたCASE文
BEGIN
    DECLARE choice DOUBLE DEFAULT 3.9;
    DECLARE result STRING;
    CASE
      WHEN choice < 2 THEN
        VALUES ('one fish');
      WHEN choice < 3 THEN
        VALUES ('two fish');
      WHEN choice < 4 THEN
        VALUES ('red fish');
      WHEN choice < 5 OR choice IS NULL THEN
        VALUES ('blue fish');
      ELSE
        VALUES ('no fish');
    END CASE;
  END;

BEGIN END複合文

SQLスクリプトブロックを実装し、一連のSQLステートメント、フロー制御ステートメント、ローカル変数宣言、および例外ハンドラを含めることができます。

以下の例は、ゼロによる除算を行っているのでエラーになります。

BEGIN
  DECLARE a INT DEFAULT 1;
  DECLARE b INT DEFAULT 5;
  SET a = b / 0;
  VALUES (a);
END;

エラーハンドリングを追加します。

-- ローカル変数、終了ハンドラ、およびネストされた複合文を含む複合文
BEGIN
    DECLARE a INT DEFAULT 1;
    DECLARE b INT DEFAULT 5;
    DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
      div0: BEGIN
        VALUES (15);
      END div0;
    SET a = 10;
    SET a = b / 0;
    VALUES (a);
END;

DIVIDE_BY_ZEROのエラーを捕捉して15が返却されました。

FOR文

クエリによって返される各行に対して、ステートメントのリストの実行を繰り返します。

BEGIN
    DECLARE sum INT DEFAULT 0;
    sumNumbers: FOR row AS SELECT num FROM range(1, 20) AS t(num) DO
      IF num > 10 THEN
         LEAVE sumNumbers;
      ELSEIF num % 2 = 0 THEN
        ITERATE sumNumbers;
      END IF;
      SET sum = sum + row.num;
    END FOR sumNumbers;
    VALUES (sum);
  END;

-- より効率的なリレーショナル計算と比較:
SELECT sum(num) FROM range(1, 10) AS t(num) WHERE num % 2 = 1;

ビルトインの関数で実装できるなら、そちらを使った方が高速です。

GET DIAGNOSTICS文

例外ハンドラーで処理された条件に関する情報を取得します。

BEGIN
    DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
      BEGIN
        DECLARE cond STRING;
        DECLARE message STRING;
        DECLARE state STRING;
        DECLARE args MAP<STRING, STRING>;
        DECLARE line BIGINT;
        DECLARE argstr STRING;
        DECLARE log STRING;
        GET DIAGNOSTICS CONDITION 1
           cond    = CONDITION_IDENTIFIER,
           message = MESSAGE_TEXT,
           state   = RETURNED_SQLSTATE,
           args    = MESSAGE_ARGUMENTS,
           line    = LINE_NUMBER;
        SET argstr =
          (SELECT aggregate(array_agg('Parm:' || key || ' Val: value '),
                            '', (acc, x)->(acc || ' ' || x))
             FROM explode(args) AS args(key, val));
        SET log = 'Condition: ' || cond ||
                  ' Message: ' || message ||
                  ' SQLSTATE: ' || state ||
                  ' Args: ' || argstr ||
                  ' Line: ' || line;
        VALUES (log);
      END;
    SELECT 10/0;
  END;

argstrやlogで設定した書式でエラーメッセージを取得できます。

Condition: DIVIDE_BY_ZERO Message: [DIVIDE_BY_ZERO] [DIVIDE_BY_ZERO] Division by zero. Use try_divide to tolerate divisor being 0 and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. SQLSTATE: 22012 SQLSTATE: 22012 SQLSTATE: 22012 Args: Parm:config Val: value Line: 28

IF THEN ELSE文

BEGIN
    DECLARE choice DOUBLE DEFAULT 3.9;
    DECLARE result STRING;
    IF choice < 2 THEN
      VALUES ('one fish');
    ELSEIF choice < 3 THEN
      VALUES ('two fish');
    ELSEIF choice < 4 THEN
      VALUES ('red fish');
    ELSEIF choice < 5 OR choice IS NULL THEN
      VALUES ('blue fish');
    ELSE
      VALUES ('no fish');
    END IF;
  END;

ITERATE文

ループ条件が満たされている場合、ループ文の繰り返しの実行を終了し、次の繰り返しに進みます。

-- 1から10までの奇数をすべて合計する
BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE num INT DEFAULT 0;
    sumNumbers: LOOP
      SET num = num + 1;
      IF num > 10 THEN
        LEAVE sumNumbers;
      END IF;
      IF num % 2 = 0 THEN
        ITERATE sumNumbers;
      END IF;
      SET sum = sum + num;
    END LOOP sumNumbers;
    VALUES (sum);
  END;

LEAVE文

ループ文の繰り返しの実行を終了し、ループを終了します。

このステートメントは、複合ステートメント内でのみ使用できます。

-- 1から10までの奇数をすべて合計する
-- 偶数を繰り返し、10に達したらループを終了する。
BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE num INT DEFAULT 0;
    sumNumbers: LOOP
      SET num = num + 1;
      IF num > 10 THEN
        LEAVE sumNumbers;
      END IF;
      IF num % 2 = 0 THEN
        ITERATE sumNumbers;
      END IF;
      SET sum = sum + num;
    END LOOP sumNumbers;
    VALUES (sum);
  END;

LOOP文

-- 1から10までの奇数の合計を計算する
BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE num INT DEFAULT 0;
    sumNumbers: LOOP
      SET num = num + 1;
      IF num > 10 THEN
        LEAVE sumNumbers;
      END IF;
      -- 偶数の場合はループの次の反復にスキップ
      IF num % 2 = 0 THEN
        ITERATE sumNumbers;
      END IF;
      -- 奇数を合計に加算
      SET sum = sum + num;
    END LOOP sumNumbers;
    VALUES (sum);
  END;

REPEAT文

BEGIN
  DECLARE sum INT DEFAULT 0;
  DECLARE num INT DEFAULT 0;
  sumNumbers: REPEAT
      SET num = num + 1;
      IF num % 2 = 0 THEN
        ITERATE sumNumbers;
      END IF;
      SET sum = sum + num;
    UNTIL num = 10
    END REPEAT sumNumbers;
    VALUES (sum);
  END;

RESIGNAL文

条件ハンドラによって処理された条件を再度発生させます。

以下ではエラーメッセージを格納するテーブルを作成しています。

CREATE TABLE takaakiyayoi_catalog.sql_scripting.log(eventtime TIMESTAMP, log STRING);

以下の後半でRESIGNALがない場合、エラーメッセージがテーブルに追加されるのみで画面には表示されません。RESIGNALを実行することで、画面にエラーが表示されます。

BEGIN
    DECLARE EXIT HANDLER FOR DIVIDE_BY_ZERO
      BEGIN
        DECLARE cond STRING;
        DECLARE message STRING;
        DECLARE state STRING;
        DECLARE args MAP<STRING, STRING>;
        DECLARE line BIGINT;
        DECLARE argstr STRING;
        DECLARE log STRING;
        GET DIAGNOSTICS CONDITION 1
           cond = CONDITION_IDENTIFIER,
           message = MESSAGE_TEXT,
           state = RETURNED_SQLSTATE,
           args = MESSAGE_ARGUMENTS,
           line = LINE_NUMBER;
        SET argstr =
          (SELECT aggregate(array_agg('Parm:' || key || ' Val: value '),
                            '', (acc, x)->(acc || ' ' || x))
             FROM explode(args) AS args(key, val));
        SET log = 'Condition: ' || cond ||
                  ' Message: ' || message ||
                  ' SQLSTATE: ' || state ||
                  ' Args: ' || argstr ||
                  ' Line: ' || line;
        INSERT INTO takaakiyayoi_catalog.sql_scripting.log VALUES(current_timestamp(), log);
        RESIGNAL;
      END;
    SELECT 10/0;
  END;

[DIVIDE_BY_ZERO] Division by zero. Use try_divide to tolerate divisor being 0 and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error. SQLSTATE: 22012

SELECT * FROM takaakiyayoi_catalog.sql_scripting.log ORDER BY eventtime DESC LIMIT 1;

SIGNAL文

条件を発生させます。

このステートメントは、複合ステートメント内でのみ使用できます。

注意
Databricksでは、ハンドラ内から条件を発生させるために RESIGNAL を使用することを推奨します。RESIGNAL は SQL 標準で診断スタックを構築しますが、SIGNAL はスタックをクリアします。ハンドラ内で RESIGNAL を使用すると、将来にわたって診断スタックを利用できます。

DECLARE input INT DEFAULT 5;
BEGIN
    DECLARE arg_map MAP<STRING, STRING>;
    IF input > 4 THEN
      SET arg_map = map('errorMessage',
                        'Input must be <= 4.');
      SIGNAL USER_RAISED_EXCEPTION
        SET MESSAGE_ARGUMENTS = arg_map;
    END IF;
  END;

WHILE文

BEGIN
    DECLARE sum INT DEFAULT 0;
    DECLARE num INT DEFAULT 0;
    sumNumbers: WHILE num < 10 DO
      SET num = num + 1;
      IF num % 2 = 0 THEN
        ITERATE sumNumbers;
      END IF;
      SET sum = sum + num;
    END WHILE sumNumbers;
    VALUES (sum);
  END;

はじめてのDatabricks

Databricks無料トライアル

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up