Инструментарий ML внутри SQL Server

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

Расшифровка терминов

SQL – язык структурных запросов, отправляемых в базу данных.

SQL Server – реляционная база данных от Microsoft.

Службы машинного обучения — это компонент SQL Server, который дает возможность выполнять скрипты на языках Python и R с данными.

Триггер в SQL — механизм реакции на изменение в состоянии базы данных.

Скрипты – небольшие программы, рассчитанные на узкий, чаще всего периодический круг задач.

R – язык программирования, созданный специально для обработки данных.

Причины, по которым выгодно использовать SQL Server для машинного обучения

Обсудим перечень доступных преимуществ, используя ML в SQL Server.

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

Ещё одной существенной причиной является безопасность, ведь если условный сервер базы данных находится в одном месте, а запросы на него попадают с другого места, данные можно перехватить. В случае если ядро СУБД и хранит данные и вызывает код, такую проблему возможно избежать.

Помимо прочего SQL Server обладает качественной поддержкой языка программирования R как в плане поставляемых библиотек, так и в плане производительности. По самым последним бенчмаркам, ядро базы данных способно вычислять около миллиона R прогнозов в секунду (ссылка).

Проверка работоспособности SQL Server

Для начала убедимся, что служба машинного обучения и расширения для R работают корректно. Для этого выполним следующий код.

EXEC sp_execute_external_script @language = N'R'
, @script = N'
OutputDataSet <- data.frame(installed.packages()[,c("Package", ...;'
WITH result sets((Package NVARCHAR(255)
, Version NVARCHAR(100)...));

Результат:



Кратко разберём SQL команду:

1.  EXEC sp_execute_external_script @language = N'R'

Тут определяем, что ядро должно обрабатывать язык R

2.  @script = N'OutputDataSet <- data.frame(installed...'

script это специальная переменная, которая может обрабатываться в последующем запросе, равняется результату выполнения команды R

3.  WITH result sets((Package NVARCHAR(255)..

Используется рекурсивная операция отображения содержания script в виде таблицы.

Пример использования ML в SQL Server (ссылка)

Создаем таблицу для хранения данных, которыми будем обучать модель:

CREATE TABLE dbo.MTCars(
hp int NOT NULL,
...
wt decimal(10, 3) NOT NULL,
am int NOT NULL);

Заносим в неё данные:

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

Результат:



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

CREATE PROCEDURE generate_GLM
... , @script = N'carsModel <- carsModel <- glm(... data = MTCarsData, ...;
        trained_model <- ...'
    , @input_data_1 = N'SELECT hp, wt, am FROM MTCars'
    , @input_data_1_name = N'MTCarsData'
    ...;

На этом этапе хотел бы акцентировать внимание на том, как принимает данные R на движке SQL Server: @input_data_1 — это столбцы таблицы, которые в коде представлены в виде переменной @input_data_1_name

Создаем таблицу под модель:

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

Сохраняем модель:

INSERT INTO GLM_models(model)
EXEC generate_GLM;

Результат:



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

Создаем таблицу, где будут хранится данные для анализа:

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

Заполняем случайными данными, входящими в нужный диапазон:

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)

Результат:



Прогнозируем результат для новых данных:

DECLARE @glmmodel varbinary(max) = ...
    ...    , @script = N'
            -- Применяем модель для всех строк
            '
   -- Подготавливаем данные для обработки;

Результат:



Чем ближе значение predicated_am к единице, тем больше шанс, что машина имеет механическую коробку передач.

Заключение

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

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

CREATE TRIGGER `add_car` AFTER INSERT ON `NewMTCars `

… применить процедуру прогнозирования.