SE の雑記

SQL Server 2017 では、PREDICT という構文が追加されています。
PREDICT (Transact-SQL)

この構文を使用することで、次のようなことを実施できるようになります。

• ストアドプロシージャを使用しない R/Python の学習済みモデルを使用

学習済みのモデルについては、すべてのオブジェクトが使用できるということではなく、次のような制約があります。

For all of these scoring methods, you must use a model that was trained using one of the supported RevoScaleR or MicrosoftML algorithms.

• For RevoScaleR, the equivalent function is rxPredict
• For MicrosoftML, the equivalent function is rxPredict.mlModel

ReoscaleR や、MicrosoftML のパッケージで作成されたモデル (パッケージに含まれているアルゴリズム) をシリアライズしたものしか利用することはできないようですね。
この構文を使用することで RC2 時点では Machine Learning サービス (ML Services) がサポートされていない、SQL Server on Linux でも、機械学習のモデルを使用することができます。

The PREDICT function is supported in all editions of SQL Server, including Linux.

 
簡単なもので、一通り動作するところまでは確認することができたので軽くまとめてみたいと思います。
技術情報としては、Native scoring を確認するとよいかと。

R を使用した場合ン作業の流れについては、How to perform realtime scoring or native scoring in SQL Server で紹介されていますので、本投稿では、Python を使用してみたいと思います。
最初に実施しなくてはいけない内容としては、学習済みモデルをシリアライズしたバイナリデータを作成することです。
この部分については Windows 版の環境を使用した方が早いかと。
まずは、次のクエリを実行してサンプルデータをテーブルに展開します。

DROP TABLE IF EXISTS AirlineDemo;
GO
CREATE TABLE AirlineDemo (
  ArrDelay int,
  CRSDepTime float,
  DayOfWeek varchar(100)
);
GO
INSERT INTO AirlineDemo (ArrDelay, CRSDepTime, DayOfWeek)
EXECUTE sp_execute_external_script
@language = N'Python'
, @script = N'
import os
from revoscalepy import RxOptions, RxXdfData, rx_serialize_model, rx_lin_mod, rx_data_step
sample_data_path = RxOptions.get_option("sampleDataDir")
ds = RxXdfData(os.path.join(sample_data_path, "AirlineDemoSmall.xdf"))
OutputDataSet = rx_data_step(ds)
'
GO
SELECT TOP 100 * FROM AirlineDemo
GO

R だと、iris のデータを使用することが多いようですが、今回は RevoscalePy に含まれている AirlineDemoSmall を使用しています。
これで学習やテストに使用するデータができましたので、次に学習済みモデルを作成します。

本来であればトレーニング用と評価用を分けた方がよいかと思いますが、今回はすべて使用しています。

DROP TABLE IF EXISTS ml_models;
GO
CREATE TABLE ml_models (
model_name nvarchar(100) not null primary key,
model_version nvarchar(100) not null,
native_model_object varbinary(max) not null);
GO
DECLARE @model varbinary(max);
EXECUTE sp_execute_external_script
@language = N'Python'
, @script = N'
import os
from revoscalepy import RxOptions, RxXdfData, rx_serialize_model, rx_lin_mod, rx_summary
sample_data_path = RxOptions.get_option("sampleDataDir")
ds = RxXdfData(os.path.join(sample_data_path, "AirlineDemoSmall.xdf"))
linmod = rx_lin_mod("ArrDelay ~ CRSDepTime + DayOfWeek", ds)
# https://docs.microsoft.com/en-us/r-server/r-reference/revoscaler/airlinedemosmall
# ArrDelay : 到着の遅延時間 (分)
# CRSDepTime : 出発時刻
# DayOfWeek : 曜日
summary = rx_summary("ArrDelay ~ CRSDepTime + DayOfWeek", ds)
print(summary)
model = rx_serialize_model(linmod, realtime_scoring_only = True)
'
, @params = N'@model varbinary(max) OUTPUT'
, @model = @model OUTPUT
INSERT [dbo].[ml_models]([model_name], [model_version], [native_model_object])
VALUES('AirLineDemo','v1', @model) ;
GO
SELECT * FROM ml_models
GO

 
今回は ArrDelay を従属変数として予測をするために、CRSDepTime と DayOfWeek を独立変数として予測をするための値として利用をしています。

この辺の構文については Models in RevoScaleR に記載されています。

(RevoscalePy は RevoscaleR をベースにしたものですので、情報は RevoscaleR を追うことで確認できます。)
モデルについては「rx_lin_mod」で線形モデルを作成し、作成されたものを 「rx_serialize_model」でシリアライズして、テーブルのデータとして格納をしています。
これで PREDICT で使用するモデルの準備が整いましたので、次のクエリをじっくして予測を行います。

DECLARE @model varbinary(max) = (
SELECT native_model_object
FROM ml_models
WHERE model_name = 'AirLineDemo'
AND model_version = 'v1');
DROP TABLE IF EXISTS #tbl
CREATE TABLE #tbl(
ArrDelay int,
CRSDepTime float,
DayOfWeek varchar(100)
)
INSERT INTO #tbl SELECT TOP 100 * FROM AirlineDemo ORDER BY CRSDepTime, DayOfWeek
SELECT d.*, p.*
FROM PREDICT(MODEL = @model, DATA = #tbl as d)
WITH(
ArrDelay_Pred float
) as p;
GO

PREDICT には、テーブル名やテーブル関数を渡せるのですが、件数を絞った状態で予測を行いたかったため、一時テーブルにデータをいれて渡しています。
これによって、渡したデータをもとに予測 (スコアリング) が行われます。

このスコアリングですが、ML Services とは異なり、ネイティブな C++ ライブラリを使用して行われているため、ML services がインストールされていない環境でも実行することができます。
How native scoring works

Native scoring uses native C++ libraries from Microsoft that can read the model from a special binary format and generate scores. Because a model can be published and used for scoring without having to call the R interpreter, the overhead of multiple process interactions

そのため。モデルのバイナリデータを SQL Serer on Linux に格納することで、学習済みモデルを SQL Server on Linux でも使用することができます。
sys.dm_os_loaded_modules から確認できるのですが、SQL Servver 2017 では、ML Services のインストールにかかわらず 「ExacorePredict.dll」「ExacorePredictSql.dll」という、2 種類のモジュールがロードされており、このモジュールが予測を処理しているのだと思います。
従属変数が複数設定されているモデルは PREDICT でしよすることはできず、単一項目の予測値を返すという限定的な機能となっているようですが、SQL Server on Linux でも動作する、予測モデルの使用方法として面白い使い方ができそうですね。