Partilhar via

Tutorial do Python: Treinar e salvar um modelo Python usando T-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 cinco partes, você aprenderá como treinar um modelo de aprendizado de máquina usando os pacotes Python scikit-learn e revoscalepy. Essas bibliotecas Python já estão instaladas com o aprendizado de máquina do SQL Server.

Você carregará os módulos e chamará as funções necessárias para criar e treinar o modelo usando um procedimento armazenado do SQL Server. O modelo requer os recursos de dados que você projetou em partes anteriores desta série de tutoriais. Finalmente, você salvará o modelo treinado em uma tabela do SQL Server.

Neste artigo, você:

  • Criar e treinar um modelo usando um procedimento armazenado SQL
  • Salvar o modelo treinado em uma tabela SQL

Na primeira parte, você instalou os pré-requisitos e restaurou o banco de dados de exemplo.

Na segunda parte, você explorou os dados de amostra e gerou alguns gráficos.

Na terceira parte, você aprendeu como criar recursos a partir de dados brutos usando uma função Transact-SQL. Em seguida, você chamou essa função de um procedimento armazenado para criar uma tabela que contém os valores de recurso.

Na quinta parte, você aprenderá a operacionalizar os modelos que treinou e salvou na quarta parte.

Divida os dados de amostra em conjuntos de treinamento e teste

  1. Crie um procedimento armazenado chamado PyTrainTestSplit para dividir os dados na tabela nyctaxi_sample em duas partes: nyctaxi_sample_training e nyctaxi_sample_testing.

    Execute o seguinte código para criá-lo:

    DROP PROCEDURE IF EXISTS PyTrainTestSplit;
GO

CREATE PROCEDURE [dbo].[PyTrainTestSplit] (@pct int)
AS

DROP TABLE IF EXISTS dbo.nyctaxi_sample_training
SELECT * into nyctaxi_sample_training FROM nyctaxi_sample WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) < @pct

DROP TABLE IF EXISTS dbo.nyctaxi_sample_testing
SELECT * into nyctaxi_sample_testing FROM nyctaxi_sample
WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) > @pct
GO

  2. Para dividir seus dados usando uma divisão personalizada, execute o procedimento armazenado e forneça um parâmetro inteiro que represente a porcentagem de dados a serem alocados para o conjunto de treinamento. Por exemplo, a instrução a seguir alocaria 60% de dados para o conjunto de treinamento.

    EXEC PyTrainTestSplit 60
GO

Construir um modelo de regressão logística

Depois que os dados forem preparados, você poderá usá-los para treinar um modelo. Você faz isso chamando um procedimento armazenado que executa algum código Python, tomando como entrada a tabela de dados de treinamento. Para este tutorial, você cria dois modelos, ambos modelos de classificação binária:

  • O procedimento armazenado PyTrainScikit cria um modelo de previsão de ponta usando o pacote scikit-learn .
  • O procedimento armazenado TrainTipPredictionModelRxPy cria um modelo de previsão de ponta usando o pacote revoscalepy .

Cada procedimento armazenado usa os dados de entrada fornecidos para criar e treinar um modelo de regressão logística. Todo o código Python é encapsulado no procedimento armazenado do sistema, sp_execute_external_script.

Para facilitar o retreinamento do modelo com novos dados, envolve a chamada para sp_execute_external_script num outro procedimento armazenado, e passe os novos dados de treinamento como um parâmetro. Esta secção irá guiá-lo através desse processo.

PyTrainScikit

  1. No Management Studio, abra uma nova janela Query e execute a seguinte instrução para criar o procedimento armazenado PyTrainScikit. O procedimento armazenado contém uma definição dos dados de entrada, portanto, você não precisa fornecer uma consulta de entrada.

    DROP PROCEDURE IF EXISTS PyTrainScikit;
GO

CREATE PROCEDURE [dbo].[PyTrainScikit] (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import numpy
import pickle
from sklearn.linear_model import LogisticRegression

##Create SciKit-Learn logistic regression model
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

SKLalgo = LogisticRegression()
logitObj = SKLalgo.fit(X, y)

##Serialize model
trained_model = pickle.dumps(logitObj)
',
@input_data_1 = N'
select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, 
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
from nyctaxi_sample_training
',
@input_data_1_name = N'InputDataSet',
@params = N'@trained_model varbinary(max) OUTPUT',
@trained_model = @trained_model OUTPUT;
;
END;
GO

  2. Execute as instruções SQL a seguir para inserir o modelo treinado na tabela nyc_taxi_models.

    DECLARE @model VARBINARY(MAX);
EXEC PyTrainScikit @model OUTPUT;
INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model);

    O processamento dos dados e o ajuste do modelo podem levar alguns minutos. As mensagens que seriam canalizadas para o fluxo stdout do Python são exibidas na janela Mensagens do Management Studio. Por exemplo:

    STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

  3. Abra a tabela nyc_taxi_models. Você pode ver que uma nova linha foi adicionada e que contém o modelo serializado na coluna model.

    SciKit_model
0x800363736B6C6561726E2E6C696E6561....

TrainTipPredictionModelRxPy

Este procedimento armazenado usa o pacote Python revoscalepy. Ele contém objetos, transformação e algoritmos semelhantes aos fornecidos para o pacote RevoScaleR da linguagem R.

Usando o revoscalepy, você pode criar contextos de computação remotos, mover dados entre contextos de computação, transformar dados e treinar modelos preditivos usando algoritmos populares, como regressão logística e linear, árvores de decisão e muito mais. Para obter mais informações, consulte módulo revoscalepy no SQL Server e referência da função revoscalepy.

  1. No Management Studio, abra uma nova janela Query e execute a instrução a seguir para criar o procedimento armazenado TrainTipPredictionModelRxPy. Como o procedimento armazenado já inclui uma definição dos dados de entrada, não é necessário fornecer uma consulta de entrada.

    DROP PROCEDURE IF EXISTS TrainTipPredictionModelRxPy;
GO

CREATE PROCEDURE [dbo].[TrainTipPredictionModelRxPy] (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import numpy
import pickle
from revoscalepy.functions.RxLogit import rx_logit

## Create a logistic regression model using rx_logit function from revoscalepy package
logitObj = rx_logit("tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance", data = InputDataSet);

## Serialize model
trained_model = pickle.dumps(logitObj)
',
@input_data_1 = N'
select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, 
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
from nyctaxi_sample_training
',
@input_data_1_name = N'InputDataSet',
@params = N'@trained_model varbinary(max) OUTPUT',
@trained_model = @trained_model OUTPUT;
;
END;
GO

    Este procedimento armazenado executa as seguintes etapas como parte do treinamento do modelo:

    • A consulta SELECT aplica a função escalar personalizada fnCalculateDistance para calcular a distância direta entre os locais de embarque e desembarque. Os resultados da consulta são armazenados na variável de entrada padrão do Python, InputDataset.
    • A variável binária inclinada é usada como o rótulo ou coluna de resultado, e o modelo é ajustado usando estas colunas de recurso: passenger_count, trip_distance, trip_time_in_secs e direct_distance.
    • O modelo treinado é serializado e armazenado na variável logitObjPython . Ao adicionar a palavra-chave T-SQL OUTPUT, você pode adicionar a variável como uma saída do procedimento armazenado. Na próxima etapa, essa variável é usada para inserir o código binário do modelo em uma tabela de banco de dados nyc_taxi_models. Este mecanismo facilita o armazenamento e a reutilização de modelos.

  2. Execute o procedimento armazenado da seguinte forma para inserir o modelo revoscalepy treinado na tabela nyc_taxi_models.

    DECLARE @model VARBINARY(MAX);
EXEC TrainTipPredictionModelRxPy @model OUTPUT;
INSERT INTO nyc_taxi_models (name, model) VALUES('revoscalepy_model', @model);

    O processamento dos dados e o ajuste do modelo podem demorar um pouco. As mensagens que seriam canalizadas para o fluxo stdout do Python são exibidas na janela Mensagens do Management Studio. Por exemplo:

    STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

  3. Abra a tabela nyc_taxi_models. Você pode ver que uma nova linha foi adicionada e que contém o modelo serializado na coluna model.

    revoscalepy_model
0x8003637265766F7363616c....

Na próxima parte deste tutorial, você usará os modelos treinados para criar previsões.

Próximos passos

Neste artigo, você:

  • Criou e treinou um modelo usando um procedimento armazenado SQL
  • Salvo o modelo treinado em uma tabela SQL

Tutorial do Python: Executar previsões usando Python incorporado em um procedimento armazenado

  • Last updated on