Implementa o modelo R e usa-o no SQL Server (walkthrough)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores

Nesta lição, aprenda a implementar modelos R num ambiente de produção chamando um modelo treinado a partir de um procedimento armazenado. Pode invocar o procedimento armazenado do R ou de qualquer linguagem de programação de aplicações que suporte Transact-SQL (como C#, Java, Python, etc.) e usar o modelo para fazer previsões sobre novas observações.

Este artigo demonstra as duas formas mais comuns de usar um modelo na pontuação:

O modo de pontuação em lote gera múltiplas previsões
O modo de pontuação individual gera previsões uma a uma

Pontuação em lote

Crie um procedimento armazenado, PredictTipBatchMode, que gera múltiplas previsões, passando uma consulta SQL ou tabela como entrada. É devolvida uma tabela de resultados, que pode inserir diretamente numa tabela ou escrever num ficheiro.

Obtém um conjunto de dados de entrada como uma consulta SQL
Chama o modelo de regressão logística treinado que guardou na lição anterior
Prevê a probabilidade de o condutor receber uma gorjeta de valor superior a zero

No Management Studio, abra uma nova janela de consulta e execute o seguinte script T-SQL para criar o procedimento armazenado PredictTipBatchMode.
```
USE [NYCTaxi_Sample]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PredictTipBatchMode')
DROP PROCEDURE v
GO

CREATE PROCEDURE [dbo].[PredictTipBatchMode] @input nvarchar(max)
AS
BEGIN
  DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1 model  FROM nyc_taxi_models);
  EXEC sp_execute_external_script @language = N'R',
     @script = N'
       mod <- unserialize(as.raw(model));
       print(summary(mod))
       OutputDataSet<-rxPredict(modelObject = mod,
         data = InputDataSet,
         outData = NULL,
         predVarNames = "Score", type = "response",
         writeModelVars = FALSE, overwrite = TRUE);
       str(OutputDataSet)
       print(OutputDataSet)',
  @input_data_1 = @input,
  @params = N'@model varbinary(max)',
  @model = @lmodel2
  WITH RESULT SETS ((Score float));
END
```
- Utiliza-se uma instrução SELECT para chamar o modelo armazenado a partir de uma tabela SQL. O modelo é recuperado da tabela como dados varbinary(max), armazenado na variável SQL @lmodel2 e passado como o parâmetro mod para o procedimento armazenado do sistema sp_execute_external_script.
- Os dados usados como entradas para a pontuação são definidos como uma consulta SQL e armazenados como uma string na variável SQL @input. À medida que os dados são recuperados da base de dados, são armazenados num quadro de dados chamado InputDataSet, que é apenas o nome padrão para os dados de entrada do procedimento sp_execute_external_script ; Podes definir outro nome de variável, se necessário, usando o parâmetro @input_data_1_name.
- Para gerar as pontuações, o procedimento armazenado chama a função rxPredict da biblioteca RevoScaleR .
- O valor devolvido, Score, é a probabilidade, de acordo com o modelo, de o condutor receber uma gorjeta. Opcionalmente, podes facilmente aplicar algum tipo de filtro aos valores devolvidos para categorizar os valores de retorno em grupos "tip" e "sem tip". Por exemplo, uma probabilidade inferior a 0,5 significa que uma sugestão é pouco provável.

Para chamar o procedimento armazenado em modo batch, defines a consulta necessária como entrada para o procedimento armazenado. Abaixo está a consulta SQL, que pode executar no SSMS para verificar se funciona.

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

Use este código R para criar a cadeia de entrada a partir da consulta SQL:

input <- "N'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 (1 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'";
q <- paste("EXEC PredictTipBatchMode @input = ", input, sep="");

Para executar o procedimento armazenado a partir de R, chame o método sqlQuery do pacote RODBC e use a ligação conn SQL que definiu anteriormente:
```
sqlQuery (conn, q);
```
Se aparecer um erro ODBC, verifique se há erros de sintaxe e se tem o número correto de aspas.

Se receber um erro de permissões, certifique-se de que o login tem capacidade para executar o procedimento armazenado.

Classificação numa única linha

O modo de pontuação individual gera previsões uma de cada vez, passando um conjunto de valores individuais ao procedimento armazenado como entrada. Os valores correspondem a características no modelo, que o modelo utiliza para criar uma previsão ou gerar outro resultado, como um valor de probabilidade. Pode então devolver esse valor à aplicação ou ao utilizador.

Ao chamar o modelo para previsão linha a linha, passa um conjunto de valores que representam características para cada caso individual. O procedimento armazenado devolve então uma única previsão ou probabilidade.

O procedimento armazenado PredictTipSingleMode demonstra esta abordagem. Recebe como entrada múltiplos parâmetros que representam valores de características (por exemplo, número de passageiros e distância da viagem), pontua essas características usando o modelo R armazenado e produz a probabilidade da ponta.

Execute a seguinte Transact-SQL instrução para criar o procedimento armazenado.

USE [NYCTaxi_Sample]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PredictTipSingleMode')
DROP PROCEDURE v
GO

CREATE PROCEDURE [dbo].[PredictTipSingleMode] @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 TOP 1 model FROM nyc_taxi_models);

  EXEC sp_execute_external_script @language = N'R',  @script = N'
        mod <- unserialize(as.raw(model));
        print(summary(mod))
        OutputDataSet<-rxPredict(
          modelObject = mod,
          data = InputDataSet,
          outData = NULL,
          predVarNames = "Score",
          type = "response",
          writeModelVars = FALSE,
          overwrite = TRUE);
        str(OutputDataSet)
        print(OutputDataSet)
        ',
  @input_data_1 = @inquery,
  @params = N'
  -- passthrough columns
  @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',
  -- mapped variables
  @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

No SQL Server Management Studio, pode usar o procedimento Transact-SQL EXEC (ou EXECUTE) para chamar o procedimento armazenado e passar-lhe as entradas necessárias. Por exemplo, tente executar esta instrução no Management Studio:
```
EXEC [dbo].[PredictTipSingleMode] 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
```
Os valores aqui passados são, respetivamente, para as variáveis passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude e dropoff_longitude.
Para executar esta mesma chamada a partir de código R, basta definir uma variável R que contém toda a chamada ao procedimento armazenado, como esta:
```
q2 = "EXEC PredictTipSingleMode 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303 ";
```
Os valores aqui passados são, respetivamente, para as variáveis passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude e dropoff_longitude.
Chame sqlQuery (do pacote RODBC) e passe a cadeia de ligação, juntamente com a variável de cadeia de caracteres que contém a chamada ao procedimento armazenado.
```
# predict with stored procedure in single mode
sqlQuery (conn, q2);
```
Sugestão

O R Tools for Visual Studio (RTVS) oferece uma excelente integração tanto com SQL Server como com R. Consulte este artigo para mais exemplos de utilização do RODBC com uma ligação ao SQL Server: Trabalhar com SQL Server e R

Comentários

Esta página foi útil?

Última atualização a 2026-08-24

Implementa o modelo R e usa-o no SQL Server (walkthrough)

Pontuação em lote

Classificação numa única linha

Conteúdo relacionado

Comentários

Recursos adicionais