Kurz: Nasazení prediktivního modelu v R pomocí strojového učení SQL

Platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL Managed Instance

Ve čtvrté části této čtyřdílné série kurzů nasadíte model strojového učení vyvinutý v jazyce R do služby SQL Server Machine Learning Services nebo do clusterů s velkými objemy dat.

V tomto článku se naučíte:

• Vytvoření uložené procedury, která vygeneruje model strojového učení
• Uložení modelu v tabulce databáze
• Vytvoření uložené procedury, která vytváří předpovědi pomocí modelu
• Spuštění modelu s novými daty

V první části jste zjistili, jak obnovit ukázkovou databázi.

V druhé části jste se dozvěděli, jak importovat ukázkovou databázi a pak připravit data, která se použijí k trénování prediktivního modelu v jazyce R.

Ve třetí části jste se naučili vytvářet a trénovat více modelů strojového učení v jazyce R a pak zvolit nejpřesnější.

Požadavky

Část 4 tohoto kurzu předpokládá, že jste splnili požadavky první části a dokončili kroky ve druhé a třetí části.

Vytvoření uložené procedury, která vygeneruje model

Ve třetí části této série kurzů jste se rozhodli, že model rozhodovacího stromu (dtree) byl nejpřesnější. Teď pomocí skriptů R, které jste vytvořili, vytvořte uloženou proceduru (generate_rental_model), která trénuje a generuje model dtree pomocí rpart z balíčku R.

V Nástroji Azure Data Studio spusťte následující příkazy.

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

Uložení modelu v tabulce databáze

Vytvořte tabulku v databázi TutorialDB a pak model uložte do tabulky.

1. Vytvořte tabulku (rental_models) pro uložení modelu.

   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. Uložte model do tabulky jako binární objekt s názvem modelu "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;
   

Vytvoření uložené procedury, která vytváří předpovědi

Vytvořte uloženou proceduru (predict_rentalcount_new), která vytváří předpovědi pomocí natrénovaného modelu a sady nových dat.

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

Spuštění modelu s novými daty

Teď můžete pomocí uložené procedury predict_rentalcount_new předpovědět počet pronájmů z nových dat.

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

Měl by se zobrazit výsledek podobný následujícímu.

RentalCount_Predicted
332.571428571429

Úspěšně jste vytvořili, natrénovali a nasadili model v databázi. Pak jste tento model použili v uložené proceduře k predikci hodnot na základě nových dat.

Vyčistěte zdroje

Až budete s použitím databáze TutorialDB hotovi, odstraňte ji ze serveru.

Další kroky

Ve čtvrté části této série kurzů jste se naučili:

• Vytvoření uložené procedury, která vygeneruje model strojového učení
• Uložení modelu v tabulce databáze
• Vytvoření uložené procedury, která vytváří předpovědi pomocí modelu
• Spuštění modelu s novými daty

Další informace o používání jazyka R ve službě Machine Learning Services najdete tady:

• Spouštění jednoduchých skriptů jazyka R
• Datové struktury R, typy a objekty
• Funkce jazyka R