Menampilkan dan meringkas data SQL Server menggunakan R (panduan)

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

Pelajaran ini memperkenalkan Anda ke fungsi dalam paket RevoScaleR dan langkah-langkah Anda melalui tugas-tugas berikut:

• Sambungkan ke SQL Server
• Tentukan kueri yang memiliki data yang Anda butuhkan, atau tentukan tabel atau tampilan
• Tentukan satu atau beberapa konteks komputasi yang akan digunakan saat menjalankan kode R
• Secara opsional, tentukan transformasi yang diterapkan ke sumber data saat sedang dibaca dari sumbernya

Menentukan konteks komputasi SQL Server

Jalankan pernyataan R berikut di lingkungan R di stasiun kerja klien. Bagian ini mengasumsikan stasiun kerja ilmu data dengan Klien Microsoft R, karena mencakup semua paket RevoScaleR, serta sekumpulan alat R dasar yang ringan. Misalnya, Anda dapat menggunakan Rgui.exe untuk menjalankan skrip R di bagian ini.

1. Jika paket RevoScaleR belum dimuat, jalankan baris kode R ini:

   library("RevoScaleR")
   

   Tanda kutip bersifat opsional, dalam hal ini, meskipun disarankan.

   Jika Anda mendapatkan kesalahan, pastikan lingkungan pengembangan R Anda menggunakan pustaka yang menyertakan paket RevoScaleR. Gunakan perintah seperti .libPaths() untuk melihat jalur pustaka saat ini.

2. Buat string koneksi untuk SQL Server dan simpan dalam variabel R, connStr.

   Anda harus mengubah tempat penampung "your_server_name" menjadi nama instans SQL Server yang valid. Untuk nama server, Anda mungkin hanya dapat menggunakan nama instans, atau Anda mungkin perlu sepenuhnya memenuhi syarat nama, tergantung pada jaringan Anda.

   Untuk autentikasi SQL Server, sintaks koneksinya adalah sebagai berikut:

   connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Uid=your-sql-login;Pwd=your-login-password"
   

   Untuk autentikasi Windows, sintaksnya sedikit berbeda:

   connStr <- "Driver=SQL Server;Server=your_server_name;Database=nyctaxi_sample;Trusted_Connection=True"
   

   Umumnya, kami sarankan Anda menggunakan autentikasi Windows jika memungkinkan, untuk menghindari penyimpanan kata sandi dalam kode R Anda.

3. Tentukan variabel yang akan digunakan dalam membuat konteks komputasi baru. Setelah membuat objek konteks komputasi, Anda dapat menggunakannya untuk menjalankan kode R pada instans SQL Server.

   sqlShareDir <- paste("C:\\AllShare\\",Sys.getenv("USERNAME"),sep="")
   sqlWait <- TRUE
   sqlConsoleOutput <- FALSE
   

   • R menggunakan direktori sementara saat membuat serial objek R bolak-balik antara stasiun kerja Anda dan komputer SQL Server. Anda dapat menentukan direktori lokal yang digunakan sebagai sqlShareDir, atau menerima default.

   • Gunakan sqlWait untuk menunjukkan apakah Anda ingin R menunggu hasil dari server. Untuk diskusi tentang menunggu versus pekerjaan yang tidak menunggu, lihat Komputasi terdistribusi dan paralel dengan RevoScaleR di Microsoft R.

   • Gunakan argumen sqlConsoleOutput untuk menunjukkan bahwa Anda tidak ingin melihat output dari konsol R.

4. Anda memanggil konstruktor RxInSqlServer untuk membuat objek konteks komputasi dengan variabel dan string koneksi yang sudah ditentukan, dan menyimpan objek baru dalam variabel R sqlcc.

   sqlcc <- RxInSqlServer(connectionString = connStr, shareDir = sqlShareDir, wait = sqlWait, consoleOutput = sqlConsoleOutput)
   

5. Secara default, konteks komputasi bersifat lokal, jadi Anda perlu secara eksplisit mengatur konteks komputasi aktif .

   rxSetComputeContext(sqlcc)
   

   • rxSetComputeContext mengembalikan konteks komputasi yang sebelumnya aktif sehingga Anda dapat menggunakannya
   • rxGetComputeContext mengembalikan konteks komputasi aktif

   Perhatikan bahwa mengatur konteks komputasi hanya memengaruhi operasi yang menggunakan fungsi dalam paket RevoScaleR ; konteks komputasi tidak memengaruhi cara operasi R sumber terbuka dilakukan.

Membuat sumber data menggunakan RxSqlServer

Saat menggunakan pustaka Microsoft R seperti RevoScaleR dan MicrosoftML, sumber data adalah objek yang Anda buat menggunakan fungsi RevoScaleR. Objek sumber data menentukan beberapa kumpulan data yang ingin Anda gunakan untuk tugas, seperti pelatihan model atau ekstraksi fitur. Anda bisa mendapatkan data dari berbagai sumber termasuk SQL Server. Untuk daftar sumber yang saat ini didukung, lihat RxDataSource.

Sebelumnya Anda menentukan string koneksi, dan menyimpan informasi tersebut dalam variabel R. Anda dapat menggunakan kembali informasi koneksi tersebut untuk menentukan data yang ingin Anda dapatkan.

1. Simpan kueri SQL sebagai variabel string. Kueri menentukan data untuk melatih model.

   sampleDataQuery <- "SELECT TOP 1000 tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"
   

   Kami telah menggunakan klausul TOP di sini untuk membuat semuanya berjalan lebih cepat, tetapi baris aktual yang dikembalikan oleh kueri dapat bervariasi tergantung pada urutan. Oleh karena itu, hasil ringkasan Anda mungkin juga berbeda dari yang tercantum di bawah ini. Jangan ragu untuk menghapus klausul TOP.

2. Teruskan definisi kueri sebagai argumen ke fungsi RxSqlServerData .

   inDataSource <- RxSqlServerData(
     sqlQuery = sampleDataQuery,
     connectionString = connStr,
     colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
     dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
     rowsPerRead=500
     )
   

   • Argumen colClasses menentukan jenis kolom yang akan digunakan saat memindahkan data antara SQL Server dan R. Ini penting karena SQL Server menggunakan jenis data yang berbeda dari R, dan lebih banyak jenis data. Untuk informasi selengkapnya, lihat Pustaka R dan Jenis Data.

   • Baris argumenPerRead penting untuk mengelola penggunaan memori dan komputasi yang efisien. Sebagian besar fungsi analitik yang disempurnakan inR Services (In-Database) memproses data dalam gugus dan mengakumulasi hasil perantara, mengembalikan komputasi akhir setelah semua data dibaca. Dengan menambahkan parameter rowsPerRead , Anda dapat mengontrol berapa banyak baris data yang dibaca ke dalam setiap gugus untuk diproses. Jika nilai parameter ini terlalu besar, akses data mungkin lambat karena Anda tidak memiliki cukup memori untuk memproses potongan data yang begitu besar secara efisien. Pada beberapa sistem, mengatur rowsPerRead ke nilai yang terlalu kecil juga dapat memberikan performa yang lebih lambat.

3. Pada titik ini, Anda telah membuat objek inDataSource , tetapi tidak berisi data apa pun. Data tidak ditarik dari kueri SQL ke lingkungan lokal hingga Anda menjalankan fungsi seperti rxImport atau rxSummary.

   Namun, sekarang setelah Anda menentukan objek data, Anda dapat menggunakannya sebagai argumen ke fungsi lain.

Menggunakan data SQL Server dalam ringkasan R

Di bagian ini, Anda akan mencoba beberapa fungsi yang disediakan dalam R Services (In-Database) yang mendukung konteks komputasi jarak jauh. Dengan menerapkan fungsi R ke sumber data, Anda dapat menjelajahi, meringkas, dan membuat bagan data SQL Server.

1. Panggil fungsi rxGetVarInfo untuk mendapatkan daftar variabel di sumber data dan jenis datanya.

   rxGetVarInfo adalah fungsi yang berguna; Anda dapat memanggilnya pada bingkai data apa pun, atau pada sekumpulan data dalam objek data jarak jauh, untuk mendapatkan informasi seperti nilai maksimum dan minimum, jenis data, dan jumlah tingkat dalam kolom faktor.

   Pertimbangkan untuk menjalankan fungsi ini setelah segala jenis input data, transformasi fitur, atau rekayasa fitur. Dengan demikian, Anda dapat memastikan bahwa semua fitur yang ingin Anda gunakan dalam model Anda adalah jenis data yang diharapkan dan menghindari kesalahan.

   rxGetVarInfo(data = inDataSource)
   

   Hasil

   Var 1: tipped, Type: integer
   Var 2: fare_amount, Type: numeric
   Var 3: passenger_count, Type: integer
   Var 4: trip_time_in_secs, Type: numeric, Storage: int64
   Var 5: trip_distance, Type: numeric
   Var 6: pickup_datetime, Type: character
   Var 7: dropoff_datetime, Type: character
   Var 8: pickup_longitude, Type: numeric
   Var 9: pickup_latitude, Type: numeric
   Var 10: dropoff_longitude, Type: numeric
   

2. Sekarang, panggil fungsi RevoScaleR rxSummary untuk mendapatkan statistik yang lebih rinci tentang variabel individual.

   rxSummary didasarkan pada fungsi R summary , tetapi memiliki beberapa fitur dan keuntungan tambahan. rxSummary bekerja dalam beberapa konteks komputasi dan mendukung penggugusan. Anda juga dapat menggunakan rxSummary untuk mengubah nilai, atau meringkas berdasarkan tingkat faktor.

   Dalam contoh ini, Anda meringkas jumlah tarif berdasarkan jumlah penumpang.

   start.time <- proc.time()
   rxSummary(~fare_amount:F(passenger_count,1,6), data = inDataSource)
   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 summarize the inDataSource.", sep=""))
   

   • Argumen pertama untuk rxSummary menentukan rumus atau istilah yang akan diringkas. Di sini, F() fungsi digunakan untuk mengonversi nilai dalam passenger_count menjadi faktor sebelum meringkas. Anda juga harus menentukan nilai minimum (1) dan nilai maksimum (6) untuk variabel faktor passenger_count .
   • Jika Anda tidak menentukan statistik yang akan dihasilkan, secara default output rxSummary Mean, StDev, Min, Max, dan jumlah pengamatan yang valid dan hilang.
   • Contoh ini juga mencakup beberapa kode untuk melacak waktu fungsi dimulai dan selesai, sehingga Anda dapat membandingkan performa.

   Hasil

   Jika fungsi rxSummary berhasil berjalan, Anda akan melihat hasil seperti ini, diikuti dengan daftar statistik berdasarkan kategori.

   rxSummary(formula = ~fare_amount:F(passenger_count, 1,6), data = inDataSource)
   Data: inDataSource (RxSqlServerData Data Source)
   Number of valid observations: 1000
   

Latihan bonus pada big data

Coba tentukan string kueri baru dengan semua baris. Kami sarankan Anda menyiapkan objek sumber data baru untuk eksperimen ini. Anda mungkin juga mencoba mengubah parameter rowsToRead untuk melihat pengaruhnya terhadap throughput.

bigDataQuery  <- "SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime, pickup_longitude, pickup_latitude, dropoff_longitude, dropoff_latitude FROM nyctaxi_sample"

bigDataSource <- RxSqlServerData(
      sqlQuery = bigDataQuery,
      connectionString = connStr,
      colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
      dropoff_longitude = "numeric", dropoff_latitude = "numeric"),
      rowsPerRead=500
      )

start.time <- proc.time()
rxSummary(~fare_amount:F(passenger_count,1,6), data = bigDataSource)
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 summarize the inDataSource.", sep=""))

Tip

Saat ini berjalan, Anda dapat menggunakan alat seperti Process Explorer atau SQL Profiler untuk melihat bagaimana koneksi dibuat dan kode R dijalankan menggunakan layanan SQL Server.

Langkah berikutnya

Membuat grafik dan plot menggunakan R