R-zelfstudie: Voorspellingen uitvoeren in opGESLAGEN SQL-procedures

Van toepassing op: SQL Server 2016 (13.x) en latere versies van Azure SQL Managed Instance

In deel vijf van deze vijfdelige reeks zelfstudies leert u hoe u het model operationeel maakt dat u in het vorige deel hebt getraind en opgeslagen met behulp van het model om potentiële resultaten te voorspellen. Het model wordt verpakt in een opgeslagen procedure die rechtstreeks kan worden aangeroepen door andere toepassingen.

In dit artikel worden twee manieren beschreven om scoren uit te voeren:

• Batchscoremodus: Gebruik een SELECT-query als invoer voor de opgeslagen procedure. De opgeslagen procedure retourneert een tabel met waarnemingen die overeenkomen met de invoercases.

• Afzonderlijke scoremodus: geef een set afzonderlijke parameterwaarden door als invoer. De opgeslagen procedure retourneert één rij of waarde.

In dit artikel gaat u het volgende doen:

In deel 1 hebt u de vereisten geïnstalleerd en de voorbeelddatabase hersteld.

In deel twee hebt u de voorbeeldgegevens gecontroleerd en enkele plots gegenereerd.

In deel drie hebt u geleerd hoe u functies maakt op basis van onbewerkte gegevens met behulp van een Transact-SQL-functie. Vervolgens hebt u die functie aangeroepen vanuit een opgeslagen procedure om een tabel te maken die de functiewaarden bevat.

In deel vier hebt u de modules geladen en de benodigde functies aangeroepen om het model te maken en te trainen met behulp van een opgeslagen SQL Server-procedure.

Basis scoreberekening

De opgeslagen procedure RPredict illustreert de basissyntaxis voor het verpakken van een PREDICT aanroep in een opgeslagen procedure.

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

• De SELECT-instructie haalt het geserialiseerde model op uit de database en slaat het model op in de R-variabele mod voor verdere verwerking met behulp van R.

• De nieuwe gevallen voor scoren worden verkregen uit de Transact-SQL query die is opgegeven in @inquery, de eerste parameter voor de opgeslagen procedure. Terwijl de querygegevens worden gelezen, worden de rijen opgeslagen in het standaardgegevensframe. InputDataSet Dit gegevensframe wordt doorgegeven aan de functie PREDICT waarmee de scores worden gegenereerd.

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

  Omdat een data.frame één rij kan bevatten, kunt u dezelfde code gebruiken voor batch- of enkele score.

• De waarde die door de PREDICT functie wordt geretourneerd, is een float die de kans aangeeft dat het stuurprogramma een fooi van een willekeurig bedrag krijgt.

Batchgewijze scoreberekening (een lijst met voorspellingen)

Een veelvoorkomender scenario is het genereren van voorspellingen voor meerdere waarnemingen in de batchmodus. In deze stap kijken we hoe batchgewijs scoren werkt.

1. Begin met het ophalen van een kleinere set invoergegevens om mee te werken. Met deze query maakt u een lijst 'top 10' met reizen met het aantal passagiers en andere functies die nodig zijn om een voorspelling te doen.

   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
   

   Voorbeeldresultaten

   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. Maak een opgeslagen procedure met de naam RPredictBatchOutput in 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. Geef de querytekst in een variabele op en geef deze als parameter door aan de opgeslagen procedure:

   -- 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;
   

De opgeslagen procedure retourneert een reeks waarden die de voorspelling voor elk van de top 10-ritten vertegenwoordigen. De bovenste ritten zijn echter ook reizen met één passagier met een relatief korte reisafstand, waarvoor de chauffeur waarschijnlijk geen tip krijgt.

Beoordelen met enkele rij van meerdere invoer

Soms wilt u meerdere invoerwaarden doorgeven en één voorspelling krijgen op basis van deze waarden. U kunt bijvoorbeeld een Excel-werkblad, webtoepassing of Reporting Services-rapport instellen om de opgeslagen procedure aan te roepen en invoer op te geven die is getypt of geselecteerd door gebruikers uit die toepassingen.

In deze sectie leert u hoe u enkelvoudige voorspellingen maakt met behulp van een opgeslagen procedure die meerdere invoer gebruikt, zoals het aantal passagiers, reisafstand, enzovoort. Met de opgeslagen procedure wordt een score gemaakt op basis van het eerder opgeslagen R-model.

Als u de opgeslagen procedure aanroept vanuit een externe toepassing, moet u ervoor zorgen dat de gegevens voldoen aan de vereisten van het R-model. Dit kan bijvoorbeeld zijn om ervoor te zorgen dat de invoergegevens kunnen worden gecast of geconverteerd naar een R-gegevenstype, of om het gegevenstype en de gegevenslengte te valideren.

1. Maak een opgeslagen procedure 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. Probeer het uit door de waarden handmatig op te geven.

   Open een nieuw queryvenster en roep de opgeslagen procedure aan en geef waarden op voor elk van de parameters. De parameters vertegenwoordigen functiekolommen die door het model worden gebruikt en zijn vereist.

   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
   

   Of gebruik deze kortere vorm die wordt ondersteund voor parameters voor een opgeslagen procedure:

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

3. De resultaten geven aan dat de kans op het krijgen van een tip laag (nul) is op deze top 10 reizen, omdat alle ritten met één passagier over een relatief korte afstand zijn.

Conclusies

Nu u hebt geleerd om R-code in te sluiten in opgeslagen procedures, kunt u deze procedures uitbreiden om zelf modellen te bouwen. Dankzij de integratie met Transact-SQL is het veel eenvoudiger om R-modellen te implementeren voor voorspellingen en om het opnieuw trainen van modellen op te nemen als onderdeel van een werkstroom voor zakelijke gegevens.

Volgende stappen

In dit artikel gaat u als volgt te werk:

Zie R-extensie in SQL Server voor meer informatie over R.