Bereitstellen des R-Modells und Verwendung in SQL Server (exemplarische Vorgehensweise)

Gilt für: SQL Server 2016 (13.x) und höhere Versionen

In dieser Lektion erfahren Sie, wie Sie R-Modelle in einer Produktionsumgebung bereitstellen können, indem Sie ein trainiertes Modell aus einer gespeicherten Prozedur abrufen. Sie können die gespeicherte Prozedur von R oder einer beliebigen Anwendungsprogrammiersprache aus aufrufen, die Transact-SQL unterstützt (z. B. C#, Java, Python usw.) und das Modell verwenden, um Vorhersagen zu neuen Beobachtungen zu treffen.

In diesem Artikel werden die häufigsten Möglichkeiten vorgestellt, ein Modell bei der Bewertung zu verwenden:

Im Batchbewertungsmodus werden mehrere Vorhersagen generiert.
Einzelbewertungsmodus generiert Vorhersagen jeweils einzeln.

Batchbewertung

Erstellen Sie die gespeicherte Prozedur PredictTipBatchMode, die mehrere Vorhersagen generiert und eine SQL-Abfrage oder Tabelle als Eingabe übergibt. Eine Tabelle mit Ergebnissen wird zurückgegeben. Diese können Sie direkt in eine Tabelle einfügen oder in eine Datei schreiben.

Ruft einen Satz von Eingabedaten als SQL-Abfrage ab
Ruft das trainierte logistische Regressionsmodell auf, das Sie in der vorherigen Lektion gespeichert haben
Prognostiziert die Wahrscheinlichkeit, dass der Fahrer ein Trinkgeld größer als null erhält

Öffnen Sie in Management Studio ein neues Abfragefenster, und führen Sie folgendes T-SQL-Skript aus, um die gespeicherte Prozedur „PredictTipBatchMode“ zu erstellen.
```
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
```
- Verwenden Sie eine SELECT-Anweisung, um das gespeicherte Modell aus einer SQL-Tabelle abzurufen. Das Modell wird aus der Tabelle als varbinary(max)-Daten abgerufen, in der SQL-Variablen @lmodel2 gespeichert und als Parameter mod an die gespeicherte Systemprozedur sp_execute_external_script übergeben.
- Die Daten, die als Eingaben für die Bewertung verwendet werden, werden als SQL-Abfrage definiert und als String in der SQL-Variablen @input gespeichert. Wenn die Daten aus der Datenbank abgerufen werden, werden sie in einem Datenrahmen namens InputDataSet gespeichert, der nur der Standardname für die Eingabedaten für die Prozedur sp_execute_external_script ist. Sie können bei Bedarf einen anderen Variablennamen definieren, indem Sie den Parameter @input_data_1_name verwenden.
- Die gespeicherte Prozedur ruft die rxPredict-Funktion aus der RevoScaleR-Bibliothek auf, um die Bewertung zu generieren.
- Der Rückgabewert Score entspricht der Wahrscheinlichkeit, dass der Fahrer gemäß dem Modell ein Trinkgeld bekommt. Optional können Sie einfach einen Filter auf die zurückgegebenen Werten anwenden, um die Rückgabewerte in Gruppen wie „Trinkgeld“ oder „Kein Trinkgeld“ zu kategorisieren. Eine Wahrscheinlichkeit von weniger als 0,5 würde beispielsweise bedeuten, dass wahrscheinlich kein Trinkgeld gegeben wird.

Sie können die gespeicherte Prozedur im Batchmodus abrufen, indem Sie die erforderliche Abfrage als Eingabe für die gespeicherte Prozedur definieren. Im Folgenden sehen Sie die SQL-Abfrage, die Sie zur Überprüfung in SSMS ausführen können.

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

Mit diesem R-Code können Sie die Eingabezeichenfolge aus der SQL-Abfrage erstellen:

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="");

Sie können die gespeicherte Prozedur mit R ausführen, indem Sie die sqlQuery-Methode des RODBC-Pakets aufrufen und die zuvor definierte SQL-Verbindung conn verwenden:
```
sqlQuery (conn, q);
```
Wenn ein ODBC-Fehler angezeigt wird, sollten Sie überprüfen, ob Syntaxfehler vorliegen und die richtige Anzahl von Anführungszeichen vorhanden ist.

Wenn Sie einen Berechtigungsfehler erhalten, stellen Sie sicher, dass das Anmeldekonto über die Berechtigung verfügt, die gespeicherte Prozedur auszuführen.

Bewertung einer einzelnen Zeile

Im Einzelbewertungsmodus wird jeweils eine Vorhersage generiert, wobei ein Satz einzelner Werte als Eingabe an die gespeicherte Prozedur übergeben wird. Diese Werte entsprechen den Eigenschaften des Modells, anhand derer das Modell eine Vorhersage erstellt oder andere Ergebnisse wie einen Wahrscheinlichkeitswert generiert. Sie können diesen Wert dann der Anwendung oder dem Benutzer zurückgeben.

Wenn Sie das Modell für eine Vorhersage auf Zeilenbasis aufrufen, übergeben Sie die Werte, die den Eigenschaften der einzelnen Fälle entsprechen. Die gespeicherte Prozedur gibt dann eine einzelne Vorhersage oder Wahrscheinlichkeit zurück.

Dieser Ansatz wird in der gespeicherten Prozedur PredictTipSingleMode veranschaulicht. Als Eingabe werden mehrere Parameter verwendet, die Merkmalswerte darstellen (z. B. Fahrgastanzahl und Fahrtdistanz), diese Merkmale werden mithilfe des gespeicherten R-Modells bewertet, und als Ausgabe wird die Trinkgeldwahrscheinlichkeit erzeugt.

Führen Sie die folgende Transact-SQL-Anweisung aus, um die gespeicherte Prozedur zu erstellen.

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

In SQL Server Management Studio können Sie über TransactSQL EXEC (oder EXECUTE) die gespeicherte Prozedur aufrufen und die erforderlichen Eingaben an diese übergeben. Führen Sie diese Anweisung in Management Studio aus:
```
EXEC [dbo].[PredictTipSingleMode] 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
```
Die hier übergebenen Werte sind jeweils für die Variablen passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude und dropoff_longitude.
Definieren Sie einfach eine R-Variable, die den gesamten Aufruf der gespeicherten Prozedur enthält, um diesen gleichen Aufruf von R-Code ausführen zu können.
```
q2 = "EXEC PredictTipSingleMode 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303 ";
```
Die hier übergebenen Werte gelten jeweils für die Variablen passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude und dropoff_longitude.
Rufen Sie sqlQuery (aus dem RODBC-Paket) auf und übergeben Sie die Verbindungszeichenfolge zusammen mit der Zeichenfolgenvariable, die den Aufruf der gespeicherten Prozedur enthält.
```
# predict with stored procedure in single mode
sqlQuery (conn, q2);
```
Tipp

R Tools für Visual Studio (RTVS) ist eng mit SQL Server und R verzahnt. Im folgenden Artikel finden Sie weitere Beispiele für die Verwendung von RODBC mit einer SQL Server-Verbindung: Arbeiten mit SQL Server und R

Feedback

War diese Seite hilfreich?

Last updated on 2026-08-25

Bereitstellen des R-Modells und Verwendung in SQL Server (exemplarische Vorgehensweise)

Batchbewertung

Bewertung einer einzelnen Zeile

Verwandte Inhalte

Feedback

Zusätzliche Ressourcen