Wdroż model R i użyj go w SQL Server (poradnik techniczny)

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje

W tej lekcji dowiedz się, jak wdrażać modele R w środowisku produkcyjnym, wywołując wytrenowany model z procedury przechowywanej. Możesz wywołać procedurę przechowywaną z R lub dowolnego języka programowania obsługującego Transact-SQL (takich jak C#, Java, Python itd.) i wykorzystać model do przewidywania nowych obserwacji.

Ten artykuł pokazuje dwa najczęściej stosowane sposoby wykorzystania modelu w ocenie:

Tryb oceniania wsadowego generuje wiele predykcji
Tryb indywidualnego punktowania generuje przewidywania pojedynczo

Wsadowe ocenianie

Stwórz procedurę przechowywaną PredictTipBatchMode, która generuje wiele przewidywań, przekazując zapytanie SQL lub tabelę jako wejście. Zwracana jest tabela wyników, którą możesz wstawić bezpośrednio do tabeli lub zapisać do pliku.

Otrzymuje zestaw danych wejściowych jako zapytanie SQL
Nazywa wytrenowany model regresji logistycznej, który zanotowałeś w poprzedniej lekcji
Przewiduje prawdopodobieństwo, że kierowca otrzyma dowolną niezerową wskazówkę

W Management Studio otwórz nowe okno zapytań i uruchom następujący skrypt T-SQL, aby utworzyć procedurę przechowywaną 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
```
- Używasz instrukcji SELECT do wywołania przechowywanego modelu z tabeli SQL. Model jest pobierany z tabeli jako dane typu varbinary(max), przechowywany w zmiennej SQL @lmodel2 i przekazywany jako parametr mod do systemowej procedury składowanej sp_execute_external_script.
- Dane używane jako dane wejściowe do punktacji definiowane są jako zapytanie SQL i przechowywane jako ciąg w zmiennej SQL @input. W miarę pobierania danych z bazy danych są one przechowywane w ramce danych zwanej InputDataSet, która jest domyślną nazwą danych wejściowych do procedury sp_execute_external_script ; Możesz zdefiniować nazwę innej zmiennej, jeśli zajdzie taka potrzeba, używając parametru @input_data_1_name.
- Aby wygenerować wyniki, procedura przechowywana wywołuje funkcję rxPredict z biblioteki RevoScaleR .
- Wartość zwracana, Score, to prawdopodobieństwo, według modelu, że kierowca otrzyma napiwek. Opcjonalnie można łatwo zastosować jakiś filtr do zwróconych wartości, aby podzielić je na grupy "tip" i "no tip". Na przykład prawdopodobieństwo mniejsze niż 0,5 oznacza, że napiwek jest mało prawdopodobny.

Aby wywołać procedurę przechowywaną w trybie wsadowym, definiujesz wymagane zapytanie jako wejście do procedury przechowywanej. Poniżej znajduje się zapytanie SQL, które możesz uruchomić w SSMS, aby zweryfikować, czy działa.

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

Użyj tego kodu R, aby utworzyć ciąg wejściowy z zapytania 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="");

Aby uruchomić procedurę przechowywaną z R, wywołaj metodę sqlQuery w pakiecie RODBC i użyj połączenia conn SQL zdefiniowanego wcześniej:
```
sqlQuery (conn, q);
```
Jeśli pojawi się błąd ODBC, sprawdź błędy składniowe i czy masz odpowiednią liczbę cudzysłowów.

Jeśli pojawi się błąd uprawnień, upewnij się, że logowanie ma możliwość wykonania procedury przechowywanej.

Punktacja w pojedynczym rzędzie

Tryb oceniania indywidualnego generuje predykcje pojedynczo, przekazując do procedury składowanej zestaw pojedynczych wartości jako dane wejściowe. Wartości te odpowiadają cechom modelu, które model wykorzystuje do stworzenia przewidywania lub generowania innego wyniku, takiego jak wartość prawdopodobieństwa. Następnie możesz zwrócić tę wartość aplikacji lub użytkownikowi.

Wywołując model do predykcji wiersz po wierszu, przekazujesz zestaw wartości reprezentujących cechy dla każdego indywidualnego przypadku. Procedura przechowywana zwraca wtedy pojedynczą prognozę lub prawdopodobieństwo.

Procedura przechowywana PredictTipSingleMode demonstruje to podejście. Przyjmuje jako wejście wiele parametrów reprezentujących wartości cech (na przykład liczbę pasażerów i odległość podróży), ocenia te cechy za pomocą zapisanego modelu R i generuje prawdopodobieństwo wyrzutu.

Uruchom następującą instrukcję Transact-SQL, aby utworzyć procedurę przechowywaną.

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

W SQL Server Management Studio możesz użyć procedury EXEC (lub EXECUTE) Transact-SQL, aby wywołać procedurę przechowywaną i przekazać jej wymagane wejścia. Na przykład, spróbuj uruchomić to zdanie w Management Studio:
```
EXEC [dbo].[PredictTipSingleMode] 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
```
Wartości wprowadzone tutaj to odpowiednio zmienne passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude i dropoff_longitude.
Aby wykonać to samo wywołanie z kodu R, wystarczy zdefiniować zmienną R, która zawiera całe wywołanie procedury przechowywanej, na przykład taką:
```
q2 = "EXEC PredictTipSingleMode 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303 ";
```
Wartości wprowadzone tutaj to odpowiednio zmienne passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude i dropoff_longitude.
Wywołaj sqlQuery (z pakietu RODBC) i przekaż parametr połączenia razem ze zmienną tekstową zawierającą wywołanie procedury przechowywanej.
```
# predict with stored procedure in single mode
sqlQuery (conn, q2);
```
Wskazówka

R Tools for Visual Studio (RTVS) zapewnia świetną integrację zarówno z SQL Server, jak i R. Zobacz ten artykuł, aby poznać więcej przykładów wykorzystania RODBC z połączeniem SQL Server: Praca z SQL Server i R

Opinia

Czy ta strona była pomocna?

Last updated on 2026-08-25

Wdroż model R i użyj go w SQL Server (poradnik techniczny)

Wsadowe ocenianie

Punktacja w pojedynczym rzędzie

Treści powiązane

Opinia

Dodatkowe zasoby