Tutorial R: Explorar e visualizar dados

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

Na segunda parte desta série de tutoriais de cinco partes, você explorará os dados de exemplo e gerará alguns gráficos. Mais tarde, você aprenderá como serializar objetos gráficos em Python e, em seguida, desserializar esses objetos e criar gráficos.

Na segunda parte desta série de tutoriais de cinco partes, irá rever os dados de exemplo e, em seguida, gerar alguns gráficos usando as funções genéricas barplot e hist no R base.

Um dos principais objetivos deste artigo é mostrar como chamar funções R de Transact-SQL em procedimentos armazenados e salvar os resultados em formatos de arquivo de aplicativo:

• Crie um procedimento armazenado usando barplot para gerar um gráfico em R como dados varbinary. Use bcp para exportar o fluxo binário para um arquivo de imagem.
• Crie um procedimento armazenado usando hist para gerar um gráfico, salvando os resultados como saída JPG e PDF.

Neste artigo, você:

Na primeira parte, você instalou os pré-requisitos e restaurou o banco de dados de exemplo.

Na terceira parte, você aprenderá a criar recursos a partir de dados brutos usando uma função Transact-SQL. Em seguida, você chamará essa função a partir de um procedimento armazenado para criar uma tabela que contenha os valores do recurso.

Na quarta parte, você carregará os módulos e chamará as funções necessárias para criar e treinar o modelo usando um procedimento armazenado do SQL Server.

Na quinta parte, você aprenderá a operacionalizar os modelos que treinou e salvou na quarta parte.

Revisar os dados

O desenvolvimento de uma solução de ciência de dados geralmente inclui exploração intensiva de dados e visualização de dados. Portanto, primeiro reserve um minuto para analisar os dados de exemplo, se ainda não o fez.

No conjunto de dados público original, os identificadores de táxi e os registos de viagem eram fornecidos em ficheiros separados. No entanto, para tornar os dados de exemplo mais fáceis de usar, os dois conjuntos de dados originais foram unidos nas colunas medalhão, hack_license e pickup_datetime. Os registros também foram amostrados para obter apenas 1% do número original de registros. O conjunto de dados resultante da amostra reduzida tem 1.703.957 linhas e 23 colunas.

Identificadores de táxi

• A coluna medalhão representa o número de identificação único do táxi.

• A coluna hack_license contém o número da carteira de motorista de táxi (anonimizado).

Registos de viagens e tarifas

• Cada registro de viagem inclui o local e o horário de embarque e desembarque e a distância da viagem.

• Cada registro de tarifa inclui informações de pagamento, como o tipo de pagamento, o valor total do pagamento e o valor da gorjeta.

• As três últimas colunas podem ser usadas para várias tarefas de aprendizado de máquina. A coluna tip_amount contém valores numéricos contínuos e pode ser usada como coluna de rótulo para análise de regressão. A coluna inclinada tem apenas valores sim/não e é usada para classificação binária. A coluna tip_class tem vários rótulos de classe e, portanto, pode ser usada como o rótulo para tarefas de classificação de várias classes.

  Este passo a passo demonstra apenas a tarefa de classificação binária; Você está convidado a tentar criar modelos para as outras duas tarefas de aprendizado de máquina, regressão e classificação multiclasse.

• Os valores usados para as colunas de rótulo são todos baseados na coluna tip_amount , usando estas regras de negócios:

  Nome da coluna derivada	Regra
  inclinado	Se tip_amount > 0, marcado = 1, caso contrário marcado = 0
  tip_class	Classe 0: tip_amount = $0 Classe 1: tip_amount > $0 e tip_amount <= $5 Classe 2: tip_amount > 5 USD e tip_amount < = 10 USD Classe 3: tip_amount > $10 e tip_amount <= $20 Classe 4: tip_amount > $20

Criar gráficos usando R em T-SQL

Para criar o gráfico, use a função barplotR . Esta etapa plota um histograma com base em dados de uma consulta Transact-SQL. Você pode encapsular essa função em um procedimento armazenado, RPlotHistogram.

1. No SQL Server Management Studio, no Pesquisador de Objetos, clique com o botão direito do mouse no banco de dados NYCTaxi_Sample e selecione Nova Consulta. Ou, no Azure Data Studio, selecione Novo Bloco de Anotações no menu Arquivo e conecte-se ao banco de dados.

2. Cole o seguinte script para criar um procedimento armazenado que desenha o histograma. Este exemplo é chamado RPlotHistogram.

   CREATE PROCEDURE [dbo].[RPlotHistogram]
   AS
   BEGIN
     SET NOCOUNT ON;
     DECLARE @query nvarchar(max) =  
     N'SELECT tipped FROM [dbo].[nyctaxi_sample]'  
     EXECUTE sp_execute_external_script @language = N'R',  
                                        @script = N'  
      image_file = tempfile();  
      jpeg(filename = image_file);  
      #Plot histogram  
      barplot(table(InputDataSet$tipped), main = "Tip Histogram", col="lightgreen", xlab="Tipped or not", ylab = "Counts", space=0)
      dev.off();  
      OutputDataSet <- data.frame(data=readBin(file(image_file, "rb"), what=raw(), n=1e6));  
      ',  
      @input_data_1 = @query  
      WITH RESULT SETS ((plot varbinary(max)));  
   END
   GO
   

Os principais pontos a serem compreendidos neste script incluem o seguinte:

• A variável @query define o texto da consulta ('SELECT tipped FROM nyctaxi_sample'), que é passado para o script R como o argumento para a variável de entrada do script, @input_data_1. Para scripts R executados como processos externos, deve-se estabelecer um mapeamento um-para-um entre as entradas do seu script e as entradas para o procedimento do sistema armazenado sp_execute_external_script que dá início a uma sessão R no SQL Server.

• Dentro do script R, uma variável (image_file) é definida para armazenar a imagem.

• A barplot função é chamada para gerar o gráfico.

• O dispositivo R está definido como desativado porque você está executando esse comando como um script externo no SQL Server. Normalmente, em R, quando você emite um comando de plotagem de alto nível, R abre uma janela gráfica, chamada de dispositivo. Você pode desligar o dispositivo se estiver gravando em um arquivo ou manipulando a saída de outra maneira.

• O objeto gráfico do R é serializado em um data.frame do R para ser usado na saída.

Execute o procedimento armazenado e use bcp para exportar dados binários para um arquivo de imagem

O procedimento armazenado retorna a imagem como um fluxo de dados varbinary, que obviamente não é possível visualizar diretamente. No entanto, você pode usar o utilitário bcp para obter os dados varbinary e salvá-los como um arquivo de imagem em um computador cliente.

1. No Management Studio, execute a seguinte instrução:

   EXEC [dbo].[RPlotHistogram]
   

   Results

   plot0xFFD8FFE000104A4649...

2. Abra um prompt de comando do PowerShell e execute o seguinte comando, fornecendo o nome da instância apropriado, o nome do banco de dados, o nome de usuário e as credenciais como argumentos. Para aqueles que usam identidades do Windows, você pode substituir -U e -P por -T.

   bcp "exec RPlotHistogram" queryout "plot.jpg" -S <SQL Server instance name> -d  NYCTaxi_Sample  -U <user name> -P <password> -T
   

   Observação

   As opções de comando do bcp são sensíveis a maiúsculas e minúsculas.

3. Se a conexão for bem-sucedida, você será solicitado a inserir mais informações sobre o formato de arquivo gráfico.

   Pressione ENTER em cada prompt para aceitar os padrões, exceto para estas alterações:

   • Para prefixo-comprimento do gráfico de campo, digite 0.

   • Digite Y se quiser salvar os parâmetros de saída para reutilização posterior.

   Enter the file storage type of field plot [varbinary(max)]: 
   Enter prefix-length of field plot [8]: 0
   Enter length of field plot [0]:
   Enter field terminator [none]:
   
   Do you want to save this format information in a file? [Y/n]
   Host filename [bcp.fmt]:
   

   Results

   Starting copy...
   1 rows copied.
   Network packet size (bytes): 4096
   Clock Time (ms.) Total     : 3922   Average : (0.25 rows per sec.)
   

   Sugestão

   Se você salvar as informações de formato no arquivo (bcp.fmt), o utilitário bcp gerará uma definição de formato que poderá ser aplicada a comandos semelhantes no futuro sem que sejam solicitadas opções de formato de arquivo gráfico. Para usar o arquivo de formato, adicione -f bcp.fmt ao final de qualquer linha de comando, após o argumento password.

4. O arquivo de saída será criado no mesmo diretório onde você executou o comando PowerShell. Para visualizar o gráfico, basta abrir o arquivo plot.jpg.

   

Criar um procedimento armazenado usando hist

Normalmente, os cientistas de dados geram várias visualizações de dados para obter informações sobre os dados de diferentes perspetivas. Neste exemplo, você criará um procedimento armazenado chamado RPlotHist para gravar histogramas, gráficos de dispersão e outros gráficos R no formato .JPG e .PDF.

Este procedimento armazenado usa a hist função para criar o histograma, exportando os dados binários para formatos populares como .JPG, .PDF e .PNG.

1. No SQL Server Management Studio, no Pesquisador de Objetos, clique com o botão direito do mouse no banco de dados NYCTaxi_Sample e selecione Nova Consulta.

2. Cole o seguinte script para criar um procedimento armazenado que traça o histograma. Este exemplo é chamado RPlotHist .

   CREATE PROCEDURE [dbo].[RPlotHist]  
   AS  
   BEGIN  
     SET NOCOUNT ON;  
     DECLARE @query nvarchar(max) =  
     N'SELECT cast(tipped as int) as tipped, tip_amount, fare_amount FROM [dbo].[nyctaxi_sample]'  
     EXECUTE sp_execute_external_script @language = N'R',  
     @script = N'  
      # Set output directory for files and check for existing files with same names   
       mainDir <- ''C:\\temp\\plots''  
       dir.create(mainDir, recursive = TRUE, showWarnings = FALSE)  
       setwd(mainDir);  
       print("Creating output plot files:", quote=FALSE)
   
       # Open a jpeg file and output histogram of tipped variable in that file.  
       dest_filename = tempfile(pattern = ''rHistogram_Tipped_'', tmpdir = mainDir)  
       dest_filename = paste(dest_filename, ''.jpg'',sep="")  
       print(dest_filename, quote=FALSE);  
       jpeg(filename=dest_filename);  
       hist(InputDataSet$tipped, col = ''lightgreen'', xlab=''Tipped'',   
           ylab = ''Counts'', main = ''Histogram, Tipped'');  
        dev.off();  
   
       # Open a pdf file and output histograms of tip amount and fare amount.   
       # Outputs two plots in one row  
       dest_filename = tempfile(pattern = ''rHistograms_Tip_and_Fare_Amount_'', tmpdir = mainDir)  
       dest_filename = paste(dest_filename, ''.pdf'',sep="")  
       print(dest_filename, quote=FALSE);  
       pdf(file=dest_filename, height=4, width=7);  
       par(mfrow=c(1,2));  
       hist(InputDataSet$tip_amount, col = ''lightgreen'',   
           xlab=''Tip amount ($)'',   
           ylab = ''Counts'',   
           main = ''Histogram, Tip amount'', xlim = c(0,40), 100);  
       hist(InputDataSet$fare_amount, col = ''lightgreen'',   
           xlab=''Fare amount ($)'',   
           ylab = ''Counts'',   
           main = ''Histogram,   
           Fare amount'',   
           xlim = c(0,100), 100);  
       dev.off();  
   
       # Open a pdf file and output an xyplot of tip amount vs. fare amount using lattice;  
       # Only 10,000 sampled observations are plotted here, otherwise file is large.  
       dest_filename = tempfile(pattern = ''rXYPlots_Tip_vs_Fare_Amount_'', tmpdir = mainDir)  
       dest_filename = paste(dest_filename, ''.pdf'',sep="")  
       print(dest_filename, quote=FALSE);  
       pdf(file=dest_filename, height=4, width=4);  
       plot(tip_amount ~ fare_amount,   
           data = InputDataSet[sample(nrow(InputDataSet), 10000), ],   
           ylim = c(0,50),   
           xlim = c(0,150),   
           cex=.5,   
           pch=19,   
           col=''darkgreen'',    
           main = ''Tip amount by Fare amount'',   
           xlab=''Fare Amount ($)'',   
           ylab = ''Tip Amount ($)'');   
       dev.off();',  
     @input_data_1 = @query  
   END
   

Os principais pontos a serem compreendidos neste script incluem o seguinte:

• A saída da consulta SELECT dentro do procedimento armazenado é armazenada no quadro de dados R padrão, InputDataSet. Várias funções de plotagem R podem ser chamadas para gerar os arquivos gráficos reais. A maioria do script R incorporado representa opções para essas funções gráficas, como plot ou hist.

• O dispositivo R está definido como desativado porque você está executando esse comando como um script externo no SQL Server. Normalmente, em R, quando você executa um comando de plotagem de alto nível, R inicia uma janela gráfica, chamada dispositivo. Você pode desligar o dispositivo se estiver gravando em um arquivo ou manipulando a saída de outra maneira.

• Todos os arquivos são salvos na pasta local C:\temp\Plots. A pasta de destino é definida pelos argumentos fornecidos ao script R como parte do procedimento armazenado. Para guardar os ficheiros numa pasta diferente, altere o valor da variável mainDir no script R que está incorporado no procedimento armazenado. Você também pode modificar o script para produzir formatos diferentes, mais arquivos e assim por diante.

Executar o procedimento armazenado

Execute a instrução a seguir para exportar dados de plotagem binários para formatos de arquivo JPEG e PDF.

EXEC RPlotHist

Results

STDOUT message(s) from external script:
[1] Creating output plot files:[1] C:\temp\plots\rHistogram_Tipped_18887f6265d4.jpg[1] 

C:\temp\plots\rHistograms_Tip_and_Fare_Amount_1888441e542c.pdf[1]

C:\temp\plots\rXYPlots_Tip_vs_Fare_Amount_18887c9d517b.pdf

Os números nos nomes de arquivo são gerados aleatoriamente para garantir que você não receba um erro ao tentar gravar em um arquivo existente.

Visualizar saída

Para visualizar o gráfico, abra a pasta de destino e revise os arquivos que foram criados pelo código R no procedimento armazenado.

1. Vá para a pasta indicada na mensagem STDOUT (no exemplo, isso é C:\temp\plots)

2. Abra rHistogram_Tipped.jpg para visualizar o número de viagens que receberam uma gorjeta em comparação com as viagens que não receberam gorjeta (este histograma é semelhante ao que foi gerado na etapa anterior).

3. Abra rHistograms_Tip_and_Fare_Amount.pdf para visualizar a distribuição dos valores das gorjetas, representados graficamente em relação aos valores da tarifa.

   

4. Abra rXYPlots_Tip_vs_Fare_Amount.pdf para visualizar um gráfico de dispersão com o valor da tarifa no eixo x e o valor da ponta no eixo y.

   

Próximos passos

Neste artigo, você: