Поделиться через

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

  • Статья

Область применения: SQL Server 2017 (14.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure Azure 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 No Да
Управляемый экземпляр SQL Azure Да Да
База данных SQL Azure No Да
SQL Azure для пограничных вычислений Да Нет
Azure Synapse Analytics Да Нет

Модели ONNX

Модель должна быть в формате Open Neural Network Exchange (ONNX).

Модели RevoScale

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

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

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

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

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

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

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

Примеры

PREDICT с моделью ONNX

В этом примере показано, как использовать модель ONNX, хранимую в таблице dbo.models, для собственной оценки.

DECLARE @model VARBINARY(max) = (
        SELECT DATA
        FROM dbo.models
        WHERE id = 1
        );

WITH predict_input
AS (
    SELECT TOP (1000) [id]
        , CRIM
        , ZN
        , INDUS
        , CHAS
        , NOX
        , RM
        , AGE
        , DIS
        , RAD
        , TAX
        , PTRATIO
        , B
        , LSTAT
    FROM [dbo].[features]
    )
SELECT predict_input.id
    , p.variable1 AS MEDV
FROM PREDICT(MODEL = @model, DATA = predict_input, RUNTIME=ONNX) WITH (variable1 FLOAT) AS p;

Примечание.

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

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.

Следующие шаги