Partager via

Scoring natif à l’aide de la fonction T-SQL PREDICT avec le Machine Learning SQL

  • Article

S’applique à : SQL Server 2017 (14.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Découvrez comment utiliser le scoring natif avec la fonction T-SQL PREDICT afin de générer des valeurs de prédiction pour de nouvelles entrées de données en quasi-temps réel. Le scoring natif implique de disposer d’un modèle déjà entraîné.

La fonction PREDICT utilise les fonctionnalités d’extension C++ natives du Machine Learning SQL. Cette méthodologie, qui offre la vitesse de traitement la plus rapide possible pour les charges de travail de prévision et de prédiction, prend en charge les modèles au format ONNX (Open Neural Network Exchange) et les modèles entraînés à l’aide des packages RevoScaleR et revoscalepy.

Fonctionnement du scoring natif

Le scoring natif utilise des bibliothèques permettant de lire des modèles au format ONNX ou dans un format binaire prédéfini et de générer des scores pour les nouvelles entrées de données fournies. Le modèle étant entraîné, déployé et stocké, vous pouvez l’utiliser pour le scoring sans avoir à appeler l’interpréteur R ou Python. La surcharge liée aux interactions de plusieurs processus s’en trouve donc réduite, ce qui accroît les performances de prédiction.

Pour utiliser le scoring natif, appelez la fonction T-SQL PREDICT et transmettez les entrées obligatoires suivantes :

  • Un modèle compatible basé sur un modèle et un algorithme pris en charge
  • Des données d’entrée, généralement définies sous forme de requête T-SQL

La fonction retourne des prédictions pour les données d’entrée ainsi que toutes les colonnes de données sources que vous souhaitez passer.

Prérequis

PREDICT est disponible sur :

  • Toutes les éditions de SQL Server 2017 et versions ultérieures sur Windows et Linux
  • Azure SQL Managed Instance
  • Azure SQL Database
  • Azure SQL Edge
  • Azure Synapse Analytics

La fonction est activée par défaut. Il n’est pas nécessaire d’installer R ou Python ni d’activer des fonctionnalités supplémentaires.

Modèles pris en charge

Les formats de modèle pris en charge par la fonction PREDICT dépendent de la plateforme SQL sur laquelle est effectué le scoring natif. Consultez le tableau ci-dessous pour connaître les formats de modèle pris en charge selon la plateforme.

Plateforme Format de modèle ONNX Format de modèle RevoScale
SQL Server Non Oui
Azure SQL Managed Instance Oui Oui
Azure SQL Database Non Oui
Azure SQL Edge Oui Non
Azure Synapse Analytics Oui Non

Modèles ONNX

Le modèle doit être au format ONNX (Open Neural Network Exchange).

Modèles RevoScale

Le modèle doit être entraîné à l’avance avec l’un des algorithmes rx pris en charge de la liste ci-dessous à l’aide du package RevoScaleR ourevoscalepy.

Sérialisez le modèle avec rxSerialize pour R et rx_serialize_model pour Python. Ces fonctions de sérialisation ont été optimisées pour prendre en charge le scoring rapide.

Algorithmes RevoScale pris en charge

Les algorithmes suivants sont pris en charge dans revoscalepy et RevoScaleR.

Si vous devez utiliser un algorithme de MicrosoftML ou microsoftml, optez pour le scoring en temps réel avec sp_rxPredict.

Parmi les types de modèles non pris en charge, citons les suivants :

  • Modèles contenant d’autres transformations
  • Modèles utilisant les algorithmes rxGlm ou rxNaiveBayes dans les équivalents RevoScaleR ou revoscalepy
  • Modèles PMML
  • Modèles créés à l’aide d’autres bibliothèques open source ou tierces

Exemples

PREDICT avec un modèle ONNX

Cet exemple montre comment utiliser un modèle ONNX stocké dans la table dbo.models pour le scoring natif.

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;

Notes

Les colonnes et les valeurs retournées par PREDICT pouvant varier selon le type de modèle, vous devez définir le schéma des données retournées à l’aide d’une clause WITH.

PREDICT avec un modèle RevoScale

Dans cet exemple, vous créez un modèle à l’aide de RevoScaleR en R, puis appelez la fonction de prédiction en temps réel à partir de T-SQL.

Étape 1. Préparer et enregistrer le modèle

Exécutez le code suivant pour créer l’exemple de base de données et les tables obligatoires.

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

Utilisez l’instruction suivante pour remplir la table de données avec les données du jeu de données 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

Créez à présent une table pour stocker les modèles.

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

Le code suivant crée un modèle basé sur le jeu de données iris et l’enregistre dans la table nommée 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) ;

Notes

Veillez à utiliser la fonction rxSerializeModel de RevoScaleR pour enregistrer le modèle. La fonction R serialize standard ne peut pas générer le format demandé.

Vous pouvez exécuter une instruction telle que la suivante pour afficher le modèle stocké au format binaire :

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

Étape 2. Exécuter PREDICT sur le modèle

L’instruction PREDICT simple suivante obtient une classification à partir du modèle d’arbre de décision à l’aide de la fonction de scoring natif. Elle prédit les espèces d’iris en fonction des attributs que vous fournissez (longueur et largeur des pétales).

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

Le message « Une erreur s’est produite lors de l’exécution de la fonction PREDICT. Le modèle est endommagé ou non valide. » signifie généralement que votre requête n’a pas retourné de modèle. Vérifiez que vous avez correctement tapé le nom du modèle ou que la table des modèles n’est pas vide.

Notes

Les colonnes et les valeurs retournées par PREDICT pouvant varier selon le type de modèle, vous devez définir le schéma des données retournées à l’aide d’une clause WITH.

Étapes suivantes