カテゴリ:

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

CREATE FUNCTION

新しい UDF (ユーザー定義関数) を作成します。UDF には、次のいずれかを含めることができます。

  • SQL 式。

  • JavaScript コード。

  • Javaコード(ソースコードまたはコンパイル済みコード)。

UDF は、スカラーまたは表形式の結果を返すことができます。

こちらもご参照ください。

ALTER FUNCTIONDESCRIBE FUNCTIONDROP FUNCTIONSHOW USER FUNCTIONS

構文

SQL

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

JavaScript

CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
  RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
  [ [ NOT ] NULL ]
  LANGUAGE JAVASCRIPT
  [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
  [ VOLATILE | IMMUTABLE ]
  [ COMMENT = '<string_literal>' ]
  AS '<function_definition>'

Java

ソースコードがインラインの場合は、以下の構文を使用します。

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] FUNCTION [ IF NOT EXISTS ] <name>(<args>)
    RETURNS <return_type>
    [ [ NOT ] NULL ]
    LANGUAGE JAVA
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    [ COMMENT = '<string_literal>' ]
    [ IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name>_to_read' ...] ) ]
    HANDLER = '<path_to_method>'
    [ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
    AS '<function_definition>'

ソースコードがプリコンパイルされている場合(つまり、コードが JAR ファイルとして提供されている場合)は、次の構文を使用します。

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] FUNCTION [ IF NOT EXISTS ] <name>(<args>)
    RETURNS <return_type>
    [ [ NOT ] NULL ]
    LANGUAGE JAVA
    [ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
    [ VOLATILE | IMMUTABLE ]
    [ COMMENT = '<string_literal>' ]
    IMPORTS = ( '<stage_path_and_file_name_to_read>' [, '<stage_path_and_file_name_to_read>' ...] )
    HANDLER = '<path_to_method>'

必須パラメーター

すべての言語

名前 ( [ 引数名 引数データ型 ] [ , ... ] )

UDFの識別子(およびオプションで1つ以上の引数/入力)を指定します。UDFs は名前と引数の型によって識別および解決されるため、UDF が作成されるスキーマに対して識別子は一意である必要はありません。

ただし、識別子はアルファベットで始まる必要があり、識別子文字列全体が二重引用符で囲まれていない限り、スペースや特殊文字を含めることはできません(例: "My object")。二重引用符で囲まれた識別子も大文字と小文字が区別されます。

詳細については、 識別子の要件 をご参照ください。

RETURNS ...

UDF 型を決定する UDF によって返される結果を指定します。

  • 結果のデータ型: 指定されたデータ型の単一の値を返すスカラー UDF を作成します。

  • TABLE ( 列名 列データ型 , ... ): 指定したテーブル列と列型で表形式の結果を返すテーブル UDF を作成します。

注釈

Java UDFs の場合、

AS 関数定義

UDFによって実行されるコードを定義します。内容は、作成される UDF の型によって異なります。

  • SQL UDF: 有効な SQL 式。例など、SQL UDFs の詳細については、 SQL UDFs をご参照ください。

  • JavaScript UDF: 有効な JavaScript 。例など、JavaScript UDFs の詳細については、 JavaScript UDFs をご参照ください。

  • Java UDF: 有効なJava。例など、Java UDFs の詳細については、 Java UDFs をご参照ください。

詳細については、 使用上の注意 (このトピック内)をご参照ください。

注釈

関数定義 は、プリコンパイル済みの UDFs には必要ありません。

JavaScript

LANGUAGE JAVASCRIPT

コードは JavaScript 言語であることを指定します。

Java

LANGUAGE JAVA

コードはJava言語であることを指定します。

読み込むステージパスとファイル名

インポートするファイルの場所(ステージ)、パス、および名前。

ファイルは、 JAR ファイルまたは別の型のファイルにすることができます。

ファイルが JAR ファイルの場合は、1個以上の.classファイルと0個以上のリソースファイルを含めることができます。

JNI (Java Native Interface)はサポートされていません。Snowflakeは、(Javaバイトコードではなく)ネイティブコードを含むライブラリのロードを禁止しています。

Java UDFs は、 JAR 以外のファイルも読み取ることができます。例については、 Java UDF 内からのファイルの読み取り をご参照ください。

ファイル(JAR ファイルまたは他のファイル)をステージにコピーする場合、 PUT コマンドは名前付き内部ステージへのファイルのコピーをサポートしており、通常は JAR ファイルをステージに移動する最も簡単な方法であるため、Snowflakeは PUT 名前付き内部ステージを使用することをお勧めします。

外部ステージは許可されていますが、 PUT ではサポートされていません。

IMPORTS 句の各ファイルは、ファイルが異なるサブディレクトリまたは異なるステージにある場合でも、一意の名前を持っている必要があります。

IMPORTS 句と TARGET_PATH 句の両方が存在する場合、ファイルが異なるサブディレクトリまたは異なるステージにあっても、 TARGET_PATH 句のファイル名は IMPORTS 句の各ファイル名とは異なる必要があります。

TARGET_PATH が既存のファイルと一致する場合、Snowflakeはエラーを返します。 TARGET_PATH を使用して既存のファイルを上書きすることはできません。

プリコンパイル済みのJava UDF の場合は、 UDF を含む JAR ファイルの場所を指定するために IMPORTS 句が必要です。

インラインJava UDF の場合、 IMPORTS 句は、インラインJava UDF によって、ライブラリやテキストファイルなどの他のファイルにアクセスする必要がある場合にのみ必要です。

メソッドへのパス

該当.jarファイル内の静的メソッドのパスと名前。これは通常、次の形式です。

com.my_company.my_package.my_class.my_method

条件:

com.my_company.my_package

Javaソースコードファイルの先頭にある「package」コマンドに対応します。例:

package com.my_company.my_package。

JAR ファイルがパッケージの外部で作成された場合、 メソッドへのパス にはパッケージ名を含めないでください。

インラインJava

関数定義

インラインJava UDFs には、 関数定義 が必要です。

オプションのパラメーター

すべての言語

SECURE

関数が安全であることを指定します。セキュア関数の詳細については、 安全な UDFs をご参照ください。

[ [ NOT ] NULL ]

関数は NULL 値を返すことができるか、 NON-NULL 値のみを返す必要があるかを指定します。デフォルトは NULL です(つまり、関数は NULL を返すことができます)。

注釈

現在、 SQL UDFs には NOT NULL 句は適用されていません。 NOT NULL として宣言された SQL UDFs は、NULL 値を返すことができます。関数のコードに NULL 値が返されないように記述されている場合を除き、 SQL UDFs には NOT NULL を使用しないことをSnowflakeはお勧めします。

CALLED ON NULL INPUT または . RETURNS NULL ON NULL INPUT | STRICT

null入力で呼び出されたときの UDF の動作を指定します。入力がnullの場合は常にnullを返すシステム定義関数とは対照的に、 UDFs はnull入力を処理でき、入力がnullの場合でもnull以外の値を返します。

  • CALLED ON NULL INPUT 常にnull入力で UDF を呼び出します。そのような値を適切に処理するのは UDF 次第です。

  • RETURNS NULL ON NULL INPUT (またはその同義語 STRICT)は、入力がnullの場合、 UDF を呼び出しません。代わりに、その行に対して常にnull値が返されます。 UDF はnull以外の入力に対してもnullを返す場合があります。

デフォルト: CALLED ON NULL INPUT

VOLATILE | IMMUTABLE

結果を返すときの UDF の動作を指定します。

  • VOLATILE: UDF は、同じ入力でも(例えば、非決定性とステートフルネスのため)、異なる行に対して異なる値を返す場合があります。

  • IMMUTABLE: UDF は、関数が同じ入力で呼び出された場合、常に同じ結果を返すと想定しています。この保証はチェックされません。同じ入力に対して異なる値を返す UDF に IMMUTABLE を指定すると、未定義の動作が発生します。

デフォルト: VOLATILE

COMMENT = '文字列リテラル'

SHOW FUNCTIONS および SHOW USER FUNCTIONS 出力の DESCRIPTION 列に表示される UDFのコメントを指定します。

デフォルト: user-defined function

Java

インラインJava

書き込むためのステージパスおよびファイル名

TARGET_PATH 句は、 関数定義 で指定されたソースコードをコンパイルした後、Snowflakeがコンパイルされたコード(JAR ファイル)を書き込む場所を指定します。

この句が含まれている場合、ユーザーは JAR ファイルが不要になったとき(通常はJava UDF がドロップされたとき)に手動で削除する必要があります。

この句を省略すると、Snowflakeはコードが必要になるたびにソースコードを再コンパイルします。JAR ファイルは永続的には保存されないため、ユーザーは JAR ファイルをクリーンアップする必要はありません。

使用上の注意

すべての言語

  • 関数定義 にはサイズ制限があります。最大許容サイズは変更される可能性があります。

  • 関数定義 を囲む区切り文字は、一重引用符またはドル記号のペアのいずれかです。

    区切り文字として $$ を使用すると、一重引用符を含むストアドプロシージャの記述が簡単になります。

    関数の本体の区切り文字が一重引用符文字である場合、 関数定義 (例: 文字列リテラル)内の一重引用符は一重引用符でエスケープする 必要 があります。

SQL

  • 現在、 SQL UDFs には NOT NULL 句は適用されていません。

JavaScript

  • Snowflakeは UDF 作成時に JavaScript コードを検証しません(つまり、コードが有効かどうかに関係なく UDF の作成は成功します)。コードが有効でない場合は、クエリ時に UDF が呼び出されるとエラーが返されます。

Java

  • Javaでは、プリミティブデータ型は NULL 値を許可しないため、そのような型の引数に NULL を渡すと、エラーが発生します。

  • HANDLER 句では、メソッド名で大文字と小文字が区別されます。

  • IMPORTS および TARGET_PATH 句の場合、

    • パッケージ、クラス、およびファイル名では 大文字と小文字が区別されます

    • ステージ名では 大文字と小文字が区別されません

  • Snowflakeは次を検証します。

    • CREATE FUNCTION ステートメントの HANDLER で指定された JAR ファイルが存在し、指定されたクラスとメソッドが含まれていること。

    • UDF 宣言で指定された入力および出力タイプは、Javaメソッドの入力および出力タイプと互換性があること。

    検証は、作成時または実行時に実行できます。

    • CREATE FUNCTION ステートメントの実行時に、ユーザーがアクティブなSnowflakeウェアハウスに接続している場合、 UDF は作成時に検証されます。

    • それ以外の場合、 UDF は作成されますが、すぐには検証されず、Snowflakeは次のメッセージを返します。 Function <名前> created successfully, but could not be validated since there is no active warehouse

SQL

数学定数piのハードコーディングされた近似値を返す単純な SQL スカラー UDF を作成します。

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;

ハードコードされた値を返す単純な SQL テーブル UDF を作成します。

CREATE FUNCTION simple_table_function ()
  RETURNS TABLE (x INTEGER, y INTEGER)
  AS
  $$
    SELECT 1, 2
    UNION ALL
    SELECT 3, 4
  $$
  ;
SELECT * FROM TABLE(simple_table_function());

出力:

+---+---+
| X | Y |
|---+---|
| 1 | 2 |
| 3 | 4 |
+---+---+

クエリの結果を返す get_countries_for_user という名前の SQL テーブル UDF を作成します。

CREATE OR REPLACE FUNCTION get_countries_for_user ( id number )
  RETURNS TABLE (country_code char, country_name varchar)
  AS 'select distinct c.country_code, c.country_name
      from user_addresses a, countries c
      where a.user_id = id
      and c.country_code = a.country_code';

JavaScript

js_factorial という名前の JavaScript UDF を作成します。

CREATE OR REPLACE FUNCTION js_factorial(d double)
  RETURNS double
  LANGUAGE JAVASCRIPT
  STRICT
  AS '
  if (D <= 0) {
    return 1;
  } else {
    var result = 1;
    for (var i = 2; i <= D; i++) {
      result = result * i;
    }
    return result;
  }
  ';

Java

インライン CREATE FUNCTION ステートメントの基本的な例を次に示します。

create or replace function echo_varchar(x varchar)
returns varchar
language java
called on null input
handler='TestFunc.echo_varchar'
target_path='@~/testfunc.jar'
as
'class TestFunc {
  public static String echo_varchar(String x) {
    return x;
  }
}';

プリコンパイル済みの CREATE FUNCTION ステートメントの基本的な例を次に示します。

create function my_decrement_udf(i numeric(9, 0))
    returns numeric
    language java
    imports = ('@~/my_decrement_udf_package_dir/my_decrement_udf_jar.jar')
    handler = 'my_decrement_udf_package.my_decrement_udf_class.my_decrement_udf_method'
    ;

Java UDFs のさらなる例については、 をご参照ください。