次の方法で共有


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

適用対象: 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 クエリを利用したバッチ スコアリング

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

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

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

  1. スコアリングに 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 に近い値が表示されます。

  2. スコアリングに 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 拡張機能に関するページを参照してください。