Поделиться через

Развертывание модели R и ее использование в SQL Server (пошаговое руководство)

Область применения: SQL Server 2016 (13.x) и более поздних версий

На этом занятии вы узнаете, как развертывать модели R в рабочей среде, вызывая обученную модель из хранимой процедуры. Вы можете вызывать хранимую процедуру из R или любого прикладного языка программирования с поддержкой Transact-SQL (например, C#, Java, Python и так далее), чтобы с помощью модели составлять прогнозы для новых наблюдений.

В этой статье показаны два наиболее распространенных способа использования модели в оценке:

  • Режим пакетной оценки создает несколько прогнозов
  • Режим индивидуальной оценки создает прогнозы по одному за раз

Пакетная оценка

Создайте хранимую процедуру PredictTipBatchMode, которая создает несколько прогнозов, передавая запрос SQL или таблицу в качестве входных данных. Возвращается таблица результатов, которая может быть вставлена непосредственно в таблицу или записана в файл.

  • получает набор входных данных в виде запроса SQL;
  • вызывает обученную модель логистической регрессии, которая была сохранена на предыдущем занятии;
  • прогнозирует вероятность того, что водитель получит чаевые больше нуля.

  1. В Management Studio откройте новое окно запроса и выполните следующий скрипт T-SQL, чтобы создать хранимую процедуру 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

    • Для вызова хранимой модели из таблицы SQL используется инструкция SELECT. Модель извлекается из таблицы как данные varbinary(max), сохраняется в переменной SQL @lmodel2 и передается в параметре mod в системную хранимую процедуру sp_execute_external_script.

    • Данные, используемые в качестве входных данных для оценки, включаются в SQL-запрос и хранятся в строковом формате в переменной SQL @input. Данные, извлекаемые из базы данных, хранятся в кадре данных с именем InputDataSet. Это стандартное имя для любых входных данных для процедуры sp_execute_external_script, но вы можете определить другое имя переменной с помощью параметра @input_data_1_name.

    • Для формирования оценок хранимая процедура вызывает функцию rxPredict из библиотеки RevoScaleR.

    • Возвращаемое значение Score является вероятностью получения водителем чаевых с учетом модели. При необходимости можно легко применить определенный фильтр к возвращаемым значениям, чтобы разделить их на категории "чаевые" или "без чаевых". Например, вероятность менее 0,5 означает вероятное отсутствие чаевых.

  2. Чтобы вызвать хранимую процедуру в пакетном режиме, необходимо определить запрос в качестве входных данных для хранимой процедуры. Ниже приведен SQL-запрос, который можно запустить в среде SSMS, чтобы убедиться, что это работает.

    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. Используйте этот код R для создания входной строки из SQL запроса:

    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. Чтобы запустить хранимую процедуру из R, вызовите метод sqlQuery пакета RODBC и используйте определенный ранее conn подключения SQL:

    sqlQuery (conn, q);

    При возникновении ошибки ODBC проверьте наличие синтаксических ошибок и правильного числа кавычек.

    При получении ошибки разрешений убедитесь, что у имени входа есть возможность выполнения хранимой процедуры.

Оценка одной строки

Режим индивидуальной оценки создает прогнозы по одному за раз, передавая набор отдельных значений в хранимую процедуру в качестве входных данных. Значения соответствуют признакам модели, которые модель использует для создания прогноза или другого результата, например значения вероятности. Затем это значение можно вернуть приложению или пользователю.

При вызове модели для прогнозирования по строкам передается набор значений, представляющих признаки для каждого отдельного случая. Затем хранимая процедура возвращает один прогноз или вероятность.

Хранимая процедура PredictTipSingleMode демонстрирует этот подход. Она принимает в качестве входных данных несколько параметров, представляющих значения признаков (например, количество пассажиров и расстояние поездок), оценивает эти признаки с помощью хранимой модели R и выводит вероятность чаевых.

  1. Выполните следующую инструкцию Transact-SQL, чтобы создать хранимую процедуру.

    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. В SQL Server Management Studio можно использовать инструкцию Transact-SQL EXEC (или EXECUTE) для вызова хранимой процедуры и передачи ей необходимых входных данных. Например, попробуйте выполнить эту инструкцию в Management Studio:

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

    Здесь передаются значения для переменных passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitudeи dropoff_longitudeсоответственно.

  3. Чтобы выполнить этот же вызов из кода R, просто определите переменную R, содержащую весь вызов хранимой процедуры, например:

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

    Здесь передаются значения для переменных passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitudeи dropoff_longitudeсоответственно.

  4. Вызовите функцию sqlQuery (из пакета RODBC) и передайте строку подключения и строковую переменную, содержащую вызов хранимой процедуры.

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

    Совет

    Инструменты R для Visual Studio (RTVS) обеспечивает большую интеграцию с SQL Server и R. Дополнительные примеры использования RODBC с подключением к SQL Server см. в этой статье. Работа с SQL Server и R

Следующие шаги

Теперь, когда вы узнали, как работать с данными SQL Server и сохранять обученные модели R в SQL Server, это должно быть относительно легко для создания новых моделей на основе этого набора данных. Например, вы можете попробовать создать следующие дополнительные модели:

  • модель регрессии, прогнозирующая размер чаевых;
  • модель многоклассовой классификации, прогнозирующая, будет ли размер чаевых большим, средним или небольшим.

Вы также можете изучить следующие дополнительные примеры и ресурсы:

  • Last updated on