Creare grafici e tracciati con SQL e R (procedura dettagliata)

Si applica a: SQL Server 2016 (13.x) e versioni successive

In questa parte della procedura dettagliata vengono illustrate le tecniche per generare grafici e mappe usando R con dati di SQL Server. Si creerà un istogramma semplice e quindi si svilupperà un tracciato mappa più complesso.

Prerequisiti

Questo passaggio presuppone una sessione R in corso basata sui passaggi precedenti di questa procedura dettagliata. Usa le stringhe di connessione e gli oggetti origine dati creati in tali passaggi. Per eseguire lo script vengono usati gli strumenti e i pacchetti seguenti.

• Rgui.exe per eseguire i comandi R
• Management Studio per eseguire T-SQL
• googMap
• Pacchetto ggmap
• Pacchetto mapproj

Creare un istogramma

1. Generare il primo tracciato usando la funzione rxHistogram . La funzione rxHistogram fornisce funzionalità simili a quelle presenti nei pacchetti R open source, ma può essere eseguita in un contesto di esecuzione 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. L'immagine viene restituita nel dispositivo grafico R per l'ambiente di sviluppo. Ad esempio, in RStudio fare clic sulla finestra Plot . In R Tools per Visual Studio viene aperta una finestra grafica separata.

   

   Nota

   Il grafico ha un aspetto diverso?

   Il motivo è che inDataSource usa solo le prime 1.000 righe. L'ordinamento delle righe mediante TOP non è deterministico in assenza di una clausola ORDER BY, quindi è previsto che i dati e il grafico risultante possano essere diversi. Questa particolare immagine è stata generata usando circa 10.000 righe di dati. È consigliabile provare con diversi numeri di righe per ottenere grafici differenti, osservando il tempo necessario alla restituzione dei risultati nell'ambiente in uso.

Creare un tracciato mappa

In genere, i server di database bloccano l'accesso a Internet. Questo può risultare scomodo quando si usano pacchetti R che devono scaricare mappe o altre immagini per generare i tracciati. Tuttavia, esiste una soluzione alternativa che può risultare utile per lo sviluppo di applicazioni personalizzate. Di base, si genera la rappresentazione della mappa nel client e quindi si sovrappongono alla mappa i punti, archiviati come attributi nella tabella di SQL Server.

1. Definire la funzione che crea l'oggetto tracciato R. La funzione personalizzata mapPlot crea un grafico a dispersione che usa i punti di inizio corsa dei taxi e traccia il numero di corse iniziate da ogni posizione. Usa i pacchetti ggplot2 e ggmap, che dovrebbero già essere installati e caricati.

   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))
   }
   

   • La funzione mapPlot accetta due argomenti: un oggetto dati esistente (definito in precedenza con RxSqlServerData) e la rappresentazione della mappa passata dal client.
   • Nella riga che inizia con la variabile ds, rxImport viene usato per caricare i dati in memoria dall'origine dati creata in precedenza, inDataSource. Tale origine dati contiene solo 1.000 righe. Se si vuole creare una mappa con più punti dati, è possibile sostituirla con un'origine dati diversa.
   • Quando si usano funzioni R open source è necessario che i dati siano caricati nella memoria locale in data frame. Tuttavia, chiamando la funzione rxImport, è possibile eseguire nella memoria del contesto di calcolo remoto.

2. Cambiare il contesto di calcolo in quello locale e caricare le librerie necessarie per la creazione delle mappe.

   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');
   

   • La variabile gc memorizza un set di coordinate per Times Square, New York.

   • La riga che inizia con googmap genera una mappa con al centro le coordinate specificate.

3. Passare al contesto di calcolo SQL Server ed eseguire il rendering dei risultati eseguendo il wrapping della funzione di creazione del tracciato in rxExec come illustrato di seguito. La funzione rxExec è inclusa nel pacchetto RevoScaleR e supporta l'esecuzione di funzioni R arbitrarie in un contesto di calcolo remoto.

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

   • I dati della mappa in googMap vengono passati come argomento alla funzione mapPlot eseguita in modalità remota. Poiché le mappe sono state generate nell'ambiente locale, devono essere passate alla funzione per creare il tracciato nel contesto di SQL Server.

   • Quando la riga che inizia con plot viene eseguita, i dati di cui è stato eseguito il rendering vengono di nuovo serializzati nell'ambiente R locale, in modo che sia possibile vederli nel client R.

   Nota

   Se si usa SQL Server in una macchina virtuale di Azure, a questo punto si potrebbe ricevere un errore. Si verifica un errore quando la regola del firewall predefinita in Azure blocca l'accesso alla rete da parte del codice R. Per informazioni dettagliate su come correggere questo errore, vedere Installare Machine Learning Services con R in una macchina virtuale di Azure.

4. La figura seguente mostra il tracciato di output. I punti di inizio corsa dei taxi vengono aggiunti alla mappa come punti rossi. L'immagine potrebbe essere diversa, a seconda del numero di posizioni presenti nell'origine dati usata.

   

Passaggi successivi