SQL Server сделал значительные прогресс в интеграции языков машинного обучения и статистического анализа в свой движок базы данных. Начиная с SQL Server 2016, Microsoft представила интеграцию языка R, а в SQL Server 2017 был добавлен Python. Эти языки позволяют пользователям применять аналитику непосредственно к данным, обеспечивая возможность получения предсказаний практически в режиме реального времени с использованием различных алгоритмов машинного обучения.

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

Метод 1: Построение модели машинного обучения непосредственно в SQL Server

Для начала нам необходимо обучить модель. Мы можем сделать это, создав модель машинного обучения непосредственно в SQL Server. Для этой демонстрации мы будем использовать набор данных об автомобилях, доступный из UC Irvine Machine Learning Repository. После очистки данных и загрузки их в таблицу, мы разделяем данные на обучающую и тестовую таблицы.

-- Выберите 10% строк в новую тестовую таблицу
SELECT TOP 10 PERCENT * 
INTO Automobile_test
FROM Automobile

-- Поместите оставшиеся 90% в таблицу для обучения
SELECT * 
INTO Automobile_train
FROM Automobile
EXCEPT
SELECT TOP 10 PERCENT * FROM Automobile

Затем мы создаем таблицу для хранения нашей обученной модели. Эта таблица будет содержать имя модели, версию и саму обученную модель, хранящуюся в виде VARBINARY(MAX).

CREATE TABLE models 
(
	model_name nvarchar(100) not null,
	model_version nvarchar(100) not null,
	model_object varbinary(max) not null
)

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

DECLARE @model VARBINARY(MAX)
EXEC sp_execute_external_script 
	@language = N'R', 
	@script = N'
		automobiles.linmod <- rxLinMod(price ~ wheel_base + length + width + height + curb_weight + engine_size + horsepower, data = automobiles)
		model <- rxSerializeModel(automobiles.linmod, realtimeScoringOnly = FALSE)',
	@input_data_1 = N'
		SELECT * 
		FROM Automobile_Train',
	@input_data_1_name = N'automobiles',
	@params = N'@model varbinary(max) OUTPUT',
	@model = @model OUTPUT

INSERT models
(
	model_name, 
	model_version, 
	model_object
)
VALUES
(
	'automobiles.linmod',
	'v1', 
	@model
)

Метод 2: Предсказание с использованием sp_execute_external_script

Первый метод выполнения предсказаний в базе данных – использование того же метода, который мы использовали для обучения модели машинного обучения, sp_execute_external_script. Этот метод обеспечивает гибкость и не зависит от конкретных алгоритмов или функций. Мы можем использовать как R, так и Python, и даже импортировать библиотеки, такие как ggplot или dplyr. Однако для его использования необходимо включить функцию R в базе данных во время установки SQL Server.

DECLARE @lin_model_raw VARBINARY(MAX) = (SELECT model_object FROM models WHERE model_name = 'automobiles.linmod')

EXECUTE sp_execute_external_script
	@language = N'R',
	@script = N'
		model = rxUnserializeModel(lin_model);
		automobiles_prediction = rxPredict(model, automobiles_test)
		automobiles_pred_results <- cbind(automobiles_test, automobiles_prediction)',
	@input_data_1 = N'
		SELECT
			wheel_base,
			length,
			width,
			height,
			curb_weight,
			engine_size,
			horsepower,
			price 
		FROM Automobile_Test',
	@input_data_1_name = N'automobiles_test',
	@output_data_1_name = N'automobiles_pred_results',
	@params = N'@lin_model varbinary(max)',
	@lin_model = @lin_model_raw
WITH RESULT SETS (("wheel_base" FLOAT, "length" FLOAT, "width" FLOAT, "height" FLOAT, "curb_weight" FLOAT, "engine_size" FLOAT, "horsepower" FLOAT, "price" FLOAT, "predicted_price" FLOAT))

Метод 3: Предсказание с использованием sp_rxPredict

Второй метод выполнения предсказаний в базе данных – использование sp_rxPredict, библиотеки на основе CLR, оптимизированной для быстрых предсказаний в режиме реального времени. Для этого метода требуется включение интеграции CLR и отметка базы данных как доверенной. Он поддерживает только алгоритмы RevoScaleR и MicrosoftML и предполагает наличие предварительно обученной модели в SQL Server.

DECLARE @lin_model_raw VARBINARY(MAX) = (SELECT model_object FROM models WHERE model_name = 'automobiles.linmod')

EXEC sp_rxPredict 
	@model = @lin_model_raw,
	@inputData = N'
		SELECT
			wheel_base,
			length,
			width,
			height,
			curb_weight,
			engine_size,
			horsepower,
			price 
		FROM Automobile_Test'

Метод 4: Предсказание с использованием PREDICT

Третий метод выполнения предсказаний в базе данных – использование функции PREDICT, введенной в SQL Server 2017. Этот метод позволяет легко интегрировать его в синтаксис T-SQL и не требует включения функции машинного обучения в базе данных. Однако он поддерживает только алгоритмы RevoScaleR и доступен только в SQL Server 2017.

DECLARE @lin_model_raw VARBINARY(MAX) = (SELECT model_object FROM models WHERE model_name = 'automobiles.linmod')

SELECT 
	a.*, 
	p.*
FROM PREDICT(MODEL = @lin_model_raw, DATA = dbo.Automobile_test as a)
WITH("price_Pred" float) as p;

Вывод

В заключение, мы рассмотрели три метода, доступных в SQL Server 2016 и 2017 для выполнения предсказаний в базе данных. Каждый метод имеет свои преимущества и недостатки:

• sp_execute_external_script: Поддерживает как R, так и Python, обеспечивает гибкость, но требует включения функции R в базе данных