ユーザー定義関数（UDF）を使うことで分析業務のデータ加工や集計作業の効率を上げることが可能です。
例えば、同じ構文を繰り返すことでSQLが冗長となるケースでは、UDFを利用することで可読性・メンテナンス性があがります。
また、Snowflakeが提供する組み込みのシステム定義関数だけは実現できない加工なども、UDFを作成することで実現可能となります。
この記事では、ユーザー定義関数（UDF）の基礎知識や、実際の作成方法・利用方法について説明します。
UDFの基礎知識
ユーザー定義関数（UDF）とは、Select構文で、ユーザーが自分で作った関数を使うことができる機能です。
例えば「func（）」というUDFを作った場合、以下のように呼び出すことができます。
select func() as col1 from XXX;func()関数の中身はユーザー自身で作成できるので、自由度の高い加工処理が実現できます。
UDF作成に利用できる言語はいくつかあるのですが、分析屋としては主にSQL・Pythonを利用します。Pythonは分析用のライブラリも充実しているので相性が良いです。
以降は、SQL版とPython版のUDF作成方法について解説します。
UDFの作成方法について（SQL版）
SnowfakeでUDFを作成するためには、CREATE FUNCTION構文を使いますが、まずは理解しやすいSQL版のUDF作成について説明します。
まず、サンプルのUDFとして、引数n、mに対して、ｎが10より小さい場合 nを返し、それ以外の場合はmを返す関数を作成することとします。コードは以下の通り。
CREATE OR REPLACE FUNCTION cale_f(n INT ,m INT)
// ①戻り値の型を指定
RETURNS NUMBER
// ②言語を指定
LANGUAGE SQL
AS
$$
// ③SQL処理を記載
case when
10 < n
then
n
else m end
$$
;上記、UDF（SQL版）作成時のポイントは以下となります。
- ①戻り値の型を指定
- ②言語を指定
- ③処理を記載
以下各ポイントの詳細を記載します。
①戻り値の型を指定
CREATE OR REPLACE FUNCTION構文のRETURNSパラメータは、戻り値の型を指定するパラメータです。
今回のサンプルは、数字を返すので「Number」と指定します。
②言語を指定
LANGUAGEパラメータは、UDFで利用する言語を指定します。「PYTHON」または「SQL」を指定可能です。
③SQL処理を記載
処理を記載する部分は「$$」で囲まれている必要があります。
今回のサンプルでは、SQL文としてcase when ～end 構文を利用しています。
UDFの作成方法（Python版）
Python版のUDF作成について説明します。
今回のUDFは、引数 strの文字の長さを測定して返す関数となります。コードは以下の通り。
CREATE OR REPLACE FUNCTION p_len(str string)
RETURNS number(1,0)
LANGUAGE PYTHON
//① PYTHONのバージョンを指定
RUNTIME_VERSION = '3.8'
//② 呼び出すPython関数名を指定
HANDLER = 'p_len'
as
$$
# ③Python関数を定義
def p_len(str):
# ④Python処理を記載
if str is None:
return 0
return len(str)
$$;Python版のUDF作成時のポイントは以下となります。
なお、UDF作成時のポイントについては、SQL版で説明した内容は割愛し、Python版独自のポイントのみ説明しています。
- ①PYTHONのバージョンを指定
- ②呼び出すPython関数名を指定
- ③Python関数を定義
- ④Python処理を記載
以下各ポイントの詳細を記載します。
①PYTHONのバージョンを指定
RUNTIME_VERSIONパラメータは、Pythonの実行環境のバージョンを指定します。このサンプルでは’3.8’を指定しています。
‘3.8’を調べると24年10月でサポート終了するようですが・・執筆時点で影響は不明です。スミマセン、システム関係の知識はあまりなく・・。
②呼び出すPython関数名を指定
HANDLERパラメータは、UDF関数から呼び出す、Python関数名を指定します。Python関数は def で定義した関数のことです。
今回のケースでは、UDF関数名はCreate Functionで定義した「p_len」、Python関数名はdefで定義した「p_len」です。
別の言語でUDFを動作させるため、HANDLERパラメータで別の言語の関数を指定します。
③Python関数を定義
Python関数の定義を記載します。引数はUDFの引数と同じ個数を設定しましょう。
def 構文はPythonのお作法で、def 関数名( 引数 ): でPython関数を定義します。
④Python処理を記載
Python言語で処理を記載します。
なお、SQLとPythonの両方の言語で記載するため、コメントの使い方も言語によって使い分ける必要があります。
SQLでは「//」以降がコメント扱いされますが、Python「#」以降がコメントとなるので注意ください。
また関数の戻り値については、return文で設定します。
UDFの呼び出し方法について
UDFを呼び出すには、以下のようにselect文内で作成した関数を指定するだけです。
select
cale_f(1 ,2) as col1
,cale_f(11 ,2) as col2
,p_len('aiueo') as col3
,p_len(null) as col4;
また、上記のように同じ関数を複数回呼び出すこともできます。ただし、１つのSQLにUDFを呼び出せる上限回数があるようなので気を付けてください。100個ぐらいだったでしょうか？
UDFの呼び出し結果は以下のように表示されます。
コメント