Démarrage rapide : Créer un modèle prédictif doté d’un score en R avec SQL Server Machine Learning ServicesQuickstart: Create and score a predictive model in R with SQL Server Machine Learning Services

S’APPLIQUE À : ouiSQL Server nonAzure SQL Database nonAzure Synapse Analytics (SQL DW) nonParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Dans ce guide de démarrage rapide, vous allez créer et effectuer l’apprentissage d’un modèle prédictif à l’aide de R, enregistrer le modèle dans une table de votre instance de SQL Server, puis utiliser le modèle pour prédire des valeurs à partir de nouvelles données avec SQL Server Machine Learning Services.In this quickstart, you'll create and train a predictive model using R, save the model to a table in your SQL Server instance, then use the model to predict values from new data using SQL Server Machine Learning Services.

Vous allez créer et exécuter deux procédures stockées qui s’exécutent dans SQL.You'll create and execute two stored procedures running in SQL. La première utilise le jeu de données mtcars inclus avec R et génère un modèle linéaire généralisé simple (GLM) qui prédit la probabilité qu’un véhicule ait été équipé d’une transmission manuelle.The first one uses the mtcars dataset included with R and generates a simple generalized linear model (GLM) that predicts the probability that a vehicle has been fitted with a manual transmission. La deuxième procédure concerne le scoring : elle appelle le modèle généré dans la première procédure pour générer un ensemble de prédictions basées sur de nouvelles données.The second procedure is for scoring - it calls the model generated in the first procedure to output a set of predictions based on new data. En plaçant le code R dans une procédure stockée SQL, les opérations sont contenues dans SQL et sont réutilisables. Elles peuvent alors être appelées par d’autres procédures stockées et applications clientes.By placing R code in a SQL stored procedure, operations are contained in SQL, are reusable, and can be called by other stored procedures and client applications.

Conseil

Si vous avez besoin de rafraîchir vos connaissances sur les modèles linéaires, consultez le tutoriel suivant, qui décrit le processus d’ajustement des modèles linéaire à l’aide de rxLInMod : Fitting Linear Models (Ajustement des modèles linéaires)If you need a refresher on linear models, try this tutorial which describes the process of fitting a model using rxLinMod: Fitting Linear Models

En suivant ce guide de démarrage rapide, vous apprendrez :By completing this quickstart, you'll learn:

  • Comment incorporer du code R dans une procédure stockéeHow to embed R code in a stored procedure
  • Comment transmettre des entrées dans votre code via des entrées sur la procédure stockéeHow to pass inputs to your code through inputs on the stored procedure
  • Comment les procédures stockées sont utilisées pour rendre les modèles opérationnelsHow stored procedures are used to operationalize models

Conditions préalables requisesPrerequisites

  • Ce démarrage rapide nécessite l’accès à une instance de SQL Server avec SQL Server Machine Learning Services, ainsi que l’installation du langage R.This quickstart requires access to an instance of SQL Server with SQL Server Machine Learning Services with the R language installed.

    Votre instance SQL Server peut se trouver sur une machine virtuelle Azure ou être locale.Your SQL Server instance can be in an Azure virtual machine or on-premises. N’oubliez pas que la fonctionnalité de script externe est désactivée par défaut. Par conséquent, vous devrez peut-être activer les scripts externes et vérifier que le service SQL Server Launchpad est en cours d’exécution avant de commencer.Just be aware that the external scripting feature is disabled by default, so you might need to enable external scripting and verify that SQL Server Launchpad service is running before you start.

  • Vous aurez également besoin d’un outil pour exécuter des requêtes SQL qui contiennent des scripts R.You also need a tool for running SQL queries that contain R scripts. Vous pouvez exécuter ces scripts à l’aide de n’importe quel outil de gestion de base de données ou de requête, à condition qu’il puisse se connecter à une instance de SQL Server et exécuter une requête T-SQL ou une procédure stockée.You can run these scripts using any database management or query tool, as long as it can connect to a SQL Server instance, and run a T-SQL query or stored procedure. Ce guide de démarrage rapide utilise SQL Server Management Studio (SSMS).This quickstart uses SQL Server Management Studio (SSMS).

Créer le modèleCreate the model

Pour créer le modèle, vous allez créer des données sources pour l’apprentissage, créer le modèle et effectuer son apprentissage à l’aide des données, puis stocker le modèle dans une base de données SQL où il peut être utilisé pour générer des prédictions avec de nouvelles données.To create the model, you'll create source data for training, create the model and train it using the data, then store the model in a SQL database where it can be used to generate predictions with new data.

Créer la source de donnéesCreate the source data

  1. Ouvrez SSMS, connectez-vous à votre instance SQL Server et ouvrez une nouvelle fenêtre de requête.Open SSMS, connect to your SQL Server instance, and open a new query window.

  2. Créez une table pour enregistrer les données d’apprentissage.Create a table to save the training data.

    CREATE TABLE dbo.MTCars(
        mpg decimal(10, 1) NOT NULL,
        cyl int NOT NULL,
        disp decimal(10, 1) NOT NULL,
        hp int NOT NULL,
        drat decimal(10, 2) NOT NULL,
        wt decimal(10, 3) NOT NULL,
        qsec decimal(10, 2) NOT NULL,
        vs int NOT NULL,
        am int NOT NULL,
        gear int NOT NULL,
        carb int NOT NULL
    );
    
  3. Insérez les données à partir du jeu de données intégré mtcars.Insert the data from the built-in dataset mtcars.

    INSERT INTO dbo.MTCars
    EXEC sp_execute_external_script @language = N'R'
        , @script = N'MTCars <- mtcars;'
        , @input_data_1 = N''
        , @output_data_1_name = N'MTCars';
    

    Conseil

    Le runtime R contient de nombreux datasets de tailles diverses.Many datasets, small and large, are included with the R runtime. Pour obtenir la liste des datasets installées avec R, tapez library(help="datasets") à partir d’une invite de commandes R.To get a list of datasets installed with R, type library(help="datasets") from an R command prompt.

Créer et effectuer l’apprentissage du modèleCreate and train the model

Les données de vitesse de voiture contiennent deux colonnes, toutes deux numériques, puissance (hp) et poids (wt).The car speed data contains two columns, both numeric: horsepower (hp) and weight (wt). À partir de ces données, vous allez créer un modèle GLM qui estime la probabilité qu’un véhicule ait été équipé d’une transmission manuelle.From this data, you'll create a generalized linear model (GLM) that estimates the probability that a vehicle has been fitted with a manual transmission.

Pour créer le modèle, vous devez définir la formule à l’intérieur de votre code R et passer les données en tant que paramètre d’entrée.To build the model, you define the formula inside your R code, and pass the data as an input parameter.

DROP PROCEDURE IF EXISTS generate_GLM;
GO
CREATE PROCEDURE generate_GLM
AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'carsModel <- glm(formula = am ~ hp + wt, data = MTCarsData, family = binomial);
        trained_model <- data.frame(payload = as.raw(serialize(carsModel, connection=NULL)));'
    , @input_data_1 = N'SELECT hp, wt, am FROM MTCars'
    , @input_data_1_name = N'MTCarsData'
    , @output_data_1_name = N'trained_model'
    WITH RESULT SETS ((model VARBINARY(max)));
END;
GO
  • Le premier argument de glm est le paramètre formula qui définit am comme étant dépendante de hp + wt.The first argument to glm is the formula parameter, which defines am as dependent on hp + wt.
  • Les données d’entrée sont stockées dans la variable MTCarsData, qui est remplie par la requête SQL.The input data is stored in the variable MTCarsData, which is populated by the SQL query. Si vous n’attribuez pas de nom spécifique à vos données d’entrée, le nom par défaut de la variable est InputDataSet.If you don't assign a specific name to your input data, the default variable name would be InputDataSet.

Stocker le modèle dans la base de données SQLStore the model in the SQL database

Ensuite, stockez le modèle dans une base de données SQL afin de pouvoir l’utiliser à des fins de prédiction ou de reformation.Next, store the model in a SQL database so you can use it for prediction or retrain it.

  1. Créez une table pour stocker le modèle.Create a table to store the model.

    La sortie d’un package R qui crée un modèle est généralement un objet binaire.The output of an R package that creates a model is usually a binary object. Par conséquent, la table dans laquelle vous stockez le modèle doit contenir une colonne de type varbinary(max) .Therefore, the table where you store the model must provide a column of varbinary(max) type.

    CREATE TABLE GLM_models (
        model_name varchar(30) not null default('default model') primary key,
        model varbinary(max) not null
    );
    
  2. Exécutez l’instruction Transact-SQL suivante de façon à appeler la procédure stockée, à générer le modèle, puis à l’enregistrer dans la table créée.Run the following Transact-SQL statement to call the stored procedure, generate the model, and save it to the table you created.

    INSERT INTO GLM_models(model)
    EXEC generate_GLM;
    

    Conseil

    Si vous exécutez ce code une deuxième fois, vous obtenez cette erreur : Violation de la contrainte CLÉ PRIMAIRE... Impossible d’insérer une clé en double dans l’objet dbo.stopping_distance_models.If you run this code a second time, you get this error: "Violation of PRIMARY KEY constraint...Cannot insert duplicate key in object dbo.stopping_distance_models". Vous pouvez éviter cette erreur en mettant à jour le nom de chaque nouveau modèle.One option for avoiding this error is to update the name for each new model. Par exemple, vous pouvez opter pour un nom plus descriptif en indiquant par exemple le type du modèle, le jour de sa création, etc.For example, you could change the name to something more descriptive, and include the model type, the day you created it, and so forth.

    UPDATE GLM_models
    SET model_name = 'GLM_' + format(getdate(), 'yyyy.MM.HH.mm', 'en-gb')
    WHERE model_name = 'default model'
    

Noter les nouvelles données à l’aide du modèle forméScore new data using the trained model

La notation est un terme utilisé dans la science des données pour signifier la génération de prédictions, de probabilités ou d’autres valeurs en fonction de nouvelles données introduites dans un modèle formé.Scoring is a term used in data science to mean generating predictions, probabilities, or other values based on new data fed into a trained model. Vous utiliserez le modèle que vous avez créé dans la section précédente pour noter les prédictions en fonction des nouvelles données.You'll use the model you created in the previous section to score predictions against new data.

Créer une table de nouvelles donnéesCreate a table of new data

Tout d’abord, créez une table avec de nouvelles données.First, create a table with new data.

CREATE TABLE dbo.NewMTCars(
    hp INT NOT NULL
    , wt DECIMAL(10,3) NOT NULL
    , am INT NULL
)
GO

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (110, 2.634)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (72, 3.435)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (220, 5.220)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (120, 2.800)
GO

Prédire la transmission manuellePredict manual transmission

Pour générer des prédictions basées sur votre modèle, vous devez écrire un script SQL qui effectue les opérations suivantes :To get predictions based on your model, write a SQL script that does the following:

  1. Obtenir le modèle vouluGets the model you want
  2. Obtenir les nouvelles données d’entréeGets the new input data
  3. Appeler une fonction de prédiction R compatible avec ce modèleCalls an R prediction function that is compatible with that model

Vous utilisez peut-être une table qui contient plusieurs modèles R, créés avec des paramètres ou algorithmes différents, ou formés à partir de plusieurs sous-ensembles de données.Over time, the table might contain multiple R models, all built using different parameters or algorithms, or trained on different subsets of data. Dans cet exemple, nous allons utiliser le modèle nommé default model.In this example, we'll use the model named default model.

DECLARE @glmmodel varbinary(max) = 
    (SELECT model FROM dbo.GLM_models WHERE model_name = 'default model');

EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'
            current_model <- unserialize(as.raw(glmmodel));
            new <- data.frame(NewMTCars);
            predicted.am <- predict(current_model, new, type = "response");
            str(predicted.am);
            OutputDataSet <- cbind(new, predicted.am);
            '
    , @input_data_1 = N'SELECT hp, wt FROM dbo.NewMTCars'
    , @input_data_1_name = N'NewMTCars'
    , @params = N'@glmmodel varbinary(max)'
    , @glmmodel = @glmmodel
WITH RESULT SETS ((new_hp INT, new_wt DECIMAL(10,3), predicted_am DECIMAL(10,3)));

Le script ci-dessus effectue les étapes suivantes :The script above performs the following steps:

  • Utilisez une instruction SELECT pour obtenir un seul modèle de la table à passer comme paramètre d’entrée.Use a SELECT statement to get a single model from the table, and pass it as an input parameter.

  • Après avoir récupéré le modèle de la table, appelez la fonction unserialize sur ce modèle.After retrieving the model from the table, call the unserialize function on the model.

  • Appliquez la fonction predict avec les arguments appropriés au modèle et fournissez les nouvelles données d’entrée.Apply the predict function with appropriate arguments to the model, and provide the new input data.

Notes

Dans l’exemple, la fonction str est ajoutée pendant le test pour vérifier le schéma de données renvoyées à partir de R. Vous pourrez supprimer l’instruction ultérieurement si vous le souhaitez.In the example, the str function is added during the testing phase, to check the schema of data being returned from R. You can remove the statement later.

Les noms de colonne utilisés dans le script R ne sont pas nécessairement transmis à la sortie de la procédure stockée.The column names used in the R script are not necessarily passed to the stored procedure output. Ici, la clause WITH RESULTs est utilisée pour définir de nouveaux noms de colonnes.Here the WITH RESULTS clause is used to define some new column names.

RésultatsResults

Jeu de résultats pour la probabilité de prédiction d’une transmission manuelle

Il est également possible d’utiliser l’instruction PREDICT (Transact-SQL) pour générer une valeur prédite ou un score basé sur un modèle stocké.It's also possible to use the PREDICT (Transact-SQL) statement to generate a predicted value or score based on a stored model.

Étapes suivantesNext steps

Pour plus d’informations sur SQL Server Machine Learning Services, consultez :For more information on SQL Server Machine Learning Services, see: