はじめに
本記事では、Rで記述された機械学習の関数をVerticaに読み込んで予測を行う方法に関して説明します。
流れは以下の通りになります。
・使用するデータ(irisデータ)を読み込む。
・学習用データからモデルを作成する関数train_funcを実行する。
・作成したモデルを評価用データに適用し予測値を得る関数test_funcを実行する。
・作成した関数をSQLで実行し処理結果を得る。
サンプルスキーマ、データのダウンロード
以下URLよりサンプルファイルをダウンロードします。
https://github.com/vertica/Machine-Learning-Examples
画面右上にある「Clone or Download」をクリックします。
展開される画面の右下にある「Download ZIP」をクリックしてファイルを保存します。
サンプルスキーマの作成、データのロード
ダウンロードしたファイルをVerticaサーバ上の任意のディレクトリに転送します。
転送後、以下コマンドでファイルを解凍します。
$ cd
$ unzip Machine-Learning-Examples-master.zip解凍後に以下コマンドでサンプルスキーマとテーブルの作成、データロードを実行します。
$ cd Machine-Learning-Examples-master/data
$ /opt/vertica/bin/vsql -d <データベース名> -w <パスワード> -f load_ml_data.sql
DROP TABLE
DROP TABLE
DROP TABLE
CREATE TABLE
～途中、省略～
COMMIT
CREATE TABLE
CREATE TABLE
以下のようなテーブルが作成されます。
$ /opt/vertica/bin/vsql -d <データベース名> -w <パスワード>
dbadmin=> \d
List of tables
Schema | Name | Kind | Owner | Comment
--------+-------------------+-------+---------+---------
public | agar_dish | table | dbadmin |
public | agar_dish_1 | table | dbadmin |
public | agar_dish_2 | table | dbadmin |
public | baseball | table | dbadmin |
public | dem_votes | table | dbadmin |
public | faithful | table | dbadmin |
public | faithful_testing | table | dbadmin |
public | faithful_training | table | dbadmin |
public | house84 | table | dbadmin |
public | house84_clean | table | dbadmin |
public | house84_test | table | dbadmin |
public | house84_train | table | dbadmin |
public | iris | table | dbadmin |
public | iris1 | table | dbadmin |
public | iris2 | table | dbadmin |
public | mtcars | table | dbadmin |
public | mtcars_test | table | dbadmin |
public | mtcars_train | table | dbadmin |
public | rep_votes | table | dbadmin |
public | salary_data | table | dbadmin |
public | transaction_data | table | dbadmin |
(21 rows)データの読み込み
今回使用するirisデータには「あやめ」の3品種’setosa’、’versicolor’、’virginica’のSepal(がく片)の長さと幅、及びPetal(花びら)の長さと幅の情報が含まれています。今回は、あらかじめ学習用データ(iris1)と評価用データ(iris2)の2つにあらかじめ分割されたものを用います。
学習用データiris1を表示してみます。
dbadmin=>SELECT * FROM schema.iris1;
id | Sepal_Length | Sepal_Width | Petal_Length | Petal_Width | Species
-----+--------------+-------------+--------------+-------------+------------
1 | 5.1 | 3.5 | 1.4 | 0.2 | setosa
2 | 4.9 | 3 | 1.4 | 0.2 | setosa
3 | 4.7 | 3.2 | 1.3 | 0.2 | setosa
4 | 4.6 | 3.1 | 1.5 | 0.2 | setosa
6 | 5.4 | 3.9 | 1.7 | 0.4 | setosa
7 | 4.6 | 3.4 | 1.4 | 0.3 | setosa
8 | 5 | 3.4 | 1.5 | 0.2 | setosa
9 | 4.4 | 2.9 | 1.4 | 0.2 | setosa
11 | 5.4 | 3.7 | 1.5 | 0.2 | setosa
12 | 4.8 | 3.4 | 1.6 | 0.2 | setosa
13 | 4.8 | 3 | 1.4 | 0.1 | setosa
16 | 5.7 | 4.4 | 1.5 | 0.4 | setosa
17 | 5.4 | 3.9 | 1.3 | 0.4 | setosa
18 | 5.1 | 3.5 | 1.4 | 0.3 | setosa
19 | 5.7 | 3.8 | 1.7 | 0.3 | setosa
20 | 5.1 | 3.8 | 1.5 | 0.3 | setosa
23 | 4.6 | 3.6 | 1 | 0.2 | setosa
26 | 5 | 3 | 1.6 | 0.2 | setosa
27 | 5 | 3.4 | 1.6 | 0.4 | setosa
28 | 5.2 | 3.5 | 1.5 | 0.2 | setosa
29 | 5.2 | 3.4 | 1.4 | 0.2 | setosa
30 | 4.7 | 3.2 | 1.6 | 0.2 | setosa
31 | 4.8 | 3.1 | 1.6 | 0.2 | setosa
36 | 5 | 3.2 | 1.2 | 0.2 | setosa
37 | 5.5 | 3.5 | 1.3 | 0.2 | setosa
40 | 5.1 | 3.4 | 1.5 | 0.2 | setosa
41 | 5 | 3.5 | 1.3 | 0.3 | setosa
44 | 5 | 3.5 | 1.6 | 0.6 | setosa
49 | 5.3 | 3.7 | 1.5 | 0.2 | setosa
50 | 5 | 3.3 | 1.4 | 0.2 | setosa
52 | 6.4 | 3.2 | 4.5 | 1.5 | versicolor
53 | 6.9 | 3.1 | 4.9 | 1.5 | versicolor
55 | 6.5 | 2.8 | 4.6 | 1.5 | versicolor
56 | 5.7 | 2.8 | 4.5 | 1.3 | versicolor
58 | 4.9 | 2.4 | 3.3 | 1 | versicolor
60 | 5.2 | 2.7 | 3.9 | 1.4 | versicolor
61 | 5 | 2 | 3.5 | 1 | versicolor
67 | 5.6 | 3 | 4.5 | 1.5 | versicolor
68 | 5.8 | 2.7 | 4.1 | 1 | versicolor
69 | 6.2 | 2.2 | 4.5 | 1.5 | versicolor
70 | 5.6 | 2.5 | 3.9 | 1.1 | versicolor
72 | 6.1 | 2.8 | 4 | 1.3 | versicolor
74 | 6.1 | 2.8 | 4.7 | 1.2 | versicolor
76 | 6.6 | 3 | 4.4 | 1.4 | versicolor
77 | 6.8 | 2.8 | 4.8 | 1.4 | versicolor
78 | 6.7 | 3 | 5 | 1.7 | versicolor
79 | 6 | 2.9 | 4.5 | 1.5 | versicolor
80 | 5.7 | 2.6 | 3.5 | 1 | versicolor
81 | 5.5 | 2.4 | 3.8 | 1.1 | versicolor
83 | 5.8 | 2.7 | 3.9 | 1.2 | versicolor
84 | 6 | 2.7 | 5.1 | 1.6 | versicolor
85 | 5.4 | 3 | 4.5 | 1.5 | versicolor
86 | 6 | 3.4 | 4.5 | 1.6 | versicolor
88 | 6.3 | 2.3 | 4.4 | 1.3 | versicolor
92 | 6.1 | 3 | 4.6 | 1.4 | versicolor
93 | 5.8 | 2.6 | 4 | 1.2 | versicolor
94 | 5 | 2.3 | 3.3 | 1 | versicolor
95 | 5.6 | 2.7 | 4.2 | 1.3 | versicolor
98 | 6.2 | 2.9 | 4.3 | 1.3 | versicolor
99 | 5.1 | 2.5 | 3 | 1.1 | versicolor
100 | 5.7 | 2.8 | 4.1 | 1.3 | versicolor
103 | 7.1 | 3 | 5.9 | 2.1 | virginica
105 | 6.5 | 3 | 5.8 | 2.2 | virginica
106 | 7.6 | 3 | 6.6 | 2.1 | virginica
108 | 7.3 | 2.9 | 6.3 | 1.8 | virginica
109 | 6.7 | 2.5 | 5.8 | 1.8 | virginica
111 | 6.5 | 3.2 | 5.1 | 2 | virginica
112 | 6.4 | 2.7 | 5.3 | 1.9 | virginica
114 | 5.7 | 2.5 | 5 | 2 | virginica
116 | 6.4 | 3.2 | 5.3 | 2.3 | virginica
117 | 6.5 | 3 | 5.5 | 1.8 | virginica
121 | 6.9 | 3.2 | 5.7 | 2.3 | virginica
122 | 5.6 | 2.8 | 4.9 | 2 | virginica
123 | 7.7 | 2.8 | 6.7 | 2 | virginica
124 | 6.3 | 2.7 | 4.9 | 1.8 | virginica
125 | 6.7 | 3.3 | 5.7 | 2.1 | virginica
126 | 7.2 | 3.2 | 6 | 1.8 | virginica
128 | 6.1 | 3 | 4.9 | 1.8 | virginica
130 | 7.2 | 3 | 5.8 | 1.6 | virginica
131 | 7.4 | 2.8 | 6.1 | 1.9 | virginica
132 | 7.9 | 3.8 | 6.4 | 2 | virginica
135 | 6.1 | 2.6 | 5.6 | 1.4 | virginica
137 | 6.3 | 3.4 | 5.6 | 2.4 | virginica
138 | 6.4 | 3.1 | 5.5 | 1.8 | virginica
139 | 6 | 3 | 4.8 | 1.8 | virginica
140 | 6.9 | 3.1 | 5.4 | 2.1 | virginica
144 | 6.8 | 3.2 | 5.9 | 2.3 | virginica
147 | 6.3 | 2.5 | 5 | 1.9 | virginica
149 | 6.2 | 3.4 | 5.4 | 2.3 | virginica
150 | 5.9 | 3 | 5.1 | 1.8 | virginica
(90 rows)説明変数には、Sepal_Length, Sepal_Width, Petal_Length, Petal_Widthを用います。目的変数はSpeciesであり、3種類の花のうちいずれかとなっています。
Rコード
‘/home/dbadmin/’に、Rファイル’randomforest_model.r’を格納します。
このファイルには学習用関数train_funcと推定用関数test_funcを記述しています。
学習用関数train_funcの例：
#ライブラリの読み込み
library(randomForest)
library(dplyr)
#メイン関数
train_func <- function(input.data.frame, parameters.data.frame) {
#パラメータ値の取得
tree <- parameters.data.frame[['tree']]
model_save_path <- parameters.data.frame[['model_save_path']]
#モデルを構築して保存
randomForest(Species~., data = input.data.frame, ntrees = tree) %>%
saveRDS(file = model_save_path)
#文字列を出力
'OK' %>%
return()
}
#メイン関数の名前や形、入出力変数の型を指定する関数
train_func_Factory <- function() {
list(name = train_func,
udxtype = c("transform"),
intype = c("varchar","float","float","float","float"),
outtype = c("varchar"),
parametertypecallback = train_func_Parameters)
}
#パラメータの型や名前などを指定する関数
train_func_Parameters <- function() {
parameters <- list(datatype = c("int","varchar"),
length = c(NA,100),
scale = c(NA,NA),
name = c("tree","model_save_path"))
return(parameters)
}推定用関数test_funcの例：
#メイン関数
test_func <- function(input.data.frame, parameters.data.frame){
#パラメータの値取得、また保存されているモデルの読み込み
model_read_path <- parameters.data.frame[['model_read_path']]
model <- readRDS(model_read_path)
#予測値の算出
input.data.frame %>%
dplyr::mutate(pred = predict(model, newdata = .)) %>%
dplyr::select(id, Species, pred) %>%
return()
}
#メイン関数の名前や形、入出力変数の型を指定する関数
test_func_Factory <- function() {
list(name = test_func,
udxtype = c("transform"),
intype = c("int","float","float","float","float","varchar"),
outtype = c("int","varchar","varchar"),
outtypecallback = test_func_Return,
parametertypecallback = test_func_Parameters)
}
#パラメータの型や名前などを指定する関数
test_func_Parameters <- function() {
parameters <- list(datatype = c("varchar"),
length = c(100),
scale = c(NA),
name = c("model_read_path"))
return(parameters)
}
#アウトプットの列名などを指定するための関数
test_func_Return <- function(arguments.data.frame, parameters.data.frame){
output.return.type <- data.frame(datatype = c("int","varchar","varchar"),
length = c(NA,12,12),
scale = rep(NA,3),
name = c("ID","Species","Predict"))
}関数の作成
vsqlで関数を作成します。
/* Rプログラム randomforest_model.r をVerticaのライブラリRlibとして登録する */
dbadmin=>CREATE LIBRARY Rlib AS '/home/dbadmin/randomforest_model.r' LANGUAGE 'R';
CREATE LIBRARY
/* ライブラリRlibに、関数train_funcを定義する */
dbadmin=>CREATE TRANSFORM FUNCTION train_func AS LANGUAGE 'R' NAME 'train_func_Factory' LIBRARY Rlib;
CREATE TRANSFORM FUNCTION
/* ライブラリRlibに、関数test_funcを定義する */
dbadmin=>CREATE TRANSFORM FUNCTION test_func AS LANGUAGE 'R' NAME 'test_func_Factory' LIBRARY Rlib;
CREATE TRANSFORM FUNCTION関数の実行
/* 関数train_funcを実行する。インプットする列、パラメータ、の順に変数をセットする */
dbadmin=>SELECT train_func(Species,Sepal_Length,Sepal_Width, Petal_Length, Petal_Width USING PARAMETERS tree=100, model_save_path='/home/dbadmin/RFC_model.obj') OVER() FROM iris1;
col0
------
OK
(1 row)
/* 関数test_funcを実行する。IDと実際のアヤメの種類(Species)、予測値(Predict)が出力される。*/
dbadmin=>SELECT test_func(id,Sepal_Length,Sepal_Width, Petal_Length, Petal_Width,Species USING PARAMETERS model_read_path = '/home/dbadmin/RFC_model.obj') OVER() FROM iris2;
id | Species | Predict
-----+------------+------------
5 | setosa | setosa
10 | setosa | setosa
14 | setosa | setosa
15 | setosa | setosa
21 | setosa | setosa
22 | setosa | setosa
24 | setosa | setosa
25 | setosa | setosa
32 | setosa | setosa
33 | setosa | setosa
34 | setosa | setosa
35 | setosa | setosa
38 | setosa | setosa
39 | setosa | setosa
42 | setosa | setosa
43 | setosa | setosa
45 | setosa | setosa
46 | setosa | setosa
47 | setosa | setosa
48 | setosa | setosa
51 | versicolor | versicolor
54 | versicolor | versicolor
57 | versicolor | versicolor
59 | versicolor | versicolor
62 | versicolor | versicolor
63 | versicolor | versicolor
64 | versicolor | versicolor
65 | versicolor | versicolor
66 | versicolor | versicolor
71 | versicolor | virginica
73 | versicolor | versicolor
75 | versicolor | versicolor
82 | versicolor | versicolor
87 | versicolor | versicolor
89 | versicolor | versicolor
90 | versicolor | versicolor
91 | versicolor | versicolor
96 | versicolor | versicolor
97 | versicolor | versicolor
101 | virginica | virginica
102 | virginica | virginica
104 | virginica | virginica
107 | virginica | versicolor
110 | virginica | virginica
113 | virginica | virginica
115 | virginica | virginica
118 | virginica | virginica
119 | virginica | virginica
120 | virginica | versicolor
127 | virginica | virginica
129 | virginica | virginica
133 | virginica | virginica
134 | virginica | versicolor
136 | virginica | virginica
141 | virginica | virginica
142 | virginica | virginica
143 | virginica | virginica
145 | virginica | virginica
146 | virginica | virginica
148 | virginica | virginica
(60 rows)検証バージョンについて
この記事の内容はVertica 9.3.1で確認しています。