Distribuera R-modellen och använd den i SQL Server (genomgång)

gäller för: SQL Server 2016 (13.x) och senare versioner

I denna lektion lär du dig hur man distribuerar R-modeller i en produktionsmiljö genom att anropa en tränad modell från en lagrad procedur. Du kan anropa den lagrade proceduren från R eller vilket programmeringsspråk som helst som stödjer Transact-SQL (såsom C#, Java, Python och så vidare) och använda modellen för att göra förutsägelser på nya observationer.

Den här artikeln visar de två vanligaste sätten att använda en modell vid poängräkning:

  • Batchpoängläge genererar flera förutsägelser
  • Individuellt poängsättningsläge genererar förutsägelser en i taget

Batchbedömning

Skapa en lagrad procedur, PredictTipBatchMode, som genererar flera förutsägelser och skickar en SQL-fråga eller tabell som inmatning. En tabell med resultat returneras, som du kan infoga direkt i en tabell eller skriva till en fil.

  • Får en uppsättning indata som en SQL-fråga
  • Anropar den tränade logistiska regressionsmodellen som du sparade i föregående lektion
  • Förutspår sannolikheten att föraren får dricks som är större än noll

  1. I Management Studio öppnar du ett nytt frågefönster och kör följande T-SQL-skript för att skapa den lagrade proceduren 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

    • Du använder en SELECT-sats för att anropa den lagrade modellen från en SQL-tabell. Modellen hämtas från tabellen som data av typen varbinary(max), lagras i SQL-variabeln @lmodel2 och skickas som parametern mod till den systemlagrade proceduren sp_execute_external_script.

    • Datan som används som indata för poängräkning definieras som en SQL-fråga och lagras som en sträng i SQL-variabeln @input. När data hämtas från databasen lagras den i en dataram som kallas InputDataSet, vilket bara är standardnamnet för indata till sp_execute_external_script proceduren; Du kan definiera ett annat variabelnamn om det behövs genom att använda parametern @input_data_1_name.

    • För att generera poängen anropar den lagrade proceduren rxPredict-funktionen från RevoScaleR-biblioteket .

    • Avkastningsvärdet, Score, är sannolikheten, givet modellen, att föraren får en dricks. Eventuellt kan du enkelt använda någon form av filter på de returnerade värdena för att kategorisera dem i "tips"- och "no tips"-grupper. Till exempel skulle en sannolikhet mindre än 0,5 innebära att ett tips är osannolikt.

  2. För att anropa den lagrade proceduren i batchläge definierar du den fråga som krävs som indata till den lagrade proceduren. Nedan finns SQL-frågan, som du kan köra i SSMS för att verifiera att den fungerar.

    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

  3. Använd denna R-kod för att skapa indatasträngen från SQL-frågan:

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

  4. För att köra den lagrade proceduren från R, anropa sqlQuery-metoden i RODBC-paketet och använd SQL-anslutningen conn som du definierade tidigare:

    sqlQuery (conn, q);

    Om du får ett ODBC-fel, kontrollera om det finns syntaxfel och om du har rätt antal citattecken.

    Om du får ett behörighetsfel, se till att inloggningen har möjlighet att utföra den lagrade proceduren.

Poängsättning på en rad

Individuellt poängsättningsläge genererar förutsägelser en i taget, och skickar en uppsättning individuella värden till den lagrade proceduren som indata. Värdena motsvarar egenskaper i modellen, som modellen använder för att skapa en förutsägelse eller generera ett annat resultat såsom ett sannolikhetsvärde. Du kan sedan returnera det värdet till applikationen eller användaren.

När man anropar modellen för prediktion rad för rad skickar man en uppsättning värden som representerar egenskaper för varje enskilt fall. Den lagrade proceduren returnerar sedan en enda förutsägelse eller sannolikhet.

Den lagrade proceduren PredictTipSingleMode demonstrerar detta tillvägagångssätt. Den tar som indata flera parametrar som representerar funktionsvärden (till exempel passagerarantal och resavstånd), poängsätter dessa egenskaper med hjälp av den lagrade R-modellen och ger ut sannolikheten för tips.

  1. Kör följande Transact-SQL-sats för att skapa den lagrade proceduren.

    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

  2. I SQL Server Management Studio kan du använda Transact-SQL EXEC-proceduren (eller EXECUTE) för att anropa den lagrade proceduren och skicka de nödvändiga inmatningarna. Till exempel, prova att köra detta uttalande i Management Studio:

    EXEC [dbo].[PredictTipSingleMode] 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

    De värden som skickas in här är respektive för variablerna passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude och dropoff_longitude.

  3. För att köra samma anrop från R-kod definierar du helt enkelt en R-variabel som innehåller hela det lagrade proceduranropet, som detta:

    q2 = "EXEC PredictTipSingleMode 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303 ";

    De värden som skickas in här är respektive för variablerna passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude och dropoff_longitude.

  4. Anropa sqlQuery (från RODBC-paketet) och skicka reťazec pripojenia, tillsammans med strängvariabeln som innehåller anropet för lagrad procedur.

    # predict with stored procedure in single mode
sqlQuery (conn, q2);

    Tip

    R Tools for Visual Studio (RTVS) erbjuder utmärkt integration med både SQL Server och R. Se denna artikel för fler exempel på att använda RODBC med en SQL Server-anslutning: Att arbeta med SQL Server och R

Relaterat innehåll

  • Uppdaterades senast