分析業務の中でデータ加工・集計作業を実施していると、Snowflakeが提供する組み込みのシステム定義関数だけは実現できない加工処理や、同じ構文を繰り返すことでSQLが冗長となるケースがありますが、ユーザー定義関数（UDF）を使うことで、課題を解決できる場合があります。
冗長なSQLについては、シンプルにSQL記載できることで分析・集計業務効率が上がります。システム関数で処理できない場合はPython等にてUDFを作成することで柔軟な処理を記述できます。
この記事では、ユーザー定義関数（UDF）の基礎知識や、実際の作成方法・利用方法について説明します。
UDFの基礎知識
ユーザー定義関数（UDF）は、Select構文で、ユーザーが自分で作った関数を使うことができる機能です。
例えば「func（）」というUDFを作った場合、以下のように呼び出すことができます。
select func() as col1 from XXX;func()関数の中身はユーザー自身で作成できるので、自由度の高い加工処理が実現できます。
UDF作成に利用できる言語はいくつかあるのですが、分析屋としては主にSQL・Pythonを利用します。
以降は、SQL版とPython版のUDF作成方法について解説します。
UDFの作成方法について（SQL版）
SnowfakeでUDFを作成するためには、CREATE FUNCTION構文を使いますが、まずは理解しやすいSQL版のUDF作成について説明します。
UDF（SQL版）作成時のポイントは以下となります。
- ①戻り値の型を指定
- ②言語を指定
- ③処理を記載
以下はサンプルSQLです。
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
$$
;関数の動作としては、引数n、mに対して、ｎが10より小さい場合 nを返し、それ以外の場合はmを返します。
以下各ポイントの詳細を記載します。
①戻り値の型を指定
CREATE OR REPLACE FUNCTION構文のRETURNSパラメータは、戻り値の型を指定するパラメータです。
今回のサンプルは、数字を返すので「Number」と指定します。
②言語を指定
LANGUAGEパラメータは、UDFで利用する言語を指定します。「PYTHON」または「SQL」を指定可能です。
③SQL処理を記載
処理を記載する部分は「$$」で囲まれている必要があります。
今回のサンプルでは、SQL文としてcase when ～end 構文を利用しています。
UDFの作成方法（Python版）
Python版のUDF作成について説明します。
UDF作成時のポイントについては、SQL版で説明した内容は割愛し、Python版独自のポイントのみ説明します。
Python版のUDF作成時のポイントは以下となります。
- ①PYTHONのバージョンを指定
- ②呼び出すPython関数名を指定
- ③Python関数を定義
- ④Python処理を記載
以下はサンプルSQLです。
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)
$$;関数自体は、引数 strの文字の長さを測定して返します。
以下各ポイントの詳細を記載します。
①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ではコメント扱いされませんので、うっかり記載するとエラーとなります。
Pythonでコメントを記載する場合は、「#」以降にコメントを記載する必要があります。
戻り値については、return文で設定します。
UDFの呼び出し方法について
UDFの呼び出しは、select文で関数を呼び出すだけです。UDFは作成は大変ですが、呼び出しは以下のように、シンプルとなります。
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の呼び出し結果は以下のように表示されます。
コメント