はじめに
本記事では、Verticaの拡張機能であるユーザー定義拡張関数(UDx)を紹介します。
ユーザー定義拡張関数(UDx)とは
ユーザー定義拡張関数とは、外部の言語を用いて書かれた関数です。通常のSQLでの記述が難しい分析処理や、機械学習においてVerticaには実装されていないアルゴリズムを用いたい場合に有効な手法です。
現在Verticaでは、R, Python, C++, Java の4つの言語を利用することができます。今回は、プログラムコード例をRで記述しています。
ユーザー定義拡張関数の種類
Rで使用できるユーザー定義拡張関数には、大きくわけてスカラー関数とトランスフォーム関数の2種類があります。(この点はPythonでも同様）
スカラー関数
スカラー関数は、読み込んだデータ1行に対して単一の値（列）を返す関数です。この範囲に収まらない場合は、後述するトランスフォーム関数を利用する必要があります。
トランスフォーム関数
トランスフォーム関数は、読み込んだデータ1行に対して複数の行および複数の列を返すことができます。出力が多岐にわたる場合やテーブルの外形を変化させる処理を実施する場合は、こちらの関数を利用します。
スカラー関数の利用例
例1：2倍の値を返す
入力値と出力値が １列 対 １列 となる場合、スカラー関数で実装可能です。
例2：2列の和の値を返す
入力値と出力値が 複数列 対１列 となる場合でも、スカラー関数で実装可能です。
トランスフォーム関数の利用例
例3：2列の和と差の値を2列で返す
入力値と出力値が 複数列 対 複数列 となる場合には、トランスフォーム関数を利用する必要があります。
例4：2列の和と差の値を2行で返す
この例は入力値と出力値が 複数列 対 １列 となっています。しかし１行の入力値に対して複数行の出力値を返しているため、このようなケースではトランスフォーム関数を利用する必要があります。
スカラー関数のプログラムコード例
2列の和の値を返すスカラー関数をRで実装します。
Rコードの例（ファイル名：col_sum_func_sc.r）
#メインの関数col_sum_func_sc, 処理を記述する
col_sum_func_sc <- function(input.data.frame){
x1 <- input.data.frame[,1]
x2 <- input.data.frame[,2]
y <- x1 + x2
return(y)
}
#ファンクションcol_sum_func_scの定義や入出力の型を決定するファンクションcol_sum_func_sc_Factory
col_sum_func_sc_Factory <- function(){
list(
name = col_sum_func_sc
,udxtype = c("scalar")
,intype = c("float", "float")
,outtype = c("float")
)
}テーブルTable1を確認します。
dbadmin=> SELECT * FROM schema.Table1;
X1 | X2
----+----
2 | 3
5 | 7
11 | 10
(3 rows)Rプログラム col_sum_func_sc.r をVerticaのライブラリRlibSCとして登録します。
dbadmin=> CREATE OR REPLACE LIBRARY schema.RlibSC AS '/home/dbadmin/col_sum_func_sc.r' LANGUAGE 'R';ライブラリRlibに、ファンクションcol_sum_func_scを定義します。
この際、入出力の型を定義するファンクションcol_sum_func_sc_Factoryも同時に定義します。
dbadmin=> CREATE OR REPLACE FUNCTION col_sum_func_sc AS NAME 'col_sum_func_sc_Factory' LIBRARY schema.RlibSC;ファンクションcol_sum_func_scを呼び出すSQLを実行します。
dbadmin=> SELECT X1
dbadmin-> ,X2
dbadmin-> ,col_sum_func_sc(X1,X2) AS Y
dbadmin-> FROM schema.Table1;
X1 | X2 | Y
----+----+----
2 | 3 | 5
5 | 7 | 12
11 | 10 | 21
(3 rows)トランスフォーム関数のプログラムコード例
2列の和と差（1列目-2列目）の値を2列で返すトランスフォーム関数をRで実装します。
Rコードの例（ファイル名：col_sum_func_tf.r）
#メインの関数col_sum_func_tf, 処理を記述する
col_sum_func_tf <- function(input.data.frame){
x1 <- input.data.frame[,1]
x2 <- input.data.frame[,2]
y1 <- x1 + x2
y2 <- x1 - x2
output.data.frame <- data.frame(Y1 = y1, Y2 = y2)
return(output.data.frame)
}
#ファンクションcol_sum_func_tfの定義や入出力の型を決定するファンクションcol_sum_func_tf_Factory
col_sum_func_tf_Factory <- function(){
list(
name = col_sum_func_tf
,udxtype = c("transform")
,intype = c("float", "float")
,outtype = c("float","float")
)
}Rプログラム col_sum_func_tf.r をVerticaのライブラリRlibとして登録します。
dbadmin=> CREATE OR REPLACE LIBRARY schema.RlibTF AS '/home/dbadmin/col_sum_func_tf.r' LANGUAGE 'R';ライブラリRlibに、ファンクションcol_sum_func_tfを定義します。
この際、入出力の型を定義するファンクションcol_sum_func_tf_Factoryも同時に定義します。
dbadmin=> CREATE OR REPLACE TRANSFORM FUNCTION col_sum_func_tf AS NAME 'col_sum_func_tf_Factory' LIBRARY schema.RlibTF;ファンクションcol_sum_func_tfを呼び出すSQLを実行します。
dbadmin=> SELECT X1
dbadmin-> ,X2
dbadmin-> ,col_sum_func_tf(X1,X2)
dbadmin-> OVER(partition by X1,X2)
dbadmin-> FROM schema.Table1;
X1 | X2 | col0 | col1
----+----+------+------
2 | 3 | 5 | -1
5 | 7 | 12 | -2
11 | 10 | 21 | 1
(3 rows)※トランスフォーム関数では複数の列を出力するため、列名を指定するためにはオプション関数（parametertypecallback Function）を別に定義する必要があります。デフォルトの列名はcol0, col1……となります。
参考情報
Developing User-Defined Extensions (UDxs)
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ExtendingVertica/UDx/DevelopingUDxs.htm
Scalar Functions (UDSFs)
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ExtendingVertica/UDx/ScalarFunctions/ScalarFunctions.htm
Transform Functions (UDTFs)
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ExtendingVertica/UDx/TransformFunctions/TransformFunctions.htm
検証バージョンについて
この記事の内容はVertica 9.3.1で確認しています。