Olá pessoal! Neste post vou falar um pouco sobre o recurso R Services, que pertence à plataforma Microsoft SQL Server. Em breve uma versão similar deste artigo, porém um pouco mais completa do ponto de vista teórico, será publicada na revista SQL Magazine…vou compartilhar o link assim que tê-lo em mãos (aos assinantes). Agora, vamos para a nossa aventura em um dos mais recentes recursos de analytics da Microsoft! Na minha opinião, vale muito a pena testá-lo.

R Services

A versão 2016 do SQL Server já permite a criação de procedimentos usando código T-SQL e R. A grande vantagem é que temos uma integração mais simples entre o local em que os dados estão armazenados (database engine) e a plataforma na qual os mesmos serão analisados (analytics environment).

O nome deste novo recurso do SQL Server é conhecido como R Services e faz parte da linha de iniciativas da Microsoft focada em análise de dados avançada. O fato da linguagem de programação R ser bastante utilizada por analistas de dados, assim como ter uma comunidade open source muito ativa, faz com que o R Services já comece com bons recursos para análise de dados.

Outro ponto interessante com esta integração entre SQL Server e R é a maior possibilidade de se utilizar R em ambientes de produção. É comum encontrarmos projetos em que R é utilizado durante a fase de prototipação e outras linguagens (como Python) entram em ação para implementar a análise nos chamados data products. Cada vez mais a linguagem R vem se adaptando a este novo cenário, sendo que o R Services é mais uma opção para utilização do R em sistemas de grande escala.

Uma das limitações de se utilizar R isoladamente é seu desempenho, pois a linguagem requer que os dados sejam carregados inteiramente na memória disponível. Também é difícil de se trabalhar com processamento paralelo. Estes dois problemas podem ser resolvidos com R Services. Vale a pena destacar neste ponto que o pacote RevoScaleR da linguagem R já está integrado com o SQL Server e pode ser usado para criar aplicações de grande escala.

Instalando as ferramentas necessárias

Para utilizar o R Services é necessário instalar a versão Enterprise ou Developer do SQL Server 2016. Durante a instalação ative os recursos relacionados ao serviço em questão. Para seguir este tutorial também é necessário instalar o próprio R, uma IDE para desenvolvimento dos nossos scripts em R e o Microsoft R Client.

Faça download do R no site https://cran.r-project.org. A IDE de desenvolvimento escolhida neste tutorial foi o R Studio, disponível para download em https://www.rstudio.com. Já o Microsoft R Client pode ser encontrado no site https://msdn.microsoft.com. Instale todos os softwares para seguir este artigo.

Não se esqueça de ter o SQL Server Management Studio (SSMS) instalado na sua máquina para a execução de scripts SQL e T-SQL.

Configurando o R Services

Acesse o SSMS, crie uma nova consulta e execute o seguinte comando para habilitar a execução de scripts externos:

Exec sp_configure  ‘external scripts enabled’, 1
Reconfigure with override

Em seguida reinicie o serviço do SQL Server e garanta que o serviço do Launchpad também esteja em execução. Você pode realizar estas tarefas por meio do gerenciador de serviços do seu sistema operacional. O Launchpad é o serviço responsável pela integração entre comandos R e T-SQL.

Para verificar se a execução de scripts externos foi habilitada execute o seguinte comando dentro de uma nova consulta no SSMS:

Exec sp_configure  ‘external scripts enabled’, 1
Reconfigure with override

Se estiver tudo certo o comando retornará o valor 1 para o parâmetro run_value, conforme apresentado na figura abaixo.

figura_rservices_1

Execute o comando a seguir para verificar se já é possível executar programas R dentro da sua instância do SQL Server:

exec sp_execute_external_script  @language =N’R’,
@script=N’OutputDataSet<-InputDataSet’,
@input_data_1 =N’select ”Testando Input e Output com R” as coluna’
with result sets (([coluna] varchar(max) not null));
Go

Se você receber a mensagem “Testando Input e Output com R”, então você já está conseguindo executar código R dentro do SQL Server!

Configurando o R Cliente em sua IDE de desenvolvimento em R

Até este momento já instalamos as ferramentas e habilitamos o R Services. Agora vamos fazer com que o nossa IDE utilize o R Client para executar nossos programas desenvolvidos em R.

Siga os seguintes passos:

  1. Abra o programa R Studio.
  2. Acesse o menu Ferramentas (Tools) -> Opções globais (Global options). Na guia Geral (General) mude o caminho (Path) para C:\Program Files\Microsoft\R Client\R_SERVER\bin\x64

Agora o R Cliente será utilizado como mecanismo padrão para a execução de scripts R dentro do R Studio.

Criando um banco de dados para gravar o modelo e os dados de treinamento

O último passo antes de começarmos a criar o modelo de aprendizado de máquina é a criação de um banco de dados para armazenar nosso modelo e os dados que serão utilizados para treinar o mesmo.

Crie um novo banco de dados na sua instância do SQL Server e atribua o nome de bdmodelagem. Em seguida crie uma tabela chamada tb_modelos com a configuração a seguir.

USE bdmodelagem;
GO
DROP TABLE IF EXISTS tb_modelos;
GO
CREATE TABLE tb_modelos (
cod_modelo INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
nome VARCHAR(30) NOT NULL,
modelo VARBINARY(MAX) NOT NULL);
GO

Esta tabela será usada para armazenamento dos nossos modelos de aprendizado de máquina. Os mesmos serão armazenados no formato binário, por meio da coluna “modelo”.

Agora faça download dos dados de treino que serão utilizados neste tutorial no seguinte link: https://archive.ics.uci.edu/ml/machine-learning-databases/pima-indians-diabetes.

Este é um conjunto de dados público conhecido como PimaIndiansDiabetes e utilizado em problemas de classificação. Estes dados são mantidos e disponibilizados pelo Center for Machine Learning and Intelligent Systems, da universidade da Califórnia.

O conjunto de dados PimaIndiansDiabetes classifica a presença de diabetes em um grupo de índios de acordo com alguns detalhes médicos. Crie uma tabela no SQL Server chamada PimaIndiansDiabetes com colunas abaixo.

USE bdmodelagem;
GO
DROP TABLE IF EXISTS PimaIndiansDiabetes;
GO
CREATE TABLE PimaIndiansDiabetes(
pregnant NUMERIC(19, 0) NULL,
glucose NUMERIC (19, 0) NULL,
pressure NUMERIC (19, 0) NULL,
triceps NUMERIC (19, 0) NULL,
insulin NUMERIC (19, 0) NULL,
mass NUMERIC (19, 3) NULL,
pedigree NUMERIC (19, 3) NULL,
age NUMERIC (19, 0) NULL,
diabetes VARCHAR(50) NULL);
GO

Em seguida importe os dados do arquivo fornecido para esta tabela. Você pode utilizar o utilitário de importação do SQL Server para realizar esta tarefa.

Criando nosso modelo de aprendizado de máquina

Agora que temos tudo instalado e configurado podemos desenvolver o nosso modelo de aprendizado de máquina. Na prática, o processo para criação de um modelo exige muitos testes e análise exploratória dos dados. Sendo assim, podemos utilizar a IDE de desenvolvimento R Studio para explorar os dados, treinar e validar o modelo. Quando finalizarmos, podemos operacionalizar o trabalho no R Services.

O código a seguir treina um modelo de árvore de decisão no R Studio e retorna algumas métricas para avaliação da qualidade do modelo. Para simplificar o código, não foram utilizadas técnicas de modelagem como validação cruzada e otimização de parâmetros.

# Instala os pacotes
install.packages(“mlbench”)
install.packages(“caret”)
install.packages(“e1071”)
# Carrega os pacotes
library (mlbench)
library(caret)
library (e1071)
# Carrega os dados
data(“PimaIndiansDiabetes”)
head(PimaIndiansDiabetes)
# Divide o dataset em 80% para treino e 20% para teste
trainIndex <- createDataPartition(PimaIndiansDiabetes$diabetes, p=0.80, list=FALSE)
dataTrain <- PimaIndiansDiabetes[ trainIndex,]
dataTest <- PimaIndiansDiabetes[-trainIndex,]
# Treina o modelo
tree <- rxDTree(diabetes ~ pregnant+glucose+pressure+triceps+insulin+mass+pedigree+age, data = PimaIndiansDiabetes);
# Executa o modelo nos dados de teste
resultado = rxPredict(tree, dataTest, writeModelVars = TRUE);
head(resultado)
# Verifica o resultado nos dados de teste
confusionMatrix(ifelse (resultado$pos_prob>0.5, 1, 0), ifelse(dataTest$diabetes == ‘pos’, 1, 0))

Note que o modelo consegue acertar 80,39% dos casos, conforme apresentado a seguir.

figura_rservices_2

Operacionalizando o modelo no R Services

Agora que já sabemos a configuração final do nosso modelo podemos usar o R Services para operacionalizar a solução final. O primeiro passo será criar a stored procedure usp_gera_modelo. Ela será responsável por treinar o modelo, serializá-lo e retorná-lo em formato binário.

DROP PROCEDURE IF EXISTS usp_gera_modelo;
GO
CREATE PROCEDURE usp_gera_modelo (@modelo VARBINARY(MAX) OUTPUT)
AS
BEGIN
EXECUTE sp_execute_external_script
@language = N’R’
,@script = N’
require(“RevoScaleR”);
#Cria o modelo de arvore de decisão
tree <- rxDTree(diabetes ~ pregnant+glucose+pressure+triceps+insulin+mass+pedigree+age, data = PimaIndiansDiabetes);
#Serializa o modelo
modelo <- as.raw(serialize(tree, connection=NULL));’
,@input_data_1 = N’select pregnant, glucose, pressure, triceps, insulin, mass, pedigree, age, diabetes FROM PimaIndiansDiabetes’
,@input_data_1_name = N’PimaIndiansDiabetes’
,@params = N’@modelo VARBINARY(MAX) OUTPUT’
,@modelo = @modelo OUTPUT;
END;
GO

Em seguida vamos executar a procedure que acabou de ser criada e armazenar seu resultado na tabela tb_modelos.

DECLARE @modelo VARBINARY(MAX);
EXEC usp_gera_modelo @modelo OUTPUT;
INSERT INTO tb_modelos (nome, modelo) VALUES(‘tree’, @modelo);
GO

Finalmente criaremos uma stored procedure que carrega e executa o modelo contra um determinado conjunto de dados. Neste caso, a procedure será responsável prever se uma pessoa (dada as condições recebidas como entrada) será diagnosticada com diabetes ou não.

DROP PROCEDURE IF EXISTS usp_predict_diabetes;
GOCREATE PROCEDURE usp_predict_diabetes (
@m VARCHAR(100),@q NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @modelo VARBINARY(MAX) = (SELECT modelo FROM tb_modelos WHERE nome = @m);
EXECUTE sp_execute_external_script
@language = N’R’
,@script = N’
require(“RevoScaleR”);
novosDados = InputDataSet;
modeloFinal = unserialize(modelo);
resultado = rxPredict(modeloFinal, novosDados);’,@input_data_1 = @q
,@output_data_1_name = N’resultado’
,@params = N’@modelo VARBINARY(MAX)’
,@modelo = @modelo
;END;
GO

Agora estamos prontos para enviar informações ao modelo e receber as predições. O código abaixo envia um exemplo para análise.

EXEC dbo.usp_predict_diabetes @m = ‘tree’,
@q =’SELECT CONVERT(NUMERIC(19,0), 6) AS pregnant,
CONVERT(NUMERIC(19,0), 148) AS glucose,
CONVERT(NUMERIC(19,0), 72) AS pressure,
CONVERT(NUMERIC(19,0), 35) AS triceps,
CONVERT(NUMERIC(19,0), 0) AS insulin,
CONVERT(NUMERIC(19,3), 33.6) AS mass,
CONVERT(NUMERIC(19,3), 30.627) AS pedigree,
CONVERT(NUMERIC(19,0), 50) AS age’;
GO

Abraços!
Weslley Moura

 

Anúncios