Tutorial: Implantar um modelo preditivo em R com aprendizado de máquina SQL

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Instância Gerenciada SQL do Azure

Na quarta parte desta série de tutoriais de quatro partes, você implantará um modelo de aprendizado de máquina desenvolvido em R nos Serviços de Aprendizado de Máquina do SQL Server ou em Clusters de Big Data.

Neste artigo, você aprenderá a:

• Criar um procedimento armazenado que gere o modelo de aprendizado de máquina
• Armazenar o modelo em uma tabela de banco de dados
• Criar um procedimento armazenado que faz previsões usando o modelo
• Executar o modelo com novos dados

Na primeira parte, você aprendeu como restaurar o banco de dados de exemplo.

Na segunda parte, você aprendeu como importar um banco de dados de exemplo e, em seguida, preparar os dados a serem usados para treinar um modelo preditivo em R.

Na terceira parte, você aprendeu como criar e treinar vários modelos de aprendizado de máquina em R e, em seguida, escolher o mais preciso.

Pré-requisitos

A quarta parte deste tutorial pressupõe que você preencheu os pré-requisitos da parte um e concluiu as etapas da parte dois e da parte três.

Criar um procedimento armazenado que gera o modelo

Na terceira parte desta série de tutoriais, você decidiu que um modelo de árvore de decisão (dtree) era o mais preciso. Agora, usando os scripts R que você desenvolveu, crie um procedimento armazenado (generate_rental_model) que treina e gera o modelo dtree usando rpart do pacote R.

Execute os seguintes comandos no 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

Armazenar o modelo em uma tabela de banco de dados

Crie uma tabela no banco de dados TutorialDB e salve o modelo na tabela.

1. Crie uma tabela (rental_models) para armazenar o modelo.

   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. Salve o modelo na tabela como um objeto binário, com o nome do modelo "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;
   

Criar um procedimento armazenado que faz previsões

Crie um procedimento armazenado (predict_rentalcount_new) que faça previsões usando o modelo treinado e um conjunto de novos dados.

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

Executar o modelo com novos dados

Agora você pode usar o procedimento predict_rentalcount_new armazenado para prever a contagem de aluguel a partir de novos dados.

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

Você verá um resultado semelhante ao seguinte.

RentalCount_Predicted
332.571428571429

Você criou, treinou e implantou com êxito um modelo em um banco de dados. Em seguida, você usou esse modelo em um procedimento armazenado para prever valores com base em novos dados.

Limpeza de recursos

Quando terminar de usar o banco de dados TutorialDB, exclua-o do servidor.

Próximos passos

Na quarta parte desta série de tutoriais, você aprendeu como:

• Criar um procedimento armazenado que gere o modelo de aprendizado de máquina
• Armazenar o modelo em uma tabela de banco de dados
• Criar um procedimento armazenado que faz previsões usando o modelo
• Executar o modelo com novos dados

Para saber mais sobre como usar o R nos Serviços de Aprendizado de Máquina, consulte:

• Executar scripts simples em R
• Estruturas, tipos e objetos de dados R
• Funções R