Mulai cepat: Struktur data, jenis data, dan objek menggunakan R dengan pembelajaran mesin SQL

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

Dalam mulai cepat ini, Anda akan mempelajari cara menggunakan struktur data dan jenis data saat menggunakan R di SQL Server Pembelajaran Mesin Services atau di Kluster Big Data. Anda akan mempelajari tentang memindahkan data antara R dan SQL Server, dan masalah umum yang mungkin terjadi.

Masalah umum untuk diketahui di muka meliputi:

• Jenis data terkadang tidak cocok
• Konversi implisit mungkin terjadi
• Operasi transmisi dan konversi terkadang diperlukan
• R dan SQL menggunakan objek data yang berbeda

Prasyarat

Anda memerlukan prasyarat berikut untuk menjalankan mulai cepat ini.

• Layanan Pembelajaran Mesin SQL Server. Untuk menginstal layanan Pembelajaran Mesin, lihat panduan penginstalan Windows atau panduan penginstalan Linux. Anda juga dapat mengaktifkan Layanan Pembelajaran Mesin di Kluster Big Data SQL Server.

• Alat untuk menjalankan kueri SQL yang berisi skrip R. Mulai cepat ini menggunakan Azure Data Studio.

Selalu kembalikan bingkai data

Saat skrip Anda mengembalikan hasil dari R ke SQL Server, skrip harus mengembalikan data sebagai data.frame. Jenis objek lain yang Anda hasilkan dalam skrip Anda - apakah itu daftar, faktor, vektor, atau data biner - harus dikonversi ke bingkai data jika Anda ingin mengeluarkannya sebagai bagian dari hasil prosedur tersimpan. Untungnya, ada beberapa fungsi R untuk mendukung perubahan objek lain ke bingkai data. Anda bahkan dapat membuat serial model biner dan mengembalikannya dalam bingkai data, yang akan Anda lakukan nanti dalam mulai cepat ini.

Pertama, mari kita bereksperimen dengan beberapa objek R dasar R - vektor, matriks, dan daftar - dan lihat bagaimana konversi ke bingkai data mengubah output yang diteruskan ke SQL Server.

Bandingkan kedua skrip "Halo Dunia" ini di R. Skrip terlihat hampir identik, tetapi yang pertama mengembalikan satu kolom dari tiga nilai, sedangkan yang kedua mengembalikan tiga kolom dengan nilai tunggal masing-masing.

Contoh 1

EXECUTE sp_execute_external_script
       @language = N'R'
     , @script = N' mytextvariable <- c("hello", " ", "world");
       OutputDataSet <- as.data.frame(mytextvariable);'
     , @input_data_1 = N' ';

Contoh 2

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' OutputDataSet<- data.frame(c("hello"), " ", c("world"));'
      , @input_data_1 = N'  ';

Mengidentifikasi skema dan jenis data

Mengapa hasilnya begitu berbeda?

Jawabannya biasanya dapat ditemukan dengan menggunakan perintah R str() . Tambahkan fungsi str(object_name) di mana saja dalam skrip R Anda agar skema data objek R yang ditentukan dikembalikan sebagai pesan informasi.

Untuk mengetahui mengapa Contoh 1 dan Contoh 2 memiliki hasil yang berbeda, sisipkan baris str(OutputDataSet) di akhir @script definisi variabel di setiap pernyataan, seperti ini:

Contoh 1 dengan fungsi str ditambahkan

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' mytextvariable <- c("hello", " ", "world");
      OutputDataSet <- as.data.frame(mytextvariable);
      str(OutputDataSet);'
      , @input_data_1 = N'  '
;

Contoh 2 dengan fungsi str ditambahkan

EXECUTE sp_execute_external_script
  @language = N'R', 
  @script = N' OutputDataSet <- data.frame(c("hello"), " ", c("world"));
    str(OutputDataSet);' , 
  @input_data_1 = N'  ';

Sekarang, tinjau teks di Pesan untuk melihat mengapa output berbeda.

Hasil - Contoh 1

STDOUT message(s) from external script:
'data.frame':	3 obs. of  1 variable:
$ mytextvariable: Factor w/ 3 levels " ","hello","world": 2 1 3

Hasil - Contoh 2

STDOUT message(s) from external script:
'data.frame':	1 obs. of  3 variables:
$ c..hello..: Factor w/ 1 level "hello": 1
$ X...      : Factor w/ 1 level " ": 1
$ c..world..: Factor w/ 1 level "world": 1

Seperti yang Anda lihat, sedikit perubahan dalam sintaks R memiliki efek besar pada skema hasil. Kami tidak akan masuk ke mengapa, tetapi perbedaan dalam jenis data R dijelaskan secara rinci di bagian Struktur Data di "Advanced R" oleh Hadley Wickham.

Untuk saat ini, perlu diketahui bahwa Anda perlu memeriksa hasil yang diharapkan saat memaksa objek R ke dalam bingkai data.

Tip

Anda juga dapat menggunakan fungsi identitas R, seperti is.matrix, is.vector, untuk mengembalikan informasi tentang struktur data internal.

Konversi implisit objek data

Setiap objek data R memiliki aturan sendiri tentang bagaimana nilai ditangani saat dikombinasikan dengan objek data lain jika dua objek data memiliki jumlah dimensi yang sama, atau jika ada objek data yang berisi jenis data heterogen.

Pertama, buat tabel kecil data pengujian.

CREATE TABLE RTestData (col1 INT NOT NULL)

INSERT INTO RTestData
VALUES (1);

INSERT INTO RTestData
VALUES (10);

INSERT INTO RTestData
VALUES (100);
GO

Misalnya, asumsikan Anda menjalankan pernyataan berikut untuk melakukan perkalian matriks menggunakan R. Anda mengalikan matriks kolom tunggal dengan tiga nilai dengan array dengan empat nilai, dan mengharapkan matriks 4x3 sebagai hasilnya.

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:15);
    OutputDataSet <- as.data.frame(x %*% y);'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (([Col1] int, [Col2] int, [Col3] int, Col4 int));

Di bawah sampul, kolom tiga nilai dikonversi menjadi matriks kolom tunggal. Karena matriks hanyalah kasus khusus array dalam R, array y secara implisit dikoercasikan ke matriks kolom tunggal untuk membuat dua argumen sesuai.

Hasil

Col1	Col2	Col3	Col4
12	13	14	15
120	130	140	150
1200	1300	1400	1500

Namun, perhatikan apa yang terjadi ketika Anda mengubah ukuran array y.

execute sp_execute_external_script
   @language = N'R'
   , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:14);
   OutputDataSet <- as.data.frame(y %*% x);'
   , @input_data_1 = N' SELECT [Col1]  from RTestData;'
   WITH RESULT SETS (([Col1] int ));

Sekarang R mengembalikan satu nilai sebagai hasilnya.

Hasil

Col1
1542

Mengapa? Dalam hal ini, karena dua argumen dapat ditangani sebagai vektor dengan panjang yang sama, R mengembalikan produk dalam sebagai matriks. Ini adalah perilaku yang diharapkan sesuai dengan aturan aljabar linier; namun, itu dapat menyebabkan masalah jika aplikasi hilir Anda mengharapkan skema output tidak pernah berubah!

Tip

Mendapatkan kesalahan? Pastikan Anda menjalankan prosedur tersimpan dalam konteks database yang berisi tabel, dan bukan di master atau database lain.

Selain itu, kami sarankan Anda menghindari penggunaan tabel sementara untuk contoh-contoh ini. Beberapa klien R akan mengakhiri koneksi antara batch, menghapus tabel sementara.

Gabungkan atau kalikan kolom dengan panjang yang berbeda

R memberikan fleksibilitas yang besar untuk bekerja dengan vektor dengan ukuran yang berbeda, dan untuk menggabungkan struktur seperti kolom ini ke dalam bingkai data. Daftar vektor bisa terlihat seperti tabel, tetapi tidak mengikuti semua aturan yang mengatur tabel database.

Misalnya, skrip berikut mendefinisikan array numerik dengan panjang 6 dan menyimpannya dalam variabel df1R . Array numerik kemudian dikombinasikan dengan bilangan bulat tabel RTestData, yang berisi tiga (3) nilai, untuk membuat bingkai data baru, df2.

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
               df1 <- as.data.frame( array(1:6) );
               df2 <- as.data.frame( c( InputDataSet , df1 ));
               OutputDataSet <- df2'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (( [Col2] int not null, [Col3] int not null ));

Untuk mengisi bingkai data, R mengulangi elemen yang diambil dari RTestData sebanyak yang diperlukan untuk mencocokkan jumlah elemen dalam array df1.

Hasil

Col2	Col3
1	1
10	2
100	3
1	4
10	5
100	6

Ingatlah bahwa bingkai data hanya terlihat seperti tabel, dan sebenarnya adalah daftar vektor.

Mentransmisikan atau mengonversi data

R dan SQL Server tidak menggunakan jenis data yang sama, jadi saat Anda menjalankan kueri di SQL Server untuk mendapatkan data lalu meneruskan ke runtime R, beberapa jenis konversi implisit biasanya terjadi. Serangkaian konversi lain terjadi saat Anda mengembalikan data dari R ke SQL Server.

• SQL Server mendorong data dari kueri ke proses R yang dikelola oleh layanan Launchpad dan mengonversinya ke representasi internal untuk efisiensi yang lebih besar.
• Runtime R memuat data ke dalam variabel data.frame dan melakukan operasinya sendiri pada data.
• Mesin database mengembalikan data ke SQL Server menggunakan koneksi internal yang aman dan menyajikan data dalam hal jenis data SQL Server.
• Anda mendapatkan data dengan menyambungkan ke SQL Server menggunakan klien atau pustaka jaringan yang dapat mengeluarkan kueri SQL dan menangani himpunan data tabular. Aplikasi klien ini berpotensi memengaruhi data dengan cara lain.

Untuk melihat cara kerjanya, jalankan kueri seperti ini di gudang data AdventureWorksDW . Tampilan ini mengembalikan data penjualan yang digunakan dalam membuat prakiraan.

USE AdventureWorksDW
GO

SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = 'M200 Europe'
           ORDER BY ReportingDate ASC

Catatan

Anda dapat menggunakan versi AdventureWorks apa pun, atau membuat kueri yang berbeda menggunakan database Anda sendiri. Intinya adalah mencoba menangani beberapa data yang berisi teks, tanggalwaktu, dan nilai numerik.

Sekarang, coba tempelkan kueri ini sebagai input ke prosedur tersimpan.

EXECUTE sp_execute_external_script
       @language = N'R'
      , @script = N' str(InputDataSet);
      OutputDataSet <- InputDataSet;'
      , @input_data_1 = N'
           SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = ''M200 Europe''
           ORDER BY ReportingDate ASC ;'
WITH RESULT SETS undefined;

Jika Anda mendapatkan kesalahan, Anda mungkin perlu melakukan beberapa pengeditan pada teks kueri. Misalnya, predikat string dalam klausul WHERE harus diapit oleh dua set tanda kutip tunggal.

Setelah Kueri berfungsi, tinjau hasil str fungsi untuk melihat bagaimana R memperlakukan data input.

Hasil

STDOUT message(s) from external script: 'data.frame':    37 obs. of  3 variables:
STDOUT message(s) from external script: $ ReportingDate: POSIXct, format: "2010-12-24 23:00:00" "2010-12-24 23:00:00"
STDOUT message(s) from external script: $ ProductSeries: Factor w/ 1 levels "M200 Europe",..: 1 1 1 1 1 1 1 1 1 1
STDOUT message(s) from external script: $ Amount       : num  3400 16925 20350 16950 16950

• Kolom tanggalwaktu telah diproses menggunakan jenis data R, POSIXct.
• Kolom teks "ProductSeries" telah diidentifikasi sebagai faktor, yang berarti variabel kategoris. Nilai string ditangani sebagai faktor secara default. Jika Anda meneruskan string ke R, string tersebut dikonversi ke bilangan bulat untuk penggunaan internal, lalu dipetakan kembali ke string pada output.

Ringkasan

Bahkan dari contoh singkat ini, Anda dapat melihat kebutuhan untuk memeriksa efek konversi data saat meneruskan kueri SQL sebagai input. Karena beberapa jenis data SQL Server tidak didukung oleh R, pertimbangkan cara-cara ini untuk menghindari kesalahan:

• Uji data Anda terlebih dahulu dan verifikasi kolom atau nilai dalam skema Anda yang bisa menjadi masalah saat diteruskan ke kode R.
• Tentukan kolom di sumber data input Anda satu per satu, daripada menggunakan SELECT *, dan ketahui bagaimana setiap kolom akan ditangani.
• Lakukan transmisi eksplisit seperlunya saat menyiapkan data input Anda, untuk menghindari kejutan.
• Hindari meneruskan kolom data (seperti GUID atau rowguid) yang menyebabkan kesalahan dan tidak berguna untuk pemodelan.

Untuk informasi selengkapnya tentang jenis data yang didukung dan tidak didukung, lihat Pustaka R dan jenis data.

Langkah berikutnya

Untuk mempelajari tentang menulis fungsi R tingkat lanjut dengan pembelajaran mesin SQL, ikuti mulai cepat ini:

Menulis fungsi R tingkat lanjut dengan pembelajaran mesin SQL