Nasadit model R a použít ho v SQL Server (návod)

platí pro: SQL Server 2016 (13.x) a novější verze

V této lekci se naučíte, jak nasazovat R modely v produkčním prostředí tím, že zavoláte trénovaný model ze uložené procedury. Můžete vyvolat uloženou proceduru z R nebo jakéhokoli programovacího jazyka, který podporuje Transact-SQL (například C#, Java, Python a podobně) a použít model k předpovědi nových pozorování.

Tento článek ukazuje dva nejběžnější způsoby, jak model využít při skórování:

Režim dávkového skórování generuje více predikcí
Individuální způsob skórování generuje predikce jednu po druhé

Dávkové vyhodnocování

Vytvořte uloženou proceduru PredictTipBatchMode, která generuje více predikcí a jako vstup předává SQL dotaz nebo tabulku. Vrátí se tabulka výsledků, kterou můžete vložit přímo do tabulky nebo zapsat do souboru.

Získá sadu vstupních dat jako SQL dotaz
Volá model logistické regrese, který jste natrénovali a uložili v předchozí lekci
Předpovídá pravděpodobnost, že řidič získá jakýkoli nenulový tip

V Management Studiu otevřete nové okno dotazu a spusťte následující T-SQL skript pro vytvoření uložené procedury 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
```
- Použijete příkaz SELECT k volání uloženého modelu z SQL tabulky. Model je načten z tabulky ve formě dat typu varbinary(max), uložen do proměnné SQL @lmodel2 a předán jako parametr mod systémové uložené proceduře sp_execute_external_script.
- Data používaná jako vstupy pro skórování jsou definována jako SQL dotaz a ukládají se jako řetězec v SQL proměnné @input. Když jsou data získávána z databáze, jsou uložena v datovém rámci nazvaném InputDataSet, což je výchozí název pro vstupní data do sp_execute_external_script procedury; Pokud je potřeba, můžete definovat další název proměnné pomocí parametru @input_data_1_name.
- Pro generování skóre uložená procedura volá funkci rxPredict z knihovny RevoScaleR .
- Návratová hodnota Score je podle modelu pravděpodobnost, že daný řidič dostane spropitné. Volitelně můžete snadno použít nějaký filtr na vrácené hodnoty, abyste je rozdělili do skupin "tip" a "no tip". Například pravděpodobnost menší než 0,5 znamená, že tip je nepravděpodobný.

Pro volání uložené procedury v dávkovém režimu definujete požadovaný dotaz jako vstup do uložené procedury. Níže je SQL dotaz, který můžete spustit v SSMS, abyste ověřili, že funguje.

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

Použijte tento kód R k vytvoření vstupního řetězce ze SQL dotazu:

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

Pro spuštění uložené procedury z R zavolejte metodu sqlQuery v balíčku RODBC a použijte SQL spojení conn , které jste dříve definovali:
```
sqlQuery (conn, q);
```
Pokud dostanete chybu v ODBC, zkontrolujte syntaxní chyby a zda máte správný počet uvozovek.

Pokud se objeví chyba oprávnění, ujistěte se, že přihlášení má možnost spustit uloženou proceduru.

Bodování v jednotlivých řadách

Individuální režim skórování generuje predikce jednu po druhé, přičemž jako vstup předává sadu jednotlivých hodnot uložené procedurě. Hodnoty odpovídají rysům v modelu, které model používá k vytvoření predikce nebo k vygenerování jiného výsledku, například pravděpodobnostní hodnoty. Pak můžete tuto hodnotu vrátit aplikaci nebo uživateli.

Při volání modelu pro predikci po řádcích předáte sadu hodnot, které reprezentují rysy pro každý jednotlivý případ. Uložená procedura pak vrací jedinou predikci nebo pravděpodobnost.

Uložená procedura PredictTipSingleMode tento přístup demonstruje. Jako vstup bere více parametrů reprezentujících hodnoty prvků (například počet cestujících a vzdálenost cesty), hodnotí tyto vlastnosti pomocí uloženého modelu R a vyhodnocuje pravděpodobnost spropitného.

Spusť následující příkaz Transact-SQL pro vytvoření uložené procedury.

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

V SQL Server Management Studio můžete použít proceduru Transact-SQL EXEC (nebo EXECUTE) k volání uložené procedury a předání požadovaných vstupů. Například zkuste spustit tento příkaz v Management Studiu:
```
EXEC [dbo].[PredictTipSingleMode] 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
```
Hodnoty zde jsou pro proměnné passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude a dropoff_longitude.
Pro spuštění stejného volání z R kódu jednoduše definujete proměnnou R, která obsahuje celé volání uložené procedury, například tento:
```
q2 = "EXEC PredictTipSingleMode 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303 ";
```
Hodnoty zde jsou pro proměnné passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude a dropoff_longitude.
Volejte sqlQuery (z balíčku RODBC) a předejte připojovací řetězec spolu s proměnnou string obsahující volání uložené procedury.
```
# predict with stored procedure in single mode
sqlQuery (conn, q2);
```
Tip

R Tools for Visual Studio (RTVS) nabízí skvělou integraci jak se SQL Server, tak s R. Viz tento článek pro více příkladů použití RODBC s připojením k SQL Server: Práce se SQL Server a R

Váš názor

Byla tato stránka užitečná?

Poslední aktualizace v 2026-08-24

Nasadit model R a použít ho v SQL Server (návod)

Dávkové vyhodnocování

Bodování v jednotlivých řadách

Související obsah

Váš názor

Další materiály