SQL Server 2017で機械学習(その1)

2017.10.12

こんにちは。YDCのたかもん です。
今回のトピックは、2017年4月にリリースされたプレビュー版 SQL Server 2017で実装された「Machine Learning Services」について紹介したいと思います。
SQL Server 2016では「R Services」という機能で、R言語を使用して機械学習ができるようになりましたが、
2017では新たにPythonも使用可能になりました。これにより、SQL Serverのストアドプロシージャ内でPythonやRスクリプトを
実行することが可能になり、SQL Serverのデータベースと簡単に連携できます。
今回は簡単な機械学習を織り交ぜてその触りを紹介していきます。

1.SQL Server 2017のインストール

SQL Server 2017のインストーラをダウンロードします。
以下からダウンロード可能です。
https://www.microsoft.com/ja-jp/sql-server/sql-server-2017

SQL Serverをインストールします。
インストーラ起動直後の画面です。「カスタム」を選択して進めていきます。
sqlserver2017_2.png

「機能の選択」画面で、「Machine Learning サービス(データベース内)」の「Python」にチェックをします。
sqlserver2017_10.jpg

あとは普通に進めていけばOKです。

2.SQL Server Management Studioのインストール

SQL Server Management Studioをダウンロードします。最新版は17.2になります。
以下からダウンロード可能です。
https://docs.microsoft.com/ja-jp/sql/ssms/download-sql-server-management-studio-ssms

Management Studioをインストールします。
普通にインストール画面を進めていけばOKです。インストールが完了したら、Management Studioを起動します。

3.Pythonの動作確認

「クエリ」画面からPythonが実行できるか確認してみます。print文で文字列を表示してみます。
構文は簡単で「@language」で言語を指定して、「@script」で指定した言語の命令文を指定します。
その際、必ず先頭に「N」を入れるのと「'」で囲むことがポイントです。

exec sp_execute_external_script
@language = N'Python'
, @script = N'
print("テスト")'
go

結果
 メッセージ 39023、レベル 16、状態 1、プロシージャ sp_execute_external_script、行 1 [バッチ開始行 0]
 SQL Server のこのインスタンスでは、'sp_execute_external_script' は無効になっています。
 これを有効にするには、sp_configure の 'external scripts enabled' を使用します。

おや、エラーになりました。どうやら external scripts enabledパラメータを有効化する必要があるようです。

exec sp_configure
'external scripts enabled', 1;
go
reconfigure
go

SQL Serverのインスタンスを再起動し、再度実行してみます。

exec sp_execute_external_script
@language = N'Python'
, @script = N'
print("テスト")'
go

結果
 テスト

うまく動作したようです。

次にNumpyを使って配列を表示してみます。

exec sp_execute_external_script
@language = N'Python'
, @script = N'
import numpy as np
print(np.array([[1,2,3],[4,5,6],[7,8,9]]))'
go

結果
[ [1 2 3] [4 5 6] [7 8 9]]

こちらも問題ないようです。

Pythonでお得意のグラフ表示も可能か確認してみます。
色々調べてみると、さすがにManagement Studio内に表示は無理そうです。外部ファイルとして
保存できるようなので試してみましょう。正規分布を描画してみます。

exec sp_execute_external_script
@language = N'Python'
, @script = N'
import numpy as np
import math
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt

# 正規分布を描画

f = lambda x: (math.exp(-x**2/2)) / math.sqrt(2*math.pi)
n = np.linspace(-5.0, 5.0, 100000)
p = []
for i in range(len(n)):
p.append(f(n[i]))
plt.plot(n, p)
plt.show()
plt.savefig("D:/temp/Plot.png")'

go

保存されたファイルを開いてみます。
graph1.png
奇麗に表示されてますね。簡単にグラフを描画できるところがPythonやRの良いところです。

4.簡単な機械学習

簡単な機械学習をやってみましょう。機械学習の範囲は大変広いです。ここでは機械学習そのものの詳しい説明は
割愛しますが、大きく、「教師あり学習」・「教師なし学習」・「強化学習」に分かれます。
「教師あり学習」とは、データと正解がセットになった情報を学習させ、予測性能を持つ学習モデルの構築を目的とします。
文字や画像認識などはここに当てはまります。機械学習の大部分は「教師あり」です。
「教師なし学習」とは、データはあるがそれに対する答えがない中で、データの構造や法則などを見つけ出すことを
目的とします。マーケティング分野で使用されることが多いです。
「強化学習」とは、報酬という情報を手がかりに自らが試行錯誤することによって学習する手法です。
囲碁で有名になったAlphaGoもこれに該当します。

教師あり学習の分類器の一つであるサポートベクトルマシン(以下、SVM)で簡単に試してみます。
SVMについては、ここでは多くは説明しませんが、以下の図のようにデータを2つのグループに分けるときに各グループからそれぞれなるべく離れている場所に線を引く(マージン最大化)ことで分類を最適化することを目的とします。
svm.png
少しわかりにくいですが、右のほうが引いた線から離れているのがわかります。

それでは早速データを準備します。機械学習に携わってる人は誰もが知っているIrisを使用してみます。Irisはアヤメのデータで3種 (setosa,versicolor,virginica) の種分に対して、がく片長(Sepal Length)、がく片幅(Sepal Width)、花びら長(Petal Length)、花びら幅(Petal Width)が記録されたデータセットです。
細かいことは抜きに進めてみましょう。Irisは、Pythonのscikit-learnライブラリにバンドルされてますのですぐに使用可能です。折角なので、Pythonから一旦SQL Serverのテーブルに登録してみます。

まずはテーブルを作成します。

create table iris_tbl (
"Sepal Length" float,
"Sepal Width" float,
"Petal Length" float,
"Petal Width" float,
"Species" char(10)
); go

次に、データ登録用にストアドプロシージャを作成します。その際、プロシージャ内でIrisのデータセットの取得も行います。

create procedure proc_get_iris_dataset
as
begin
exec sp_execute_external_script
@language = N'Python'
, @script = N'
import pandas as pd
from sklearn.datasets import load_iris
# irisデータセットをロード
iris = load_iris()
# irisデータセットをデータフレーム化
iris_df=pd.DataFrame(iris.data,columns=iris.feature_names)
iris_df["Species"]=iris.target_names[iris.target]
OutputDataSet = iris_df
InputDataSet = OutputDataSet'

with result sets ((
"Sepal.Length" float,
"Sepal.Width" float,
"Petal.Length" float,
"Petal.Width" float,
"Species" char(10)
));
end;
go

データをテーブルへ登録してみます。

insert into iris_tbl exec proc_get_iris_dataset
select * from iris_tbl
go

結果
Sepal Length Sepal Width Petal Length Petal Width Species 5.1 3.5 1.4 0.2 setosa 4.9 3 1.4 0.2 setosa 4.7 3.2 1.3 0.2 setosa 4.6 3.1 1.5 0.2 setosa 5 3.6 1.4 0.2 setosa ・・・

無事データの登録を確認できました

次にSQL Serverに登録されたデータを抽出し、SVMの分類器を通して学習させ、検証スコアや分類性能、混合行列の結果を
表示するストアドプロシージャを作成します。
プロシージャ内の「InputDataSet」、「input_data_1」についてですが、データベースから抽出した結果を
sp_execute_external_scriptプロシージャの@input_data_1変数で渡すとPython内でInputDataSet予約変数で受けることができます。InputDataSet はデータフレームとしてわたってきます。これを利用して、Pythonで機械学習を行うわけです。
なお、本来であればホールドアウトではなくK-分割交差検証を使用してモデルの精度を検証します。また、ハイパーパラメータは適当ですので、グリッドサーチによるチューニングもすると良いでしょう。あと、過学習しているかのチェックも忘れずに。

create procedure proc_SVM
as
begin
exec sp_execute_external_script
@language = N'Python'
, @script = N'
import sklearn
from sklearn.preprocessing import StandardScaler
from sklearn import svm
from sklearn import model_selection as ms
from sklearn.pipeline import Pipeline
from sklearn.metrics import classification_report
from sklearn.metrics import confusion_matrix

iris_df = InputDataSet
# 説明変数
x = iris_df.iloc[:,:4]
# 目的変数
y = iris_df.iloc[:,4:].values.flatten()
# クロスバリデーション(ホールドアウト)
X_train, X_test, Y_train, Y_test = ms.train_test_split(x,y, test_size=0.2,random_state=42)
# パイプライン化:標準化+非線形サポートベクトルマシン
pipe_svm = Pipeline([("scl", StandardScaler()),
("svm", svm.SVC(kernel="rbf",C=10,gamma=0.01))])
# モデル学習
pipe_svm.fit(X_train, Y_train)
# 検証スコア
print("検証スコア")
print(pipe_svm.score(X_test,Y_test))

# 分類性能
print("分類性能")
print(classification_report(Y_test,pipe_svm.predict(X_test)))
# 混合行列
print("混合行列")
print(confusion_matrix(Y_test,pipe_svm.predict(X_test)))'

,
@input_data_1 = N'select "Sepal Length","Petal Length","Sepal Width","Petal Width","Species" from iris_tbl'
end;
go

ストアドプロシージャを実行します。

exec proc_SVM
go

結果
検証スコア 0.966666666667
分類性能 precision recall f1-score support setosa 1.00 1.00 1.00 10 versicolor 1.00 0.89 0.94 9 virginica 0.92 1.00 0.96 11 avg / total 0.97 0.97 0.97 30 混合行列 [[10 0 0] [ 0 8 1] [ 0 0 11]]

誤分類の件数は1つで、96%の精度のようです。いつもこれくらい精度が高いと良いですね。
今回はここまでです。正直、Management Studio上で一からコーディングするよりも、Pythonの記述部分は事前にPython上で
動作検証済みの内容を張り付ける方がミスも少なくて良いと思いました。
SQL Serverと連携することでもっとダイナミックなことができるかもしれません。
引き続き検証していきたいと思います。

Standby Expressに関するお問合わせ

  • TEL 042-333-6217
  • FAX 042-352-6101
  • LINE
  • Mail