Membuat grafik dan plot menggunakan SQL dan R (panduan)

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru

Di bagian panduan ini, Anda mempelajari teknik untuk menghasilkan plot dan peta menggunakan R dengan data SQL Server. Anda membuat histogram sederhana dan kemudian mengembangkan plot peta yang lebih kompleks.

Prasyarat

Langkah ini mengasumsikan sesi R yang sedang berlangsung berdasarkan langkah-langkah sebelumnya dalam panduan ini. Ini menggunakan string koneksi dan objek sumber data yang dibuat dalam langkah-langkah tersebut. Alat dan paket berikut digunakan untuk menjalankan skrip.

• Rgui.exe untuk menjalankan perintah R
• Management Studio untuk menjalankan T-SQL
• googMap
• paket ggmap
• paket mapproj

Membuat histogram

1. Hasilkan plot pertama, menggunakan fungsi rxHistogram . Fungsi rxHistogram menyediakan fungsionalitas yang mirip dengan yang ada dalam paket R sumber terbuka, tetapi dapat berjalan dalam konteks eksekusi jarak jauh.

   # 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. Gambar dikembalikan di perangkat grafis R untuk lingkungan pengembangan Anda. Misalnya, di RStudio, klik jendela Plot . Di R Tools for Visual Studio, jendela grafis terpisah dibuka.

   

   Catatan

   Apakah grafik Anda terlihat berbeda?

   Itu karena inDataSource hanya menggunakan 1000 baris teratas. Pengurutan baris yang menggunakan TOP tidak deterministik dengan tidak adanya klausa ORDER BY, sehingga diharapkan data dan grafik yang dihasilkan mungkin bervariasi. Gambar khusus ini dihasilkan menggunakan sekitar 10.000 baris data. Sebaiknya Anda bereksperimen dengan jumlah baris yang berbeda untuk mendapatkan grafik yang berbeda, dan perhatikan berapa lama waktu yang diperlukan untuk mengembalikan hasil di lingkungan Anda.

Membuat plot peta

Biasanya, server database memblokir akses Internet. Ini bisa merepotkan saat menggunakan paket R yang perlu mengunduh peta atau gambar lain untuk menghasilkan plot. Namun, ada solusi yang mungkin berguna bagi Anda saat mengembangkan aplikasi Anda sendiri. Pada dasarnya, Anda menghasilkan representasi peta pada klien, lalu melapisi pada peta titik yang disimpan sebagai atribut dalam tabel SQL Server.

1. Tentukan fungsi yang membuat objek plot R. MapPlot fungsi kustom membuat plot sebar yang menggunakan lokasi penjemputan taksi, dan memplot jumlah perjalanan yang dimulai dari setiap lokasi. Ini menggunakan paket ggplot2 dan ggmap, yang seharusnya sudah diinstal dan dimuat.

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

   • Fungsi mapPlot mengambil dua argumen: objek data yang ada, yang Anda tentukan sebelumnya menggunakan RxSqlServerData, dan representasi peta yang diteruskan dari klien.
   • Dalam baris yang dimulai dengan variabel ds , rxImport digunakan untuk memuat ke dalam data memori dari sumber data yang dibuat sebelumnya, inDataSource. (Sumber data tersebut hanya berisi 1000 baris; jika Anda ingin membuat peta dengan lebih banyak titik data, Anda dapat mengganti sumber data yang berbeda.)
   • Setiap kali Anda menggunakan fungsi R sumber terbuka, data harus dimuat ke dalam bingkai data dalam memori lokal. Namun, dengan memanggil fungsi rxImport , Anda dapat menjalankan dalam memori konteks komputasi jarak jauh.

2. Ubah konteks komputasi menjadi lokal, dan muat pustaka yang diperlukan untuk membuat peta.

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

   • Variabel gc menyimpan satu set koordinat untuk Times Square, NY.

   • Garis yang dimulai dengan googmap menghasilkan peta dengan koordinat yang ditentukan di tengah.

3. Beralih ke konteks komputasi SQL Server, dan render hasilnya, dengan membungkus fungsi plot di rxExec seperti yang ditunjukkan di sini. Fungsi rxExec adalah bagian dari paket RevoScaleR , dan mendukung eksekusi fungsi R arbitrer dalam konteks komputasi jarak jauh.

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

   • Data peta diteruskan googMap sebagai argumen ke mapPlot fungsi yang dijalankan dari jarak jauh. Karena peta dihasilkan di lingkungan lokal Anda, peta harus diteruskan ke fungsi untuk membuat plot dalam konteks SQL Server.

   • Saat baris dimulai dengan plot eksekusi, data yang dirender diserialisasikan kembali ke lingkungan R lokal sehingga Anda dapat melihatnya di klien R Anda.

   Catatan

   Jika Anda menggunakan SQL Server di komputer virtual Azure, Anda mungkin mendapatkan kesalahan saat ini. Kesalahan terjadi ketika aturan firewall default di Azure memblokir akses jaringan oleh kode R. Untuk detail tentang cara memperbaiki kesalahan ini, lihat Menginstal Layanan Pembelajaran Mesin (R) di Azure VM.

4. Gambar berikut menunjukkan plot output. Lokasi penjemputan taksi ditambahkan ke peta sebagai titik merah. Gambar Anda mungkin terlihat berbeda, tergantung berapa banyak lokasi di sumber data yang Anda gunakan.

   

Langkah berikutnya

Membuat fitur data menggunakan R dan SQL