Mulai cepat: Membuat dan menilai model prediktif di R dengan pembelajaran mesin SQL

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

Dalam mulai cepat ini, Anda akan membuat dan melatih model prediktif menggunakan T. Anda akan menyimpan model ke tabel dalam instans SQL Server Anda, lalu menggunakan model untuk memprediksi nilai dari data baru menggunakan SQL Server Machine Learning Services atau di Kluster Big Data.

Anda akan membuat dan menjalankan dua prosedur tersimpan yang berjalan di SQL. Yang pertama menggunakan himpunan data mtcar yang disertakan dengan R dan menghasilkan model linier umum sederhana (GLM) yang memprediksi probabilitas bahwa kendaraan telah dipasangi dengan transmisi manual. Prosedur kedua adalah untuk penilaian - ini memanggil model yang dihasilkan dalam prosedur pertama untuk menghasilkan serangkaian prediksi berdasarkan data baru. Dengan menempatkan kode R dalam prosedur tersimpan SQL, operasi terkandung dalam SQL, dapat digunakan kembali, dan dapat dipanggil oleh prosedur tersimpan lainnya dan aplikasi klien.

Tip

Jika Anda memerlukan penyegaran pada model linier, coba tutorial ini yang menjelaskan proses pemasangan model menggunakan rxLinMod: Fitting Linear Models

Dengan menyelesaikan mulai cepat ini, Anda akan mempelajari:

• Cara menyematkan kode R dalam prosedur tersimpan
• Cara meneruskan input ke kode Anda melalui input pada prosedur tersimpan
• Bagaimana prosedur tersimpan digunakan untuk mengoprasionalkan model

Prasyarat

Anda memerlukan prasyarat berikut untuk menjalankan mulai cepat ini.

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

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

Membuat model

Untuk membuat model, Anda akan membuat data sumber untuk pelatihan, membuat model dan melatihnya menggunakan data, lalu menyimpan model dalam database tempat model dapat digunakan untuk menghasilkan prediksi dengan data baru.

Membuat data sumber

1. Buka Azure Data Studio, sambungkan ke instans Anda, dan buka jendela kueri baru.

2. Buat tabel untuk menyimpan data pelatihan.

   CREATE TABLE dbo.MTCars(
       mpg decimal(10, 1) NOT NULL,
       cyl int NOT NULL,
       disp decimal(10, 1) NOT NULL,
       hp int NOT NULL,
       drat decimal(10, 2) NOT NULL,
       wt decimal(10, 3) NOT NULL,
       qsec decimal(10, 2) NOT NULL,
       vs int NOT NULL,
       am int NOT NULL,
       gear int NOT NULL,
       carb int NOT NULL
   );
   

3. Sisipkan data dari himpunan data mtcarsbawaan .

   INSERT INTO dbo.MTCars
   EXEC sp_execute_external_script @language = N'R'
       , @script = N'MTCars <- mtcars;'
       , @input_data_1 = N''
       , @output_data_1_name = N'MTCars';
   

   Tip

   Banyak himpunan data, kecil dan besar, disertakan dengan runtime R. Untuk mendapatkan daftar himpunan data yang diinstal dengan R, ketik library(help="datasets") dari prompt perintah R.

Membuat dan melatih model

Data kecepatan mobil berisi dua kolom, baik numerik: tenaga kuda (hp) dan berat (wt). Dari data ini, Anda akan membuat model linier umum (GLM) yang memperkirakan kemungkinan kendaraan telah dipasangi dengan transmisi manual.

Untuk membuat model, Anda menentukan rumus di dalam kode R Anda, dan meneruskan data sebagai parameter input.

DROP PROCEDURE IF EXISTS generate_GLM;
GO
CREATE PROCEDURE generate_GLM
AS
BEGIN
    EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'carsModel <- glm(formula = am ~ hp + wt, data = MTCarsData, family = binomial);
        trained_model <- data.frame(payload = as.raw(serialize(carsModel, connection=NULL)));'
    , @input_data_1 = N'SELECT hp, wt, am FROM MTCars'
    , @input_data_1_name = N'MTCarsData'
    , @output_data_1_name = N'trained_model'
    WITH RESULT SETS ((model VARBINARY(max)));
END;
GO

• Argumen pertama untuk glm adalah parameter rumus , yang mendefinisikan am sebagai dependen pada hp + wt.
• Data input disimpan dalam variabel MTCarsData, yang diisi oleh kueri SQL. Jika Anda tidak menetapkan nama tertentu ke data input Anda, nama variabel defaultnya adalah InputDataSet.

Menyimpan model dalam database

Selanjutnya, simpan model dalam database sehingga Anda dapat menggunakannya untuk prediksi atau melatihnya kembali.

1. Buat tabel untuk menyimpan model.

   Output paket R yang membuat model biasanya merupakan objek biner. Oleh karena itu, tabel tempat Anda menyimpan model harus menyediakan kolom jenis varbinary(max).

   CREATE TABLE GLM_models (
       model_name varchar(30) not null default('default model') primary key,
       model varbinary(max) not null
   );
   

2. Jalankan pernyataan Transact-SQL berikut untuk memanggil prosedur tersimpan, menghasilkan model, dan menyimpannya ke tabel yang Anda buat.

   INSERT INTO GLM_models(model)
   EXEC generate_GLM;
   

   Tip

   Jika Anda menjalankan kode ini untuk kedua kalinya, Anda mendapatkan kesalahan ini: "Pelanggaran batasan KUNCI PRIMER... Tidak dapat menyisipkan kunci duplikat dalam dbo.stopping_distance_models objek". Salah satu opsi untuk menghindari kesalahan ini adalah memperbarui nama untuk setiap model baru. Misalnya, Anda dapat mengubah nama menjadi sesuatu yang lebih deskriptif, dan menyertakan jenis model, hari Anda membuatnya, dan sebagainya.

   UPDATE GLM_models
   SET model_name = 'GLM_' + format(getdate(), 'yyyy.MM.HH.mm', 'en-gb')
   WHERE model_name = 'default model'
   

Menilai data baru menggunakan model terlatih

Penilaian adalah istilah yang digunakan dalam ilmu data untuk berarti menghasilkan prediksi, probabilitas, atau nilai lain berdasarkan data baru yang disalurkan ke dalam model terlatih. Anda akan menggunakan model yang Anda buat di bagian sebelumnya untuk menilai prediksi terhadap data baru.

Membuat tabel data baru

Pertama, buat tabel dengan data baru.

CREATE TABLE dbo.NewMTCars(
	hp INT NOT NULL
	, wt DECIMAL(10,3) NOT NULL
	, am INT NULL
)
GO

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (110, 2.634)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (72, 3.435)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (220, 5.220)

INSERT INTO dbo.NewMTCars(hp, wt)
VALUES (120, 2.800)
GO

Memprediksi transmisi manual

Untuk mendapatkan prediksi berdasarkan model Anda, tulis skrip SQL yang melakukan hal berikut:

1. Mendapatkan model yang Anda inginkan
2. Mendapatkan data input baru
3. Memanggil fungsi prediksi R yang kompatibel dengan model tersebut

Seiring waktu, tabel mungkin berisi beberapa model R, semua dibuat menggunakan parameter atau algoritma yang berbeda, atau dilatih pada subset data yang berbeda. Dalam contoh ini, kita akan menggunakan model bernama default model.

DECLARE @glmmodel varbinary(max) = 
    (SELECT model FROM dbo.GLM_models WHERE model_name = 'default model');

EXEC sp_execute_external_script
    @language = N'R'
    , @script = N'
            current_model <- unserialize(as.raw(glmmodel));
            new <- data.frame(NewMTCars);
            predicted.am <- predict(current_model, new, type = "response");
            str(predicted.am);
            OutputDataSet <- cbind(new, predicted.am);
            '
    , @input_data_1 = N'SELECT hp, wt FROM dbo.NewMTCars'
    , @input_data_1_name = N'NewMTCars'
    , @params = N'@glmmodel varbinary(max)'
    , @glmmodel = @glmmodel
WITH RESULT SETS ((new_hp INT, new_wt DECIMAL(10,3), predicted_am DECIMAL(10,3)));

Skrip di atas melakukan langkah-langkah berikut:

• Gunakan pernyataan SELECT untuk mendapatkan satu model dari tabel, dan teruskan sebagai parameter input.

• Setelah mengambil model dari tabel, panggil unserialize fungsi pada model.

• Terapkan predict fungsi dengan argumen yang sesuai ke model, dan berikan data input baru.

Catatan

Dalam contoh, str fungsi ditambahkan selama fase pengujian, untuk memeriksa skema data yang dikembalikan dari R. Anda dapat menghapus pernyataan nanti.

Nama kolom yang digunakan dalam skrip R belum tentu diteruskan ke output prosedur tersimpan. Di sini klausa WITH RESULTS digunakan untuk menentukan beberapa nama kolom baru.

Hasil

Anda juga dapat menggunakan pernyataan PREDICT (Transact-SQL) untuk menghasilkan nilai atau skor yang diprediksi berdasarkan model yang disimpan.

Langkah berikutnya

Untuk informasi selengkapnya tentang tutorial untuk R dengan pembelajaran mesin SQL, lihat:

• Tutorial R