DatabricksのSQLスクリプティング

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

https://qiita.com/taka_yayoi/items/4d4000f086e11cde30f6

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

https://docs.databricks.com/aws/ja/sql/release-notes/2025#2025%E5%B9%B44%E6%9C%8810%E6%97%A5

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

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-scripting

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

# CASE文

https://docs.databricks.com/aws/ja/sql/language-manual/control-flow/case-stmt

```sql
-- シンプルな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;
```

![Screenshot 2025-05-24 at 14.17.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/6991e78f-edac-4381-b625-64ea97edbfdd.png)


```py
-- 検索された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;
```
![Screenshot 2025-05-24 at 14.18.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/592656be-fc25-4d76-ace5-1e0a07eb30f5.png)

# BEGIN END複合文

https://docs.databricks.com/aws/ja/sql/language-manual/control-flow/compound-stmt

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

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

```sql
BEGIN
  DECLARE a INT DEFAULT 1;
  DECLARE b INT DEFAULT 5;
  SET a = b / 0;
  VALUES (a);
END;
```
![Screenshot 2025-05-24 at 14.19.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/45940f12-a669-45a4-b591-f25aa638105f.png)

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

```sql
-- ローカル変数、終了ハンドラ、およびネストされた複合文を含む複合文
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が返却されました。

![Screenshot 2025-05-24 at 14.20.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/a25c7ba9-4223-45ff-af80-f84f359bae01.png)

# FOR文

https://docs.databricks.com/aws/en/sql/language-manual/control-flow/for-stmt

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

```sql
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;
```
![Screenshot 2025-05-24 at 14.27.07.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/39c6c4ee-dc9d-45cc-a9fe-654e55656932.png)

```sql
-- より効率的なリレーショナル計算と比較:
SELECT sum(num) FROM range(1, 10) AS t(num) WHERE num % 2 = 1;
```
![Screenshot 2025-05-24 at 14.27.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/e178c21c-1fe2-4288-b8d1-723f858e56cd.png)

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

# GET DIAGNOSTICS文

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

```sql
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文

https://docs.databricks.com/aws/en/sql/language-manual/control-flow/if-stmt

```sql
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;
```
![Screenshot 2025-05-24 at 14.33.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/2946b112-1b51-4875-90f3-9412081502d3.png)

# ITERATE文

https://docs.databricks.com/aws/en/sql/language-manual/control-flow/iterate-stmt

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

```sql
-- 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;
```
![Screenshot 2025-05-24 at 14.35.33.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/7ebd358b-07d7-44f9-a205-329e20a4af93.png)

# LEAVE文

https://docs.databricks.com/aws/en/sql/language-manual/control-flow/leave-stmt

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

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

```sql
-- 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;
```
![Screenshot 2025-05-24 at 14.37.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/ab13a4cc-0d7f-43e6-9794-f8ce172f0cca.png)

# LOOP文

https://docs.databricks.com/aws/en/sql/language-manual/control-flow/loop-stmt

```sql
-- 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;
```
![Screenshot 2025-05-24 at 14.39.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/4cb3ae2f-ceba-41e8-9394-65a7116e80a2.png)

# REPEAT文

https://docs.databricks.com/aws/en/sql/language-manual/control-flow/repeat-stmt

```sql
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;
```
![Screenshot 2025-05-24 at 14.40.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/c2798951-c19e-4b38-83b1-392d5036bf54.png)

# RESIGNAL文

https://docs.databricks.com/aws/en/sql/language-manual/control-flow/resignal-stmt

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

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

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

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

```sql
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

```sql
SELECT * FROM takaakiyayoi_catalog.sql_scripting.log ORDER BY eventtime DESC LIMIT 1;
```
![Screenshot 2025-05-24 at 14.46.03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/3814d3ff-9039-4909-8b27-25b06edf8a44.png)

# SIGNAL文

https://docs.databricks.com/aws/en/sql/language-manual/control-flow/signal-stmt

条件を発生させます。

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

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

```sql
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;
```
![Screenshot 2025-05-24 at 14.50.02.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/8a83e951-6186-4473-b36f-52678c296bda.png)


# WHILE文

https://docs.databricks.com/aws/en/sql/language-manual/control-flow/while-stmt

```sql
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;
```
![Screenshot 2025-05-24 at 14.47.11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1168882/b6e2c4c8-7ef1-4d62-a71b-fd87b4409520.png)




### はじめてのDatabricks

[はじめてのDatabricks](https://qiita.com/taka_yayoi/items/8dc72d083edb879a5e5d)

### Databricks無料トライアル

[Databricks無料トライアル](https://databricks.com/jp/try-databricks)