Assegnazione dei punteggi nativa tramite la funzione PREDICT T-SQL con Machine Learning in SQL

  • Articolo

Si applica a: SQL Server 2017 (14.x) e versioni successive Database SQL di AzureIstanza gestita di SQL di AzureAzure Synapse Analytics

Informazioni su come usare l'assegnazione dei punteggi nativa con la funzione PREDICT T-SQL per generare valori di stima per i nuovi input di dati near real-time. Per l'assegnazione dei punteggi nativa è necessario disporre di un modello già sottoposto a training.

La funzione PREDICT usa le funzionalità dell'estensione C++ native in Machine Learning in SQL. Questa metodologia assicura la velocità di elaborazione più rapida possibile per i carichi di lavoro di previsione e stima e supporta i modelli in formato ONNX (Open Neural Network Exchange) o i modelli sottoposti a training usando i pacchetti RevoScaleR e revoscalepy.

Funzionamento dell'assegnazione dei punteggi nativa

L'assegnazione dei punteggi nativa usa librerie in grado di leggere i modelli in ONNX o in un formato binario predefinito e genera punteggi per i nuovi input di dati forniti. Poiché il modello viene sottoposto a training, distribuito e archiviato, può essere usato per l'assegnazione dei punteggi senza dover chiamare l'interprete R o Python. Ciò significa che il sovraccarico delle interazioni di più processi viene ridotto e le prestazioni di stima risultano di conseguenza più veloci.

Per usare l'assegnazione dei punteggi nativa, chiamare la funzione PREDICT T-SQL e passare gli input richiesti seguenti:

  • Un modello compatibile basato su un modello e un algoritmo supportati.
  • Dati di input, in genere definiti come query T-SQL.

La funzione restituisce le stime per i dati di input, insieme alle eventuali colonne di dati di origine da passare.

Prerequisiti

PREDICT è disponibile in:

  • Tutte le edizioni di SQL Server 2017 e versioni successive in Windows e Linux
  • Istanza gestita di SQL di Azure
  • database SQL di Azure
  • SQL Edge di Azure
  • Azure Synapse Analytics

La funzione è abilitata per impostazione predefinita. Non è necessario installare R o Python, né abilitare funzionalità aggiuntive.

Modelli supportati

I formati di modello supportati dalla funzione PREDICT dipendono dalla piattaforma SQL in cui si esegue l'assegnazione dei punteggi nativa. Vedere la tabella seguente per informazioni sui formati di modello supportati in base alla piattaforma.

Piattaforma Formato di modello ONNX Formato di modello RevoScale
SQL Server No Sì
Istanza gestita di SQL di Azure Sì Sì
Database SQL di Microsoft Azure No Sì
SQL Edge di Azure Sì No
Azure Synapse Analytics Sì No

Modelli ONNX

Il modello deve essere in un formato ONNX (Open Neural Network Exchange).

Modelli RevoScale

Il training del modello deve essere eseguito in anticipo con uno degli algoritmi rx supportati elencati di seguito usando il pacchetto RevoScaleR o revoscalepy.

Serializzare il modello usando rxSerialize per R e rx_serialize_model per Python. Queste funzioni di serializzazione sono state ottimizzate per supportare l'assegnazione rapida dei punteggi.

Algoritmi RevoScale supportati

Gli algoritmi seguenti sono supportati in revoscalepy e RevoScaleR.

Se è necessario usare un algoritmo di MicrosoftML o microsoftml, usare l'assegnazione dei punteggi in tempo reale con sp_rxPredict.

I tipi di modelli non supportati includono i seguenti:

  • Modelli contenenti altre trasformazioni
  • Modelli che usano gli algoritmi rxGlm o rxNaiveBayes negli equivalenti RevoScaleR o revoscalepy
  • Modelli PMML
  • Modelli creati con altre librerie open source o di terze parti

Esempi

PREDICT con un modello ONNX

Questo esempio mostra come usare un modello ONNX archiviato nella tabella dbo.models per l'assegnazione dei punteggi 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;

Nota

Poiché le colonne e i valori restituiti da PREDICT possono variare in base al tipo di modello, è necessario definire lo schema dei dati restituiti usando una clausola WITH.

PREDICT con il modello RevoScale

In questo esempio viene creato un modello usando RevoScaleR in R e quindi viene chiamata la funzione di stima in tempo reale da T-SQL.

Passaggio 1: Preparare e salvare il modello

Eseguire il codice seguente per creare il database di esempio e le tabelle richieste.

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

Usare l'istruzione seguente per popolare la tabella dati con i dati del set di dati 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

Creare ora una tabella per archiviare i modelli.

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

Il codice seguente crea un modello basato sul set di dati iris e lo salva nella tabella denominata 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) ;

Nota

Assicurarsi di usare la funzione rxSerializeModel da RevoScaleR per salvare il modello. La funzione serialize R standard non è in grado di generare il formato richiesto.

Per visualizzare il modello archiviato in formato binario, è possibile eseguire un'istruzione come la seguente:

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

Passaggio 2. Eseguire PREDICT sul modello

Con la semplice istruzione PREDICT seguente si ottiene una classificazione dal modello di albero delle decisioni usando la funzione di assegnazione dei punteggi nativa. Consente di stimare le specie di iris in base agli attributi specificati, ovvero lunghezza e larghezza dei petali.

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 si ottiene l'errore "Si è verificato un errore durante l'esecuzione della funzione PREDICT. Il modello è danneggiato o non è valido", in genere significa che la query non ha restituito un modello. Verificare se il nome del modello è stato digitato correttamente o se la tabella models è vuota.

Nota

Poiché le colonne e i valori restituiti da PREDICT possono variare in base al tipo di modello, è necessario definire lo schema dei dati restituiti usando una clausola WITH.

Passaggi successivi