Python Tutorial: Implantar um modelo de regressão linear com aprendizado de máquina SQL

Aplica-se a: SQL Server 2017 (14.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 regressão linear desenvolvido em Python em um banco de dados do SQL Server usando Serviços de Aprendizado de Máquina ou 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 carregar os dados de um banco de dados em um quadro de dados Python e preparar os dados em Python.

Na terceira parte, você aprendeu como treinar um modelo de aprendizado de máquina de regressão linear em Python.

Pré-requisitos

• A quarta parte deste tutorial pressupõe que você concluiu a primeira parte e seus pré-requisitos.

Criar um procedimento armazenado que gera o modelo

Agora, usando os scripts Python que você desenvolveu, crie um generate_rental_py_model de procedimento armazenado que treina e gera o modelo de regressão linear usando LinearRegression do scikit-learn.

Execute a seguinte instrução T-SQL no Azure Data Studio para criar o procedimento armazenado para treinar o modelo.

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

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. Execute a seguinte instrução T-SQL no Azure Data Studio para criar uma tabela chamada dbo.rental_py_models que é usada para armazenar o modelo.

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

Criar um procedimento armazenado que faz previsões

1. Crie um py_predict_rentalcount de procedimento armazenado que faça previsões usando o modelo treinado e um conjunto de novos dados. Execute o T-SQL abaixo no 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. Crie uma tabela para armazenar as previsões.

   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. Execute o procedimento armazenado para prever contagens de aluguel

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

   Você verá resultados semelhantes aos seguintes.

   

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

Próximos passos

Na quarta parte desta série de tutoriais, você concluiu estas etapas:

• 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 Python com aprendizado de máquina SQL, consulte:

• Tutoriais em Python