Машинное обучение в среде SQL-Server — Машинное обучение на vc.ru

Инструментарий 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 `