UDFsの概要

.

関連トピック

• ユーザー定義関数、外部関数、およびストアドプロシージャ用の DDL

このトピックでは、 UDFs （ユーザー定義関数）に適用される概念と使用法の詳細について説明します。

このトピックの内容:

スカラーおよび表形式の UDFs

UDFs は、スカラーの場合と表形式の場合があります。

スカラー 関数は、入力行ごとに1つの出力行を返します。返される行は、単一の列/値で構成されます。

スカラーUDFsの作成および使用の詳細については、次をご参照ください。

• Java UDFs の紹介

• JavaScript UDFs の紹介

• Python UDFsの紹介

• SQL UDFs の紹介

テーブル関数 とも呼ばれる 表形式 関数は、入力行ごとに0、1、または複数の行を返します。表形式の UDF は、 TABLE キーワードを含み、テーブルの結果にある列の名前とデータ型を指定する戻り句を指定することにより定義されます。表形式の UDFs は、しばしば UDTFs （ユーザー定義のテーブル関数）または テーブル UDFs と呼ばれます。

例を含む、 UDTFs の作成および使用の詳細については、次をご参照ください。

• 表形式のJava UDFs （UDTFs）

• 表形式 JavaScript UDFs （UDTFs）

• Pythonでのユーザー定義テーブル関数（UDTFs）の実装

• 表形式 SQL UDFs （UDTFs）

UDFs 作成のためにサポートされているプログラミング言語

Snowflakeは次のプログラミング言語をサポートしています。

SQL

SQL UDF は、 スカラー または 表形式 のいずれかです。

SQL UDF は、任意の SQL 式を評価し、式の結果を返します。

式は、一般式またはクエリ式のいずれかです。

• この UDF は、一般式 pi() * radius * radius を使用します。

  CREATE FUNCTION area_of_circle(radius FLOAT)
    RETURNS FLOAT
    AS
    $$
      pi() * radius * radius
    $$
    ;
  

• この UDF は、クエリ式を使用します。

  CREATE FUNCTION profit()
    RETURNS NUMERIC(11, 2)
    AS
    $$
      SELECT SUM((retail_price - wholesale_price) * number_sold)
          FROM purchases
    $$
    ;
  

非表形式の UDFs の場合、クエリ式は1つの列を含む最大1つの行を返すことが保証されている必要があります。

UDF を定義する式は、上記の例に示すように、関数の入力引数を参照できます。

UDF を定義する式は、上記の例に示すように、テーブル、ビュー、シーケンスなどのデータベースオブジェクトを参照できます。ただし、次の制限が適用されます。

• UDF 所有者 には、 UDF がアクセスするデータベースオブジェクトに対する適切な権限が必要です。

• 動的 SQL を使用してデータベースオブジェクトを参照することはできません。たとえば、次のステートメントは、 IDENTIFIER(table_name_parameter) が許可されていないため失敗します。

  CREATE OR REPLACE FUNCTION profit2(table_name_parameter VARCHAR)
    RETURNS NUMERIC(11, 2)
    AS
    $$
      SELECT SUM((retail_price - wholesale_price) * number_sold)
          FROM IDENTIFIER(table_name_parameter)
    $$
    ;
  

UDF を定義する式には、ステートメントターミネーターとしてセミコロンを含めないでください。たとえば、上記のクエリ式 UDF の SELECT はセミコロンで終了していないことに注意してください。

SQL UDF の定義式は、他のユーザー定義関数を参照できます。ただし、直接または間接的に（つまり、別の関数を呼び出して）それ自体を再帰的に参照することはできません。

詳細については、 SQL UDFs の紹介 をご参照ください。

JavaScript

JavaScript UDFs は、 JavaScript プログラミング言語とランタイム環境を使用してデータを操作できます。JavaScript UDFs は、SQL UDFsと同じ方法で作成されますが、 LANGUAGE パラメーターを JAVASCRIPT に設定します。

SQL UDFs と同様に、 JavaScript UDFs は UDF の定義方法に応じて、スカラーまたは表形式の結果を返すことができます。

JavaScript UDF の定義式はそれ自体を再帰的に参照できますが、他のユーザー定義関数を参照することはできません。

詳細については、 JavaScript UDFs の紹介 をご参照ください。

注釈

JavaScript UDFsには、SQL UDFsで適用されない要件、使用方法の詳細、制限があります。例などの詳細については、 JavaScript UDFs をご参照ください。

Java

Java UDFs により、Javaプログラミング言語とランタイム環境を使用してデータを操作できます。

SQL や JavaScript UDFs と同様に、Java UDFs は UDF の定義方法に応じて、スカラーまたは表形式の結果を返すことができます。

Java UDFs は、次に挙げる2つの方法のいずれかで作成できます。

• インライン: CREATE FUNCTION コマンドの一部としてJavaコードを入力します。

• JAR file: CREATE FUNCTION コマンドの一部として、コンパイル済みの JAR ファイルの場所を指定します。

詳細については、 Java UDFs の紹介 をご参照ください。

注釈

Java UDFs には、 SQL UDFs で適用されない要件、使用方法の詳細、制限があります。詳細については、以下をご参照ください。

• Java UDFs の制限。

• Snowflakeによる制約の範囲内にとどまるJava UDFs の設計。

Python

Python UDFsは、Pythonプログラミング言語とランタイム環境を使用してデータを操作できます。

Java UDFsと同様に、Python UDFsはUDFの定義方法に応じて、スカラーまたは表形式の結果を返すことができます。

Python UDFsは、次に挙げる2つの方法のいずれかで作成できます。

• インライン: CREATE FUNCTIONコマンドの一部としてPythonコードを入力します。

• ステージにコピーされたモジュールとして: CREATE FUNCTIONコマンドの一部としてモジュールファイルの場所を指定します。

詳細については、 Python UDFsの紹介 をご参照ください。

注釈

Python UDFsには、SQL UDFsで適用されない要件、使用方法の詳細、制限があります。詳細については、以下をご参照ください。

• Python UDFsの制限。

• Snowflakeによって課せられた制約の範囲内にとどまるPython UDFsの設計。

プログラミング言語の選択

UDF の記述に使用するプログラミング言語の選択には、多くの要因が影響を及ぼす可能性があります。要因には次のものが含まれます。

• 特定の言語のコードがすでにあるかどうか。たとえば、必要な作業を実行するメソッドを含む.jarファイルがすでにある場合は、プログラミング言語としてJavaを使用することをお勧めします。

• 言語の機能。

• 言語に、必要な処理を実行するために役立つライブラリがあるかどうか。

次のテーブルは、各 UDF プログラミング言語の主な機能をまとめたものです。以下のリストは、テーブルの列を説明しています。

• 表形式: Snowflakeが（スカラー関数に加えて）テーブル関数の記述をサポートするかどうかを示します。

• ループと分岐: プログラミング言語がループと分岐をサポートしているかどうかを示します。

• プリコンパイル済み: UDF のコードをプリコンパイル済みファイルとして（例: .JAR ファイルとして）提供できるかどうかを示します。

• インライン: CREATE FUNCTION ステートメントでコードをテキストとして提供できるかどうかを示します。

• 共有可能: この言語で記述された UDFs が、Snowflake Secure Data Sharing 機能で使用できるかどうかを示します。

言語	表形式	ループと分岐	プリコンパイル済みまたはステージングされたソース	インライン	共有可能
SQL	可	いいえ	いいえ	可	可
JavaScript	可	可	いいえ	可	可
Java	可	可	はい（プリコンパイル済み）	可	不可 1
Python	可	可	はい（ステージング済み）	可	不可 2

1

Java UDFs の共有に関する制限の詳細については、 Java UDFs の制限 をご参照ください。

2

Python UDFs の共有に関する制限の詳細については、 Python UDFsの制限 をご参照ください。

UDFs の命名規則

UDFs はデータベースオブジェクトであり、指定されたデータベースとスキーマで作成されます。そのため、 db.schema.function_name の形式で、名前空間により定義された完全修飾名を持っています。たとえば、

SELECT temporary_db_qualified_names_test.temporary_schema_1.udf_pi();

完全修飾名なしで呼び出された場合、 UDFs はセッションで使用されているデータベースとスキーマに従って解決されます。

これは、Snowflakeが提供する組み込みのシステム定義関数とは対照的です。Snowflakeは名前空間を持たないため、どこからでも呼び出すことができます。

システム定義関数との競合

関数を呼び出すときの競合を避けるために、Snowflakeでは、システム定義の関数と同じ名前の UDFs を作成できません。

UDF名のオーバーロード

Snowflakeは、SQL UDF名のオーバーロードをサポートしています。同じスキーマ内の複数のSQL UDFsは、引数の署名が引数の数または引数の種類によって異なる限り、同じ名前を持つことができます。オーバーロードされたUDFが呼び出されると、Snowflakeは引数をチェックし、正しい関数を呼び出します。

add5 という名前の2つのSQL UDFsを作成する次の例を検討してください。

CREATE OR REPLACE FUNCTION add5 (n number)
  RETURNS number
  AS 'n + 5';

CREATE OR REPLACE FUNCTION add5 (s string)
  RETURNS string
  AS 's || ''5''';

重要

2番目のADD5関数では、文字列リテラル '5' をエスケープするために一重引用符が使用されます。UDF定義で使用される一重引用符は、 必ず 一重引用符でエスケープする必要があります。

add5 が数値引数で呼び出された場合、最初の実装が選択され、文字列型の引数は2番目の実装を使用します。引数が数値でも文字列でもない場合、実装はSnowflakeの暗黙的な型変換規則に依存します。例えば、日付型の引数は文字列に変換され、 DATE から NUMBER への変換はサポートされていないため、文字列実装が選択されます。

例：

select add5(1);

+---------+
| ADD5(1) |
|---------|
|       6 |
+---------+

select add5('1');

+-----------+
| ADD5('1') |
|-----------|
| 15        |
+-----------+

select add5('hello');

+---------------+
| ADD5('HELLO') |
|---------------|
| hello5        |
+---------------+

select add5(to_date('2014-01-01'));

+-----------------------------+
| ADD5(TO_DATE('2014-01-01')) |
|-----------------------------|
| 2014-01-015                 |
+-----------------------------+

オーバーロードを使用する場合は注意してください。自動型変換とオーバーロードの組み合わせにより、軽微なユーザーエラーが予期しない結果を引き起こしやすくなります。例については、 関数名のオーバーロード をご参照ください。

ご用心

SQL 以外の言語で記述された UDFs の場合は、ルールが異なる可能性があります。

例

関数名のオーバーロード

このトピック内の UDF名のオーバーロード で説明されているように、関数名をオーバーロードできます。

次のオーバーロードの例は、オーバーロードと自動型変換を組み合わせて、予期しない結果を簡単に得る方法を示しています。

FLOAT パラメーターを受け取る関数を作成します。

CREATE FUNCTION add_pi(PARAM_1 FLOAT)
    RETURNS FLOAT
    LANGUAGE SQL
    AS $$
        PARAM_1 + 3.1415926::FLOAT
    $$;

関数を2回呼び出します。1回目は、 FLOATを渡します。2回目は、 VARCHARを渡します。 VARCHAR は FLOATに変換され、各呼び出しからの出力は同じです。

SELECT add_pi(1.0), add_pi('1.0');
+-------------+---------------+
| ADD_PI(1.0) | ADD_PI('1.0') |
|-------------+---------------|
|   4.1415926 |     4.1415926 |
+-------------+---------------+

次に、 VARCHAR パラメーターを受け取るオーバーロード関数を作成します。

CREATE FUNCTION add_pi(PARAM_1 VARCHAR)
    RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
        PARAM_1 || ' + 3.1415926'
    $$;

前とまったく同じ CALLs を使用します。これら2つの CALLs と前の2つの CALLsの出力の違いに注意してください。

SELECT add_pi(1.0), add_pi('1.0');
+-------------+-----------------+
| ADD_PI(1.0) | ADD_PI('1.0')   |
|-------------+-----------------|
|   4.1415926 | 1.0 + 3.1415926 |
+-------------+-----------------+