Deploy het R-model en gebruik het in SQL Server (walkthrough)

Van toepassing op: SQL Server 2016 (13.x) en latere versies

In deze les leer je hoe je R-modellen in een productieomgeving kunt uitrollen door een getraind model aan te roepen vanuit een opgeslagen procedure. Je kunt de opgeslagen procedure aanroepen vanuit R of elke programmeertaal die Transact-SQL ondersteunt (zoals C#, Java, Python, enzovoort) en het model gebruiken om voorspellingen te doen op nieuwe waarnemingen.

Dit artikel toont de twee meest voorkomende manieren om een model te gebruiken bij het scoren:

De batch scoringmodus genereert meerdere voorspellingen
De individuele scoremodus genereert voorspellingen één voor één

Batchscore

Maak een opgeslagen procedure, PredictTipBatchMode, die meerdere voorspellingen genereert en een SQL-query of tabel als invoer doorgeeft. Er wordt een tabel met resultaten teruggegeven, die je direct in een tabel kunt invoegen of naar een bestand kunt schrijven.

Krijgt een set invoerdata als een SQL-query
Roept het getrainde logistische regressiemodel aan dat je in de vorige les hebt opgeslagen
Voorspelt de kans dat de bestuurder een fooi hoger dan nul krijgt

Open in Management Studio een nieuw queryvenster en voer het volgende T-SQL-script uit om de opgeslagen procedure PredictTipBatchMode te maken.
```
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
```
- Je gebruikt een SELECT-instructie om het opgeslagen model aan te roepen vanuit een SQL-tabel. Het model wordt uit de tabel gehaald als varbinary(max) -gegevens, opgeslagen in de SQL-variabele @lmodel2, en als parametermod doorgegeven aan de systeem-stored procedure sp_execute_external_script.
- De gegevens die als invoer voor scoring worden gebruikt, worden gedefinieerd als een SQL-query en opgeslagen als een string in de SQL-variabele @input. Wanneer gegevens uit de database worden opgehaald, worden ze opgeslagen in een dataframe genaamd InputDataSet, wat gewoon de standaardnaam is voor invoergegevens van de sp_execute_external_script procedure; Je kunt indien nodig een andere variabelenaam definiëren door de parameter @input_data_1_name te gebruiken.
- Om de scores te genereren, roept de opgeslagen procedure de rxPredict-functie aan uit de RevoScaleR-bibliotheek .
- De retourwaarde, Score, is de waarschijnlijkheid, gegeven het model, dat de driver een fooi krijgt. Optioneel kun je eenvoudig een soort filter toepassen op de teruggestuurde waarden om de terugkomstwaarden in "tip" en "geen fooi" groepen te categoriseren. Bijvoorbeeld, een kans van minder dan 0,5 betekent dat een tip onwaarschijnlijk is.

Om de opgeslagen procedure in batchmodus aan te roepen, definieer je de query die nodig is als invoer voor de opgeslagen procedure. Hieronder staat de SQL-query, die je in SSMS kunt uitvoeren om te verifiëren dat deze werkt.

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

Gebruik deze R-code om de invoerstring te maken uit de SQL-query:

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

Om de opgeslagen procedure vanuit R uit te voeren, roep je de sqlQuery-methode van het RODBC-pakket aan en gebruik je de SQL-verbinding conn die je eerder hebt gedefinieerd:
```
sqlQuery (conn, q);
```
Als je een ODBC-fout krijgt, controleer dan op syntaxisfouten en of je het juiste aantal aanhalingstekens hebt.

Als je een foutmelding krijgt met de machtigingen, zorg er dan voor dat de login de mogelijkheid heeft om de opgeslagen procedure uit te voeren.

Enkele rij puntentelling

De individuele scoringsmodus genereert voorspellingen één voor één, waarbij een set individuele waarden als input aan de opgeslagen procedure wordt doorgegeven. De waarden komen overeen met kenmerken in het model, die het model gebruikt om een voorspelling te maken of een ander resultaat te genereren, zoals een kanswaarde. Je kunt die waarde dan teruggeven aan de applicatie of gebruiker.

Wanneer je het model voor voorspelling rij-voor-rij aanroept, geef je een set waarden door die kenmerken voor elk individueel geval vertegenwoordigen. De opgeslagen procedure geeft vervolgens één voorspelling of kans terug.

De opgeslagen procedure PredictTipSingleMode demonstreert deze benadering. Het neemt als invoer meerdere parameters die featurewaarden voorstellen (bijvoorbeeld passagiersaantal en reisafstand), beoordeelt deze features met het opgeslagen R-model en geeft de tipkans uit.

Voer de volgende Transact-SQL-instructie uit om de opgeslagen procedure te maken.

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 kun je de Transact-SQL EXEC-procedure (of EXECUTE) gebruiken om de opgeslagen procedure aan te roepen en de benodigde invoer door te geven. Probeer bijvoorbeeld deze instructie uit te voeren in Management Studio:
```
EXEC [dbo].[PredictTipSingleMode] 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
```
De waarden die hier worden doorgegeven zijn respectievelijk voor de variabelen passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude en dropoff_longitude.
Om dezezelfde aanroep uit R-code uit te voeren, definieer je simpelweg een R-variabele die de volledige stored procedure-aanroep bevat, zoals deze:
```
q2 = "EXEC PredictTipSingleMode 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303 ";
```
De waarden die hier worden doorgegeven zijn respectievelijk voor de variabelen passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude en dropoff_longitude.
Roep sqlQuery aan (uit het RODBC-pakket) en geef de verbindingsreeks door, samen met de stringvariabele die de stored procedure call bevat.
```
# predict with stored procedure in single mode
sqlQuery (conn, q2);
```
Tip

R Tools for Visual Studio (RTVS) biedt uitstekende integratie met zowel SQL Server als R. Zie dit artikel voor meer voorbeelden van het gebruik van RODBC met een SQL Server-verbinding: Werken met SQL Server en R

Feedback

Is deze pagina nuttig?

Last updated on 2026-08-24

Deploy het R-model en gebruik het in SQL Server (walkthrough)

Batchscore

Enkele rij puntentelling

Verwante onderwerpen

Feedback

Aanvullende resources