Python のチュートリアル:ストアドプロシージャに埋め込まれた Python を使用した予測の実行

[アーティクル]
05/30/2023

適用対象: SQL Server 2017 (14.x) 以降 Azure SQL Managed Instance

全 5 回からなるこのチュートリアルシリーズの第 5 回では、前回のチュートリアルでトレーニングし、保存したモデルを "運用化" する方法について学習します。

このシナリオでは、運用とは、スコアリングのためにモデルを実稼働環境にデプロイすることを意味します。 SQL Server との統合により、Python コードをストアドプロシージャに埋め込むことができるため、これは非常に簡単になります。新しい入力に基づき、モデルから予測を取得するには、アプリケーションからストアドプロシージャを呼び出し、新しいデータを渡します。

チュートリアルのこの回では、Python モデルに基づいて予測を作成する 2 つの方法、つまりバッチスコアリングと行ごとのスコアリングについて説明します。

バッチスコアリング: 入力データの複数の行を指定するには、SELECT クエリを引数としてストアドプロシージャに渡します。結果は、入力ケースに対応する観察のテーブルを返します。
個別のスコアリング:個々のパラメーターセットを入力パラメーターとして渡します。このストアドプロシージャは、1 つの行または値を返します。

スコアリングに必要なすべての Python コードは、ストアドプロシージャの一部として提供されています。

この記事では、次のことを行います。

バッチスコアリングのストアドプロシージャを作成し、使用する
1 つの行をスコアリングするためのストアドプロシージャを作成し、使用する

パート 1 では、前提条件をインストールしてサンプルデータベースを復元しました。

第 2 回では、サンプルデータを探索し、いくつかのプロットを生成しました。

第 3 回では、Transact-SQL 関数を使用して生データから特徴を作成する方法を学習しました。その後、その関数をストアドプロシージャから呼び出し、機能の値を含むテーブルを作成しました。

第 4 回では、モジュールを読み込み、必要な関数を呼び出し、SQL Server ストアドプロシージャを使用してモデルを作成し、トレーニングしました。

バッチスコアリング

以下のスクリプトを使用して作成される最初の 2 つのストアドプロシージャでは、Python 予測の呼び出しをストアドプロシージャ内にラップするための基本的な構文が示されています。どちらのストアドプロシージャでも、入力としてデータのテーブルが必要です。

使用するモデルの名前は、ストアドプロシージャへの入力パラメーターとして提供されます。ストアドプロシージャは、ストアドプロシージャの SELECT ステートメントを使用して、データベーステーブル nyc_taxi_models.table から、シリアル化されたモデルを読み込みます。
シリアル化されたモデルは、Python を使用してmodさらに処理するために、Python 変数に格納されます。
スコア付けが必要な新しいケースは、@input_data_1 で指定された Transact-SQL クエリから取得されます。クエリデータが読み取られると、行が既定のデータフレーム InputDataSetに保存されます。
どちらのストアドプロシージャも、sklearn の関数を使用して精度メトリック (曲線の下の領域) を計算します。 AUC などの精度メトリックは、ターゲットラベル (チップ列) も指定する場合にのみ生成できます。予測には、ターゲットラベル (変数 y) は必要ありませんが、精度メトリックの計算には必要です。

したがって、スコア付けされるデータのターゲットラベルがない場合は、ストアドプロシージャを変更して、AUC の計算を削除し、特徴 (ストアドプロシージャの変数 X) からチップを得る確率のみを返すことができます。

PredictTipSciKitPy

次の T-SQL ステートメントを実行して、ストアドプロシージャ PredictTipSciKitPy を作成します。このストアドプロシージャによって、そのパッケージに固有の関数が使用されるため、scikit-learn パッケージに基づくモデルが必要です。

入力を含むデータフレームは、ロジスティック回帰モデルのpredict_proba関数modに渡されます。 predict_proba関数（probArray = mod.predict_proba(X)）は、(金額を問わず) チップを得えられる確率を表す浮動を返します。

DROP PROCEDURE IF EXISTS PredictTipSciKitPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSciKitPy] (@model varchar(50), @inquery nvarchar(max))
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from sklearn import metrics

mod = pickle.loads(lmodel2)
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

probArray = mod.predict_proba(X)
probList = []
for i in range(len(probArray)):
  probList.append((probArray[i])[1])

probArray = numpy.asarray(probList)
fpr, tpr, thresholds = metrics.roc_curve(y, probArray)
aucResult = metrics.auc(fpr, tpr)
print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',	
  @input_data_1 = @inquery,
  @input_data_1_name = N'InputDataSet',
  @params = N'@lmodel2 varbinary(max)',
  @lmodel2 = @lmodel2
WITH RESULT SETS ((Score float));
END
GO

PredictTipRxPy

次の T-SQL ステートメントを実行して、ストアドプロシージャ PredictTipRxPy を作成します。このストアドプロシージャは同じ入力を使用し、前のストアドプロシージャと同じ種類のスコアを作成しますが、SQL Server Machine Learning で提供されている revoscalepy パッケージの関数を使用します。

DROP PROCEDURE IF EXISTS PredictTipRxPy;
GO

CREATE PROCEDURE [dbo].[PredictTipRxPy] (@model varchar(50), @inquery nvarchar(max))
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from sklearn import metrics
from revoscalepy.functions.RxPredict import rx_predict;

mod = pickle.loads(lmodel2)
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

probArray = rx_predict(mod, X)
probList = probArray["tipped_Pred"].values 

probArray = numpy.asarray(probList)
fpr, tpr, thresholds = metrics.roc_curve(y, probArray)
aucResult = metrics.auc(fpr, tpr)
print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @input_data_1_name = N'InputDataSet',
  @params = N'@lmodel2 varbinary(max)',
  @lmodel2 = @lmodel2
WITH RESULT SETS ((Score float));
END
GO

SELECT クエリを利用したバッチスコアリング

ストアドプロシージャ PredictTipSciKitPy と PredictTipRxPy には、次の2つの入力パラメーターが必要です。

スコアリングのためにデータを取得するクエリ
トレーニング済みのモデルの名前

これらの引数をストアドプロシージャに渡すことで、特定のモデルを選択したり、スコアリングに使用するデータを変更したりできます。

スコアリングに scikit-learn モデルを使用するには、ストアドプロシージャ PredictTipSciKitPy を呼び出して、モデル名とクエリ文字列を入力として渡します。
```
DECLARE @query_string nvarchar(max) -- Specify input query
  SET @query_string='
  select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance,
  dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
  from nyctaxi_sample_testing'
EXEC [dbo].[PredictTipSciKitPy] 'SciKit_model', @query_string;
```
このストアドプロシージャは、入力クエリの一部として渡された各乗車について予測確率を返します。

クエリの実行に SSMS (SQL Server Management Studio) を使用している場合、確率は [結果] ペインにテーブルとして表示されます。 [メッセージ] ペインには、精度メトリック (AUC または曲線の下の面積) が出力され、0.56 に近い値が表示されます。

スコアリングに revoscalepy モデルを使用するには、ストアドプロシージャ PredictTipRxPy を呼び出して、モデル名とクエリ文字列を入力として渡します。

DECLARE @query_string nvarchar(max) -- Specify input query
  SET @query_string='
  select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance,
  dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
  from nyctaxi_sample_testing'
EXEC [dbo].[PredictTipRxPy] 'revoscalepy_model', @query_string;

単一行のスコアリング

バッチスコアリングではなく、アプリケーションから値を取得し、単一のケースを渡し、それらの値に基づいて単一の結果を返すことが必要になる場合があります。たとえば、Excel ワークシート、web アプリケーション、またはレポートを設定して、ストアドプロシージャを呼び出し、ユーザーが入力または選択した入力に渡すことができます。

このセクションでは、次の2つのストアドプロシージャを呼び出して単一の予測を作成する方法について説明します。

PredictTipSingleModeSciKitPy は、scikit-learn モデルを使用して単一行スコアリングを目的として設計されています。
PredictTipSingleModeRxPy は、revoscalepy モデルを使用して単一行スコアリングを目的として設計されています。
まだモデルをトレーニングしていない場合は、第 5 回に戻ってください。

どちらのモデルも、乗客数、走行距離などの一連の単一値を入力として受け取ります。テーブル値関数の fnEngineerFeatures は、入力から緯度と経度の値を使用して、新しい機能 (直線距離) に変換します。第 4 回には、このテーブル値関数の説明が含まれています。

どちらのストアドプロシージャも、Python モデルに基づいてスコアを作成します。

Note

外部アプリケーションからストアドプロシージャを呼び出すときに、Python モデルに必要なすべての入力機能を提供することが重要です。エラーを回避するには、データ型とデータ長を検証するだけでなく、入力データを Python データ型にキャストまたは変換することが必要になる場合があります。

PredictTipSingleModeSciKitPy

次のストアドプロシージャ PredictTipSingleModeSciKitPy では、scikit-learn モデルを使用してスコアリングが実行されます。

DROP PROCEDURE IF EXISTS PredictTipSingleModeSciKitPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSingleModeSciKitPy] (@model varchar(50), @passenger_count int = 0,
  @trip_distance float = 0,
  @trip_time_in_secs int = 0,
  @pickup_latitude float = 0,
  @pickup_longitude float = 0,
  @dropoff_latitude float = 0,
  @dropoff_longitude float = 0)
AS
BEGIN
  DECLARE @inquery nvarchar(max) = N'
  SELECT * FROM [dbo].[fnEngineerFeatures]( 
    @passenger_count,
    @trip_distance,
    @trip_time_in_secs,
    @pickup_latitude,
    @pickup_longitude,
    @dropoff_latitude,
    @dropoff_longitude)
    '
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;

# Load model and unserialize
mod = pickle.loads(model)

# Get features for scoring from input data
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)
probList = []
probList.append((mod.predict_proba(X)[0])[1])

# Create output data frame
OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,
    @trip_time_in_secs int ,
    @pickup_latitude float ,
    @pickup_longitude float ,
    @dropoff_latitude float ,
    @dropoff_longitude float',
    @model = @lmodel2,
    @passenger_count =@passenger_count ,
    @trip_distance=@trip_distance,
    @trip_time_in_secs=@trip_time_in_secs,
    @pickup_latitude=@pickup_latitude,
    @pickup_longitude=@pickup_longitude,
    @dropoff_latitude=@dropoff_latitude,
    @dropoff_longitude=@dropoff_longitude
WITH RESULT SETS ((Score float));
END
GO

PredictTipSingleModeRxPy

次のストアドプロシージャ PredictTipSingleModeRxPy では、revoscalepy モデルを使用してスコアリングが実行されます。

DROP PROCEDURE IF EXISTS PredictTipSingleModeRxPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSingleModeRxPy] (@model varchar(50), @passenger_count int = 0,
  @trip_distance float = 0,
  @trip_time_in_secs int = 0,
  @pickup_latitude float = 0,
  @pickup_longitude float = 0,
  @dropoff_latitude float = 0,
  @dropoff_longitude float = 0)
AS
BEGIN
DECLARE @inquery nvarchar(max) = N'
  SELECT * FROM [dbo].[fnEngineerFeatures]( 
    @passenger_count,
    @trip_distance,
    @trip_time_in_secs,
    @pickup_latitude,
    @pickup_longitude,
    @dropoff_latitude,
    @dropoff_longitude)
  '
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from revoscalepy.functions.RxPredict import rx_predict;

# Load model and unserialize
mod = pickle.loads(model)

# Get features for scoring from input data
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)

probArray = rx_predict(mod, X)

probList = []
probList = probArray["tipped_Pred"].values

# Create output data frame
OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,
    @trip_time_in_secs int ,
    @pickup_latitude float ,
    @pickup_longitude float ,
    @dropoff_latitude float ,
    @dropoff_longitude float',
    @model = @lmodel2,
    @passenger_count =@passenger_count ,
    @trip_distance=@trip_distance,
    @trip_time_in_secs=@trip_time_in_secs,
    @pickup_latitude=@pickup_latitude,
    @pickup_longitude=@pickup_longitude,
    @dropoff_latitude=@dropoff_latitude,
    @dropoff_longitude=@dropoff_longitude
WITH RESULT SETS ((Score float));
END
GO

モデルからスコアを生成する

ストアドプロシージャが作成されれば、どちらのモデルに基づいても簡単にスコアを生成できます。新しい [クエリ] ウィンドウを開き、各特徴列のパラメーターを指定します。

これらの特徴列に必要な 7 つの値は、順番に次のとおりです。

passenger_count
trip_distance
trip_time_in_secs
pickup_latitude
pickup_longitude
dropoff_latitude
dropoff_longitude

例:

revoscalepy モデルを使用して予測を生成するには、次のステートメントを実行します。

EXEC [dbo].[PredictTipSingleModeRxPy] 'revoscalepy_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

scikit-learn モデルを使用してスコアを生成するには、次のステートメントを実行します。
```
EXEC [dbo].[PredictTipSingleModeSciKitPy] 'SciKit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
```

両方のプロシージャからの出力結果は、指定されたパラメーターまたは特徴を使用したときの、タクシー乗車で支払われるチップの確率です。

まとめ

このチュートリアルシリーズでは、ストアドプロシージャに埋め込まれた Python コードを操作する方法について説明しました。 Transact-SQL との統合により、Python モデルを展開して予測することと、エンタープライズデータワークフローの一部としてモデルを組み込み、維持することが簡単になります。

次のステップ

この記事では、次の内容について説明します。

バッチスコアリングのストアドプロシージャを作成し、使用しました
1 つの行をスコアリングするためのストアドプロシージャを作成し、使用しました

Python の詳細については、SQL Server の Python 拡張機能に関するページを参照してください。

Python のチュートリアル:ストアドプロシージャに埋め込まれた Python を使用した予測の実行

バッチスコアリング

PredictTipSciKitPy

PredictTipRxPy

SELECT クエリを利用したバッチスコアリング

単一行のスコアリング

PredictTipSingleModeSciKitPy

PredictTipSingleModeRxPy

モデルからスコアを生成する

まとめ

次のステップ

フィードバック

フィードバック

その他のリソース

Python のチュートリアル:ストアド プロシージャに埋め込まれた Python を使用した予測の実行

バッチ スコアリング

PredictTipSciKitPy

PredictTipRxPy

SELECT クエリを利用したバッチ スコアリング

単一行のスコアリング

PredictTipSingleModeSciKitPy

PredictTipSingleModeRxPy

モデルからスコアを生成する

まとめ

次のステップ

フィードバック

フィードバック

その他のリソース

Python のチュートリアル:ストアドプロシージャに埋め込まれた Python を使用した予測の実行

バッチスコアリング

SELECT クエリを利用したバッチスコアリング