Tutorial Python: Menyebarkan model regresi linier dengan pembelajaran mesin SQL

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

Di bagian empat dari seri tutorial empat bagian ini, Anda akan menyebarkan model regresi linier yang dikembangkan di Python ke dalam database SQL Server menggunakan Layanan Pembelajaran Mesin atau 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
• Menjalankan model dengan data baru

Di bagian satu, Anda mempelajari cara memulihkan database sampel.

Di bagian dua, Anda mempelajari cara memuat data dari database ke dalam bingkai data Python, dan menyiapkan data di Python.

Di bagian tiga, Anda belajar cara melatih model pembelajaran mesin regresi linier di Python.

Prasyarat

• Bagian empat dari tutorial ini mengasumsikan Anda telah menyelesaikan bagian satu dan prasyaratnya.

Membuat prosedur tersimpan yang menghasilkan model

Sekarang, menggunakan skrip Python yang Anda kembangkan, buat prosedur tersimpan generate_rental_py_model yang melatih dan menghasilkan model regresi linier menggunakan LinearRegression dari scikit-learn.

Jalankan pernyataan T-SQL berikut di Azure Data Studio untuk membuat prosedur tersimpan untuk melatih model.

-- Stored procedure that trains and generates a Python model using the rental_data and a linear regression algorithm
DROP PROCEDURE IF EXISTS generate_rental_py_model;
go
CREATE PROCEDURE generate_rental_py_model (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
    EXECUTE sp_execute_external_script
      @language = N'Python'
    , @script = N'
from sklearn.linear_model import LinearRegression
import pickle

df = rental_train_data

# Get all the columns from the dataframe.
columns = df.columns.tolist()

# Store the variable well be predicting on.
target = "RentalCount"

# Initialize the model class.
lin_model = LinearRegression()

# Fit the model to the training data.
lin_model.fit(df[columns], df[target])

# Before saving the model to the DB table, convert it to a binary object
trained_model = pickle.dumps(lin_model)'

, @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. Jalankan pernyataan T-SQL berikut di Azure Data Studio untuk membuat tabel yang disebut dbo.rental_py_models yang digunakan untuk menyimpan model.

   USE TutorialDB;
   DROP TABLE IF EXISTS dbo.rental_py_models;
   GO
   CREATE TABLE dbo.rental_py_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 linear_model.

   DECLARE @model VARBINARY(MAX);
   EXECUTE generate_rental_py_model @model OUTPUT;
   
   INSERT INTO rental_py_models (model_name, model) VALUES('linear_model', @model);
   

Membuat prosedur tersimpan yang membuat prediksi

1. Buat prosedur tersimpan py_predict_rentalcount yang membuat prediksi menggunakan model terlatih dan sekumpulan data baru. Jalankan T-SQL di bawah ini di Azure Data Studio.

   DROP PROCEDURE IF EXISTS py_predict_rentalcount;
   GO
   CREATE PROCEDURE py_predict_rentalcount (@model varchar(100))
   AS
   BEGIN
       DECLARE @py_model varbinary(max) = (select model from rental_py_models where model_name = @model);
   
       EXECUTE sp_execute_external_script
                   @language = N'Python',
                   @script = N'
   
   # Import the scikit-learn function to compute error.
   from sklearn.metrics import mean_squared_error
   import pickle
   import pandas
   
   rental_model = pickle.loads(py_model)
   
   df = rental_score_data
   
   # Get all the columns from the dataframe.
   columns = df.columns.tolist()
   
   # Variable you will be predicting on.
   target = "RentalCount"
   
   # Generate the predictions for the test set.
   lin_predictions = rental_model.predict(df[columns])
   print(lin_predictions)
   
   # Compute error between the test predictions and the actual values.
   lin_mse = mean_squared_error(lin_predictions, df[target])
   #print(lin_mse)
   
   predictions_df = pandas.DataFrame(lin_predictions)
   
   OutputDataSet = pandas.concat([predictions_df, df["RentalCount"], df["Month"], df["Day"], df["WeekDay"], df["Snow"], df["Holiday"], df["Year"]], axis=1)
   '
   , @input_data_1 = N'Select "RentalCount", "Year" ,"Month", "Day", "WeekDay", "Snow", "Holiday"  from rental_data where Year = 2015'
   , @input_data_1_name = N'rental_score_data'
   , @params = N'@py_model varbinary(max)'
   , @py_model = @py_model
   with result sets (("RentalCount_Predicted" float, "RentalCount" float, "Month" float,"Day" float,"WeekDay" float,"Snow" float,"Holiday" float, "Year" float));
   
   END;
   GO
   

2. Buat tabel untuk menyimpan prediksi.

   DROP TABLE IF EXISTS [dbo].[py_rental_predictions];
   GO
   
   CREATE TABLE [dbo].[py_rental_predictions](
    [RentalCount_Predicted] [int] NULL,
    [RentalCount_Actual] [int] NULL,
    [Month] [int] NULL,
    [Day] [int] NULL,
    [WeekDay] [int] NULL,
    [Snow] [int] NULL,
    [Holiday] [int] NULL,
    [Year] [int] NULL
   ) ON [PRIMARY]
   GO
   

3. Jalankan prosedur tersimpan untuk memprediksi jumlah sewa

   --Insert the results of the predictions for test set into a table
   INSERT INTO py_rental_predictions
   EXEC py_predict_rentalcount 'linear_model';
   
   -- Select contents of the table
   SELECT * FROM py_rental_predictions;
   

   Anda akan melihat hasil yang mirip dengan yang berikut ini.

   

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

Langkah berikutnya

Di bagian empat seri tutorial ini, Anda menyelesaikan langkah-langkah berikut:

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

Untuk mempelajari selengkapnya tentang menggunakan Python dengan pembelajaran mesin SQL, lihat:

• Tutorial Python