Pontuação nativa usando a função T-SQL PREDICT com o machine learning do SQL

Artigo
05/30/2023

Aplica-se a: SQL Server 2017 (14.x) e posteriores Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics

Saiba como usar a pontuação nativa com a função PREDICT T-SQL a fim de gerar valores de previsão para novas entradas de dados quase em tempo real. A pontuação nativa exige que você tenha um modelo já treinado.

A função PREDICT usa as funcionalidades de extensão nativas do C++ no machine learning do SQL. Essa metodologia oferece a velocidade de processamento mais rápida possível de cargas de trabalho de previsão e modelos de suporte no formato ONNX (Open Neural Network Exchange) ou modelos treinados por meio dos pacotes RevoScaleR e revoscalepy.

Como funciona a pontuação nativa

A pontuação nativa usa bibliotecas que podem ler modelos no ONNX ou em um formato binário predefinido e gerar pontuações para novas entradas de dados fornecidas. Como o modelo é treinado, implantado e armazenado, ele pode ser usado para pontuação sem precisar chamar o interpretador do R ou do Python. Isso significa que a sobrecarga de várias interações de processo é reduzida, resultando em um desempenho de previsão mais rápido.

Para usar a pontuação nativa, chame a função PREDICT T-SQL e transmita as seguintes entradas necessárias:

Um modelo compatível baseado em um modelo e um algoritmo compatíveis.
Dados de entrada, normalmente definidos como uma consulta T-SQL.

A função retorna previsões para os dados de entrada, junto com as colunas de dados de origem que você deseja passar.

Pré-requisitos

A função PREDICT está disponível em:

Todas as edições do SQL Server 2017 e posterior no Windows e no Linux
Instância Gerenciada do Azure SQL
Banco de Dados SQL do Azure
SQL do Azure no Edge
Azure Synapse Analytics

Por padrão, a função está habilitada. Você não precisa instalar o R nem o Python, muito menos habilitar recursos adicionais.

Modelos com suporte

Os formatos de modelo compatíveis com a função PREDICT dependem da plataforma SQL na qual a pontuação nativa é executada. Confira a tabela abaixo para ver quais formatos de modelo são compatíveis com qual plataforma.

Plataforma	Formato de modelo ONNX	Formato de modelo RevoScale
SQL Server	Não	Sim
Instância Gerenciada do Azure SQL	Sim	Sim
Banco de Dados SQL do Azure	Não	Sim
SQL do Azure no Edge	Sim	No
Azure Synapse Analytics	Sim	Não

Modelos ONNX

O modelo precisa estar em um formato de modelo ONNX (Open Neural Network Exchange).

Modelos do RevoScale

O modelo precisa ser treinado com antecedência usando um dos algoritmos rx compatíveis listados abaixo por meio do pacote RevoScaleR ou revoscalepy.

Serialize o modelo usando rxSerialize para o R e rx_serialize_model para o Python. Essas funções de serialização foram otimizadas para dar suporte à pontuação rápida.

Algoritmos compatíveis do RevoScale

Os algoritmos a seguir são compatíveis com o revoscalepy e o RevoScaleR.

Algoritmos do revoscalepy
- rx_lin_mod
- rx_logit
- rx_btrees
- rx_dtree
- rx_dforest
Algoritmos do RevoScaleR
- rxLinMod
- rxLogit
- rxBTrees
- rxDtree
- rxDForest

Caso você precise usar um algoritmo do MicrosoftML ou do microsoftml, use a pontuação em tempo real com sp_rxPredict.

Os tipos de modelos sem suporte incluem os seguintes tipos:

Modelos que contêm outras transformações
Modelos que usam os algoritmos rxGlm ou rxNaiveBayes em equivalentes do RevoScaleR ou do revoscalepy
Modelos do PMML
Modelos criados com outras bibliotecas de software livre ou de terceiros

Exemplos

PREDICT com um modelo ONNX

Este exemplo mostra como usar um modelo ONNX armazenado na tabela dbo.models para pontuação nativa.

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;

Observação

Como as colunas e os valores retornados por PREDICT podem variar de acordo com o tipo de modelo, é necessário definir o esquema dos dados retornados usando uma cláusula WITH.

PREDICT com o modelo RevoScale

Neste exemplo, você criará um modelo usando o RevoScaleR no R e chamará a função de previsão em tempo real no T-SQL.

Etapa 1. Preparar e salvar o modelo

Execute o código a seguir para criar o banco de dados de exemplo e as tabelas necessárias.

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

Use a instrução a seguir para popular a tabela de dados com os dados do conjunto de dados 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

Agora, crie uma tabela para armazenar os modelos.

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

O código a seguir cria um modelo baseado no conjunto de dados iris e o salva na tabela chamada 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) ;

Observação

Lembre-se de usar a função rxSerializeModel do RevoScaleR para salvar o modelo. A função serialize padrão do R não pode gerar o formato necessário.

Execute uma instrução como a seguinte para exibir o modelo armazenado em formato binário:

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

Etapa 2. Executar PREDICT no modelo

A instrução PREDICT simples a seguir obtém uma classificação do modelo de árvore de decisão usando a função de pontuação nativa. Ele prevê as espécies de íris com base nos atributos fornecidos, tamanho e largura da pétala.

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

Se você receber o erro "Erro durante a execução da função PREDICT. O modelo está corrompido ou é inválido", isso geralmente indicará que a consulta não retornou um modelo. Verifique se você digitou o nome do modelo corretamente ou se a tabela de modelos está vazia.

Observação

Como as colunas e os valores retornados por PREDICT podem variar de acordo com o tipo de modelo, é necessário definir o esquema dos dados retornados usando uma cláusula WITH.

Compartilhar via

Pontuação nativa usando a função T-SQL PREDICT com o machine learning do SQL

Como funciona a pontuação nativa

Pré-requisitos

Modelos com suporte

Modelos ONNX

Modelos do RevoScale

Algoritmos compatíveis do RevoScale

Exemplos

PREDICT com um modelo ONNX

PREDICT com o modelo RevoScale

Etapa 1. Preparar e salvar o modelo

Etapa 2. Executar PREDICT no modelo

Próximas etapas

Comentários

Recursos adicionais