CREATE FUNCTION¶
新しい UDF (ユーザー定義関数) を作成します。この関数は、スカラー結果(UDFとして)または表形式の結果(UDTFとして)のいずれかを返すことができます。
UDFを作成するときは、サポートされている言語の1つでコードが記述されているハンドラーを指定します。ハンドラーの言語に応じて、ハンドラーソースコードをCREATE FUNCTIONステートメントにインラインするか、ハンドラーがプリコンパイルされている、またはステージのソースコードであるハンドラーの場所をCREATE FUNCTIONから参照することができます。
次のテーブルに、サポートされている各言語と、そのコードを CREATE FUNCTION でインラインに保持できるか、ステージに保持できるかをリストします。詳細については、 ハンドラーコードのインラインまたはステージ上での保持 をご参照ください。
言語 |
ハンドラーの場所 |
---|---|
インラインまたはステージング |
|
インライン |
|
インラインまたはステージング |
|
インライン |
- こちらもご参照ください。
ALTER FUNCTION、 DROP FUNCTION、 SHOW USER FUNCTIONS、 DESCRIBE FUNCTION
構文¶
CREATE FUNCTIONの構文は、UDFハンドラーとして使用している言語によって異なります。
Javaハンドラー
ソースコードがインラインの場合は、以下の構文を使用します。
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ SECURE ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
[ COPY GRANTS ]
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
LANGUAGE JAVA
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ RUNTIME_VERSION = <java_jdk_version> ]
[ COMMENT = '<string_literal>' ]
[ IMPORTS = ( '<stage_path_and_file_name_to_read>' [ , ... ] ) ]
[ PACKAGES = ( '<package_name_and_version>' [ , ... ] ) ]
HANDLER = '<path_to_method>'
[ TARGET_PATH = '<stage_path_and_file_name_to_write>' ]
AS '<function_definition>'
ハンドラーコードがステージで参照される場合(JAR 内など)は、次の構文を使用します。
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ SECURE ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
[ COPY GRANTS ]
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
LANGUAGE JAVA
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ RUNTIME_VERSION = <java_jdk_version> ]
[ COMMENT = '<string_literal>' ]
IMPORTS = ( '<stage_path_and_file_name_to_read>' [ , ... ] )
HANDLER = '<path_to_method>'
JavaScriptハンドラー
CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
[ COPY GRANTS ]
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>'
Pythonハンドラー
ソースコードがインラインの場合は、以下の構文を使用します。
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ SECURE ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
[ COPY GRANTS ]
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
LANGUAGE PYTHON
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
RUNTIME_VERSION = <python_version>
[ COMMENT = '<string_literal>' ]
[ IMPORTS = ( '<stage_path_and_file_name_to_read>' [ , ... ] ) ]
[ PACKAGES = ( '<package_name>[==<version>]' [ , ... ] ) ]
HANDLER = '<function_name>'
AS '<function_definition>'
ハンドラーコードがステージで参照される場合(モジュール内など)は、次の構文を使用します。
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] FUNCTION [ IF NOT EXISTS ] <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
[ COPY GRANTS ]
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
LANGUAGE PYTHON
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
RUNTIME_VERSION = <python_version>
[ COMMENT = '<string_literal>' ]
IMPORTS = ( '<stage_path_and_file_name_to_read>' [ , ... ] )
[ PACKAGES = ( '<package_name>[==<version>]' [ , ... ] ) ]
HANDLER = '<module_file_name>.<function_name>'
SQLハンドラー
CREATE [ OR REPLACE ] [ SECURE ] FUNCTION <name> ( [ <arg_name> <arg_data_type> ] [ , ... ] )
[ COPY GRANTS ]
RETURNS { <result_data_type> | TABLE ( <col_name> <col_data_type> [ , ... ] ) }
[ [ NOT ] NULL ]
[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]
[ VOLATILE | IMMUTABLE ]
[ MEMOIZABLE ]
[ COMMENT = '<string_literal>' ]
AS '<function_definition>'
必須パラメーター¶
すべての言語
name ( [ arg_name arg_data_type ] [ , ... ] )
UDFの識別子(およびオプションで1つ以上の引数/入力)を指定します。UDFs は名前と引数の型によって識別および解決されるため、UDF が作成されるスキーマに対して識別子は一意である必要はありません。
ただし、識別子はアルファベットで始まる必要があり、識別子文字列全体が二重引用符で囲まれていない限り、スペースや特殊文字を含めることはできません(例:
"My object"
)。二重引用符で囲まれた識別子も大文字と小文字が区別されます。詳細については、 識別子の要件 をご参照ください。
RETURNS ...
UDF 型を決定する UDF によって返される結果を指定します。
result_data_type
: 指定されたデータ型の単一の値を返すスカラー UDF を作成します。TABLE ( col_name col_data_type , ... )
: 指定したテーブル列と列タイプで表形式の結果を返すテーブル UDF を作成します。
注釈
Java UDFsについては、
result_data_type
は、 SQL-Java型マッピングテーブル のSQL Data Type
列にある必要があります。Python UDFsについては、
result_data_type
は、 SQL-Python型マッピングテーブル のSQL Data Type
列にある必要があります。
AS function_definition
UDFが呼び出されたときに実行されるハンドラーコードを定義します。
function_definition
値は、ハンドラーでサポートされている言語の1つのソースコードである必要があります。コードは次の場合があります。Java。詳細については、 Java UDFs の紹介 をご参照ください。
JavaScript。詳細については、 JavaScript UDFs の紹介 をご参照ください。
Python。詳細については、 Python UDFsの紹介 をご参照ください。
SQL 式。詳細については、 SQL UDFs の紹介 をご参照ください。
詳細については、 使用上の注意 (このトピック内)をご参照ください。
注釈
IMPORTS句を使用してステージでUDFハンドラコードが参照される場合、AS句は必要ありません。
Java
LANGUAGE JAVA
コードはJava言語であることを指定します。
RUNTIME_VERSION = java_jdk_version
使用するJava JDK ランタイムバージョンを指定します。サポートされているJavaのバージョンは次のとおりです。
11.x
RUNTIME_VERSION
が設定されていない場合は、Java JDK 11が使用されます。IMPORTS = (stage_path_and_file_name_to_read)
インポートするファイルの場所(ステージ)、パス、および名前。
ファイルは、 JAR ファイルまたは別の型のファイルにすることができます。
ファイルが JAR ファイルの場合は、1個以上の.classファイルと0個以上のリソースファイルを含めることができます。
JNI (Java Native Interface)はサポートされていません。Snowflakeは、(Javaバイトコードではなく)ネイティブコードを含むライブラリのロードを禁止しています。
Java UDFs は、 JAR 以外のファイルも読み取ることができます。例については、 IMPORTS を使用したファイルの読み取り をご参照ください。
ファイル(JAR ファイルまたは他のファイル)をステージにコピーする場合、
PUT
コマンドは名前付き内部ステージへのファイルのコピーをサポートしており、通常は JAR ファイルをステージに移動する最も簡単な方法であるため、SnowflakeはPUT
名前付き内部ステージを使用することをお勧めします。外部ステージは許可されていますが、
PUT
ではサポートされていません。IMPORTS
句の各ファイルは、ファイルが異なるサブディレクトリまたは異なるステージにある場合でも、一意の名前を持っている必要があります。IMPORTS
句とTARGET_PATH
句の両方が存在する場合、ファイルが異なるサブディレクトリまたは異なるステージにあっても、TARGET_PATH
句のファイル名はIMPORTS
句の各ファイル名とは異なる必要があります。TARGET_PATH
が既存のファイルと一致する場合、Snowflakeはエラーを返します。TARGET_PATH
を使用して既存のファイルを上書きすることはできません。ハンドラーがステージにある UDF の場合は、 UDF を含む JAR ファイルの場所を指定するために
IMPORTS
句が必要です。ハンドラーコードがインラインの UDF の場合、
IMPORTS
句は、インライン UDF によって、ライブラリやテキストファイルなどの他のファイルにアクセスする必要がある場合にのみ必要です。Snowparkパッケージ などのSnowflakeシステムパッケージの場合、 JAR ファイルを
IMPORTS
で指定するのではなく、PACKAGES
句でパッケージを指定できます。その場合は、パッケージ JAR ファイルをIMPORTS
値に含める必要はありません。インラインJava
function_definition
インラインJava UDFs には、 関数定義 が必要です。
JavaScript
LANGUAGE JAVASCRIPT
コードは JavaScript 言語であることを指定します。
Python
LANGUAGE PYTHON
コードはPython言語であることを指定します。
RUNTIME_VERSION = python_version
使用するPythonのバージョンを指定します。サポートされているPythonのバージョンは次のとおりです。
3.8
IMPORTS = (stage_path_and_file_name_to_read)
インポートするファイルの場所(ステージ)、パス、および名前。
ファイルは、.pyファイルまたは別のタイプのファイルにすることができます。
Python UDFsは、テキストファイルなどのPython以外のファイルも読み取ることができます。例については、 UDFハンドラーを使用したファイルの読み取り をご参照ください。
ファイルをステージにコピーする場合、
PUT
コマンドは名前付き内部ステージへのファイルのコピーをサポートしており、通常はPUT
コマンドはファイルをステージに移動する最も簡単な方法であるため、Snowflakeは名前付き内部ステージを使用することをお勧めします。外部ステージは許可されていますが、
PUT
ではサポートされていません。IMPORTS
句の各ファイルは、ファイルが異なるサブディレクトリまたは異なるステージにある場合でも、一意の名前を持っている必要があります。ハンドラーコードがステージに格納されている場合は、
IMPORTS
句を使用してハンドラーコードの場所を指定する必要があります。インラインPython UDFの場合、
IMPORTS
句はUDFハンドラーがライブラリやテキストファイルなどの他のファイルにアクセスする必要がある場合にのみ必要です。numpy _などのSnowflakeシステムに含まれるパッケージの場合、
PACKAGES
句のみでパッケージを指定し、パッケージのソースをIMPORTS
値として省略できます。HANDLER = handler_name
ハンドラー関数またはクラスの名前。
ハンドラーがスカラーUDF用であり、表形式ではない値を返す場合、HANDLER値は関数名である必要があります。ハンドラーコードがCREATE FUNCTIONステートメントにインラインの場合、関数名のみを使用できます。ハンドラーコードがステージで参照される場合、この値は次の形式のようにモジュール名で修飾する必要があります:
my_module.my_function
。ハンドラーが表形式のUDF用である場合、HANDLER値はハンドラークラスの名前である必要があります。
オプションのパラメーター¶
すべての言語
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 = 'string_literal'
SHOW FUNCTIONS および SHOW USER FUNCTIONS 出力の DESCRIPTION 列に表示される UDFのコメントを指定します。
デフォルト:
user-defined function
COPY GRANTS
CREATE OR REPLACE FUNCTION を使用して新しい関数を作成するときに、元の関数のアクセス権限を保持することを指定します。
このパラメーターは、既存の関数から新しい関数に、 OWNERSHIP を 除く すべての権限をコピーします。新しい関数は、スキーマ内のオブジェクト型に定義された将来の付与を継承します。デフォルトでは、 CREATE FUNCTION ステートメントを実行するロールが新しい関数を所有します。
ノート:
データ共有 では、既存の関数が別のアカウントと共有されていた場合に、代替関数も共有されます。
置換関数の SHOW GRANTS 出力には、ステートメントが実行されたときの現在のタイムスタンプとともに、コピーされた権限の被付与者が CREATE FUNCTION ステートメントを実行したロールとしてリストされます。
付与をコピーする操作は、 CREATE FUNCTION コマンドで(つまり、同じトランザクション内で)アトミックに発生します。
Java
PACKAGES = (package_name_and_version)
依存関係として必要なSnowflakeシステムパッケージの名前とバージョン番号。値は、
package_name:version_number
の形式にする必要があります。ここで、package_name
は、snowflake_domain:package
です。Snowflakeにシステムで利用可能な最新バージョンを使用させるために、バージョン番号としてlatest
を指定できることに注意してください。例:
-- Use version 1.2.0 of the Snowpark package. packages=('com.snowflake:snowpark:1.2.0') -- Use the latest version of the Snowpark package. packages=('com.snowflake:snowpark:latest')
Snowflakeで次の SQL を実行すると、サポートされているシステムパッケージのリストを見つけることができます。
select * from information_schema.packages where language ='java';
PACKAGES
で指定する依存関係の場合は、IMPORTS
句でその JAR ファイルを指定する必要はありません。インラインJava
TARGET_PATH = stage_path_and_file_name_to_write
TARGET_PATH
句は、function_definition
で指定されたソースコードをコンパイルした後、Snowflakeがコンパイルされたコード(JAR ファイル)を書き込む場所を指定します。この句が含まれている場合、ユーザーは JAR ファイルが不要になったとき(通常はJava UDF がドロップされたとき)に手動で削除する必要があります。
この句を省略すると、Snowflakeはコードが必要になるたびにソースコードを再コンパイルします。JAR ファイルは永続的には保存されないため、ユーザーは JAR ファイルをクリーンアップする必要はありません。
Python
PACKAGES = (package_name[==package_version])
依存関係として必要なパッケージの名前とバージョン番号。値は、
package_name==version_number
の形式にする必要があります。バージョン番号を省略すると、Snowflakeはシステムで利用可能な最新のパッケージを使用します。例:
-- Use version 1.2.2 of the NumPy package. packages=('numpy==1.2.2') -- Use the latest version of the NumPy package. packages=('numpy')
Snowflakeで次の SQL を実行すると、サポートされているシステムパッケージのリストを見つけることができます。
select * from information_schema.packages where language ='python';
含まれているパッケージの詳細については、 サードパーティパッケージの使用 をご参照ください。
SQL
MEMOIZABLE
関数がメモ化可能であることを指定します。
詳細については、 メモ化可能 UDFs をご参照ください。
使用上の注意¶
すべての言語
function_definition
にはサイズ制限があります。最大許容サイズは変更される可能性があります。function_definition
を囲む区切り文字は、一重引用符またはドル記号のペアのいずれかです。区切り文字として
$$
を使用すると、一重引用符を含む関数の記述が簡単になります。関数の本体の区切り文字が一重引用符文字である場合、
function_definition
(例: 文字列リテラル)内の一重引用符は一重引用符でエスケープする 必要があります。マスキングポリシー で UDF を使用する場合は、列、 UDF、およびマスキングポリシーのデータ型が一致していることを確認します。詳細については、 マスキングポリシー内のユーザー定義関数 をご参照ください。
メタデータについて。
注意
Snowflakeサービスを使用する場合、お客様は、個人データ(ユーザーオブジェクト向け以外)、機密データ、輸出管理データ、またはその他の規制されたデータがメタデータとして入力されていないことを確認する必要があります。詳細については、 Snowflakeのメタデータフィールド をご参照ください。
CREATE OR REPLACE <オブジェクト> ステートメントはアトミックです。つまり、オブジェクトが置き換えられると、古いオブジェクトの削除と新しいオブジェクトの作成が1つのトランザクションで処理されます。
Java
Javaでは、プリミティブデータ型は NULL 値を許可しないため、そのような型の引数に NULL を渡すと、エラーが発生します。
HANDLER
句では、メソッド名で大文字と小文字が区別されます。IMPORTS
およびTARGET_PATH
句の場合、パッケージ、クラス、およびファイル名では 大文字と小文字が区別されます。
ステージ名では 大文字と小文字が区別されません。
PACKAGES
句を使用して、Snowparkの依存関係など、Snowflakeシステム定義の依存関係のパッケージ名とバージョン番号を指定できます。その他の依存関係については、IMPORTS
句を使用して依存関係 JAR ファイルを指定します。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
。
JavaScript
Snowflakeは UDF 作成時に JavaScript コードを検証しません(つまり、コードが有効かどうかに関係なく UDF の作成は成功します)。コードが有効でない場合は、クエリ時にUDFが呼び出されるとエラーが返されます。
Python
HANDLER
句では、ハンドラー関数名で大文字と小文字が区別されます。IMPORTS
サブ句では:ファイル名は 大文字と小文字が区別されます。
ステージ名では 大文字と小文字が区別されません。
PACKAGES
句を使用して、Snowparkの依存関係など、依存関係のパッケージ名とバージョン番号を指定できます。その他の依存関係については、IMPORTS
句を使用して依存関係ファイルを指定します。Snowflakeは次を検証します。
CREATE FUNCTION
ステートメントのHANDLER
で指定された関数またはクラスが存在します。UDF宣言で指定された入力および出力タイプは、ハンドラーの入力および出力タイプと互換性があること。
SQL
現在、 SQL UDFs には
NOT NULL
句は適用されていません。
例¶
Java
インラインハンドラーを使用した CREATE FUNCTION の基本的な例を次に示します。
create or replace function echo_varchar(x varchar)
returns varchar
language java
called on null input
handler='TestFunc.echoVarchar'
target_path='@~/testfunc.jar'
as
'class TestFunc {
public static String echoVarchar(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 のさらなる例については、 例 をご参照ください。
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;
}
';
Python
次の例のコードは、ハンドラーコードが udf
としてインラインの py_udf
関数を作成します。
create or replace function py_udf()
returns variant
language python
runtime_version = '3.8'
packages = ('numpy','pandas','xgboost==1.5.0')
handler = 'udf'
as $$
import numpy as np
import pandas as pd
import xgboost as xgb
def udf():
return [np.__version__, pd.__version__, xgb.__version__]
$$;
次の例のコードは、ハンドラーが @my_stage
ステージにある sleepy.py
ファイル内の dream
関数を作成します。
create or replace function dream(i int)
returns variant
language python
runtime_version = '3.8'
handler = 'sleepy.snore'
imports = ('@my_stage/sleepy.py')
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());
出力:
SELECT * FROM TABLE(simple_table_function());
+---+---+
| X | Y |
|---+---|
| 1 | 2 |
| 3 | 4 |
+---+---+
複数のパラメーターを受け入れる UDF を作成します。
CREATE FUNCTION multiply1 (a number, b number)
RETURNS number
COMMENT='multiply two numbers'
AS 'a * b';
クエリの結果を返す 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';