Criar gráficos e plotagens usando o SQL e o R (instruções passo a passo)

Artigo
05/30/2023

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

Nesta parte do tutorial, você aprende técnicas para gerar plotagens e mapas usando o R com os dados do SQL Server. Você cria um histograma simples e, em seguida, desenvolve um gráfico de mapa mais complexo.

Pré-requisitos

Esta etapa pressupõe uma sessão do R em andamento com base nas etapas anteriores neste passo a passos. Ela 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 para executar comandos do R
Management Studio para executar T-SQL
googMap
pacote ggmap
pacote mapproj

Criar um histograma

Gere a primeira plotagem, usando a função rxHistogram . A função rxHistogram fornece funcionalidade semelhante a dos pacotes de R de software livre, mas pode ser executada em um 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=""))

A imagem é retornada no dispositivo gráfico do R do ambiente de desenvolvimento. Por exemplo, no RStudio, clique na janela Plotar . No Ferramentas de R para o Visual Studio, uma janela gráfica separada é aberta.

Observação

Seu grafo parece diferente?

Isso ocorre porque inDataSource usa apenas as primeiras 1.000 linhas. A ordenação de linhas que usam TOP é não determinística na ausência de uma cláusula ORDER BY, portanto, é esperado que os dados e o grafo resultantes possam variar. Essa imagem em particular foi gerada usando aproximadamente 10.000 linhas de dados. Recomendados que você teste com diferentes números de linhas para obter gráficos diferentes e observe quanto tempo leva para que os resultados sejam retornados em seu ambiente.

Criar um gráfico de mapa

Normalmente, os servidores de banco de dados bloqueiam o acesso à Internet. Isso pode ser inconveniente ao usar pacotes do R que precisam baixar mapas ou outras imagens para gerar gráficos. Porém, há uma solução alternativa que pode ser útil ao desenvolver seus próprios aplicativos. Basicamente, você gera a representação de mapa no cliente e então sobrepor no mapa os pontos armazenados como atributos na tabela do SQL Server.

Defina a função que cria o objeto de gráfico do R. A função personalizada mapPlot cria um gráfico de dispersão que usa as localizações de embarque de passageiros no táxi e plota o número de corridas que começaram em cada localização. Ela usa 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 personalizada mapPlot usa dois argumentos: um objeto de dados existente, definido anteriormente usando RxSqlServerData, e a representação do mapa passada do cliente.
- Na linha que começa com a variável ds, rxImport é usado para carregar dados de memória da fonte de dados criada anteriormente, inDataSource. (Essa fonte de dados contém apenas 1.000 linhas. Se você quiser criar um mapa com mais pontos de dados, poderá substituir uma fonte de dados diferente.)
- Sempre que você usar funções do R de software livre, os dados deverão ser carregados em quadros de dados na memória local. No entanto, ao chamar a função rxImport, você pode executar na memória do contexto de computação remota.
Altere o contexto de computação para local e carregue 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 variável gc armazena um conjunto de coordenadas para Times Square, NY.
- A linha que começa com googmap gera um mapa com as coordenadas especificadas no centro.
Alterne para o contexto de computação do SQL Server e renderize os resultados encapsulando a função de gráfico em rxExec como mostrado aqui. A função rxExec é parte do pacote RevoScaleR e dá suporte à execução de funções do R arbitrárias no 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 um argumento para a função executada remotamente, mapPlot. Uma vez que os mapas foram gerados no ambiente local, eles devem ser passados para a função a fim de criar o gráfico no contexto do SQL Server.
- Quando a linha que começa com plot é executada, os dados renderizados são serializados de volta para o ambiente local do R para que você possa exibi-los em seu cliente do R.
Observação

Se você estiver usando o SQL Server em uma máquina virtual do Azure, poderá receber um erro neste ponto. Um erro ocorre quando a regra de firewall padrão no Azure bloqueia o acesso à rede pelo código R. Para obter detalhes sobre como corrigir esse erro, confira Como instalar Serviços de Machine Learning (R) em uma VM do Azure.
A imagem a seguir mostra a plotagem de saída. Os locais em que os táxis apanham os clientes são adicionados ao mapa como pontos vermelhos. Sua imagem pode ser diferente dependendo de quantos locais estão na fonte de dados usada.

Próximas etapas

Criar recursos de dados usando o R e o SQL

Partilhar via