Tutorial R: Executar previsões em procedimentos armazenados SQL

Aplica-se a: SQL Server 2016 (13.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 o modelo que você treinou e salvou na parte anterior usando o modelo para prever resultados potenciais. O modelo é envolvido em um procedimento armazenado que pode ser chamado diretamente por outros aplicativos.

Este artigo demonstra duas maneiras de executar a pontuação:

Modo de pontuação em lote: use uma consulta SELECT como entrada para o procedimento armazenado. O procedimento armazenado retorna uma tabela de observações correspondente aos casos de entrada.
Modo de pontuação individual: Passe um conjunto de valores de parâmetros individuais como entrada. O procedimento armazenado retorna uma única linha ou valor.

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ê revisou 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 básica

O procedimento armazenado RPredict ilustra a sintaxe básica para encapsular uma PREDICT chamada em um procedimento armazenado.

CREATE PROCEDURE [dbo].[RPredict] (@model varchar(250), @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'R',
  @script = N' 
    mod <- unserialize(as.raw(model));
    print(summary(mod))
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet)
    print(OutputDataSet)
    ',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max)',
  @model = @lmodel2 
  WITH RESULT SETS (("Score" float));
END
GO

A instrução SELECT obtém o modelo serializado do banco de dados e armazena o modelo na variável mod R para processamento posterior usando R.
Os novos casos para pontuação são obtidos a partir da consulta Transact-SQL especificada em @inquery, o primeiro parâmetro para o procedimento armazenado. À medida que os dados de consulta são lidos, as linhas são salvas no quadro de dados padrão, InputDataSet. Este quadro de dados é passado para a função PREDICT que gera as pontuações.

OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));

Como um data.frame pode conter uma única linha, você pode usar o mesmo código para pontuação em lote ou única.
O valor retornado pela PREDICT função é um float que representa a probabilidade de o condutor receber uma gorjeta de qualquer valor.

Pontuação em lote (uma lista de previsões)

Um cenário mais comum é gerar previsões para várias observações em modo batch. Nesta etapa, vamos ver como funciona a pontuação em lote.

Comece por obter um conjunto mais pequeno de dados de entrada para trabalhar. Esta consulta cria uma lista "top 10" de viagens com contagem de passageiros e outros recursos necessários para fazer uma previsão.

SELECT TOP 10 a.passenger_count AS passenger_count, a.trip_time_in_secs AS trip_time_in_secs, a.trip_distance AS trip_distance, a.dropoff_datetime AS dropoff_datetime, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude,dropoff_longitude) AS direct_distance

FROM (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample)a

LEFT OUTER JOIN

(SELECT medallion, hack_license, pickup_datetime FROM nyctaxi_sample TABLESAMPLE (70 percent) REPEATABLE (98052)    )b

ON a.medallion=b.medallion AND a.hack_license=b.hack_license 
AND a.pickup_datetime=b.pickup_datetime
WHERE b.medallion IS NULL

Resultados da amostra

passenger_count   trip_time_in_secs    trip_distance  dropoff_datetime          direct_distance
1                 283                  0.7            2013-03-27 14:54:50.000   0.5427964547
1                 289                  0.7            2013-02-24 12:55:29.000   0.3797099614
1                 214                  0.7            2013-06-26 13:28:10.000   0.6970098661

Crie um procedimento armazenado chamado RPredictBatchOutput no Management Studio.

CREATE PROCEDURE [dbo].[RPredictBatchOutput] (@model varchar(250), @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'R',
  @script = N'
    mod <- unserialize(as.raw(model));
    print(summary(mod))
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet)
    print(OutputDataSet)
  ',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max)',
  @model = @lmodel2
  WITH RESULT SETS ((Score float));
END

Forneça o texto da consulta em uma variável e passe-o como um parâmetro para o procedimento armazenado:

-- Define the input data
DECLARE @query_string nvarchar(max)
SET @query_string='SELECT TOP 10 a.passenger_count as passenger_count, a.trip_time_in_secs AS trip_time_in_secs, a.trip_distance AS trip_distance, a.dropoff_datetime AS dropoff_datetime, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude,dropoff_longitude) AS direct_distance FROM  (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample  )a   LEFT OUTER JOIN (SELECT medallion, hack_license, pickup_datetime FROM nyctaxi_sample TABLESAMPLE (70 percent) REPEATABLE (98052))b ON a.medallion=b.medallion AND a.hack_license=b.hack_license AND a.pickup_datetime=b.pickup_datetime WHERE b.medallion is null'

-- Call the stored procedure for scoring and pass the input data
EXEC [dbo].[RPredictBatchOutput] @model = 'RTrainLogit_model', @inquery = @query_string;

O procedimento armazenado retorna uma série de valores que representam a previsão para cada uma das 10 principais viagens. No entanto, as principais viagens também são viagens de um único passageiro com uma distância de viagem relativamente curta, para as quais é improvável que o motorista receba uma gorjeta.

Sugestão

Em vez de retornar apenas os resultados "sim-dica" e "não-dica", você também pode retornar a pontuação de probabilidade para a previsão e, em seguida, aplicar uma cláusula WHERE aos valores da coluna Pontuação para categorizar a pontuação como "provável de gorjeta" ou "improvável de gorjeta", usando um valor limite como 0,5 ou 0,7. Esta etapa não está incluída no procedimento armazenado, mas seria fácil de implementar.

Avaliação numa única linha de múltiplas entradas

Às vezes, você deseja passar vários valores de entrada e obter uma única previsão com base nesses valores. Por exemplo, você pode configurar uma planilha do Excel, um aplicativo Web ou um relatório do Reporting Services para chamar o procedimento armazenado e fornecer entradas digitadas ou selecionadas pelos usuários desses aplicativos.

Nesta seção, você aprenderá a criar previsões únicas usando um procedimento armazenado que usa várias entradas, como contagem de passageiros, distância da viagem e assim por diante. O procedimento armazenado cria uma pontuação com base no modelo R armazenado anteriormente.

Se você chamar o procedimento armazenado de um aplicativo externo, verifique se os dados correspondem aos requisitos do modelo R. Isso pode incluir garantir que os dados de entrada possam ser convertidos para um tipo de dados R ou a validação do tipo e tamanho dos dados.

Crie um procedimento armazenado RPredictSingleRow.

CREATE PROCEDURE [dbo].[RPredictSingleRow] @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'R',
  @script = N'  
    mod <- unserialize(as.raw(model));  
    print(summary(mod));  
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet);
    print(OutputDataSet); 
    ',  
  @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

Experimente, fornecendo os valores manualmente.

Abra uma nova janela Consulta e chame o procedimento armazenado, fornecendo valores para cada um dos parâmetros. Os parâmetros representam colunas de feição usadas pelo modelo e são obrigatórios.

EXEC [dbo].[RPredictSingleRow] @model = 'RTrainLogit_model',
@passenger_count = 1,
@trip_distance = 2.5,
@trip_time_in_secs = 631,
@pickup_latitude = 40.763958,
@pickup_longitude = -73.973373,
@dropoff_latitude =  40.782139,
@dropoff_longitude = -73.977303

Ou, use esta forma mais curta suportada para parâmetros de um procedimento armazenado:

EXEC [dbo].[RPredictSingleRow] 'RTrainLogit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

Os resultados indicam que a probabilidade de receber uma gorjeta é baixa (zero) nestas 10 viagens principais, uma vez que todas são viagens de um único passageiro numa distância relativamente curta.

Conclusões

Agora que você aprendeu a incorporar o código R em procedimentos armazenados, pode estender essas práticas para criar seus próprios modelos. A integração com Transact-SQL torna muito mais fácil implantar modelos R para previsão e incorporar o retreinamento de modelos como parte de um fluxo de trabalho de dados corporativos.

Próximos passos

Neste artigo, você:

Criação e uso de procedimentos armazenados para pontuação em lote
Criou e usou procedimentos armazenados para pontuar uma única linha

Para obter mais informações sobre R, consulte Extensão R no SQL Server.

Comentários

Esta página foi útil?

Last updated on 2025-10-31