Tutorial: Menyebarkan model prediktif di R dengan pembelajaran mesin SQL

Berlaku untuk: SQL Server 2016 (13.x) dan Azure SQL Managed Instance yang lebih baru

Di bagian empat dari seri tutorial empat bagian ini, Anda akan menyebarkan model pembelajaran mesin yang dikembangkan di R ke SQL Server Pembelajaran Mesin Services atau di Kluster Big Data.

Dalam artikel ini, Anda akan mempelajari cara:

• Membuat prosedur tersimpan yang menghasilkan model pembelajaran mesin
• Menyimpan model dalam tabel database
• Membuat prosedur tersimpan yang membuat prediksi menggunakan model
• Jalankan model dengan data baru

Di bagian satu, Anda mempelajari cara memulihkan database sampel.

Di bagian dua, Anda mempelajari cara mengimpor database sampel lalu menyiapkan data yang akan digunakan untuk melatih model prediktif di R.

Di bagian tiga, Anda mempelajari cara membuat dan melatih beberapa model pembelajaran mesin di R, lalu memilih yang paling akurat.

Prasyarat

Bagian empat dari tutorial ini mengasumsikan Anda memenuhi prasyarat bagian satu dan menyelesaikan langkah-langkah di bagian dua dan bagian tiga.

Membuat prosedur tersimpan yang menghasilkan model

Di bagian tiga seri tutorial ini, Anda memutuskan bahwa model pohon keputusan (dtree) adalah yang paling akurat. Sekarang, menggunakan skrip R yang Anda kembangkan, buat prosedur tersimpan (generate_rental_model) yang melatih dan menghasilkan model dtree menggunakan rpart dari paket R.

Jalankan perintah berikut di 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

Menyimpan model dalam tabel database

Buat tabel di database TutorialDB lalu simpan model ke tabel.

1. Buat tabel (rental_models) untuk menyimpan model.

   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. Simpan model ke tabel sebagai objek biner, dengan nama model "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;
   

Membuat prosedur tersimpan yang membuat prediksi

Buat prosedur tersimpan (predict_rentalcount_new) yang membuat prediksi menggunakan model terlatih dan sekumpulan data baru.

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

Jalankan model dengan data baru

Sekarang Anda dapat menggunakan prosedur predict_rentalcount_new tersimpan untuk memprediksi jumlah sewa dari data baru.

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

Anda akan melihat hasil yang mirip dengan yang berikut ini.

RentalCount_Predicted
332.571428571429

Anda telah berhasil membuat, melatih, dan menyebarkan model dalam database. Anda kemudian menggunakan model tersebut dalam prosedur tersimpan untuk memprediksi nilai berdasarkan data baru.

Membersihkan sumber daya

Setelah selesai menggunakan database TutorialDB, hapus dari server Anda.

Langkah berikutnya

Di bagian empat seri tutorial ini, Anda mempelajari cara:

• Membuat prosedur tersimpan yang menghasilkan model pembelajaran mesin
• Menyimpan model dalam tabel database
• Membuat prosedur tersimpan yang membuat prediksi menggunakan model
• Jalankan model dengan data baru

Untuk mempelajari selengkapnya tentang menggunakan R di Pembelajaran Mesin Services, lihat:

• Menjalankan skrip R sederhana
• Struktur, jenis, dan objek data R
• Fungsi R