Esercitazione: distribuire un modello predittivo in R con Machine Learning in SQL

Si applica a: SQL Server 2016 (13.x) e versioni successive Istanza gestita di SQL di Azure

Nell'ultima parte di questa serie di esercitazioni in quattro parti si distribuirà un modello di Machine Learning, sviluppato in R, in Machine Learning Services per SQL Server oppure in cluster Big Data.

In questo articolo si apprenderà come:

• Creare una stored procedure che genera il modello di Machine Learning
• Archiviare il modello in una tabella del database
• Creare una stored procedure che esegue stime usando il modello
• Eseguire il modello con nuovi dati

Nella prima parte si è appreso come ripristinare il database di esempio.

Nella seconda parte si è appreso come importare un database di esempio e quindi preparare i dati da usare per il training di un modello predittivo in R.

Nella terza parte si è appreso come creare ed eseguire il training di più modelli di Machine Learning in R e quindi scegliere quello più accurato.

Prerequisiti

Nella quarta parte di questa esercitazione si presuppone che siano stati soddisfatti i prerequisiti della prima parte e che siano stati completati i passaggi descritti nella seconda e nella terza parte.

Creare una stored procedure che genera il modello

Nella terza parte di questa serie di esercitazioni, si è scelto come modello più accurato un albero delle decisioni (dtree). Adesso, usando gli script R sviluppati, creare una stored procedure (generate_rental_model) per il training e la generazione del modello dtree usando rpart del pacchetto R.

Eseguire i seguenti comandi in Azure Data Studio.

USE [TutorialDB]
DROP PROCEDURE IF EXISTS generate_rental_model;
GO
CREATE PROCEDURE generate_rental_model (@trained_model VARBINARY(max) OUTPUT)
AS
BEGIN
    EXECUTE sp_execute_external_script @language = N'R'
        , @script = N'
rental_train_data$Month   <- factor(rental_train_data$Month);
rental_train_data$Day     <- factor(rental_train_data$Day);
rental_train_data$Holiday <- factor(rental_train_data$Holiday);
rental_train_data$Snow    <- factor(rental_train_data$Snow);
rental_train_data$WeekDay <- factor(rental_train_data$WeekDay);

#Create a dtree model and train it using the training data set
library(rpart);
model_dtree <- rpart(RentalCount ~ Month + Day + WeekDay + Snow + Holiday, data = rental_train_data);
#Serialize the model before saving it to the database table
trained_model <- as.raw(serialize(model_dtree, connection=NULL));
'
        , @input_data_1 = N'
            SELECT RentalCount
                 , Year
                 , Month
                 , Day
                 , WeekDay
                 , Snow
                 , Holiday
            FROM dbo.rental_data
            WHERE Year < 2015
            '
        , @input_data_1_name = N'rental_train_data'
        , @params = N'@trained_model varbinary(max) OUTPUT'
        , @trained_model = @trained_model OUTPUT;
END;
GO

Archiviare il modello in una tabella del database

Creare una tabella nel database TutorialDB e quindi salvare il modello nella tabella.

1. Creare una tabella (rental_models) per memorizzare il modello.

   USE TutorialDB;
   DROP TABLE IF EXISTS rental_models;
   GO
   CREATE TABLE rental_models (
         model_name VARCHAR(30) NOT NULL DEFAULT('default model') PRIMARY KEY
       , model VARBINARY(MAX) NOT NULL
       );
   GO
   

2. Salvare il modello nella tabella come oggetto binario con il nome "DTree".

   -- Save model to table
   TRUNCATE TABLE rental_models;
   
   DECLARE @model VARBINARY(MAX);
   
   EXECUTE generate_rental_model @model OUTPUT;
   
   INSERT INTO rental_models (
         model_name
       , model
       )
   VALUES (
        'DTree'
       , @model
       );
   
   SELECT *
   FROM rental_models;
   

Creare una stored procedure che esegue stime

Creare una stored procedure (predict_rentalcount_new) per le stime in base al modello di cui è stato eseguito il training e un set di nuovi dati.

-- Stored procedure that takes model name and new data as input parameters and predicts the rental count for the new data
USE [TutorialDB]
DROP PROCEDURE IF EXISTS predict_rentalcount_new;
GO
CREATE PROCEDURE predict_rentalcount_new (
      @model_name VARCHAR(100)
    , @input_query NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @model VARBINARY(MAX) = (
            SELECT model
            FROM rental_models
            WHERE model_name = @model_name
            );

    EXECUTE sp_execute_external_script @language = N'R'
        , @script = N'
#Convert types to factors
rentals$Month   <- factor(rentals$Month);
rentals$Day     <- factor(rentals$Day);
rentals$Holiday <- factor(rentals$Holiday);
rentals$Snow    <- factor(rentals$Snow);
rentals$WeekDay <- factor(rentals$WeekDay);

#Before using the model to predict, we need to unserialize it
rental_model <- unserialize(model);

#Call prediction function
rental_predictions <- predict(rental_model, rentals);
rental_predictions <- data.frame(rental_predictions);
'
        , @input_data_1 = @input_query
        , @input_data_1_name = N'rentals'
        , @output_data_1_name = N'rental_predictions'
        , @params = N'@model varbinary(max)'
        , @model = @model
    WITH RESULT SETS(("RentalCount_Predicted" FLOAT));
END;
GO

Eseguire il modello con nuovi dati

Ora è possibile utilizzare la stored procedure predict_rentalcount_new per stimare il numero di noleggio dai nuovi dati.

-- Use the predict_rentalcount_new stored procedure with the model name and a set of features to predict the rental count
EXECUTE dbo.predict_rentalcount_new @model_name = 'DTree'
    , @input_query = '
        SELECT CONVERT(INT,  3) AS Month
             , CONVERT(INT, 24) AS Day
             , CONVERT(INT,  4) AS WeekDay
             , CONVERT(INT,  1) AS Snow
             , CONVERT(INT,  1) AS Holiday
        ';
GO

Il risultato visualizzato sarà simile al seguente.

RentalCount_Predicted
332.571428571429

Sono state eseguite le operazioni di creazione, training e distribuzione di un modello in un database. Il modello è stato quindi usato in una stored procedure per stimare i valori in base ai nuovi dati.

Pulire le risorse

Dopo aver usato il database TutorialDB, eliminarlo dal server.

Passaggi successivi

Nella quarta parte di questa serie di esercitazioni si è appreso come:

• Creare una stored procedure che genera il modello di Machine Learning
• Archiviare il modello in una tabella del database
• Creare una stored procedure che esegue stime usando il modello
• Eseguire il modello con nuovi dati

Per altre informazioni sull'uso di R in Machine Learning Services, vedere:

• Eseguire script R semplici
• Strutture di dati, tipi di dati e oggetti R
• Funzioni R