R-oktatóanyag: Előrejelzések futtatása sql-ben tárolt eljárásokban

A következőkre vonatkozik: Sql Server 2016 (13.x) és újabb verziók felügyelt Azure SQL-példány

Az ötrészes oktatóanyag-sorozat ötödik részében megtanulhatja üzembe helyezni az előző részben betanított és mentett modellt a modell használatával a lehetséges eredmények előrejelzéséhez. A modell egy tárolt eljárásba van csomagolva, amelyet más alkalmazások közvetlenül hívhatnak meg.

Ez a cikk két módszert mutat be a pontozás végrehajtására:

• Kötegelt pontozási mód: SELECT lekérdezés használata a tárolt eljárás bemeneteként. A tárolt eljárás a bemeneti eseteknek megfelelő megfigyelési táblázatot ad vissza.

• Egyéni pontozási mód: Adjon meg egy sor egyedi paraméterértéket bemenetként. A tárolt eljárás egyetlen sort vagy értéket ad vissza.

Ebben a cikkben a következőket fogja megismerni:

• Tárolt eljárások létrehozása és használata kötegelt értékeléshez
• Tárolt eljárások létrehozása és használata egyetlen sor pontozásához

Az első részben telepítette az előfeltételeket, és visszaállította a mintaadatbázist.

A második részben áttekintette a mintaadatokat, és létrehozott néhány diagramot.

A harmadik részben megtanulta, hogyan hozhat létre funkciókat nyers adatokból egy Transact-SQL függvény használatával. Ezt követően egy tárolt eljárásból meghívta ezt a függvényt, hogy létrehozhasson egy táblát, amely tartalmazza a funkcióértékeket.

A negyedik részben betöltötte a modulokat, és meghívta a modell sql serverrel tárolt eljárás használatával történő létrehozásához és betanítása szükséges függvényeket.

Alapszintű pontozás

Az RPredict tárolt eljárás a hívások tárolt eljárásban való körbefuttatásának PREDICT alapszintaxisát mutatja be.

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

• A SELECT utasítás lekéri a szerializált modellt az adatbázisból, és az R változóban mod tárolja a modellt további feldolgozás céljából az R használatával.

• Az új pontozási eseteket a rendszer az első paraméterben @inquerymegadott Transact-SQL lekérdezésből szerzi be a tárolt eljárásba. A lekérdezési adatok olvasása során a sorok az alapértelmezett adatkeretbe lesznek mentve. InputDataSet Ez az adatkeret a pontszámokat létrehozó PREDICT függvénynek lesz átadva.

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

  Mivel a data.frame egyetlen sort tartalmazhat, ugyanazt a kódot használhatja kötegekhez vagy egyetlen pontozáshoz.

• A függvény által visszaadott PREDICT érték egy lebegőpontos érték, amely annak valószínűségét jelzi, hogy a sofőr bármilyen mennyiségű borravalót kap.

Kötegelt pontozás (az előrejelzések listája)

Gyakoriabb forgatókönyv, ha több megfigyeléshez hozunk létre előrejelzéseket kötegelt módban. Ebben a lépésben lássuk, hogyan működik a kötegelt pontozás.

1. Első lépésként szerezzen be egy kisebb készletet a bemeneti adatokból, hogy azzal dolgozhasson. Ez a lekérdezés létrehozza az "első 10" listát az utasok számával és az előrejelzéshez szükséges egyéb funkciókkal.

   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
   

   Mintaeredmények

   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. Hozzon létre egy RPredictBatchOutput nevű tárolt eljárást a Management Studióban.

   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. Adja meg a lekérdezés szövegét egy változóban, és adja meg paraméterként a tárolt eljárásnak:

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

A tárolt eljárás az első 10 utazásra vonatkozó előrejelzést képviselő értékek sorozatát adja vissza. Azonban a legnépszerűbb utak is olyan egy utas által megtett utak, amelyek viszonylag rövid távolságúak, és amiért valószínűleg nem kap borravalót a sofőr.

Jótanács

Ahelyett, hogy csak az "igen-tipp" és a "no-tip" eredményt adja vissza, az előrejelzés valószínűségi pontszámát is visszaadhatja, majd egy WHERE záradékot alkalmazhat a Pontszám oszlop értékeire, hogy kategorizálja a pontszámot "valószínűleg tipp" vagy "nem valószínű, hogy tipp" értékként, egy 0,5 vagy 0,7 küszöbérték használatával. Ez a lépés nem szerepel a tárolt eljárásban, de könnyen implementálható.

Több bemenet egysoros pontozása

Előfordulhat, hogy több bemeneti értéket szeretne átadni, és ezek alapján egyetlen előrejelzést szeretne kapni. Beállíthat például egy Excel-munkalapot, webalkalmazást vagy Reporting Services-jelentést, hogy meghívja a tárolt eljárást, és adja meg az alkalmazás felhasználói által beírt vagy kiválasztott bemeneteket.

Ebben a szakaszban megtudhatja, hogyan hozhat létre egyetlen előrejelzést egy olyan tárolt eljárással, amely több bemenetet vesz igénybe, például az utasok számát, az utazási távolságot stb. A tárolt eljárás a korábban tárolt R-modell alapján hoz létre pontszámot.

Ha külső alkalmazásból hívja meg a tárolt eljárást, győződjön meg arról, hogy az adatok megfelelnek az R-modell követelményeinek. Ez magában foglalhatja annak biztosítását, hogy a bemeneti adatok R adattípussá alakíthatók vagy alakíthatók át, vagy az adattípus és az adathossz ellenőrzése.

1. Hozzon létre egy tárolt eljárást 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. Próbálja ki, adja meg az értékeket manuálisan.

   Nyisson meg egy új lekérdezési ablakot, és hívja meg a tárolt eljárást, és adja meg az egyes paraméterek értékeit. A paraméterek a modell által használt funkcióoszlopokat jelölik, és kötelezőek.

   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
   

   Vagy használja a paraméterekhez támogatott rövidebb űrlapot egy tárolt eljáráshoz:

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

3. Az eredmények azt mutatják, hogy a tipp megszerzésének valószínűsége alacsony (nulla) ezen az első 10 utazáson, mivel mindegyik egy-utas utazás viszonylag rövid távolságra.

Következtetések

Most, hogy megtanulta az R-kód beágyazását a tárolt eljárásokba, kiterjesztheti ezeket a gyakorlatokat saját modellek létrehozására. A Transact-SQL integrációja sokkal egyszerűbbé teszi az R-modellek előrejelzéshez való üzembe helyezését, valamint a modellek újratanítását egy vállalati adat-munkafolyamat részeként.

Következő lépések

Ebben a cikkben a következőt találja:

• Tárolt eljárásokat hoztam létre és használtam kötegelt pontszámításhoz.
• Tárolt eljárások létrehozása és használata egyetlen sor pontozásához

Az R-ről további információt az SQL Server R-bővítményében talál.