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

S’applique à : SQL Server 2017 (14.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceAzure 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 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.

• Algorithmes revoscalepy

  • rx_lin_mod
  • rx_logit
  • rx_btrees
  • rx_dtree
  • rx_dforest

• Algorithmes RevoScaleR

  • rxLinMod
  • rxLogit
  • rxBTrees
  • rxDtree
  • rxDForest

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 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

• Fonction T-SQL PREDICT
• Documentation sur SQL Machine Learning
• Machine Learning et IA avec ONNX dans SQL Edge
• Déploiement et prédictions avec un modèle ONNX dans Azure SQL Edge
• Scorer des modèles Machine Learning avec PREDICT dans Azure Synapse Analytics