Руководство по R. Выполнение прогнозов в хранимых процедурах SQL

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

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

В этой статье показано два способа оценки:

• Режим пакетной оценки: используйте запрос SELECT в качестве входных данных в хранимую процедуру. Хранимая процедура возвращает таблицу наблюдений, соответствующую входным вариантам.

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

Работая с этой статьей, вы узнаете о следующем.

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

Во второй части вы узнали, как проверить пример данных и создать графики.

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

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

Базовый процесс оценки

Хранимая процедура RPredict иллюстрирует базовый синтаксис для заключения вызова PREDICT в хранимую процедуру.

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 получает сериализованную модель из базы данных и сохраняет ее в переменной R mod для дальнейшей обработки с помощью языка R.

• Новые варианты для оценки извлекаются из запроса Transact-SQL, указанного в @inquery (первом параметре хранимой процедуры). По мере считывания данных запроса строки сохраняются в кадре данных по умолчанию InputDataSet. Этот кадр данных передается в функцию PREDICT, которая формирует оценки.

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

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

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

Пакетная оценка (список прогнозов)

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

1. Сначала получим набор входных данных меньшего размера, с которым будем далее работать. Этот запрос создает список первых 10 поездок с числом пассажиров и другими характеристиками, необходимыми для составления прогноза.

   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
   

   Пример результата

   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. Создайте хранимую процедуру с именем RPredictBatchOutput в 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. Задайте текст запроса в переменной и передайте ее в качестве параметра в хранимую процедуру:

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

Эта хранимая процедура возвращает ряд значений, представляющих прогнозы для каждой из 10 самых популярных поездок. Однако самые популярные поездки — это поездки с одним пассажиром на относительно короткое расстояние, за которые водители вряд ли получат чаевые.

Оценка одной строки нескольких входных значений

Иногда требуется передать несколько входных значений и получить один прогноз на основе этих значений. Например, можно настроить лист Excel, веб-приложение или отчет служб Reporting Services так, чтобы они вызывали хранимую процедуру, и передавать в нее входные значения, введенные или выбранные пользователями в этих приложениях.

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

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

1. Создайте хранимую процедуру 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. Попробуйте выполнить ее, указав значения вручную.

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

   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
   

   Можно также использовать более короткую форму, поддерживаемую для параметров хранимой процедуры:

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

3. Результаты говорят о том, что вероятность получения чаевых в этих 10 поездках очень мала (ноль), так как все они являются поездками с одним пассажиром на сравнительно короткое расстояние.

Заключения

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

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

Работая с этой статьей, вы выполните следующие задачи:

Дополнительные сведения о языке R см. в статье Расширение R в SQL Server.