Vertica UDF for R 샘플 등록해보기

Vertica 에서 지원하는 사용자 정의 함수는 C, Java, R 언어로 구현해서 등록할 수 있다.

Types of UDxs

• User-Defined Scalar Functions (UDSFs)
• User-Defined Transform Functions (UDTFs)
• User-Defined Aggregate Functions (UDAF)
• User-Defined Analytic Functions (UDAnF)
• User-Defined Load Functions (UDL)

위의 5가지 형태중에 R 언어로 구현할 수 있는 UDF 는 UDSF, UDTF 2가지 이다.

R 함수 Sample

/home/dbadmin/mylib/myRFunc.r

#실제 로직이 구현되는 함수 - scalar type

mul<- function(x)

{

        pr <- x[,1] * x[,2]

        pr

}

#함수정의 Vertica SQL 에서 함수 등록할 때 사용됨

mulFactory <- function()

{

        list(name=mul,udxtype=c("scalar"),intype=c("float","float"), outtype=c("float"))

}

#실제 로직이 구현되는 함수 - transform type without parameter

kmeansClu <- function(x)

{

        # Fix initial centroids to get predictable clustering.

        cx <- c(1.5, 2.5)

        cy <- c(3.5, 4.5)

        centroids <- data.frame(cx,cy)

        cl <- kmeans(x[,1:2], centroids)

        res <- data.frame(x[,1:2], cl$cluster)

        res

}

#함수정의 Vertica SQL 에서 함수 등록할 때 사용됨

kmeansCluFactory <- function()

{

        list(name=kmeansClu,udxtype=c("transform"),intype=c("float","float"), outtype=c("float","float","int"), outnames=c("x","y","cluster"))

}

#실제 로직이 구현되는 함수 - transform type with parameter

mykmeansPoly<-function(x,y)

{

        if(!is.null(y[['k']]))

                k=as.numeric(y[['k']])

        else

                stop("Expected parameter k")

        cols=ncol(x)

        cl<-kmeans(x[,2:cols-1],k)

        Result<-cl$cluster

        Result<-data.frame(VCol=Result)

        Result

}

#함수정의 Vertica SQL 에서 함수 등록할 때 사용됨

kmeansFactoryPoly<-function()

{

        list(name=mykmeansPoly,udxtype=c("transform"),intype=c("any"),outtype=c("int"),parametertypecallback=kmeansParameters)

}

#parameter type 정의, 함수 type 정의시 callback 함수로 선언해줌

kmeansParameters<-function()

{

        params<-data.frame(datatype=rep(NA,1),length=rep(NA,1),scale=rep(NA,1),name=rep(NA,1))

        params[1,1]="int"

        params[1,4]="k"

        params

}

위의 R Function Sample 은 R pacakge 설치와 같이 Vertica 의 각 Node 에 같은 위치에 배포를 해주어야 한다.

>scp myRFunc.r dbadmin@v002:/home/dbadmin/mylib

>scp myRFunc.r dbadmin@v003:/home/dbadmin/mylib

배포된 R Sample 을 vsql 을 통해서 UDF library 로 등록(아무 서버에서나 한번만 등록)

> create library rlib as '/home/dbadmin/mylib/myRFunc.r' language 'R';

등록된 R library 에 포함된 3가지 함수를 UDF로 등록 

• scalar 함수 등록

> CREATE FUNCTION Rmul AS LANGUAGE 'R' NAME 'mulFactory' LIBRARY rlib;

• transform 함수 등록

> CREATE TRANSFORM FUNCTION Kmeans AS LANGUAGE 'R' NAME 'kmeansCluFactory' LIBRARY rlib;

• transform with parameters 함수 등록

> CREATE TRANSFORM FUNCTION mykmeansPoly AS LANGUAGE 'R' NAME 'kmeansFactoryPoly' LIBRARY rlib;

등록된 UDF 를 제거할 때

drop transform function mykmeansPoly();

drop transform function Kmeans();

drop function Rmul();

drop library rlib;

위의 등록된 UDF 사용 예제

데이터 download

wget http://archive.ics.uci.edu/ml/machine-learning-database/iris/iris.data

iris 테이블 생성

create table iris (sl float, sw float, pl float, pw float, spec varchar(32));

생성된 테이블에 download 받은 샘플 데이터 복사

copy iris from '/home/dbadmin/mylib/iris.data' delimiter ',';

iris 테이블 생성 및 데이터 복사 완료

point_data 테이블 생성

create table point_data(x float, y float);

생성된 테이블에 데이터 입력

copy point_data from stdin delimiter ',';

1.2,3.1

2.3,2.7

1.8,2.3

3.3,5.4

3.6,4.3

3.0,4.5

\.

point_data 테이블 생성 및 데이터 입력 완료

UDF 함수 실행 테스트

scalar 함수 실행

> select sl,sw,Rmul(sl,sw) from iris;

transform without parameters 함수 실행

> select Kmeans(x,y) over() from point_data;

transform with parameters 함수 실행

> select spec, mykmeansPoly(sl,sw,pl,pw,spec USING PARAMETERS k=3) over (partition by spec) as kmean from iris;