Menyebarkan model R dan menggunakannya di SQL Server (panduan)

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

Dalam pelajaran ini, pelajari cara menyebarkan model R di lingkungan produksi dengan memanggil model terlatih dari prosedur tersimpan. Anda dapat memanggil prosedur tersimpan dari R atau bahasa pemrograman aplikasi apa pun yang mendukung Transact-SQL (seperti C#, Java, Python, dan sebagainya) dan menggunakan model untuk membuat prediksi pada pengamatan baru.

Artikel ini menunjukkan dua cara paling umum untuk menggunakan model dalam penilaian:

Mode penilaian batch menghasilkan beberapa prediksi
Mode penilaian individu menghasilkan prediksi satu per satu

Penilaian batch

Buat prosedur tersimpan, PredictTipBatchMode, yang menghasilkan beberapa prediksi, meneruskan kueri atau tabel SQL sebagai input. Tabel hasil dikembalikan, yang mungkin Anda sisipkan langsung ke dalam tabel atau tulis ke file.

Mendapatkan sekumpulan data input sebagai kueri SQL
Memanggil model regresi logistik terlatih yang Anda simpan dalam pelajaran sebelumnya
Memprediksi probabilitas bahwa driver mendapatkan tip bukan nol

Di Management Studio, buka jendela kueri baru dan jalankan skrip T-SQL berikut untuk membuat prosedur tersimpan PredictTipBatchMode.
```
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 = 'PredictTipBatchMode')
DROP PROCEDURE v
GO

CREATE PROCEDURE [dbo].[PredictTipBatchMode] @input nvarchar(max)
AS
BEGIN
  DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1 model  FROM nyc_taxi_models);
  EXEC sp_execute_external_script @language = N'R',
     @script = N'
       mod <- unserialize(as.raw(model));
       print(summary(mod))
       OutputDataSet<-rxPredict(modelObject = mod,
         data = InputDataSet,
         outData = NULL,
         predVarNames = "Score", type = "response",
         writeModelVars = FALSE, overwrite = TRUE);
       str(OutputDataSet)
       print(OutputDataSet)',
  @input_data_1 = @input,
  @params = N'@model varbinary(max)',
  @model = @lmodel2
  WITH RESULT SETS ((Score float));
END
```
- Anda menggunakan pernyataan SELECT untuk memanggil model tersimpan dari tabel SQL. Model diambil dari tabel sebagai data varbinary(max), disimpan dalam variabel SQL @lmodel2, dan diteruskan sebagai mod parameter ke prosedur tersimpan sistem sp_execute_external_script.
- Data yang digunakan sebagai input untuk penilaian didefinisikan sebagai kueri SQL dan disimpan sebagai string dalam variabel SQL @input. Karena data diambil dari database, data disimpan dalam bingkai data yang disebut InputDataSet, yang hanya merupakan nama default untuk data input ke prosedur sp_execute_external_script ; Anda dapat menentukan nama variabel lain jika diperlukan dengan menggunakan parameter @input_data_1_name.
- Untuk menghasilkan skor, prosedur tersimpan memanggil fungsi rxPredict dari pustaka RevoScaleR .
- Nilai yang dikembalikan, Skor, adalah probabilitas, mengingat model, driver tersebut mendapatkan tip. Secara opsional, Anda dapat dengan mudah menerapkan semacam filter ke nilai yang dikembalikan untuk mengategorikan nilai yang dikembalikan ke dalam grup "tip" dan "tanpa tip". Misalnya, kemungkinan kurang dari 0,5 berarti tip tidak mungkin.

Untuk memanggil prosedur tersimpan dalam mode batch, Anda menentukan kueri yang diperlukan sebagai input ke prosedur tersimpan. Di bawah ini adalah kueri SQL, yang dapat Anda jalankan di SSMS untuk memverifikasi bahwa kueri berfungsi.

SELECT TOP 10
  a.passenger_count AS passenger_count,
  a.trip_time_in_secs AS trip_time_in_secs,
  a.trip_distance AS trip_distance,
  a.dropoff_datetime AS dropoff_datetime,
  dbo.fnCalculateDistance( pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) AS direct_distance
  FROM 
    (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude 
    FROM nyctaxi_sample)a 
  LEFT OUTER JOIN
  ( SELECT medallion, hack_license, pickup_datetime
  FROM nyctaxi_sample  tablesample (1 percent) repeatable (98052)  )b
  ON a.medallion=b.medallion
  AND a.hack_license=b.hack_license
  AND a.pickup_datetime=b.pickup_datetime
  WHERE b.medallion is null

Gunakan kode R ini untuk membuat string input dari kueri SQL:

input <- "N'SELECT TOP 10 a.passenger_count AS passenger_count, a.trip_time_in_secs AS trip_time_in_secs, a.trip_distance AS trip_distance, a.dropoff_datetime AS dropoff_datetime, dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) AS direct_distance FROM (SELECT medallion, hack_license, pickup_datetime, passenger_count,trip_time_in_secs,trip_distance, dropoff_datetime, pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude FROM nyctaxi_sample)a LEFT OUTER JOIN ( SELECT medallion, hack_license, pickup_datetime FROM nyctaxi_sample  tablesample (1 percent) repeatable (98052)  )b ON a.medallion=b.medallion AND a.hack_license=b.hack_license AND  a.pickup_datetime=b.pickup_datetime WHERE b.medallion is null'";
q <- paste("EXEC PredictTipBatchMode @input = ", input, sep="");

Untuk menjalankan prosedur tersimpan dari R, panggil metode sqlQuery dari paket RODBC dan gunakan koneksi conn SQL yang Anda tentukan sebelumnya:
```
sqlQuery (conn, q);
```
Jika Anda mendapatkan kesalahan ODBC, periksa kesalahan sintaksis dan apakah Anda memiliki jumlah tanda kutip yang tepat.

Jika Anda mendapatkan kesalahan izin, pastikan login memiliki kemampuan untuk menjalankan prosedur tersimpan.

Penilaian baris tunggal

Mode penilaian individual menghasilkan prediksi satu per satu, meneruskan sekumpulan nilai individual ke prosedur tersimpan sebagai input. Nilai sesuai dengan fitur dalam model, yang digunakan model untuk membuat prediksi, atau menghasilkan hasil lain seperti nilai probabilitas. Anda kemudian dapat mengembalikan nilai tersebut ke aplikasi, atau pengguna.

Saat memanggil model untuk prediksi berdasarkan baris demi baris, Anda meneruskan serangkaian nilai yang mewakili fitur untuk setiap kasus individual. Prosedur tersimpan kemudian mengembalikan satu prediksi atau probabilitas.

Prosedur tersimpan PredictTipSingleMode menunjukkan pendekatan ini. Dibutuhkan sebagai input beberapa parameter yang mewakili nilai fitur (misalnya, jumlah penumpang dan jarak perjalanan), menilai fitur-fitur ini menggunakan model R yang disimpan, dan menghasilkan probabilitas tip.

Jalankan pernyataan Transact-SQL berikut 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 = 'PredictTipSingleMode')
DROP PROCEDURE v
GO

CREATE PROCEDURE [dbo].[PredictTipSingleMode] @passenger_count int = 0,
@trip_distance float = 0,
@trip_time_in_secs int = 0,
@pickup_latitude float = 0,
@pickup_longitude float = 0,
@dropoff_latitude float = 0,
@dropoff_longitude float = 0
AS
BEGIN
  DECLARE @inquery nvarchar(max) = N'
    SELECT * FROM [dbo].[fnEngineerFeatures](@passenger_count, @trip_distance, @trip_time_in_secs, @pickup_latitude, @pickup_longitude, @dropoff_latitude, @dropoff_longitude)'
  DECLARE @lmodel2 varbinary(max) = (SELECT TOP 1 model FROM nyc_taxi_models);

  EXEC sp_execute_external_script @language = N'R',  @script = N'
        mod <- unserialize(as.raw(model));
        print(summary(mod))
        OutputDataSet<-rxPredict(
          modelObject = mod,
          data = InputDataSet,
          outData = NULL,
          predVarNames = "Score",
          type = "response",
          writeModelVars = FALSE,
          overwrite = TRUE);
        str(OutputDataSet)
        print(OutputDataSet)
        ',
  @input_data_1 = @inquery,
  @params = N'
  -- passthrough columns
  @model varbinary(max) ,
  @passenger_count int ,
  @trip_distance float ,
  @trip_time_in_secs int ,
  @pickup_latitude float ,
  @pickup_longitude float ,
  @dropoff_latitude float ,
  @dropoff_longitude float',
  -- mapped variables
  @model = @lmodel2 ,
  @passenger_count =@passenger_count ,
  @trip_distance=@trip_distance ,
  @trip_time_in_secs=@trip_time_in_secs ,
  @pickup_latitude=@pickup_latitude ,
  @pickup_longitude=@pickup_longitude ,
  @dropoff_latitude=@dropoff_latitude ,
  @dropoff_longitude=@dropoff_longitude
  WITH RESULT SETS ((Score float));
END

Di SQL Server Management Studio, Anda dapat menggunakan prosedur Transact-SQL EXEC (atau EXECUTE) untuk memanggil prosedur tersimpan, dan meneruskannya input yang diperlukan. Misalnya, coba jalankan pernyataan ini di Management Studio:
```
EXEC [dbo].[PredictTipSingleMode] 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303
```
Nilai yang diteruskan di sini masing-masing, untuk variabel passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude, dan dropoff_longitude.
Untuk menjalankan panggilan yang sama ini dari kode R, Anda cukup menentukan variabel R yang berisi seluruh panggilan prosedur tersimpan, seperti ini:
```
q2 = "EXEC PredictTipSingleMode 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303 ";
```
Nilai yang diteruskan di sini masing-masing, untuk variabel passenger_count, trip_distance, trip_time_in_secs, pickup_latitude, pickup_longitude, dropoff_latitude, dan dropoff_longitude.
Panggil sqlQuery (dari paket RODBC) dan teruskan string koneksi, bersama dengan variabel string yang berisi panggilan prosedur tersimpan.
```
# predict with stored procedure in single mode
sqlQuery (conn, q2);
```
Tip

R Tools for Visual Studio (RTVS) menyediakan integrasi yang hebat dengan SQL Server dan R. Lihat artikel ini untuk contoh penggunaan RODBC lainnya dengan koneksi SQL Server: Bekerja dengan SQL Server dan R

Langkah berikutnya

Sekarang setelah Anda mempelajari cara bekerja dengan data SQL Server dan mempertahankan model R terlatih ke SQL Server, seharusnya relatif mudah bagi Anda untuk membuat model baru berdasarkan himpunan data ini. Misalnya, Anda dapat mencoba membuat model tambahan ini:

Model regresi yang memprediksi jumlah tip
Model klasifikasi multikelas yang memprediksi apakah tipnya besar, sedang, atau kecil

Anda mungkin juga ingin menjelajahi sampel dan sumber daya tambahan ini:

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-01-02