Bagikan melalui

Membangun model R dan menyimpan ke SQL Server (panduan)

  • Artikel

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

Dalam langkah ini, pelajari cara membangun model pembelajaran mesin dan menyimpan model di SQL Server. Dengan menyimpan model, Anda dapat memanggilnya langsung dari kode Transact-SQL, menggunakan prosedur tersimpan sistem, sp_execute_external_script atau fungsi PREDICT (T-SQL).

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
  • Paket ROCR
  • Paket RODBC

Membuat prosedur tersimpan untuk menyimpan model

Langkah ini menggunakan prosedur tersimpan untuk menyimpan model terlatih ke SQL Server. Membuat prosedur tersimpan untuk melakukan operasi ini membuat tugas lebih mudah.

Jalankan kode T-SQL berikut di jendela kueri di Management Studio untuk membuat prosedur tersimpan.

USE [NYCTaxi_Sample]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'PersistModel')
  DROP PROCEDURE PersistModel
GO

CREATE PROCEDURE [dbo].[PersistModel] @m nvarchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	insert into nyc_taxi_models (model) values (convert(varbinary(max),@m,2))
END
GO

Catatan

Jika Anda mendapatkan kesalahan, pastikan login Anda memiliki izin untuk membuat objek. Anda dapat memberikan izin eksplisit untuk membuat objek dengan menjalankan pernyataan T-SQL seperti ini: exec sp_addrolemember 'db_owner', '<user_name>'.

Membuat model klasifikasi menggunakan rxLogit

Model ini adalah pengklasifikasi biner yang memprediksi apakah pengemudi taksi kemungkinan akan mendapatkan tip pada perjalanan tertentu atau tidak. Anda akan menggunakan sumber data yang Anda buat dalam pelajaran sebelumnya untuk melatih pengklasifikasi tip, menggunakan regresi logistik.

  1. Panggil fungsi rxLogit, yang disertakan dalam paket RevoScaleR, untuk membuat model regresi logistik.

    system.time(logitObj <- rxLogit(tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance, data = featureDataSource));

    Panggilan yang membangun model diapit dalam fungsi system.time. Ini memungkinkan Anda mendapatkan waktu yang diperlukan untuk membangun model.

  2. Setelah membuat model, Anda dapat memeriksanya menggunakan summary fungsi , dan melihat koefisien.

    summary(logitObj);

    Hasil

     *Logistic Regression Results for: tipped ~ passenger_count + trip_distance + trip_time_in_secs +*
 direct_distance* 
 *Data: featureDataSource (RxSqlServerData Data Source)*
 *Dependent variable(s): tipped*
 *Total independent variables: 5*
 *Number of valid observations: 17068*
 *Number of missing observations: 0*
 *-2\*LogLikelihood: 23540.0602 (Residual deviance on 17063 degrees of freedom)*
 *Coefficients:*
 *Estimate Std. Error z value Pr(>|z|)*
 *(Intercept)       -2.509e-03  3.223e-02  -0.078  0.93793*
 *passenger_count   -5.753e-02  1.088e-02  -5.289 1.23e-07 \*\*\**
 *trip_distance     -3.896e-02  1.466e-02  -2.658  0.00786 \*\**
 *trip_time_in_secs  2.115e-04  4.336e-05   4.878 1.07e-06 \*\*\**
 *direct_distance    6.156e-02  2.076e-02   2.966  0.00302 \*\**
 *---*
 *Signif. codes:  0 '\*\*\*' 0.001 '\*\*' 0.01 '\*' 0.05 '.' 0.1 ' ' 1*
 *Condition number of final variance-covariance matrix: 48.3933*
 *Number of iterations: 4*

Menggunakan model regresi logistik untuk penilaian

Sekarang setelah model dibangun, Anda dapat menggunakan untuk memprediksi apakah driver kemungkinan akan mendapatkan tip pada drive tertentu atau tidak.

  1. Pertama, gunakan fungsi RxSqlServerData untuk menentukan objek sumber data untuk menyimpan hasil penilaian.

    scoredOutput <- RxSqlServerData(
  connectionString = connStr,
  table = "taxiScoreOutput"  )

    • Untuk membuat contoh ini lebih sederhana, input ke model regresi logistik adalah sumber data fitur yang sama (sql_feature_ds) yang Anda gunakan untuk melatih model. Biasanya, Anda mungkin memiliki beberapa data baru untuk dinilai, atau Anda mungkin telah menyisihkan beberapa data untuk pengujian vs. pelatihan.

    • Hasil prediksi akan disimpan dalam tabel, taxiscoreOutput. Perhatikan bahwa skema untuk tabel ini tidak ditentukan saat Anda membuatnya menggunakan rxSqlServerData. Skema diperoleh dari output rxPredict.

    • Untuk membuat tabel yang menyimpan nilai yang diprediksi, login SQL yang menjalankan fungsi data rxSqlServer harus memiliki hak istimewa DDL dalam database. Jika login tidak dapat membuat tabel, pernyataan gagal.

  2. Panggil fungsi rxPredict untuk menghasilkan hasil.

    rxPredict(modelObject = logitObj,
    data = featureDataSource,
    outData = scoredOutput,
    predVarNames = "Score",
    type = "response",
    writeModelVars = TRUE, overwrite = TRUE)

    Jika pernyataan berhasil, perlu waktu untuk berjalan. Setelah selesai, Anda dapat membuka SQL Server Management Studio dan memverifikasi bahwa tabel dibuat dan berisi kolom Skor dan output lain yang diharapkan.

Akurasi model plot

Untuk mendapatkan gambaran tentang akurasi model, Anda dapat menggunakan fungsi rxRoc untuk memplot Kurva Operasi Penerima. Karena rxRoc adalah salah satu fungsi baru yang disediakan oleh paket RevoScaleR yang mendukung konteks komputasi jarak jauh, Anda memiliki dua opsi:

  • Anda dapat menggunakan fungsi rxRoc untuk menjalankan plot dalam konteks komputasi jarak jauh lalu mengembalikan plot ke klien lokal Anda.

  • Anda juga dapat mengimpor data ke komputer klien R Anda, dan menggunakan fungsi plot R lainnya untuk membuat grafik performa.

Di bagian ini, Anda akan bereksperimen dengan kedua teknik.

Menjalankan plot dalam konteks komputasi jarak jauh (SQL Server)

  1. Panggil fungsi rxRoc dan berikan data yang ditentukan sebelumnya sebagai input.

    scoredOutput = rxImport(scoredOutput);
rxRoc(actualVarName= "tipped", predVarNames = "Score", scoredOutput);

    Panggilan ini mengembalikan nilai yang digunakan dalam menghitung bagan ROC. Kolom label diberi tip, yang memiliki hasil aktual yang coba Anda prediksi, sementara kolom Skor memiliki prediksi.

  2. Untuk benar-benar memplot bagan, Anda dapat menyimpan objek ROC lalu menggambarnya dengan fungsi plot. Grafik dibuat pada konteks komputasi jarak jauh, dan dikembalikan ke lingkungan R Anda.

    scoredOutput = rxImport(scoredOutput);
rocObjectOut <- rxRoc(actualVarName= "tipped", predVarNames = "Score", scoredOutput);
plot(rocObjectOut);

    Lihat grafik dengan membuka perangkat grafis R, atau dengan mengklik jendela Plot di RStudio.

    Plot ROC untuk model

Membuat plot dalam konteks komputasi lokal menggunakan data dari SQL Server

Anda dapat memverifikasi konteks komputasi bersifat lokal dengan berjalan rxGetComputeContext() di prompt perintah. Nilai yang dikembalikan harus "RxLocalSeq Compute Context".

  1. Untuk konteks komputasi lokal, prosesnya jauh sama. Anda menggunakan fungsi rxImport untuk membawa data yang ditentukan ke lingkungan R lokal Anda.

    scoredOutput = rxImport(scoredOutput)

  2. Dengan menggunakan data dalam memori lokal, Anda memuat paket ROCR , dan menggunakan fungsi prediksi dari paket tersebut untuk membuat beberapa prediksi baru.

    library('ROCR');
pred <- prediction(scoredOutput$Score, scoredOutput$tipped);

  3. Hasilkan plot lokal, berdasarkan nilai yang disimpan dalam variabel predoutput .

    acc.perf = performance(pred, measure = 'acc');
plot(acc.perf);
ind = which.max( slot(acc.perf, 'y.values')[[1]] );
acc = slot(acc.perf, 'y.values')[[1]][ind];
cutoff = slot(acc.perf, 'x.values')[[1]][ind];

    merencanakan performa model menggunakan R

Catatan

Bagan Anda mungkin terlihat berbeda dari ini, tergantung pada berapa banyak titik data yang Anda gunakan.

Menyebarkan model

Setelah Anda membangun model dan memastikan bahwa model tersebut berkinerja baik, Anda mungkin ingin menyebarkannya ke situs di mana pengguna atau orang di organisasi Anda dapat menggunakan model, atau mungkin melatih kembali dan mengkalibrasi ulang model secara teratur. Proses ini terkadang disebut mengoperasionalkan model. Di SQL Server, operasionalisasi dicapai dengan menyematkan kode R dalam prosedur tersimpan. Karena kode berada dalam prosedur, kode dapat dipanggil dari aplikasi apa pun yang dapat terhubung ke SQL Server.

Sebelum dapat memanggil model dari aplikasi eksternal, Anda harus menyimpan model ke database yang digunakan untuk produksi. Model terlatih disimpan dalam bentuk biner, dalam satu kolom jenis varbinary(maks).

Alur kerja penyebaran umum terdiri dari langkah-langkah berikut:

  1. Menserialisasikan model menjadi string heksadesimal
  2. Mengirimkan objek berseri ke database
  3. Menyimpan model dalam kolom varbinary(max)

Di bagian ini, pelajari cara menggunakan prosedur tersimpan untuk mempertahankan model dan membuatnya tersedia untuk prediksi. Prosedur tersimpan yang digunakan di bagian ini adalah PersistModel. Definisi PersistModel ada dalam Prasyarat.

  1. Beralih kembali ke lingkungan R lokal Anda jika Anda belum menggunakannya, serialisasi model, dan simpan dalam variabel.

    rxSetComputeContext("local");
modelbin <- serialize(logitObj, NULL);
modelbinstr=paste(modelbin, collapse="");

  2. Buka koneksi ODBC menggunakan RODBC. Anda dapat menghilangkan panggilan ke RODBC jika Anda sudah memiliki paket yang dimuat.

    library(RODBC);
conn <- odbcDriverConnect(connStr);

  3. Panggil prosedur tersimpan PersistModel di SQL Server untuk mengirimkan objek berseri ke database dan menyimpan representasi biner model dalam kolom.

    q <- paste("EXEC PersistModel @m='", modelbinstr,"'", sep="");
sqlQuery (conn, q);

  4. Gunakan Management Studio untuk memverifikasi bahwa model ada. Di Object Explorer, klik kanan pada tabel nyc_taxi_models dan klik Pilih 1000 Baris Teratas. Di Hasil, Anda akan melihat representasi biner di kolom model .

Menyimpan model ke tabel hanya memerlukan pernyataan INSERT. Namun, seringkali lebih mudah ketika dibungkus dalam prosedur tersimpan, seperti PersistModel.

Langkah berikutnya

Dalam pelajaran berikutnya dan terakhir, pelajari cara melakukan penilaian terhadap model yang disimpan menggunakan Transact-SQL.

Menyebarkan model R dan menggunakan di SQL