Собственная оценка с использованием функции PREDICT T-SQL с помощью машинного обучения SQL

Применимо к: SQL Server 2017 (14.x) и более поздним версиям Управляемый экземпляр SQL AzureAzure Synapse Analytics

Узнайте, как использовать собственную оценку с функцией PREDICT T-SQL для создания прогнозных значений для новых входных данных практически в реальном времени. Для собственной оценки требуется уже обученная модель.

Функция PREDICT использует собственные возможности расширения C++ в машинном обучении SQL. Эта методология обеспечивает максимально быструю скорость обработки рабочих нагрузок для прогнозирования и поддержки моделей в формате Open Neural Network Exchange (ONNX) (только для Azure Synapse Analytics) или для моделей, обученных с использованием пакетов RevoScaleR и revoscalepy.

Как работает встроенная система оценивания

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

Чтобы использовать собственную оценку, вызовите функцию PREDICT T-SQL и передайте следующие обязательные входные данные.

• Совместимая модель, основанная на поддерживаемой модели и алгоритме.
• Входные данные, обычно определяемые в виде запроса T-SQL.

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

Предварительные условия

Функция PREDICT доступна в следующих версиях.

• Все выпуски SQL Server 2017 и более поздних версий в Windows и Linux
• Управляемый экземпляр SQL Azure
• Azure Synapse Analytics

Функция включена по умолчанию. Вам не нужно устанавливать R или Python или включать дополнительные функции.

Поддерживаемые модели

Форматы моделей, которые поддерживает функция PREDICT, зависят от SQL-платформы, на которой выполняется встроенная оценка. См. таблицу ниже, чтобы узнать, какие форматы моделей поддерживаются на той или иной платформе.

Платформа	Формат модели ONNX	Формат модели RevoScale
SQL Server	Нет	Да
Управляемый экземпляр SQL Azure	Нет	Да
Azure Synapse Analytics	Да	Нет

Модели RevoScale

Модель должна быть заранее обучена с помощью одного из поддерживаемых алгоритмов rx, указанных ниже, с использованием пакетов RevoScaleR или revoscalepy.

Сериализуйте модель с помощью rxSerialize для R или rx_serialize_model для Python. Эти функции сериализации оптимизированы для поддержки быстрой оценки.

Поддерживаемые алгоритмы RevoScale

В revoscalepy и RevoScaleR поддерживаются следующие алгоритмы.

• Алгоритмы Revoscalepy

  • rx_lin_mod
  • rx_logit
  • rx_btrees
  • rx_dtree
  • rx_dforest

• Алгоритмы RevoScaleR

  • rxLinMod
  • rxLogit
  • rxBTrees
  • rxDtree
  • rxDForest

Если вам нужно использовать алгоритм из MicrosoftML или microsoftml, используйте оценку в режиме реального времени с sp_rxPredict.

Неподдерживаемые типы моделей включают следующие:

• Модели, содержащие другие преобразования
• Модели, использующие алгоритмы rxGlm или rxNaiveBayes в эквивалентах RevoScaleR или revoscalepy
• Модели PMML
• Модели, созданные с помощью других библиотек с открытым исходным кодом или библиотек сторонних разработчиков

Примеры

ПРЕДСКАЗАТЬ с моделью RevoScale

В этом примере создается модель с помощью RevoScaleR в R, а затем вызывается функция прогнозирования в реальном времени из T-SQL.

Шаг 1. Подготовка и сохранение модели

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

CREATE DATABASE NativeScoringTest;
GO
USE NativeScoringTest;
GO
DROP TABLE IF EXISTS iris_rx_data;
GO
CREATE TABLE iris_rx_data (
    "Sepal.Length" float not null, "Sepal.Width" float not null
  , "Petal.Length" float not null, "Petal.Width" float not null
  , "Species" varchar(100) null
);
GO

Используйте следующую инструкцию, чтобы заполнить таблицу данных данными из набора данных iris.

INSERT INTO iris_rx_data ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width" , "Species")
EXECUTE sp_execute_external_script
  @language = N'R'
  , @script = N'iris_data <- iris;'
  , @input_data_1 = N''
  , @output_data_1_name = N'iris_data';
GO

Теперь создайте таблицу для хранения моделей.

DROP TABLE IF EXISTS ml_models;
GO
CREATE TABLE ml_models ( model_name nvarchar(100) not null primary key
  , model_version nvarchar(100) not null
  , native_model_object varbinary(max) not null);
GO

Следующий код создает модель на основе набора данных iris и сохраняет его в таблицу models.

DECLARE @model varbinary(max);
EXECUTE sp_execute_external_script
  @language = N'R'
  , @script = N'
    iris.sub <- c(sample(1:50, 25), sample(51:100, 25), sample(101:150, 25))
    iris.dtree <- rxDTree(Species ~ Sepal.Length + Sepal.Width + Petal.Length + Petal.Width, data = iris[iris.sub, ])
    model <- rxSerializeModel(iris.dtree, realtimeScoringOnly = TRUE)
    '
  , @params = N'@model varbinary(max) OUTPUT'
  , @model = @model OUTPUT
  INSERT [dbo].[ml_models]([model_name], [model_version], [native_model_object])
  VALUES('iris.dtree','v1', @model) ;

Примечание.

Обязательно используйте функцию rxSerializeModel из RevoScaleR для сохранения модели. Стандартная функция R serialize не может создать требуемый формат.

Для просмотра сохраненной модели в двоичном формате можно выполнить следующую инструкцию:

SELECT *, datalength(native_model_object)/1024. as model_size_kb
FROM ml_models;

Шаг 2. Выполнение инструкции PREDICT для модели

Следующее простое утверждение PREDICT получает классификацию из модели дерева решений с помощью встроенной функции оценки. Она прогнозирует виды ирисов на основе указанных атрибутов: длины и ширины лепестка.

DECLARE @model varbinary(max) = (
  SELECT native_model_object
  FROM ml_models
  WHERE model_name = 'iris.dtree'
  AND model_version = 'v1');
SELECT d.*, p.*
  FROM PREDICT(MODEL = @model, DATA = dbo.iris_rx_data as d)
  WITH(setosa_Pred float, versicolor_Pred float, virginica_Pred float) as p;
go

Если вы получили сообщение об ошибке "Ошибка при выполнении функции PREDICT. "модель повреждена или является недопустимой", обычно это означает, что запрос не вернул модель. Проверьте, правильно ли указано название модели и пуста ли таблица модели.

Примечание.

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

Связанный контент

• Функция PREDICT T-SQL
• Документация по машинному обучению SQL\
• Оценка моделей машинного обучения с помощью PREDICT в Azure Synapse Analytics