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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Платформа	Формат модели ONNX	Формат модели RevoScale
SQL Server	Нет	Да
Управляемый экземпляр SQL Azure	Да	Да
База данных SQL Azure	Нет	Да
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
• Модели, созданные с помощью других библиотек с открытым исходным кодом или библиотек сторонних разработчиков

Примеры

PREDICT с моделью 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
• Машинное обучение и ИИ с применением ONNX в SQL для пограничных вычислений
• Развертывание и создание прогнозов с помощью модели ONNX в SQL Azure для пограничных вычислений
• Оценка моделей машинного обучения с помощью PREDICT в Azure Synapse Analytics