Constrói um modelo R e guarda no SQL Server (guia)

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

Neste passo, aprenda a construir um modelo de aprendizagem automática e a guardar o modelo no SQL Server. Ao guardar um modelo, pode chamá-lo diretamente a partir do código Transact-SQL, utilizando o procedimento armazenado do sistema sp_execute_external_script ou a função PREDICT (T-SQL).

Pré-requisitos

Esta etapa pressupõe uma sessão R contínua com base nas etapas anteriores neste passo a passo. Ele usa as cadeias de conexão e os objetos de fonte de dados criados nessas etapas. As seguintes ferramentas e pacotes são usados para executar o script.

• Rgui.exe executar comandos R
• Management Studio para executar T-SQL
• Pacote ROCR
• Pacote RODBC

Criar um procedimento armazenado para guardar modelos

Esta etapa utiliza um procedimento armazenado para guardar um modelo treinado no SQL Server. Criar um procedimento armazenado para realizar esta operação torna a tarefa mais fácil.

Execute o seguinte código T-SQL numa janela de consulta no Management Studio 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 = 'PersistModel')
  DROP PROCEDURE PersistModel
GO

CREATE PROCEDURE [dbo].[PersistModel] @m nvarchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	insert into nyc_taxi_models (model) values (convert(varbinary(max),@m,2))
END
GO

Crie um modelo de classificação usando rxLogit

O modelo é um classificador binário que prevê se o taxista provavelmente receberá uma gorjeta numa determinada viagem ou não. Vais usar a fonte de dados que criaste na lição anterior para treinar o classificador de dicas, usando regressão logística.

1. Chame a função rxLogit , incluída no pacote RevoScaleR , para criar um modelo de regressão logística.

   system.time(logitObj <- rxLogit(tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance, data = featureDataSource));
   

   A chamada que constrói o modelo está incluída na função system.time. Isto permite-lhe obter o tempo necessário para construir o modelo.

2. Depois de construir o modelo, pode inspecioná-lo usando a summary função e visualizar os coeficientes.

   summary(logitObj);
   

   Results

    *Logistic Regression Results for: tipped ~ passenger_count + trip_distance + trip_time_in_secs +*
    direct_distance* 
    *Data: featureDataSource (RxSqlServerData Data Source)*
    *Dependent variable(s): tipped*
    *Total independent variables: 5*
    *Number of valid observations: 17068*
    *Number of missing observations: 0*
    *-2\*LogLikelihood: 23540.0602 (Residual deviance on 17063 degrees of freedom)*
    *Coefficients:*
    *Estimate Std. Error z value Pr(>|z|)*
    *(Intercept)       -2.509e-03  3.223e-02  -0.078  0.93793*
    *passenger_count   -5.753e-02  1.088e-02  -5.289 1.23e-07 \*\*\**
    *trip_distance     -3.896e-02  1.466e-02  -2.658  0.00786 \*\**
    *trip_time_in_secs  2.115e-04  4.336e-05   4.878 1.07e-06 \*\*\**
    *direct_distance    6.156e-02  2.076e-02   2.966  0.00302 \*\**
    *---*
    *Signif. codes:  0 '\*\*\*' 0.001 '\*\*' 0.01 '\*' 0.05 '.' 0.1 ' ' 1*
    *Condition number of final variance-covariance matrix: 48.3933*
    *Number of iterations: 4*
   

Utilizar o modelo de regressão logística para pontuação

Agora que o modelo foi criado, pode usá-lo para prever se é provável que o condutor receba uma gorjeta numa determinada viagem ou não.

1. Primeiro, use a função RxSqlServerData para definir um objeto fonte de dados para armazenar o resultado da pontuação.

   scoredOutput <- RxSqlServerData(
     connectionString = connStr,
     table = "taxiScoreOutput"  )
   

   • Para simplificar este exemplo, a entrada do modelo de regressão logística é a mesma fonte de dados de características (sql_feature_ds) que usaste para treinar o modelo. Mais frequentemente, pode ter novos dados para pontuar, ou pode ter reservado alguns dados para testar em vez de treinar.

   • Os resultados da previsão serão guardados na tabela, taxiscoreOutput. Note que o esquema desta tabela não está definido quando a cria usando rxSqlServerData. O esquema é obtido a partir da saída rxPredict.

   • Para criar a tabela que armazena os valores previstos, o login SQL que executa a função de dados rxSqlServer deve ter privilégios DDL na base de dados. Se o login não conseguir criar tabelas, a instrução falha.

2. Chame a função rxPredict para gerar resultados.

   rxPredict(modelObject = logitObj,
       data = featureDataSource,
       outData = scoredOutput,
       predVarNames = "Score",
       type = "response",
       writeModelVars = TRUE, overwrite = TRUE)
   

   Se a declaração tiver sucesso, deverá demorar algum tempo a ser executada. Quando estiver concluído, pode abrir o SQL Server Management Studio e verificar se a tabela foi criada e que contém a coluna Score e outros resultados esperados.

Precisão do modelo gráfico

Para ter uma ideia da precisão do modelo, pode usar a função rxRoc para representar a Curva de Funcionamento do Recetor. Como o rxRoc é uma das novas funções fornecidas pelo pacote RevoScaleR que suporta contextos de computação remota, tens duas opções:

• Podes usar a função rxRoc para executar o gráfico no contexto de computação remota e depois devolver o gráfico ao teu cliente local.

• Também pode importar os dados para o seu computador cliente R e usar outras funções de plotamento R para criar o gráfico de desempenho.

Nesta secção, vais experimentar ambas as técnicas.

Executar um gráfico no contexto computacional remoto (SQL Server)

1. Chame a função rxRoc e forneça os dados definidos anteriormente como entrada.

   scoredOutput = rxImport(scoredOutput);
   rxRoc(actualVarName= "tipped", predVarNames = "Score", scoredOutput);
   

   Esta chamada devolve os valores usados no cálculo do gráfico ROC. A coluna do rótulo é tipada, que tem os resultados reais que está a tentar prever, enquanto a coluna de Pontuação tem a previsão.

2. Para realmente representar o gráfico, podes guardar o objeto ROC e depois desenhá-lo com a função plot. O grafo é criado no contexto de computação remota e devolvido ao seu ambiente R.

   scoredOutput = rxImport(scoredOutput);
   rocObjectOut <- rxRoc(actualVarName= "tipped", predVarNames = "Score", scoredOutput);
   plot(rocObjectOut);
   

   Veja o gráfico abrindo o dispositivo gráfico R, ou clicando na janela Plotar no RStudio.

   

Crie os gráficos no contexto de computação local usando dados do SQL Server

Pode verificar se o contexto de computação é local correndo rxGetComputeContext() no prompt de comandos. O valor de retorno deve ser "RxLocalSeq Compute Context".

1. No contexto de computação local, o processo é muito semelhante. Usas a função rxImport para trazer os dados especificados para o teu ambiente R local.

   scoredOutput = rxImport(scoredOutput)
   

2. Usando os dados na memória local, carregas o pacote ROCR e usas a função de previsão desse pacote para criar algumas novas previsões.

   library('ROCR');
   pred <- prediction(scoredOutput$Score, scoredOutput$tipped);
   

3. Gerar um gráfico local, com base nos valores armazenados na variável predde saída .

   acc.perf = performance(pred, measure = 'acc');
   plot(acc.perf);
   ind = which.max( slot(acc.perf, 'y.values')[[1]] );
   acc = slot(acc.perf, 'y.values')[[1]][ind];
   cutoff = slot(acc.perf, 'x.values')[[1]][ind];
   

   

Implementar o modelo

Depois de ter construído um modelo e confirmado que está a ter um bom desempenho, provavelmente vai querer implementá-lo num site onde os utilizadores ou pessoas da sua organização possam utilizar o modelo, ou talvez reeducar e recalibrar o modelo regularmente. Este processo é por vezes chamado de operacionalização de um modelo. No SQL Server, a operacionalização é conseguida incorporando código R num procedimento armazenado. Como o código reside no procedimento, pode ser chamado a partir de qualquer aplicação que se ligue ao SQL Server.

Antes de poder chamar o modelo a partir de uma aplicação externa, deve guardar o modelo na base de dados usada para produção. Os modelos treinados são armazenados em forma binária, numa única coluna do tipo varbinary(max).

Um fluxo de trabalho típico de implementação consiste nos seguintes passos:

1. Serializar o modelo numa cadeia hexadecimal
2. Transmitir o objeto serializado para a base de dados
3. Guardar o modelo numa coluna varbinary(max)

Nesta secção, aprenda a usar um procedimento armazenado para persistir o modelo e torná-lo disponível para previsões. O procedimento armazenado utilizado nesta secção é o PersistModel. A definição de PersistModel está em Pré-requisitos.

1. Volta ao teu ambiente R local se ainda não o estiveres a usar, serializa o modelo e guarda-o numa variável.

   rxSetComputeContext("local");
   modelbin <- serialize(logitObj, NULL);
   modelbinstr=paste(modelbin, collapse="");
   

2. Abra uma ligação ODBC usando RODBC. Podes omitir a chamada para o RODBC se já tiveres o pacote carregado.

   library(RODBC);
   conn <- odbcDriverConnect(connStr);
   

3. Chame o procedimento armazenado PersistModel no SQL Server para transmitir o objeto serializado para a base de dados e armazenar a representação binária do modelo numa coluna.

   q <- paste("EXEC PersistModel @m='", modelbinstr,"'", sep="");
   sqlQuery (conn, q);
   

4. Use o Management Studio para verificar se o modelo existe. No Object Explorer, clique com o botão direito do rato na tabela nyc_taxi_models e clique em Selecionar as 1000 Linhas Principais. Em Resultados, deverá ver uma representação binária na coluna de modelos .

Guardar um modelo numa tabela requer apenas uma INSERT instrução. No entanto, muitas vezes é mais fácil quando integrado num procedimento armazenado, como o PersistModel.

Passos seguintes

Na próxima e última lição, aprenda a fazer pontuação contra o modelo guardado usando Transact-SQL.