部署 R 模型，並將其用於 SQL Server (逐步解說)

發行項
05/04/2023

適用於：SQL Server 2016 (13.x) 和更新版本

在本課中，您將了解如何從預存程序呼叫定型模型，以便在實際執行環境中部署 R 模型。您可以從 R 或任何支援 Transact-SQL 的應用程式設計語言 (如 C#、JAVA、Python 等) 叫用預存程序，並使用模型進行新的觀察預測。

本文示範在評分中使用模型的兩個最常見方式：

批次評分模式會產生多個預測
個別評分模式一次產生一個預測

批次評分

建立一個預存程序 PredictTipBatchMode，它會透過傳遞 SQL 查詢或資料表作為輸入，產生多個預測。系統會傳回結果的資料表，您可以將其直接插入資料表或寫入至檔案。

取一組輸入資料做為 SQL 查詢
呼叫您在上一課中儲存的定型羅吉斯迴歸模型
預測司機獲得非零小費的機率

在 Management Studio 中，開啟新的查詢視窗，然後執行下列 T-SQL 指令碼來建立 PredictTipBatchMode 預存程序。
```
USE [NYCTaxi_Sample]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PredictTipBatchMode')
DROP PROCEDURE v
GO

CREATE PROCEDURE [dbo].[PredictTipBatchMode] @input nvarchar(max)
AS
BEGIN
  DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1 model  FROM nyc_taxi_models);
  EXEC sp_execute_external_script @language = N'R',
     @script = N'
       mod <- unserialize(as.raw(model));
       print(summary(mod))
       OutputDataSet<-rxPredict(modelObject = mod,
         data = InputDataSet,
         outData = NULL,
         predVarNames = "Score", type = "response",
         writeModelVars = FALSE, overwrite = TRUE);
       str(OutputDataSet)
       print(OutputDataSet)',
  @input_data_1 = @input,
  @params = N'@model varbinary(max)',
  @model = @lmodel2
  WITH RESULT SETS ((Score float));
END
```
- 您可以使用 SELECT 陳述式，從 SQL 資料表呼叫預存模型。該模型會從資料表擷取，作為 varbinary(max) 資料，並儲存於 SQL 變數 @lmodel2 中，然後作為參數 mod 傳遞至系統預存程序 sp_execute_external_script。
- 用作評分輸入的資料會定義為 SQL 查詢，並以字串形式儲存於 SQL 變數 @input 中。從資料庫擷取資料時，該資料會儲存於名為 InputDataSet 的資料框架中，而該名稱只是 sp_execute_external_script 程序中輸入資料的預設名稱；您可以視需要使用參數 @input_data_1_name 來定義另一個變數名稱。
- 預存程序會從 RevoScaleR 程式庫呼叫 rxPredict 函式來產生分數。
- 傳回值 Score 是在模型已知時，司機獲得小費的機率。 (選擇性) 您可以輕鬆地將某種篩選條件套用到傳回的值，以便將傳回值分類成「有小費」和「沒小費」群組。例如，機率小於 0.5 表示不太可能有小費。

若要在批次模式下呼叫預存程序，您可以將所需的查詢定義為預存程序的輸入。以下是 SQL 查詢，您可以在 SSMS 中執行該查詢來確認它是否正常運作。

SELECT TOP 10
  a.passenger_count AS passenger_count,
  a.trip_time_in_secs AS trip_time_in_secs,
  a.trip_distance AS trip_distance,
  a.dropoff_datetime AS dropoff_datetime,
  dbo.fnCalculateDistance( pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) AS direct_distance
  FROM 
    (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude 
    FROM nyctaxi_sample)a 
  LEFT OUTER JOIN
  ( SELECT medallion, hack_license, pickup_datetime
  FROM nyctaxi_sample  tablesample (1 percent) repeatable (98052)  )b
  ON a.medallion=b.medallion
  AND a.hack_license=b.hack_license
  AND a.pickup_datetime=b.pickup_datetime
  WHERE b.medallion is null

使用此 R 程式碼，從 SQL 查詢建立輸入字串：

input <- "N'SELECT TOP 10 a.passenger_count AS passenger_count, a.trip_time_in_secs AS trip_time_in_secs, a.trip_distance AS trip_distance, a.dropoff_datetime AS dropoff_datetime, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) AS direct_distance FROM (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample)a LEFT OUTER JOIN ( SELECT medallion, hack_license, pickup_datetime FROM nyctaxi_sample  tablesample (1 percent) repeatable (98052)  )b ON a.medallion=b.medallion AND a.hack_license=b.hack_license AND  a.pickup_datetime=b.pickup_datetime WHERE b.medallion is null'";
q <- paste("EXEC PredictTipBatchMode @input = ", input, sep="");

若要從 R 執行預存程序，呼叫 RODBC 封裝的 sqlQuery 方法，並使用您稍早定義的 SQL 連線 conn：
```
sqlQuery (conn, q);
```
如果您收到 ODBC 錯誤，請檢查是否有語法錯誤，以及是否有正確的引號數目。

如果您收到權限錯誤，請確定登入能夠執行預存程序。

單一資料列評分

個別評分模式會將一組個別的值當作輸入傳遞至預存程式，藉此一次會產生一個預測。這些值會對應至模型中的功能，而模型會使用這些功能來建立預測，或產生另一個結果，例如機率值。接著，您可以將該值傳回給應用程式或使用者。

以逐列方式呼叫模型進行預測時，您要針對每個個別案例，傳遞一組代表功能的值。接著，預存程序會傳回單一預測或機率。

預存程序 PredictTipSingleMode 會示範這種方法。它會採用代表功能值的多個參數 (例如，乘客計數和行程距離) 當作輸入、使用預存 R 模型對這些功能評分，並輸出小費機率。

執行下列 Transact-SQL 陳述式來建立預存程序。

USE [NYCTaxi_Sample]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PredictTipSingleMode')
DROP PROCEDURE v
GO

CREATE PROCEDURE [dbo].[PredictTipSingleMode] @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 TOP 1 model FROM nyc_taxi_models);

  EXEC sp_execute_external_script @language = N'R',  @script = N'
        mod <- unserialize(as.raw(model));
        print(summary(mod))
        OutputDataSet<-rxPredict(
          modelObject = mod,
          data = InputDataSet,
          outData = NULL,
          predVarNames = "Score",
          type = "response",
          writeModelVars = FALSE,
          overwrite = TRUE);
        str(OutputDataSet)
        print(OutputDataSet)
        ',
  @input_data_1 = @inquery,
  @params = N'
  -- passthrough columns
  @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',
  -- mapped variables
  @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

在 SQL Server Management Studio 中，您可以使用 Transact-SQL EXEC 程序 (或 EXECUTE) 來呼叫預存程序，並將必要的輸入傳遞給該預存程序。例如，請嘗試在 Management Studio 中執行此陳述式：
```
EXEC [dbo].[PredictTipSingleMode] 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
```
此處所傳遞的值分別是 passenger_count、 trip_distance、 trip_time_in_secs、 pickup_latitude、 pickup_longitude、 dropoff_latitude和 dropoff_longitude變數。
若要從 R 程式碼執行這個相同的呼叫，您只需定義包含整個預存程序呼叫的 R 變數，如下所示：
```
q2 = "EXEC PredictTipSingleMode 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303 ";
```
此處所傳遞的值分別是 passenger_count、 trip_distance、 trip_time_in_secs、 pickup_latitude、 pickup_longitude、 dropoff_latitude和 dropoff_longitude變數。
呼叫 sqlQuery (從 RODBC 封裝)，並傳遞連接字串以及包含預存程序呼叫的字串變數。
```
# predict with stored procedure in single mode
sqlQuery (conn, q2);
```
提示

適用於 Visual Studio 的 R 工具 (RTVS) 提供與 SQL Server 和 R 的絕佳整合。如需使用 RODBC 搭配 SQL Server 連線的其他範例，請參閱以下文章：使用 SQL Server 和 R \(部分機器翻譯\)

後續步驟

既然您已經了解如何使用 SQL Server 資料並將經訓練的 R 模型保存至 SQL Server，對您來說，根據此資料集建立新的模型應該相對輕鬆。例如，您可以嘗試建立下列額外的模型︰

可預測小費金額的迴歸模型
預測小費金額為高、中或低的多元分類模型

您也可以探索下列額外的範例和資源：

共用方式為

部署 R 模型，並將其用於 SQL Server (逐步解說)

批次評分

單一資料列評分

後續步驟

意見反應

意見反應

其他資源