SQL Server 2017 In-Database Python を使ってみた


419日の Data Amp SQL Server 2017 Python Integration が正式に発表されました。これは SQL Server 2016 で機能追加された SQL Server 2016 R Services SQL Server 2017 Machine Learning Services に拡張し、R に加えて Python による Advanced Analytics を利用可能にするという内容です。
そこで今回は Python T-SQL を用いて実行する In-Database Python を試してみようと思います。

インストール

まずインストールです。
SQL Server 2017 CTP2.1 のセットアップで Machine Learning サービス(データベース内)にチェックを入れます。

 

install_py


そのままインストールを進めていくと、「
Python のインストールに同意する」画面があらわれるので、承認ボタンをクリックすることで「次へ」ボタンを押すことができるようになります。

 

install_py_2

インストールが完了したら、サーバー構成オプションで「external script enabled オプション」を有効にしておきます。

 EXEC sp_configure 'external scripts enabled', 1;


CTP2.1
では RECONFIGURE WITH OVERRIDE が効かなかったのでインスタンスの再起動が必要でした。
また CTP 2.0 では既知の不具合があり、次のログインを作成する必要があります。

 

 CREATE LOGIN [TESTSQLSERVER\SQLRUserGroup] FROM WINDOWS  

 

インプットとアウトプット

SELECT ステートメントの結果セットを sp_execute_external_script ストアドプロシージャの @input_data_1 で渡すと、Python スクリプト内で InputDataSet 予約変数で受けることができます。下のスクリプトから分かるように、この InputDataSet はデータフレームとして渡ってきます。

 

CREATE TABLE #t1(a int, b char(10));
 INSERT INTO #t1 VALUES (1,'test1');
GO

execute sp_execute_external_script
  @language = N'Python'
 ,@script = N'
print(type(InputDataSet))
 '
 ,@input_data_1 = N'SELECT * FROM #t1' 
GO

 

 

 (1 行処理されました)
外部スクリプトからの STDOUT メッセージ:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-
packages\revoscalepy
<class 'pandas.core.frame.DataFrame'>


また
Python スクリプト内の OutputDataSet 予約変数もデータフレームタイプを持ち、結果セットとしてT-SQLへ返すことができます。

 

 execute sp_execute_external_script
@language = N'Python'
, @script = N'
import numpy as np
import pandas as pd

n = np.arange(0, 9, 1)
n = n.reshape(3, 3)
OutputDataSet = pd.DataFrame(n)
 '
WITH RESULT SETS (([col1] char(5), [col2] char(5), [col3] char(5)))
GO

 

pytest1

 

簡単な機械学習の実行

これだけ分かれば大抵のことはできるでしょう。さっそく Python で機械学習を試してみましょう。
データは以前 関連記事「Azure Machine Learning Studio を使ってモデルをスクラッチから構築してみよう」で使った "Wine Data Set" を利用しました。

あらかじめこの Wine Data Set をダウンロードし、wine というテーブルにロードしておきます。インプットとして渡せるデータ型については参考リンク "Python Libraries and Data Types" を参照してください。

 

create table wine (
[Class]        int,
[Alcohol]    float,
[Malic acid]    float,
[Ash]            float,
[Alcalinity of ash]    float,
[Magnesium]        float,
[Total phenols]    float,
[Flavanoids]    float,
[Nonflavanoid phenols]    float,
[Proanthocyanins]    float,
[Color intensity]    float,
[Hue]            float,
[OD280/OD315 of diluted wines]    float, 
[Proline]    float
)
GO

 bulk insert wine
 from 'D:\wine.data'
 with
(
BATCHSIZE = 1000,
 FIELDTERMINATOR = ',',
 ROWTERMINATOR = '0x0A',
 DATAFILETYPE 'char'
)
GO


もともとこのデータセットは
3 つの分類種別を持っていて、今回使った Python LogisticRegression モデルは多クラス分類に対応しているのですが、上述の関連記事では同じデータセットを 2 クラス分類の例として使用したので、ここでも 2 クラスのデータに絞ってモデルを構築してみました。

 

execute sp_execute_external_script
@language = N'Python'   
, @script = N'

df = InputDataSet

# データのスプリット
df_train = df.sample(frac=0.7)
df_test = df.drop(df_train.index)

# 学習用データ
X_train = df_train.loc[:,["Alcohol","Malic acid"]]
y_train = df_train["class"]

# テスト用データ
X_test  = df_test.loc[:,["Alcohol","Malic acid"]]
y_test  = df_test["class"]

# モデルの初期化
from sklearn.linear_model import LogisticRegression
logreg = LogisticRegression(penalty="l2",C=100,max_iter=100)

# 学習の実行とスコアの算出
logreg.fit(X_train, y_train)
score = logreg.score(X_test, y_test)

# 結果の出力
print("The Score is "+str(score))
  '
,@input_data_1 = N'SELECT [class], [Alcohol],[Malic acid] FROM dbo.wine 
  WHERE [class] < 3'
 GO

 

実行するとホールドアウト法によるスコアが 0.87 となりました。

 外部スクリプトからの STDOUT メッセージ:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-
packages\revoscalepy
The Score is 0.871794871795

 

 

今回は基本的な In-Database Python の操作を試してみました。Python を用いてゴリゴリデータ抽出・整形を行うところを、T-SQL SELECT ステートメントで必要なデータだけを Python スクリプトに渡すことができるので便利だと感じました。

また今回は revoscalepy で提供されている強力な Python 関数を利用しませんでしたが、これらの関数を利用することで In-Database Python のスケーラビリティとパフォーマンスを Advanced Analytics の分野に利用できることは素晴らしいことだと思います。

N

 

参考リンク

Microsoft Machine Learning Services

https://docs.microsoft.com/en-us/sql/advanced-analytics/r/r-services

Machine Learning Services with Python

https://docs.microsoft.com/en-us/sql/advanced-analytics/python/sql-server-python-services

Python Libraries and Data Types

https://docs.microsoft.com/en-us/sql/advanced-analytics/python/python-libraries-and-data-types

Introducing revoscalepy

https://docs.microsoft.com/en-us/sql/advanced-analytics/python/what-is-revoscalepy

関連記事

Azure Machine Learning Studio を使ってモデルをスクラッチから構築してみよう

https://blogs.msdn.microsoft.com/dataplatjp/2017/01/18/azure-machine-learning-studio/

Comments (0)

Skip to main content