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

05/29/2017

4月19日の 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 サービス（データベース内）にチェックを入れます。

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

インストールが完了したら、サーバー構成オプションで「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');GOexecute 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 npimport pandas as pdn = 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

簡単な機械学習の実行

これだけ分かれば大抵のことはできるでしょう。さっそく 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 LogisticRegressionlogreg = 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\revoscalepyThe Score is 0.871794871795

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

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

参考リンク

Microsoft Machine Learning Services

/en-us/sql/advanced-analytics/r/r-services

Machine Learning Services with Python /en-us/sql/advanced-analytics/python/sql-server-python-services

Python Libraries and Data Types

/en-us/sql/advanced-analytics/python/python-libraries-and-data-types

Introducing revoscalepy

/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/