Snowflakeスクリプト UDFs¶

Snowflakeは、Snowflakeスクリプトプロシージャ言語を含む SQL ユーザー定義関数（UDFs）をサポートしています。これらの UDFs は Snowflakeスクリプト UDFs と呼ばれます。

Snowflakeスクリプト UDFs は、SELECT ステートメントや INSERT ステートメントなどの SQL ステートメントで呼び出すことができます。したがって、これらは、SQL CALL コマンドでしか呼び出せないSnowflakeスクリプトストアドプロシージャよりも柔軟性があります。

一般的な使用法¶

Snowflakeスクリプト UDF は手続き型コードを評価し、スカラー（つまり単一）値を返します。

Snowflakeスクリプト UDFs では、次の Snowflakeスクリプト構文のサブセットを使用できます。

サポートされているデータ型¶

Snowflakeスクリプト UDFs は、入力引数と戻り値の両方で次のデータ型をサポートします。

数値データ型（例えば、INTEGER、 NUMBER、 FLOAT）
文字列およびバイナリデータ型（例えば、 VARCHAR や BINARY）
日付と時刻のデータ型 (例えば、 DATE、 TIME、 TIMESTAMP)。
論理データ型（例えば、 BOOLEAN）

Snowflakeスクリプト UDFs は、入力引数に対してのみ次のデータ型をサポートします。

半構造化データ型 (例えば、 VARIANT、 OBJECT、 ARRAY)。
構造化データ型（例えば、ARRAY、 OBJECT、 MAP）

制限事項¶

Snowflakeスクリプト UDFs には次の制限が適用されます。

次のタイプのSnowflakeスクリプト構文は、Snowflakeスクリプト UDFs ではサポートされていません。
SQL ステートメントは、Snowflakeスクリプト UDFs ではサポートされていません（SELECT、 INSERT、 UPDATE など）。
Snowflakeスクリプト UDFs はテーブル関数として定義できません。
次の式型はSnowflakeスクリプト UDFs ではサポートされていません。
- ユーザー定義関数
- 集計関数
- ウィンドウ関数
Snowflakeスクリプト UDFs は、マテリアライズドビューを作成する場合には使用できません。
Snowflakeスクリプト UDFs は、行アクセスポリシーおよびマスキングポリシーを作成する場合には使用できません。
Snowflakeスクリプト UDFs は、デフォルトの列値を指定するためには使用できません。
Snowflakeスクリプト UDFs は、データのロードおよびアンロード用の COPY INTO コマンドでは使用できません。
Snowflakeスクリプト UDFs はメモ化できません。
Snowflakeスクリプト UDFs には、入力引数に500個の制限があります。
Snowflakeスクリプト UDFs についてはメッセージをロギングできません。

例¶

次の例では、Snowflakeスクリプト UDFs を作成して呼び出します。

変数を使用したSnowflakeスクリプト UDF の作成
条件付きロジックを使用したSnowflakeスクリプト UDF の作成
ループを使用したSnowflakeスクリプト UDF の作成
例外処理を使用したSnowflakeスクリプト UDF の作成
INSERT ステートメントの値を返すSnowflakeスクリプト UDF の作成
WHERE および ORDER BY 句で呼び出されるSnowflakeスクリプト UDF の作成

変数を使用したSnowflakeスクリプト UDF の作成¶

2つの引数の値に基づいて利益を計算するSnowflakeスクリプト UDF を作成します。

CREATE OR REPLACE FUNCTION calculate_profit(
  cost NUMBER(38, 2),
  revenue NUMBER(38, 2))
RETURNS number(38, 2)
LANGUAGE SQL
AS
DECLARE
  profit NUMBER(38, 2) DEFAULT 0.0;
BEGIN
  profit := revenue - cost;
  RETURN profit;
END;

注釈

If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, this example requires minor changes. For more information, see Using Snowflake Scripting in Snowflake CLI, SnowSQL, and Python Connector.

クエリで calculate_profit を呼び出します。

SELECT calculate_profit(100, 110);

+----------------------------+
| CALCULATE_PROFIT(100, 110) |
|----------------------------|
|                      10.00 |
+----------------------------+

同じSnowflakeスクリプト UDF を使用して引数の列を指定できます。まず、テーブルを作成してデータを挿入します。

CREATE OR REPLACE TABLE snowflake_scripting_udf_profit(
  cost NUMBER(38, 2),
  revenue NUMBER(38, 2));

INSERT INTO snowflake_scripting_udf_profit VALUES
  (100, 200),
  (200, 190),
  (300, 500),
  (400, 401);

クエリで calculate_profit を呼び出し、引数の列を指定します。

SELECT calculate_profit(cost, revenue)
  FROM snowflake_scripting_udf_profit;

+---------------------------------+
| CALCULATE_PROFIT(COST, REVENUE) |
|---------------------------------|
|                          100.00 |
|                          -10.00 |
|                          200.00 |
|                            1.00 |
+---------------------------------+

条件付きロジックを使用したSnowflakeスクリプト UDF の作成¶

条件付きロジックを使用して、入力する INTEGER 値に基づき部門名を決定するSnowflakeスクリプト UDF を作成します。

CREATE OR REPLACE function check_dept(department_id INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  IF (department_id < 3) THEN
    RETURN 'Engineering';
  ELSEIF (department_id = 3) THEN
    RETURN 'Tool Design';
  ELSE
    RETURN 'Marketing';
  END IF;
END;

注釈

クエリで check_dept を呼び出します。

SELECT check_dept(2);

+---------------+
| CHECK_DEPT(2) |
|---------------|
| Engineering   |
+---------------+

Snowflakeスクリプト UDF を呼び出すときに、引数で SQL 変数を使用できます。次の例では SQL 変数を設定してから、check_dept UDF への呼び出しにその変数を使います。

SET my_variable = 3;

SELECT check_dept($my_variable);

+--------------------------+
| CHECK_DEPT($MY_VARIABLE) |
|--------------------------|
| Tool Design              |
+--------------------------+

ループを使用したSnowflakeスクリプト UDF の作成¶

ループを使用して、引数で指定されたターゲット値までのすべての数値をカウントし、カウントされたすべての数値の合計を計算するSnowflakeスクリプト UDF を作成します。

CREATE OR REPLACE function count_to(
  target_number INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  counter INTEGER DEFAULT 0;
  sum_total INTEGER DEFAULT 0;
BEGIN
  WHILE (counter < target_number) DO
    counter := counter + 1;
    sum_total := sum_total + counter;
  END WHILE;
  RETURN 'Counted to ' || counter || '. Sum of all numbers: ' || sum_total;
END;

注釈

クエリで count_to を呼び出します。

SELECT count_to(10);

+---------------------------------------+
| COUNT_TO(10)                          |
|---------------------------------------|
| Counted to 10. Sum of all numbers: 55 |
+---------------------------------------+

例外処理を使用したSnowflakeスクリプト UDF の作成¶

例外を宣言してから例外を発生させるSnowflakeスクリプト UDF を作成します。

CREATE OR REPLACE FUNCTION raise_exception(input_value INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  counter_val INTEGER DEFAULT 0;
  my_exception EXCEPTION (-20002, 'My exception text');
BEGIN
  WHILE (counter_val < 12) DO
    counter_val := counter_val + 1;
    IF (counter_val > 10) THEN
      RAISE my_exception;
    END IF;
  END WHILE;
  RETURN counter_val;
EXCEPTION
  WHEN my_exception THEN
    IF (input_value = 1) THEN
      RETURN 'My exception caught: ' || sqlcode;
    ELSEIF (input_value = 2) THEN
      RETURN 'My exception caught with different path: ' || sqlcode;
    END IF;
    RETURN 'Default exception handling path: ' || sqlcode;
END;

注釈

クエリで raise_exception を呼び出し、入力値に 1 を指定します。

SELECT raise_exception(1);

+-----------------------------+
| RAISE_EXCEPTION(1)          |
|-----------------------------|
| My exception caught: -20002 |
+-----------------------------+

クエリで raise_exception を呼び出し、入力値に 2 を指定します。

SELECT raise_exception(2);

+-------------------------------------------------+
| RAISE_EXCEPTION(2)                              |
|-------------------------------------------------|
| My exception caught with different path: -20002 |
+-------------------------------------------------+t

クエリで raise_exception を呼び出し、入力値に NULL を指定します。

SELECT raise_exception(NULL);

+-----------------------------------------+
| RAISE_EXCEPTION(NULL)                   |
|-----------------------------------------|
| Default exception handling path: -20002 |
+-----------------------------------------+

INSERT ステートメントの値を返すSnowflakeスクリプト UDF の作成¶

INSERT ステートメントで使用される値を返すSnowflakeスクリプト UDF を作成します。値が挿入されるテーブルを作成します。

CREATE OR REPLACE TABLE test_sql_udf_insert (num NUMBER);

数値を返す SQL UDF を作成します。

CREATE OR REPLACE FUNCTION value_to_insert(l NUMBER, r NUMBER)
RETURNS number
LANGUAGE SQL
AS
BEGIN
  IF (r < 0) THEN
    RETURN l/r * -1;
  ELSEIF (r > 0) THEN
    RETURN l/r;
  ELSE
    RETURN 0;
END IF;
END;

注釈

複数の INSERT ステートメントで value_to_insert を呼び出します。

INSERT INTO test_sql_udf_insert SELECT value_to_insert(10, 2);
INSERT INTO test_sql_udf_insert SELECT value_to_insert(10, -2);
INSERT INTO test_sql_udf_insert SELECT value_to_insert(10, 0);

テーブルをクエリして、挿入された値を表示します。

SELECT * FROM test_sql_udf_insert;

+-----+
| NUM |
|-----|
|   5 |
|   5 |
|   0 |
+-----+

WHERE および ORDER BY 句で呼び出されるSnowflakeスクリプト UDF の作成¶

WHERE または ORDER BY 句で使用される値を返すSnowflakeスクリプト UDF を作成します。テーブルを作成して値を挿入します。

CREATE OR REPLACE TABLE test_sql_udf_clauses (p1 INT, p2 INT);

INSERT INTO test_sql_udf_clauses VALUES
  (100, 7),
  (100, 3),
  (100, 4),
  (NULL, NULL);

2つの入力値の乗算の積である数値を返す SQL UDF を作成します。

CREATE OR REPLACE FUNCTION get_product(a INTEGER, b INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  RETURN a * b;
END;

注釈

クエリの WHERE 句に get_product を呼び出し、積が 350 より大きい行を返すようにします。

SELECT *
  FROM test_sql_udf_clauses
  WHERE get_product(p1, p2) > 350;

+-----+----+
|  P1 | P2 |
|-----+----|
| 100 |  7 |
| 100 |  4 |
+-----+----+

クエリの ORDER BY 句に get_product を呼び出し、UDF で返される結果を小さい積から大きい積の順に並べ替えます。

SELECT *
  FROM test_sql_udf_clauses
  ORDER BY get_product(p1, p2);

+------+------+
|  P1  | P2   |
|------+------|
| 100  | 3    |
| 100  | 4    |
| 100  | 7    |
| NULL | NULL |
+------+------+