Python 教學課程：在預存程序中使用 Python 內嵌執行預測

文章
05/04/2023

適用於： SQL Server 2017 (14.x) 和更新版本 Azure SQL 受控執行個體

在這五部分教學課程系列的第五部分中，您將了解如何運作您在上一個部分中定型並儲存的模型。

在此案例中，運作化表示將模型部署至生產環境以進行評分。與 SQL Server 的整合讓這項操作變得相當簡單，因為您可以在預存程序中內嵌 Python 程式碼。若要根據新的輸入從模型取得預測，請直接從應用程式呼叫預存程序，並傳遞新的資料。

教學課程的這個部分將示範兩種建立以 Python 模型為基礎之預測的方法：批次評分，以及依資料列評分資料列。

批次評分： 若要提供輸入資料的多個資料列，請將 SELECT 查詢做為引數傳遞給預存程序。結果是對應至輸入案例的觀察值資料表。
個別評分： 傳遞一組個別參數值作為輸入。此預存程序會傳回單一資料列或值。

評分所需的所有 Python 程式碼都是在預存程序中提供。

在本文中，您將：

建立和使用預存程序進行批次評分
建立和使用預存程序進行單一資料列的評分

在第一部分中，您已安裝必要條件並還原範例資料庫。

在第二部分中，您已探索範例資料並產生一些繪圖。

在第三部分中，您已了解如何使用 Transact-SQL 函式，從未經處理的資料建立特徵。接著，您從預存程序呼叫該函式，建立了包含特徵值的資料表。

在第四部分中，您已載入模組，並呼叫所需的函式，以使用 SQL Server 預存程序來建立和定型模型。

批次評分

使用下列指令碼建立的前兩個預存程序，說明將預測呼叫包裝在 Python 預存程序中的基本語法。這兩個預存程序都需要資料表做為輸入。

要使用的模型名稱會作為預存程序的輸入參數來提供。預存程序會使用預存程序中的 SELECT 陳述式，從資料庫資料表 nyc_taxi_models.table 中載入序列化模型。
序列化模型會儲存在 Python 變數 mod 中，以使用 Python 進行進一步處理。
需要評分的新案例是從 @input_data_1 中指定的 Transact-SQL 查詢取得。讀取查詢資料之後，這些資料列會儲存在預設資料框架 InputDataSet中。
這兩個預存程序都會使用 sklearn 的函數來計算精確度計量 AUC (曲線下的區域)。只有當您也提供目標標籤 (獲得小費資料行) 時，才可以產生精確度計量 (例如 AUC)。預測不需要目標標籤 (變數 y)，但精確度計量計算會需要。

因此，如果要計分的資料沒有目標標籤，您可以修改預存程序來移除 AUC 計算，並且只傳回功能的小費機率 (預存程序中的變數 X)。

PredictTipSciKitPy

執行下列 T-SQL 陳述式以建立預存程序 PredictTipSciKitPy。此預存程序需要以 scikit-learn 為基礎的模型，因為它使用該套件的特定函式。

包含輸入的資料框架會傳遞至羅吉斯回歸模型 mod 的 predict_proba 函數。 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 機器學習服務提供的 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 需要兩個輸入參數：

擷取評分的資料所用的查詢
定型模型的名稱

藉由將這些引數傳遞給預存程序，您可以選取特定的模型，也可以變更用於評分的資料。

若要使用 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 應用程式或報表來呼叫預存程序，並傳遞使用者鍵入或選取的輸入。

在本節中，您將瞭解如何叫用兩個預存程序來建立單一預測：

PredictTipSingleModeSciKitPy 是針對使用 scikit-learn 模型的單一資料列評分所設計。
PredictTipSingleModeRxPy 是針對使用 revoscalepy 模型的單一資料列評分所設計。
如果您尚未定型模型，請返回第五部分！

這兩種模型都接受一系列的單一值，例如乘客計數、路程距離等等。資料表值函數 fnEngineerFeatures 是用來將緯度和經度值從輸入轉換成新功能的直接距離。第四部分包含此資料表值函式的說明。

這兩個預存程序都會根據 Python 模型建立分數。

注意

您從外部應用程式呼叫預存程序時，請務必提供 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

從模型產生分數

建立預存程序之後，就可以輕鬆地根據其中一個模型來產生分數。開啟新的 [查詢] 視窗，並針對每個特徵資料行提供參數。

以下為這些特徵資料行所需的七個值，依序為：

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 模型進行預測，並納入模型重新定型作為企業資料工作流程的一部分。

後續步驟

在本文章中，您將：

已建立和使用預存程序進行批次評分
已建立和使用預存程序進行單一資料列的評分

如需 Python 的詳細資訊，請參閱 SQL Server 中的 Python 延伸模組。

分享方式：