Membuat fitur data menggunakan R dan SQL Server (panduan)

Artikel
06/02/2023

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

Rekayasa data adalah bagian penting dari pembelajaran mesin. Data sering memerlukan transformasi sebelum Anda dapat menggunakannya untuk pemodelan prediktif. Jika data tidak memiliki fitur yang Anda butuhkan, Anda dapat merekayasanya dari nilai yang ada.

Untuk tugas pemodelan ini, daripada menggunakan nilai garis lintang dan bujur mentah dari lokasi penjemputan dan pengantaran, Anda ingin memiliki jarak dalam mil antara dua lokasi. Untuk membuat fitur ini, Anda menghitung jarak linier langsung antara dua titik, dengan menggunakan rumus hasrsine.

Dalam langkah ini, pelajari dua metode berbeda untuk membuat fitur dari data:

Menggunakan fungsi R kustom
Menggunakan fungsi T-SQL kustom di Transact-SQL

Tujuannya adalah untuk membuat kumpulan data SQL Server baru yang menyertakan kolom asli ditambah fitur numerik baru, direct_distance.

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

Fiturisasi menggunakan R

Bahasa R terkenal karena pustaka statistiknya yang kaya dan bervariasi, tetapi Anda mungkin masih perlu membuat transformasi data kustom.

Pertama, mari kita lakukan seperti yang biasa dilakukan pengguna R: dapatkan data ke laptop Anda, lalu jalankan fungsi R kustom, ComputeDist, yang menghitung jarak linier antara dua titik yang ditentukan oleh nilai lintang dan bujur.

Ingatlah bahwa objek sumber data yang Anda buat sebelumnya hanya mendapatkan 1000 baris teratas. Jadi mari kita tentukan kueri yang mendapatkan semua data.

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

Buat objek sumber data baru menggunakan kueri.
```
featureDataSource <- RxSqlServerData(sqlQuery = bigQuery,colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric", dropoff_longitude = "numeric", dropoff_latitude = "numeric", passenger_count  = "numeric", trip_distance  = "numeric", trip_time_in_secs  = "numeric", direct_distance  = "numeric"), connectionString = connStr);
```
- RxSqlServerData dapat mengambil kueri yang terdiri dari kueri SELECT yang valid, yang disediakan sebagai argumen untuk parameter sqlQuery , atau nama objek tabel, yang disediakan sebagai parameter tabel .
- Jika Anda ingin mengambil sampel data dari tabel, Anda harus menggunakan parameter sqlQuery , menentukan parameter pengambilan sampel menggunakan klausa T-SQL TABLESAMPLE, dan mengatur argumen rowBuffering ke FALSE.
Jalankan kode berikut untuk membuat fungsi R kustom. ComputeDist mengambil dua pasang nilai garis lintang dan bujur, dan menghitung jarak linier di antara mereka, mengembalikan jarak dalam mil.
```
env <- new.env();
env$ComputeDist <- function(pickup_long, pickup_lat, dropoff_long, dropoff_lat){
  R <- 6371/1.609344 #radius in mile
  delta_lat <- dropoff_lat - pickup_lat
  delta_long <- dropoff_long - pickup_long
  degrees_to_radians = pi/180.0
  a1 <- sin(delta_lat/2*degrees_to_radians)
  a2 <- as.numeric(a1)^2
  a3 <- cos(pickup_lat*degrees_to_radians)
  a4 <- cos(dropoff_lat*degrees_to_radians)
  a5 <- sin(delta_long/2*degrees_to_radians)
  a6 <- as.numeric(a5)^2
  a <- a2+a3*a4*a6
  c <- 2*atan2(sqrt(a),sqrt(1-a))
  d <- R*c
  return (d)
}
```
- Baris pertama mendefinisikan lingkungan baru. Di R, lingkungan dapat digunakan untuk merangkum ruang nama dalam paket dan semacamnya. Anda dapat menggunakan search() fungsi untuk melihat lingkungan di ruang kerja Anda. Untuk melihat objek di lingkungan tertentu, ketik ls(<envname>).
- Baris yang dimulai dengan $env.ComputeDist berisi kode yang menentukan rumus hasrsine, yang menghitung jarak lingkaran besar antara dua titik pada bola.
Setelah menentukan fungsi, Anda menerapkannya ke data untuk membuat kolom fitur baru, direct_distance. tetapi sebelum Anda menjalankan transformasi, ubah konteks komputasi menjadi lokal.
```
rxSetComputeContext("local");
```
Panggil fungsi rxDataStep untuk mendapatkan data rekayasa fitur, dan terapkan env$ComputeDist fungsi ke data dalam memori.
```
start.time <- proc.time();

changed_ds <- rxDataStep(inData = featureDataSource,
transforms = list(direct_distance=ComputeDist(pickup_longitude,pickup_latitude, dropoff_longitude, dropoff_latitude),
tipped = "tipped", fare_amount = "fare_amount", passenger_count = "passenger_count",
trip_time_in_secs = "trip_time_in_secs",  trip_distance="trip_distance",
pickup_datetime = "pickup_datetime",  dropoff_datetime = "dropoff_datetime"),
transformEnvir = env,
rowsPerRead=500,
reportProgress = 3);

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 features.", sep=""));
```
- Fungsi rxDataStep mendukung berbagai metode untuk memodifikasi data di tempat. Untuk informasi selengkapnya, lihat artikel ini: Cara mengubah dan subset data di Microsft R
Namun, beberapa poin yang perlu diperhatikan mengenai rxDataStep:

Di sumber data lain, Anda dapat menggunakan argumen varsToKeep dan varsToDrop, tetapi ini tidak didukung untuk sumber data SQL Server. Oleh karena itu, dalam contoh ini, kami telah menggunakan argumen transformasi untuk menentukan kolom pass-through dan kolom yang diubah. Selain itu, saat berjalan dalam konteks komputasi SQL Server, argumen inData hanya dapat mengambil sumber data SQL Server.

Kode sebelumnya juga dapat menghasilkan pesan peringatan saat dijalankan pada himpunan data yang lebih besar. Ketika jumlah baris kali jumlah kolom yang dibuat melebihi nilai yang ditetapkan (defaultnya adalah 3.000.000), rxDataStep mengembalikan peringatan, dan jumlah baris dalam bingkai data yang dikembalikan akan dipotong. Untuk menghapus peringatan, Anda dapat memodifikasi argumen maxRowsByCols dalam fungsi rxDataStep. Namun, jika maxRowsByCols terlalu besar, Anda mungkin mengalami masalah saat memuat bingkai data ke dalam memori.
Secara opsional, Anda dapat memanggil rxGetVarInfo untuk memeriksa skema sumber data yang diubah.
```
rxGetVarInfo(data = changed_ds);
```

Fiturisasi menggunakan Transact-SQL

Dalam latihan ini, pelajari cara menyelesaikan tugas yang sama menggunakan fungsi SQL alih-alih fungsi R kustom.

Beralih ke SQL Server Management Studio atau editor kueri lain untuk menjalankan skrip T-SQL.

Gunakan fungsi SQL, bernama fnCalculateDistance. Fungsi harus sudah ada di database NYCTaxi_Sample. Di Object Explorer, verifikasi bahwa fungsi ada dengan menavigasi jalur ini: Database > NYCTaxi_Sample > Fungsi Kemampuan > Pemrograman > Fungsi > bernilai skalar dbo.fnCalculateDistance.

Jika fungsi tidak ada, gunakan SQL Server Management Studio untuk menghasilkan fungsi dalam database NYCTaxi_Sample.

CREATE FUNCTION [dbo].[fnCalculateDistance] (@Lat1 float, @Long1 float, @Lat2 float, @Long2 float)
-- User-defined function calculates the direct distance between two geographical coordinates.
RETURNS decimal(28, 10)
AS
BEGIN
  DECLARE @distance decimal(28, 10)
  -- Convert to radians
  SET @Lat1 = @Lat1 / 57.2958
  SET @Long1 = @Long1 / 57.2958
  SET @Lat2 = @Lat2 / 57.2958
  SET @Long2 = @Long2 / 57.2958
  -- Calculate distance
  SET @distance = (SIN(@Lat1) * SIN(@Lat2)) + (COS(@Lat1) * COS(@Lat2) * COS(@Long2 - @Long1))
  --Convert to miles
  IF @distance <> 0
  BEGIN
    SET @distance = 3958.75 * ATAN(SQRT(1 - POWER(@distance, 2)) / @distance);
  END
  RETURN @distance
END

Di Management Studio, di jendela kueri baru, jalankan pernyataan Transact-SQL berikut dari aplikasi apa pun yang mendukung Transact-SQL untuk melihat cara kerja fungsi.

USE nyctaxi_sample
GO

SELECT tipped, fare_amount, passenger_count,trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance, pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude 
FROM nyctaxi_sample

Untuk menyisipkan nilai langsung ke dalam tabel baru (Anda harus membuatnya terlebih dahulu), Anda bisa menambahkan klausa INTO yang menentukan nama tabel.

USE nyctaxi_sample
GO

SELECT tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance, pickup_datetime, dropoff_datetime,
dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) as direct_distance, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude
INTO NewFeatureTable
FROM nyctaxi_sample

Anda juga dapat memanggil fungsi SQL dari kode R. Beralih kembali ke Rgui dan simpan kueri fiturisasi SQL dalam variabel R.
```
featureEngineeringQuery = "SELECT tipped, fare_amount, passenger_count,
    trip_time_in_secs,trip_distance, pickup_datetime, dropoff_datetime,
    dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance,
    pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude
    FROM nyctaxi_sample
    tablesample (1 percent) repeatable (98052)"
```
Tip

Kueri ini telah dimodifikasi untuk mendapatkan sampel data yang lebih kecil, untuk membuat panduan ini lebih cepat. Anda dapat menghapus klausa TABLESAMPLE jika Anda ingin mendapatkan semua data; namun, tergantung pada lingkungan Anda, mungkin tidak mungkin untuk memuat datset penuh ke R, mengakibatkan kesalahan.

Gunakan baris kode berikut untuk memanggil fungsi Transact-SQL dari lingkungan R Anda dan menerapkannya ke data yang ditentukan dalam featureEngineeringQuery.

featureDataSource = RxSqlServerData(sqlQuery = featureEngineeringQuery,
  colClasses = c(pickup_longitude = "numeric", pickup_latitude = "numeric",
    dropoff_longitude = "numeric", dropoff_latitude = "numeric",
    passenger_count  = "numeric", trip_distance  = "numeric",
    trip_time_in_secs  = "numeric", direct_distance  = "numeric"),
  connectionString = connStr)

Sekarang setelah fitur baru dibuat, panggil rxGetVarsInfo untuk membuat ringkasan data dalam tabel fitur.
```
rxGetVarInfo(data = featureDataSource)
```
Hasil
```
Var 1: tipped, Type: integer
Var 2: fare_amount, Type: numeric
Var 3: passenger_count, Type: numeric
Var 4: trip_time_in_secs, Type: numeric
Var 5: trip_distance, Type: numeric
Var 6: pickup_datetime, Type: character
Var 7: dropoff_datetime, Type: character
Var 8: direct_distance, Type: numeric
Var 9: pickup_latitude, Type: numeric
Var 10: pickup_longitude, Type: numeric
Var 11: dropoff_latitude, Type: numeric
Var 12: dropoff_longitude, Type: numeric
```
Catatan

Dalam beberapa kasus, Anda mungkin mendapatkan kesalahan seperti ini: Izin EXECUTE ditolak pada objek 'fnCalculateDistance' Jika demikian, pastikan bahwa login yang Anda gunakan memiliki izin untuk menjalankan skrip dan membuat objek pada database, bukan hanya pada instans. Periksa skema untuk objek, fnCalculateDistance. Jika objek dibuat oleh pemilik database, dan login Anda milik peran db_datareader, Anda perlu memberikan izin eksplisit masuk untuk menjalankan skrip.

Membandingkan fungsi R dan fungsi SQL

Ingat bagian kode ini digunakan untuk waktu kode R?

start.time <- proc.time()
<your code here>
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 features.", sep=""))

Anda dapat mencoba menggunakan ini dengan contoh fungsi kustom SQL untuk melihat berapa lama transformasi data diperlukan saat memanggil fungsi SQL. Selain itu, coba alihkan konteks komputasi dengan rxSetComputeContext dan bandingkan waktunya.

Waktu Anda mungkin bervariasi secara signifikan, tergantung pada kecepatan jaringan Anda, dan konfigurasi perangkat keras Anda. Dalam konfigurasi yang kami uji, pendekatan fungsi Transact-SQL lebih cepat daripada menggunakan fungsi R kustom. Oleh karena itu, kami telah menggunakan fungsi Transact-SQL untuk perhitungan ini dalam langkah-langkah berikutnya.

Tip

Sangat sering, rekayasa fitur menggunakan Transact-SQL akan lebih cepat daripada R. Misalnya, T-SQL mencakup fungsi windowing dan peringkat cepat yang dapat diterapkan ke perhitungan ilmu data umum seperti rata-rata bergerak bergulir dan n-petak peta. Pilih metode yang paling efisien berdasarkan data dan tugas Anda.

Langkah berikutnya

Membuat model R dan menyimpan ke SQL

Bagikan melalui