R-självstudie: Köra förutsägelser i SQL-lagrade procedurer

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL Managed Instance

I del fem i den här självstudieserien i fem delar lär du dig att operationalisera den modell som du tränade och sparade i föregående del med hjälp av modellen för att förutsäga potentiella resultat. Modellen omsluts av en lagrad procedur som kan anropas direkt av andra program.

Den här artikeln visar två sätt att utföra bedömning:

• Batchbedömningsläge: Använd en SELECT-fråga som indata till den lagrade proceduren. Den lagrade proceduren returnerar en tabell med observationer som motsvarar indatafallen.

• Individuellt bedömningsläge: Skicka en uppsättning enskilda parametervärden som indata. Den lagrade proceduren returnerar en enskild rad eller ett värde.

I den här artikeln ska du:

I del ett installerade du förhandskraven och återställde exempeldatabasen.

I del två granskade du exempeldata och genererade några diagram.

I del tre lärde du dig att skapa funktioner från rådata med hjälp av en Transact-SQL funktion. Sedan anropade du funktionen från en lagrad procedur för att skapa en tabell som innehåller funktionsvärdena.

I del fyra läste du in modulerna och kallade nödvändiga funktioner för att skapa och träna modellen med hjälp av en lagrad SQL Server-procedur.

Grundläggande poängsättning

Den lagrade proceduren RPredict illustrerar den grundläggande syntaxen för att omsluta ett PREDICT anrop i en lagrad procedur.

CREATE PROCEDURE [dbo].[RPredict] (@model varchar(250), @inquery nvarchar(max))
AS 
BEGIN 

DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);  
EXEC sp_execute_external_script @language = N'R',
  @script = N' 
    mod <- unserialize(as.raw(model));
    print(summary(mod))
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet)
    print(OutputDataSet)
    ',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max)',
  @model = @lmodel2 
  WITH RESULT SETS (("Score" float));
END
GO

• SELECT-instruktionen hämtar den serialiserade modellen från databasen och lagrar modellen i R-variabeln mod för vidare bearbetning med hjälp av R.

• De nya bedömningsfallen hämtas från den Transact-SQL fråga som anges i @inquery, den första parametern till den lagrade proceduren. När frågedatan läses sparas raderna i standarddataramen, InputDataSet. Den här dataramen skickas till funktionen PREDICT som genererar poängen.

  OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));

  Eftersom en data.frame kan innehålla en enda rad kan du använda samma kod för batch- eller enskild bedömning.

• Värdet som returneras av PREDICT funktionen är en flyttal som representerar sannolikheten att föraren får ett tips av vilken summa som helst.

Batchbedömning (en lista över förutsägelser)

Ett vanligare scenario är att generera förutsägelser för flera observationer i batchläge. I det här steget ska vi se hur batchbedömning fungerar.

1. Börja med att få en mindre uppsättning indata att arbeta med. Den här frågan skapar en "topp 10"-lista över resor med antal passagerare och andra funktioner som behövs för att göra en förutsägelse.

   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 (70 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
   

   Exempelresultat

   passenger_count   trip_time_in_secs    trip_distance  dropoff_datetime          direct_distance
   1                 283                  0.7            2013-03-27 14:54:50.000   0.5427964547
   1                 289                  0.7            2013-02-24 12:55:29.000   0.3797099614
   1                 214                  0.7            2013-06-26 13:28:10.000   0.6970098661
   

2. Skapa en lagrad procedur med namnet RPredictBatchOutput i Management Studio.

   CREATE PROCEDURE [dbo].[RPredictBatchOutput] (@model varchar(250), @inquery nvarchar(max))
   AS
   BEGIN
   DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);
   EXEC sp_execute_external_script 
     @language = N'R',
     @script = N'
       mod <- unserialize(as.raw(model));
       print(summary(mod))
       OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
       str(OutputDataSet)
       print(OutputDataSet)
     ',
     @input_data_1 = @inquery,
     @params = N'@model varbinary(max)',
     @model = @lmodel2
     WITH RESULT SETS ((Score float));
   END
   

3. Ange frågetexten i en variabel och skicka den som en parameter till den lagrade proceduren:

   -- Define the input data
   DECLARE @query_string nvarchar(max)
   SET @query_string='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 (70 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'
   
   -- Call the stored procedure for scoring and pass the input data
   EXEC [dbo].[RPredictBatchOutput] @model = 'RTrainLogit_model', @inquery = @query_string;
   

Den lagrade proceduren returnerar en serie värden som representerar förutsägelsen för var och en av de 10 främsta resorna. Men de bästa resorna är också enpassagerarresor med relativt kort reseavstånd, för vilka föraren sannolikt inte kommer att få ett tips.

Enradsbedömning av flera indata

Ibland vill du skicka in flera indatavärden och få en enda förutsägelse baserat på dessa värden. Du kan till exempel konfigurera ett Excel-kalkylblad, webbprogram eller Reporting Services-rapport för att anropa den lagrade proceduren och ange indata som har skrivits eller valts av användare från dessa program.

I det här avsnittet får du lära dig hur du skapar enkla förutsägelser med hjälp av en lagrad procedur som tar flera indata, till exempel antal passagerare, reseavstånd och så vidare. Den lagrade proceduren skapar en poäng baserat på den tidigare lagrade R-modellen.

Om du anropar den lagrade proceduren från ett externt program kontrollerar du att data matchar kraven för R-modellen. Det kan vara att se till att indata kan omvandlas eller konverteras till en R-datatyp eller verifiera datatypen och datalängden.

1. Skapa en lagrad procedur RPredictSingleRow.

   CREATE PROCEDURE [dbo].[RPredictSingleRow] @model varchar(50), @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 model FROM nyc_taxi_models WHERE name = @model);
   EXEC sp_execute_external_script  
     @language = N'R',
     @script = N'  
       mod <- unserialize(as.raw(model));  
       print(summary(mod));  
       OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
       str(OutputDataSet);
       print(OutputDataSet); 
       ',  
     @input_data_1 = @inquery,  
     @params = N'@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', @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. Prova genom att ange värdena manuellt.

   Öppna ett nytt frågefönster och anropa den lagrade proceduren och ange värden för var och en av parametrarna. Parametrarna representerar funktionskolumner som används av modellen och krävs.

   EXEC [dbo].[RPredictSingleRow] @model = 'RTrainLogit_model',
   @passenger_count = 1,
   @trip_distance = 2.5,
   @trip_time_in_secs = 631,
   @pickup_latitude = 40.763958,
   @pickup_longitude = -73.973373,
   @dropoff_latitude =  40.782139,
   @dropoff_longitude = -73.977303
   

   Eller använd det här kortare formuläret som stöds för parametrar till en lagrad procedur:

   EXEC [dbo].[RPredictSingleRow] 'RTrainLogit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
   

3. Resultaten visar att sannolikheten för att få ett tips är låg (noll) på dessa topp 10 resor, eftersom alla är enstaka passagerarresor över ett relativt kort avstånd.

Slutsatser

Nu när du har lärt dig att bädda in R-kod i lagrade procedurer kan du utöka dessa metoder för att skapa egna modeller. Integreringen med Transact-SQL gör det mycket enklare att distribuera R-modeller för förutsägelse och att införliva modellomträning som en del av ett företagsdataarbetsflöde.

Nästa steg

I den här artikeln kommer du att:

Mer information om R finns i R-tillägget i SQL Server.