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

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores Instância Gerenciada SQL do Azure

Na quinta parte desta série de tutoriais de cinco partes, você aprenderá a operacionalizar os modelos que você treinou e salvou na parte anterior.

Nesse cenário, operacionalização significa implantar o modelo na produção para pontuação. A integração com o SQL Server torna isso bastante fácil, porque você pode incorporar código Python em um procedimento armazenado. Para obter previsões do modelo com base em novas entradas, basta chamar o procedimento armazenado de um aplicativo e passar os novos dados.

Esta parte do tutorial demonstra dois métodos para criar previsões com base em um modelo Python: pontuação em lote e pontuação linha por linha.

Processamento em lotes: Para fornecer várias linhas de dados de entrada, utilize uma consulta SELECT como um argumento para o procedimento armazenado. O resultado é um quadro de observações correspondente aos casos de entrada.
Pontuação individual: Passe um conjunto de valores de parâmetros individuais como entrada. O procedimento armazenado retorna uma única linha ou valor.

Todo o código Python necessário para a pontuação é fornecido como parte dos procedimentos armazenados.

Neste artigo, você:

Criar e usar procedimentos armazenados para pontuação em lote
Criar e usar procedimentos armazenados para avaliar uma única linha

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 quarta parte, você carregou os módulos e chamou as funções necessárias para criar e treinar o modelo usando um procedimento armazenado do SQL Server.

Pontuação em lote

Os dois primeiros procedimentos armazenados criados usando os scripts a seguir ilustram a sintaxe básica para encapsular uma chamada de previsão Python em um procedimento armazenado. Ambos os procedimentos armazenados requerem uma tabela de dados como entradas.

O nome do modelo a ser usado é fornecido como parâmetro de entrada para o procedimento armazenado. O procedimento armazenado carrega o modelo serializado da tabela nyc_taxi_models.table do banco de dados, usando a instrução SELECT no procedimento armazenado.
O modelo serializado é armazenado na variável mod Python para processamento posterior usando Python.
Os novos casos que precisam ser pontuados são obtidos a partir da consulta Transact-SQL especificada em @input_data_1. À medida que os dados de consulta são lidos, as linhas são salvas no quadro de dados padrão, InputDataSet.
Ambos os procedimentos armazenados usam funções de sklearn para calcular uma métrica de precisão, AUC (área sob a curva). Métricas de precisão, como AUC, só podem ser geradas se for igualmente fornecido o rótulo alvo (a coluna marcada). As previsões não precisam do rótulo de destino (variável y), mas o cálculo da métrica de precisão sim.

Portanto, se não tem etiquetas alvo para os dados a serem avaliados, pode modificar o procedimento armazenado para remover os cálculos de AUC e retornar apenas as probabilidades das características (variável X no procedimento armazenado).

PredictTipSciKitPy

Execute as seguintes instruções T-SQL para criar o procedimento PredictTipSciKitPyarmazenado . Este procedimento armazenado requer um modelo baseado no pacote scikit-learn, porque usa funções específicas para esse pacote.

O quadro de dados contendo entradas é passado para a predict_proba função do modelo de regressão logística, mod. A predict_proba função (probArray = mod.predict_proba(X)) retorna um float que representa a probabilidade de que uma gorjeta (de qualquer montante) seja dada.

DROP PROCEDURE IF EXISTS PredictTipSciKitPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSciKitPy] (@model varchar(50), @inquery nvarchar(max))
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from sklearn import metrics

mod = pickle.loads(lmodel2)
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

probArray = mod.predict_proba(X)
probList = []
for i in range(len(probArray)):
  probList.append((probArray[i])[1])

probArray = numpy.asarray(probList)
fpr, tpr, thresholds = metrics.roc_curve(y, probArray)
aucResult = metrics.auc(fpr, tpr)
print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',	
  @input_data_1 = @inquery,
  @input_data_1_name = N'InputDataSet',
  @params = N'@lmodel2 varbinary(max)',
  @lmodel2 = @lmodel2
WITH RESULT SETS ((Score float));
END
GO

PredictTipRxPy

Execute as seguintes instruções T-SQL para criar o procedimento PredictTipRxPyarmazenado . Esse procedimento armazenado usa as mesmas entradas e cria o mesmo tipo de pontuações que o procedimento armazenado anterior, mas usa funções do pacote revoscalepy fornecido com o aprendizado de máquina do SQL Server.

DROP PROCEDURE IF EXISTS PredictTipRxPy;
GO

CREATE PROCEDURE [dbo].[PredictTipRxPy] (@model varchar(50), @inquery nvarchar(max))
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from sklearn import metrics
from revoscalepy.functions.RxPredict import rx_predict;

mod = pickle.loads(lmodel2)
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

probArray = rx_predict(mod, X)
probList = probArray["tipped_Pred"].values 

probArray = numpy.asarray(probList)
fpr, tpr, thresholds = metrics.roc_curve(y, probArray)
aucResult = metrics.auc(fpr, tpr)
print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @input_data_1_name = N'InputDataSet',
  @params = N'@lmodel2 varbinary(max)',
  @lmodel2 = @lmodel2
WITH RESULT SETS ((Score float));
END
GO

Executar pontuação em lote usando uma consulta SELECT

Os procedimentos armazenados PredictTipSciKitPy e PredictTipRxPy requerem dois parâmetros de entrada:

A consulta que recupera os dados para pontuação
O nome de um modelo treinado

Ao passar esses argumentos para o procedimento armazenado, você pode selecionar um modelo específico ou alterar os dados usados para pontuação.

Para usar o modelo scikit-learn para pontuação, chame o procedimento armazenado PredictTipSciKitPy, passando o nome do modelo e a cadeia de caracteres de consulta como entradas.
```
DECLARE @query_string nvarchar(max) -- Specify input query
  SET @query_string='
  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_testing'
EXEC [dbo].[PredictTipSciKitPy] 'SciKit_model', @query_string;
```
O procedimento armazenado retorna probabilidades previstas para cada viagem que foi passada como parte da consulta de entrada.

Se você estiver usando o SSMS (SQL Server Management Studio) para executar consultas, as probabilidades aparecerão como uma tabela no painel Resultados . O painel Mensagens produz a métrica de precisão (AUC ou área sob curva) com um valor de cerca de 0,56.

Para usar o modelo revoscalepy para pontuação, chame o procedimento armazenado PredictTipRxPy, passando o nome do modelo e a cadeia de caracteres de consulta como entradas.

DECLARE @query_string nvarchar(max) -- Specify input query
  SET @query_string='
  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_testing'
EXEC [dbo].[PredictTipRxPy] 'revoscalepy_model', @query_string;

Pontuação em fila única

Às vezes, em vez de pontuação em lote, você pode querer passar em um único caso, obtendo valores de um aplicativo e retornando um único resultado com base nesses valores. Por exemplo, você pode configurar uma planilha do Excel, aplicativo Web ou relatório para chamar o procedimento armazenado e passar para ele entradas digitadas ou selecionadas pelos usuários.

Nesta seção, você aprenderá a criar previsões únicas chamando dois procedimentos armazenados:

PredictTipSingleModeSciKitPy é projetado para pontuação de linha única usando o modelo scikit-learn.
O PredictTipSingleModeRxPy foi projetado para pontuação de linha única usando o modelo revoscalepy.
Se você ainda não treinou um modelo, volte para a quinta parte!

Ambos os modelos tomam como entrada uma série de valores únicos, como contagem de passageiros, distância da viagem e assim por diante. Uma função com valor de tabela, fnEngineerFeatures, é usada para converter valores de latitude e longitude das entradas para um novo recurso, a distância direta. A quarta parte contém uma descrição desta função com valor de tabela.

Ambos os procedimentos armazenados criam uma pontuação com base no modelo Python.

Observação

É importante que você forneça todos os recursos de entrada exigidos pelo modelo Python ao chamar o procedimento armazenado de um aplicativo externo. Para evitar erros, talvez seja necessário fazer casting ou converter os dados de entrada para um tipo de dados Python, além de validar o tipo de dados e o comprimento dos dados.

PredictTipSingleModeSciKitPy

O procedimento PredictTipSingleModeSciKitPy armazenado a seguir executa a pontuação usando o modelo scikit-learn .

DROP PROCEDURE IF EXISTS PredictTipSingleModeSciKitPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSingleModeSciKitPy] (@model varchar(50), @passenger_count int = 0,
  @trip_distance float = 0,
  @trip_time_in_secs int = 0,
  @pickup_latitude float = 0,
  @pickup_longitude float = 0,
  @dropoff_latitude float = 0,
  @dropoff_longitude float = 0)
AS
BEGIN
  DECLARE @inquery nvarchar(max) = N'
  SELECT * FROM [dbo].[fnEngineerFeatures]( 
    @passenger_count,
    @trip_distance,
    @trip_time_in_secs,
    @pickup_latitude,
    @pickup_longitude,
    @dropoff_latitude,
    @dropoff_longitude)
    '
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;

# Load model and unserialize
mod = pickle.loads(model)

# Get features for scoring from input data
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)
probList = []
probList.append((mod.predict_proba(X)[0])[1])

# Create output data frame
OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,
    @trip_time_in_secs int ,
    @pickup_latitude float ,
    @pickup_longitude float ,
    @dropoff_latitude float ,
    @dropoff_longitude float',
    @model = @lmodel2,
    @passenger_count =@passenger_count ,
    @trip_distance=@trip_distance,
    @trip_time_in_secs=@trip_time_in_secs,
    @pickup_latitude=@pickup_latitude,
    @pickup_longitude=@pickup_longitude,
    @dropoff_latitude=@dropoff_latitude,
    @dropoff_longitude=@dropoff_longitude
WITH RESULT SETS ((Score float));
END
GO

PredictTipSingleModeRxPy

O procedimento PredictTipSingleModeRxPy armazenado a seguir executa a pontuação usando o modelo revoscalepy .

DROP PROCEDURE IF EXISTS PredictTipSingleModeRxPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSingleModeRxPy] (@model varchar(50), @passenger_count int = 0,
  @trip_distance float = 0,
  @trip_time_in_secs int = 0,
  @pickup_latitude float = 0,
  @pickup_longitude float = 0,
  @dropoff_latitude float = 0,
  @dropoff_longitude float = 0)
AS
BEGIN
DECLARE @inquery nvarchar(max) = N'
  SELECT * FROM [dbo].[fnEngineerFeatures]( 
    @passenger_count,
    @trip_distance,
    @trip_time_in_secs,
    @pickup_latitude,
    @pickup_longitude,
    @dropoff_latitude,
    @dropoff_longitude)
  '
DECLARE @lmodel2 varbinary(max) = (select model from nyc_taxi_models where name = @model);
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import pickle;
import numpy;
from revoscalepy.functions.RxPredict import rx_predict;

# Load model and unserialize
mod = pickle.loads(model)

# Get features for scoring from input data
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)

probArray = rx_predict(mod, X)

probList = []
probList = probArray["tipped_Pred"].values

# Create output data frame
OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max),@passenger_count int,@trip_distance float,
    @trip_time_in_secs int ,
    @pickup_latitude float ,
    @pickup_longitude float ,
    @dropoff_latitude float ,
    @dropoff_longitude float',
    @model = @lmodel2,
    @passenger_count =@passenger_count ,
    @trip_distance=@trip_distance,
    @trip_time_in_secs=@trip_time_in_secs,
    @pickup_latitude=@pickup_latitude,
    @pickup_longitude=@pickup_longitude,
    @dropoff_latitude=@dropoff_latitude,
    @dropoff_longitude=@dropoff_longitude
WITH RESULT SETS ((Score float));
END
GO

Gerar pontuações a partir de modelos

Depois que os procedimentos armazenados forem criados, é fácil gerar uma pontuação com base em qualquer um dos modelos. Abra uma nova janela Consulta e forneça parâmetros para cada uma das colunas de recursos.

Os sete valores necessários para essas colunas de recursos são, na ordem:

passenger_count
trip_distance
tempo_viagem_em_seg
latitude_de_recolha
pickup_longitude
dropoff_latitude
dropoff_longitude

Por exemplo:

Para gerar uma previsão usando o modelo revoscalepy , execute esta instrução:

EXEC [dbo].[PredictTipSingleModeRxPy] 'revoscalepy_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

Para gerar uma pontuação usando o modelo scikit-learn , execute esta instrução:

EXEC [dbo].[PredictTipSingleModeSciKitPy] 'SciKit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

A saída de ambos os procedimentos é a probabilidade de pagar uma gorjeta durante a viagem de táxi com os parâmetros especificados.

Conclusion

Nesta série de tutoriais, você aprendeu a trabalhar com código Python incorporado em procedimentos armazenados. A integração com o Transact-SQL torna muito mais fácil implantar modelos Python para previsão e incorporar o retreinamento de modelos como parte de um fluxo de trabalho de dados corporativos.

Próximos passos