Tutorial: Implementación de un modelo predictivo en R con el aprendizaje automático de SQL

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Managed Instance

En la parte cuatro de esta serie de tutoriales de cuatro partes, implementará un modelo de aprendizaje automático desarrollado en R en SQL Server Machine Learning Services o en Clústeres de macrodatos.

En este artículo, aprenderá a:

• Crear un procedimiento almacenado que genere el modelo de aprendizaje automático
• Almacenar el modelo en una tabla de base de datos
• Crear un procedimiento almacenado que realice predicciones mediante el modelo
• Ejecutar el modelo con datos nuevos

En la parte uno, ha aprendido a restaurar la base de datos de ejemplo.

En la parte dos, aprendió a importar una base de datos de ejemplo y, luego, a preparar los datos para su uso en el entrenamiento de un modelo predictivo en R.

En la parte tres, aprendió a crear y entrenar varios modelos de Machine Learning en R y, luego, elegir el más preciso.

Prerrequisitos

En la parte cuatro de este tutorial se da por hecho que ha completado los requisitos previos de la parte uno y que ha realizado los pasos de la parte dos y la parte tres.

Crear un procedimiento almacenado que genere el modelo

En la parte tres de esta serie de tutoriales, decidió que un modelo de árbol de decisión (dtree) era el más preciso. Ahora, con los scripts de R desarrollados, cree un procedimiento almacenado (generate_rental_model) que entrene y genere el modelo dtree mediante rpart desde el paquete de R.

Ejecute los comandos siguientes en 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

Almacenar el modelo en una tabla de base de datos

Cree una tabla en la base de datos TutorialDB y, después, guarde el modelo en la tabla.

1. Cree una tabla (rental_models) para almacenar el 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. Guarde el modelo en la tabla como un objeto binario, con el nombre "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;
   

Creación de un procedimiento almacenado que realiza predicciones

Cree un procedimiento almacenado (predict_rentalcount_new) que hace predicciones mediante el modelo entrenado y un conjunto de nuevos datos.

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

Ejecutar el modelo con datos nuevos

Ahora puede usar el procedimiento almacenado predict_rentalcount_new para predecir el número de alquileres a partir de los nuevos datos.

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

Verá un resultado similar al siguiente:

RentalCount_Predicted
332.571428571429

Ha creado, entrenado e implementado correctamente un modelo en una base de datos. Después, ha usado el modelo en un procedimiento almacenado para predecir valores basándose en datos nuevos.

Limpieza de recursos

Cuando termine de usar la base de datos TutorialDB, elimínela del servidor.

Pasos siguientes

En la cuarta parte de la serie de tutoriales, ha aprendido a:

• Crear un procedimiento almacenado que genere el modelo de aprendizaje automático
• Almacenar el modelo en una tabla de base de datos
• Crear un procedimiento almacenado que realice predicciones mediante el modelo
• Ejecutar el modelo con datos nuevos

Para más información sobre cómo usar R en Machine Learning Services, vea:

• Ejecución de scripts de R simples
• Estructuras de datos, tipos y objetos de R
• Funciones de R