Краткое руководство. Создание и оценка прогнозной модели в R с помощью машинного обучения SQL

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

Вы создадите и запустите две хранимые процедуры, выполняемые в SQL. В первой из них используется набор данных mtcars, входящий в состав R, на основе которого создается простая обобщенная линейная модель (ОЛМ), которая прогнозирует вероятность оснащения автомобиля механической КПП. Вторая процедура предназначена для оценки — она вызывает модель, созданную в первой процедуре, для вывода набора прогнозов на основе новых данных. Поместив код R в хранимую процедуру SQL, вы переносите операции в SQL, благодаря чему они могут многократно использоваться и вызываться другими хранимыми процедурами и клиентскими приложениями.

Выполнив это краткое руководство, вы узнаете, как делать следующее.

Необходимые компоненты

Для работы с этим кратким руководством необходимо следующее.

• Инструмент для выполнения SQL-запросов, содержащих сценарии R. В этом кратком руководстве используется Azure Data Studio.

Создание модели

Чтобы создать модель, вам необходимо сформировать исходные данные для обучения, создать модель и обучить ее с использованием этих данных, а затем сохранить эту модель в базе данных, где она будет использоваться для создания прогнозов на основе новых данных.

Создание исходных данных

1. Откройте Azure Data Studio, подключитесь к своему экземпляру и откройте новое окно запроса.

2. Создайте таблицу, чтобы сохранить данные для обучения.

   CREATE TABLE dbo.MTCars(
       mpg decimal(10, 1) NOT NULL,
       cyl int NOT NULL,
       disp decimal(10, 1) NOT NULL,
       hp int NOT NULL,
       drat decimal(10, 2) NOT NULL,
       wt decimal(10, 3) NOT NULL,
       qsec decimal(10, 2) NOT NULL,
       vs int NOT NULL,
       am int NOT NULL,
       gear int NOT NULL,
       carb int NOT NULL
   );
   

3. Вставьте данные из встроенного набора данных mtcars.

   INSERT INTO dbo.MTCars
   EXEC sp_execute_external_script @language = N'R'
       , @script = N'MTCars <- mtcars;'
       , @input_data_1 = N''
       , @output_data_1_name = N'MTCars';
   

   Совет

   Многие наборы данных, малые и большие, включаются в среду выполнения R. Чтобы получить список наборов данных, установленных с R, введите library(help="datasets") в командной строке R.

Создание и обучение модели

Данные о скорости автомобиля содержат два численных столбца — мощность в лошадиных силах (hp) и масса (wt). На основе этих данных вы создадите обобщенную линейную модель (ОЛМ), которая оценивает вероятность того, что автомобиль оснащен механической КПП.

Чтобы создать модель, необходимо определить формулу внутри кода R, а затем передать данные в качестве входных параметров.

DROP PROCEDURE IF EXISTS generate_GLM;
GO
CREATE PROCEDURE generate_GLM
AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'carsModel <- glm(formula = am ~ hp + wt, data = MTCarsData, family = binomial);
        trained_model <- data.frame(payload = as.raw(serialize(carsModel, connection=NULL)));'
    , @input_data_1 = N'SELECT hp, wt, am FROM MTCars'
    , @input_data_1_name = N'MTCarsData'
    , @output_data_1_name = N'trained_model'
    WITH RESULT SETS ((model VARBINARY(max)));
END;
GO

• Первый аргумент для функции glm — это параметр formula, где дистанция (am) является значением, зависимым от скорости (hp + wt).
• Входные данные хранятся в переменной MTCarsData, которая заполняется с помощью SQL-запроса. Если для входных данных не назначено определенное имя, имя переменной по умолчанию будет InputDataSet.

Сохранение модели в базе данных

Далее необходимо сохранить модель в базе данных, где ее можно будет использовать для прогнозирования или повторного обучения.

1. Создайте таблицу для хранения модели.

   Выходные данные пакета R, с помощью которого создается модель, обычно являются двоичным объектом. Это означает, что таблица, в которой хранится модель, должна содержать столбец типа varbinary(max).

   CREATE TABLE GLM_models (
       model_name varchar(30) not null default('default model') primary key,
       model varbinary(max) not null
   );
   

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

   INSERT INTO GLM_models(model)
   EXEC generate_GLM;
   

   Совет

   При выполнении этого кода во второй раз вы получите следующую ошибку: "Нарушение ограничения PRIMARY KEY... Невозможно вставить повторяющийся ключ в объект dbo.stopping_distance_models". Один из вариантов, который поможет избежать этой ошибки, — обновить имя для каждой новой модели. Например, можно изменить имя на нечто более описательное и включить тип модели, день создания и т. д.

   UPDATE GLM_models
   SET model_name = 'GLM_' + format(getdate(), 'yyyy.MM.HH.mm', 'en-gb')
   WHERE model_name = 'default model'
   

Оценка новых данных с использованием обученной модели

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

Создание таблицы с новыми данными

Для начала создайте таблицу с новыми данными.

CREATE TABLE dbo.NewMTCars(
	hp INT NOT NULL
	, wt DECIMAL(10,3) NOT NULL
	, am INT NULL
)
GO

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (110, 2.634)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (72, 3.435)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (220, 5.220)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (120, 2.800)
GO

Прогнозирование вероятности оснащения автомобиля механической КПП

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

1. Получение требуемой модели.
2. Получение новых входных данных.
3. Вызов функции прогнозирования R, совместимой с моделью.

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

DECLARE @glmmodel varbinary(max) = 
    (SELECT model FROM dbo.GLM_models WHERE model_name = 'default model');

EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'
            current_model <- unserialize(as.raw(glmmodel));
            new <- data.frame(NewMTCars);
            predicted.am <- predict(current_model, new, type = "response");
            str(predicted.am);
            OutputDataSet <- cbind(new, predicted.am);
            '
    , @input_data_1 = N'SELECT hp, wt FROM dbo.NewMTCars'
    , @input_data_1_name = N'NewMTCars'
    , @params = N'@glmmodel varbinary(max)'
    , @glmmodel = @glmmodel
WITH RESULT SETS ((new_hp INT, new_wt DECIMAL(10,3), predicted_am DECIMAL(10,3)));

Приведенный выше скрипт выполняет следующие действия:

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

• После извлечения модели из таблицы вызовите функцию unserialize для модели.

• Примените функцию predict с соответствующими аргументами к модели и предоставьте новые входные данные.

Результаты

Кроме того, вы можете использовать инструкцию PREDICT (Transact-SQL) для получения прогнозируемого значения или оценки на основе сохраненной модели.

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

Дополнительные сведения об учебниках по использованию R и машинного обучения SQL:

• Учебники по R