Criar gráficos e diagramas com SQL e R (guia passo a passo)

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

Nesta parte do guia, aprendes técnicas para gerar gráficos e mapas usando R com dados do SQL Server. Cria-se um histograma simples e depois desenvolve-se um gráfico de mapa mais complexo.

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
• googMap
• pacote ggmap
• Pacote mapproj

Criar um histograma

1. Gera o primeiro gráfico, usando a função rxHistogram . A função rxHistogram fornece funcionalidades semelhantes às dos pacotes R open-source, mas pode correr num contexto de execução remota.

   # Plot fare amount on SQL Server and return the plot
   start.time <- proc.time()
   rxHistogram(~fare_amount, data = inDataSource, title = "Fare Amount Histogram")
   used.time <- proc.time() - start.time
   print(paste("It takes CPU Time=", round(used.time[1]+used.time[2],2), " seconds, Elapsed Time=", round(used.time[3],2), " seconds to generate plot.", sep=""))
   

2. A imagem é devolvida ao dispositivo gráfico R do seu ambiente de desenvolvimento. Por exemplo, no RStudio, clique na janela Plotar . No R Tools para Visual Studio, é aberta uma janela gráfica separada.

   

   Note

   O teu gráfico parece diferente?

   Isto porque o inDataSource usa apenas as primeiras 1000 linhas. A ordenação das linhas usando TOP é não determinística na ausência de uma cláusula ORDER BY, pelo que espera-se que os dados e o grafo resultante possam variar. Esta imagem em particular foi gerada usando cerca de 10.000 linhas de dados. Recomendamos que experimente diferentes números de linhas para obter gráficos diferentes e observe quanto tempo demora a devolver os resultados no seu ambiente.

Criar um gráfico de mapa

Normalmente, os servidores de bases de dados bloqueiam o acesso à Internet. Isto pode ser inconveniente ao usar pacotes R que precisam de descarregar mapas ou outras imagens para gerar gráficos. No entanto, existe uma solução alternativa que pode ser útil ao desenvolver as suas próprias aplicações. Basicamente, geras a representação do mapa no cliente e depois sobrepões no mapa os pontos que estão armazenados como atributos na tabela do SQL Server.

1. Defina a função que cria o objeto R plot. A função personalizada mapPlot cria um gráfico de dispersão que utiliza os locais de recolha de táxis e representa o número de viagens que começaram em cada local. Utiliza os pacotes ggplot2 e ggmap , que já devem estar instalados e carregados.

   mapPlot <- function(inDataSource, googMap){
       library(ggmap)
       library(mapproj)
       ds <- rxImport(inDataSource)
       p <- ggmap(googMap)+
       geom_point(aes(x = pickup_longitude, y =pickup_latitude ), data=ds, alpha =.5,
   color="darkred", size = 1.5)
       return(list(myplot=p))
   }
   

   • A função mapPlot utiliza dois argumentos: um objeto de dados existente, que definiste anteriormente usando RxSqlServerData, e a representação do mapa passada pelo cliente.
   • Na linha que começa com a variável ds , o rxImport é usado para carregar na memória os dados da fonte de dados previamente criada, inDataSource. (Essa fonte de dados contém apenas 1000 linhas; se quiser criar um mapa com mais pontos de dados, pode substituir por outra fonte de dados.)
   • Sempre que utilizas funções R open-source, os dados têm de ser carregados em quadros de dados na memória local. No entanto, ao chamar a função rxImport, pode executar na memória do contexto de computação remota.

2. Muda o contexto de computação para local e carrega as bibliotecas necessárias para criar os mapas.

   rxSetComputeContext("local")
   library(ggmap)
   library(mapproj)
   gc <- geocode("Times Square", source = "google")
   googMap <- get_googlemap(center = as.numeric(gc), zoom = 12, maptype = 'roadmap', color = 'color');
   

   • A gc variável armazena um conjunto de coordenadas para Times Square, NY.

   • A linha que começa por googmap gera um mapa com as coordenadas especificadas no centro.

3. Mude para o contexto de computação do SQL Server e renderize os resultados, envolvendo a função plot no rxExec, como mostrado aqui. A função rxExec faz parte do pacote RevoScaleR e suporta a execução de funções arbitrárias de R num contexto de computação remota.

   rxSetComputeContext(sqlcc)
   myplots <- rxExec(mapPlot, inDataSource, googMap, timesToRun = 1)
   plot(myplots[[1]][["myplot"]]);
   

   • Os dados do mapa em googMap são passados como argumento para a função executada remotamente mapPlot. Como os mapas foram gerados no seu ambiente local, têm de ser passados para a função para criar o gráfico no contexto do SQL Server.

   • Quando a linha que começa por plot é executada, os dados renderizados são serializados de volta para o ambiente local R, para que possas visualizá-los no teu cliente R.

   Note

   Se estiveres a usar o SQL Server numa máquina virtual Azure, podes ter um erro neste ponto. Ocorre um erro quando a regra padrão do firewall no Azure bloqueia o acesso à rede por código R. Para detalhes sobre como corrigir este erro, consulte Instalar Serviços de Machine Learning (R) numa VM do Azure.

4. A imagem seguinte mostra o gráfico de saída. Os locais de recolha dos táxis são adicionados ao mapa como pontos vermelhos. A tua imagem pode parecer diferente, dependendo de quantas localizações estão na fonte de dados que usaste.

   

Passo seguinte