Tutorial R: Menjelajahi dan memvisualisasikan data

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru Azure SQL Managed Instance

Di bagian dua dari seri tutorial lima bagian ini, Anda akan menjelajahi data sampel dan menghasilkan beberapa plot. Nantinya, Anda akan mempelajari cara membuat serialisasi objek grafis di Python, lalu mendeserialisasi objek tersebut dan membuat plot.

Di bagian dua dari seri tutorial lima bagian ini, Anda akan meninjau data sampel dan kemudian menghasilkan beberapa plot menggunakan generik barplot dan hist fungsi di R dasar.

Tujuan utama artikel ini adalah menunjukkan cara memanggil fungsi R dari Transact-SQL dalam prosedur tersimpan dan menyimpan hasilnya dalam format file aplikasi:

  • Buat prosedur tersimpan menggunakan barplot untuk menghasilkan plot R sebagai data varbinary. Gunakan bcp untuk mengekspor aliran biner ke file gambar.
  • Buat prosedur tersimpan menggunakan hist untuk menghasilkan plot, menyimpan hasil sebagai output JPG dan PDF.

Catatan

Karena visualisasi adalah alat yang sangat kuat untuk memahami bentuk dan distribusi data, R menyediakan berbagai fungsi dan paket untuk menghasilkan histogram, plot sebar, plot kotak, dan grafik eksplorasi data lainnya. R biasanya membuat gambar menggunakan perangkat R untuk output grafis, yang dapat Anda ambil dan simpan sebagai jenis data varbinary untuk penyajian dalam aplikasi. Anda juga dapat menyimpan gambar ke salah satu format file dukungan (.JPG, .PDF, dll.).

Dalam artikel ini, Anda akan:

  • Meninjau data sampel
  • Membuat plot menggunakan R di T-SQL
  • Plot output dalam beberapa format file

Di bagian satu, Anda menginstal prasyarat dan memulihkan database sampel.

Di bagian tiga, Anda akan mempelajari cara membuat fitur dari data mentah dengan menggunakan fungsi Transact-SQL. Anda kemudian akan memanggil fungsi tersebut dari prosedur tersimpan untuk membuat tabel yang berisi nilai fitur.

Di bagian empat, Anda akan memuat modul dan memanggil fungsi yang diperlukan untuk membuat dan melatih model menggunakan prosedur tersimpan SQL Server.

Di bagian lima, Anda akan mempelajari cara mengoprasionalkan model yang Anda latih dan simpan di bagian empat.

Tinjau datanya

Mengembangkan solusi ilmu data biasanya mencakup eksplorasi data intensif dan visualisasi data. Jadi, luangkan waktu satu menit untuk meninjau data sampel, jika Anda belum melakukannya.

Dalam himpunan data publik asli, pengidentifikasi taksi dan catatan perjalanan disediakan dalam file terpisah. Namun, untuk membuat data sampel lebih mudah digunakan, dua himpunan data asli telah digabungkan pada medali kolom, hack_license, dan pickup_datetime. Rekaman juga diambil sampelnya untuk mendapatkan hanya 1% dari jumlah rekaman asli. Himpunan data sampel bawah yang dihasilkan memiliki 1.703.957 baris dan 23 kolom.

Pengidentifikasi taksi

  • Kolom medali mewakili nomor ID unik taksi.

  • Kolom hack_license berisi nomor SIM taksi (anonim).

Catatan perjalanan dan tarif

  • Setiap catatan perjalanan mencakup lokasi dan waktu penjemputan dan pengantaran, serta jarak perjalanan.

  • Setiap catatan tarif mencakup informasi pembayaran seperti jenis pembayaran, jumlah total pembayaran, dan jumlah tip.

  • Tiga kolom terakhir dapat digunakan untuk berbagai tugas pembelajaran mesin. Kolom tip_amount berisi nilai numerik berkelanjutan dan dapat digunakan sebagai kolom label untuk analisis regresi. Kolom berujung hanya memiliki nilai ya/tidak dan digunakan untuk klasifikasi biner. Kolom tip_class memiliki beberapa label kelas dan oleh karena itu dapat digunakan sebagai label untuk tugas klasifikasi multi-kelas.

    Panduan ini hanya menunjukkan tugas klasifikasi biner; Anda dipersilakan untuk mencoba membangun model untuk dua tugas pembelajaran mesin lainnya, regresi dan klasifikasi multikelas.

  • Nilai yang digunakan untuk kolom label semuanya didasarkan pada kolom tip_amount , menggunakan aturan bisnis ini:

    Nama kolom turunan Aturan
    diberi tip Jika tip_amount > 0, tip = 1, jika tidak, tip = 0
    tip_class Kelas 0: tip_amount = $0

    Kelas 1: tip_amount > $0 dan tip_amount <= $5

    Kelas 2: tip_amount > $5 dan tip_amount <= $10

    Kelas 3: tip_amount > $10 dan tip_amount <= $20

    Kelas 4: tip_amount > $20

Membuat plot menggunakan R di T-SQL

Penting

Mulai SQL Server 2019, mekanisme isolasi mengharuskan Anda untuk memberikan izin yang sesuai ke direktori tempat file plot disimpan. Untuk informasi selengkapnya tentang cara mengatur izin ini, lihat bagian Izin file di SQL Server 2019 di Windows: Perubahan isolasi untuk Layanan Pembelajaran Mesin.

Untuk membuat plot, gunakan fungsi barplotR . Langkah ini memplot histogram berdasarkan data dari kueri Transact-SQL. Anda dapat membungkus fungsi ini dalam prosedur tersimpan, RPlotHistogram.

  1. Di SQL Server Management Studio, di Object Explorer, klik kanan database NYCTaxi_Sample dan pilih Kueri Baru. Atau, di Azure Data Studio, pilih Buku Catatan Baru dari menu File dan sambungkan ke database.

  2. Tempelkan skrip berikut untuk membuat prosedur tersimpan yang memplot histogram. Contoh ini bernama 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

Poin-poin penting yang perlu dipahami dalam skrip ini meliputi hal-hal berikut:

  • Variabel @query menentukan teks kueri ('SELECT tipped FROM nyctaxi_sample'), yang diteruskan ke skrip R sebagai argumen ke variabel input skrip, @input_data_1. Untuk skrip R yang berjalan sebagai proses eksternal, Anda harus memiliki pemetaan satu-ke-satu antara input ke skrip Anda, dan input ke prosedur tersimpan sistem sp_execute_external_script yang memulai sesi R pada SQL Server.

  • Dalam skrip R, variabel (image_file) didefinisikan untuk menyimpan gambar.

  • Fungsi barplot ini dipanggil untuk menghasilkan plot.

  • Perangkat R diatur ke nonaktif karena Anda menjalankan perintah ini sebagai skrip eksternal di SQL Server. Biasanya di R, saat Anda mengeluarkan perintah plotting tingkat tinggi, R membuka jendela grafis, yang disebut perangkat. Anda dapat mematikan perangkat jika Anda menulis ke file atau menangani output dengan cara lain.

  • Objek grafis R diserialisasikan ke R data.frame untuk output.

Jalankan prosedur tersimpan dan gunakan bcp untuk mengekspor data biner ke file gambar

Prosedur tersimpan mengembalikan gambar sebagai aliran data varbinary, yang jelas Anda tidak dapat melihat secara langsung. Namun, Anda dapat menggunakan utilitas bcp untuk mendapatkan data varbinary dan menyimpannya sebagai file gambar di komputer klien.

  1. Di Management Studio, jalankan pernyataan berikut:

    EXEC [dbo].[RPlotHistogram]

    Hasil

    plot0xFFD8FFE000104A4649...

  2. Buka perintah PowerShell dan jalankan perintah berikut, berikan nama instans, nama database, nama pengguna, dan kredensial yang sesuai sebagai argumen. Bagi mereka yang menggunakan identitas Windows, Anda dapat mengganti -U dan -P dengan -T.

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

    Catatan

    Sakelar perintah untuk bcp peka huruf besar/kecil.

  3. Jika koneksi berhasil, Anda akan diminta untuk memasukkan informasi lebih lanjut tentang format file grafis.

    Tekan ENTER di setiap perintah untuk menerima default, kecuali untuk perubahan ini:

    • Untuk panjang awalan plot bidang, ketik 0.

    • Ketik Y jika Anda ingin menyimpan parameter output untuk digunakan kembali nanti.

    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]:

    Hasil

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

    Tip

    Jika Anda menyimpan informasi format ke file (bcp.fmt), utilitas bcp menghasilkan definisi format yang dapat Anda terapkan ke perintah serupa di masa mendatang tanpa dimintai opsi format file grafis. Untuk menggunakan file format, tambahkan -f bcp.fmt ke akhir baris perintah apa pun, setelah argumen kata sandi.

  4. File output akan dibuat di direktori yang sama tempat Anda menjalankan perintah PowerShell. Untuk melihat plot, cukup buka file plot.jpg.

    perjalanan taksi dengan dan tanpa tips

Membuat prosedur tersimpan menggunakan hist

Biasanya, ilmuwan data menghasilkan beberapa visualisasi data untuk mendapatkan wawasan tentang data dari perspektif yang berbeda. Dalam contoh ini, Anda akan membuat prosedur tersimpan yang disebut RPlotHist untuk menulis histogram, sebar, dan grafik R lainnya untuk .JPG dan format .PDF.

Prosedur tersimpan hist ini menggunakan fungsi untuk membuat histogram, mengekspor data biner ke format populer seperti .JPG, .PDF, dan .PNG.

  1. Di SQL Server Management Studio, di Object Explorer, klik kanan database NYCTaxi_Sample dan pilih Kueri Baru.

  2. Tempelkan skrip berikut untuk membuat prosedur tersimpan yang memplot histogram. Contoh ini bernama 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

Poin-poin penting yang perlu dipahami dalam skrip ini meliputi hal-hal berikut:

  • Output kueri SELECT dalam prosedur tersimpan disimpan dalam bingkai data R default, InputDataSet. Berbagai fungsi plot R kemudian dapat dipanggil untuk menghasilkan file grafis yang sebenarnya. Sebagian besar skrip R yang disematkan mewakili opsi untuk fungsi grafis ini, seperti plot atau hist.

  • Perangkat R diatur ke nonaktif karena Anda menjalankan perintah ini sebagai skrip eksternal di SQL Server. Biasanya di R, saat Anda mengeluarkan perintah plotting tingkat tinggi, R membuka jendela grafis, yang disebut perangkat. Anda dapat mematikan perangkat jika Anda menulis ke file atau menangani output dengan cara lain.

  • Semua file disimpan ke folder lokal C:\temp\Plots. Folder tujuan didefinisikan oleh argumen yang disediakan untuk skrip R sebagai bagian dari prosedur tersimpan. Untuk menghasilkan file ke folder lain, ubah nilai mainDir variabel dalam skrip R yang disematkan dalam prosedur tersimpan. Anda juga dapat memodifikasi skrip untuk menghasilkan format yang berbeda, lebih banyak file, dan sebagainya.

Jalankan prosedur tersimpan

Jalankan pernyataan berikut untuk mengekspor data plot biner ke format file JPEG dan PDF.

EXEC RPlotHist

Hasil

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

Angka-angka dalam nama file dibuat secara acak untuk memastikan bahwa Anda tidak mendapatkan kesalahan saat mencoba menulis ke file yang ada.

Melihat output

Untuk melihat plot, buka folder tujuan dan tinjau file yang dibuat oleh kode R dalam prosedur tersimpan.

  1. Buka folder yang ditunjukkan dalam pesan STDOUT (dalam contoh, ini adalah C:\temp\plots)

  2. Buka rHistogram_Tipped.jpg untuk menunjukkan jumlah perjalanan yang mendapat tip vs. perjalanan yang tidak mendapat tip (histogram ini mirip dengan yang Anda buat di langkah sebelumnya).

  3. Buka rHistograms_Tip_and_Fare_Amount.pdf untuk melihat distribusi jumlah tip, yang diplot terhadap jumlah tarif.

    histogram memperlihatkan histogram tip_amount dan fare_amount

  4. Buka rXYPlots_Tip_vs_Fare_Amount.pdf untuk melihat plot sebar dengan jumlah tarif pada sumbu x dan jumlah tip pada sumbu y.

    jumlah tip yang diplot selama jumlah tip jumlah tarif yang

Langkah berikutnya

Dalam artikel ini, Anda:

  • Meninjau data sampel
  • Membuat plot menggunakan R di T-SQL
  • Plot output dalam beberapa format file

Tutorial R: Membuat fitur data