Tutorial R: Menjalankan prediksi dalam prosedur tersimpan SQL

Artikel
05/30/2023

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

Di bagian lima dari seri tutorial lima bagian ini, Anda akan belajar mengoprasionalkan model yang Anda latih dan simpan di bagian sebelumnya dengan menggunakan model untuk memprediksi potensi hasil. Model ini dibungkus dalam prosedur tersimpan yang dapat dipanggil langsung oleh aplikasi lain.

Artikel ini menunjukkan dua cara untuk melakukan penilaian:

Mode penilaian batch: Gunakan kueri SELECT sebagai input ke prosedur tersimpan. Prosedur tersimpan mengembalikan tabel pengamatan yang sesuai dengan kasus input.
Mode penilaian individual: Berikan sekumpulan nilai parameter individual sebagai input. Prosedur tersimpan mengembalikan satu baris atau nilai.

Dalam artikel ini, Anda akan:

Membuat dan menggunakan prosedur tersimpan untuk penilaian batch
Membuat dan menggunakan prosedur tersimpan untuk menilai satu baris

Di bagian satu, Anda menginstal prasyarat dan memulihkan database sampel.

Di bagian dua, Anda meninjau data sampel dan menghasilkan beberapa plot.

Di bagian tiga, Anda mempelajari cara membuat fitur dari data mentah dengan menggunakan fungsi Transact-SQL. Anda kemudian memanggil fungsi tersebut dari prosedur tersimpan untuk membuat tabel yang berisi nilai fitur.

Di bagian empat, Anda memuat modul dan memanggil fungsi yang diperlukan untuk membuat dan melatih model menggunakan prosedur tersimpan SQL Server.

Penilaian dasar

Prosedur tersimpan RPredict mengilustrasikan sintaks dasar untuk membungkus PREDICT panggilan dalam prosedur tersimpan.

CREATE PROCEDURE [dbo].[RPredict] (@model varchar(250), @inquery nvarchar(max))
AS 
BEGIN 

DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);  
EXEC sp_execute_external_script @language = N'R',
  @script = N' 
    mod <- unserialize(as.raw(model));
    print(summary(mod))
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet)
    print(OutputDataSet)
    ',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max)',
  @model = @lmodel2 
  WITH RESULT SETS (("Score" float));
END
GO

Pernyataan SELECT mendapatkan model berseri dari database, dan menyimpan model dalam variabel mod R untuk pemrosesan lebih lanjut menggunakan R.
Kasus baru untuk penilaian diperoleh dari kueri Transact-SQL yang ditentukan dalam @inquery, parameter pertama ke prosedur tersimpan. Saat data kueri dibaca, baris disimpan dalam bingkai data default, InputDataSet. Bingkai data ini diteruskan ke fungsi PREDICT yang menghasilkan skor.

OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));

Karena data.frame dapat berisi satu baris, Anda dapat menggunakan kode yang sama untuk batch atau penilaian tunggal.
Nilai yang dikembalikan oleh PREDICT fungsi adalah float yang mewakili probabilitas bahwa driver mendapatkan tip dari jumlah apa pun.

Penilaian batch (daftar prediksi)

Skenario yang lebih umum adalah menghasilkan prediksi untuk beberapa pengamatan dalam mode batch. Dalam langkah ini, mari kita lihat cara kerja penilaian batch.

Mulailah dengan mendapatkan sekumpulan data input yang lebih kecil untuk dikerjakan. Kueri ini membuat daftar perjalanan "10 teratas" dengan jumlah penumpang dan fitur lain yang diperlukan untuk membuat prediksi.

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 (70 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

Hasil sampel

passenger_count   trip_time_in_secs    trip_distance  dropoff_datetime          direct_distance
1                 283                  0.7            2013-03-27 14:54:50.000   0.5427964547
1                 289                  0.7            2013-02-24 12:55:29.000   0.3797099614
1                 214                  0.7            2013-06-26 13:28:10.000   0.6970098661

Buat prosedur tersimpan yang disebut RPredictBatchOutput di Management Studio.

CREATE PROCEDURE [dbo].[RPredictBatchOutput] (@model varchar(250), @inquery nvarchar(max))
AS
BEGIN
DECLARE @lmodel2 varbinary(max) = (SELECT model FROM nyc_taxi_models WHERE name = @model);
EXEC sp_execute_external_script 
  @language = N'R',
  @script = N'
    mod <- unserialize(as.raw(model));
    print(summary(mod))
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet)
    print(OutputDataSet)
  ',
  @input_data_1 = @inquery,
  @params = N'@model varbinary(max)',
  @model = @lmodel2
  WITH RESULT SETS ((Score float));
END

Berikan teks kueri dalam variabel dan teruskan sebagai parameter ke prosedur tersimpan:

-- Define the input data
DECLARE @query_string nvarchar(max)
SET @query_string='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 (70 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'

-- Call the stored procedure for scoring and pass the input data
EXEC [dbo].[RPredictBatchOutput] @model = 'RTrainLogit_model', @inquery = @query_string;

Prosedur tersimpan mengembalikan serangkaian nilai yang mewakili prediksi untuk masing-masing dari 10 perjalanan teratas. Namun, perjalanan teratas juga merupakan perjalanan penumpang tunggal dengan jarak perjalanan yang relatif singkat, di mana pengemudi tidak mungkin mendapatkan tip.

Tip

Daripada hanya mengembalikan hasil "yes-tip" dan "no-tip", Anda juga dapat mengembalikan skor probabilitas untuk prediksi, dan kemudian menerapkan klausul WHERE ke nilai kolom Skor untuk mengategorikan skor sebagai "kemungkinan tip" atau "tidak mungkin untuk tip", menggunakan nilai ambang seperti 0,5 atau 0,7. Langkah ini tidak termasuk dalam prosedur tersimpan tetapi akan mudah diterapkan.

Penilaian baris tunggal dari beberapa input

Terkadang Anda ingin meneruskan beberapa nilai input dan mendapatkan satu prediksi berdasarkan nilai-nilai tersebut. Misalnya, Anda bisa menyiapkan lembar kerja Excel, aplikasi web, atau laporan Reporting Services untuk memanggil prosedur tersimpan dan menyediakan input yang dititik atau dipilih oleh pengguna dari aplikasi tersebut.

Di bagian ini, Anda mempelajari cara membuat prediksi tunggal menggunakan prosedur tersimpan yang mengambil beberapa input, seperti jumlah penumpang, jarak perjalanan, dan sebagainya. Prosedur tersimpan membuat skor berdasarkan model R yang disimpan sebelumnya.

Jika Anda memanggil prosedur tersimpan dari aplikasi eksternal, pastikan bahwa data cocok dengan persyaratan model R. Ini mungkin termasuk memastikan bahwa data input dapat ditransmisikan atau dikonversi ke jenis data R, atau memvalidasi jenis data dan panjang data.

Buat prosedur tersimpan RPredictSingleRow.

CREATE PROCEDURE [dbo].[RPredictSingleRow] @model varchar(50), @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 model FROM nyc_taxi_models WHERE name = @model);
EXEC sp_execute_external_script  
  @language = N'R',
  @script = N'  
    mod <- unserialize(as.raw(model));  
    print(summary(mod));  
    OutputDataSet <- data.frame(predict(mod, InputDataSet, type = "response"));
    str(OutputDataSet);
    print(OutputDataSet); 
    ',  
  @input_data_1 = @inquery,  
  @params = N'@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', @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

Cobalah, dengan memberikan nilai secara manual.

Buka jendela Kueri baru, dan panggil prosedur tersimpan, yang menyediakan nilai untuk setiap parameter. Parameter mewakili kolom fitur yang digunakan oleh model dan diperlukan.

EXEC [dbo].[RPredictSingleRow] @model = 'RTrainLogit_model',
@passenger_count = 1,
@trip_distance = 2.5,
@trip_time_in_secs = 631,
@pickup_latitude = 40.763958,
@pickup_longitude = -73.973373,
@dropoff_latitude =  40.782139,
@dropoff_longitude = -73.977303

Atau, gunakan formulir yang lebih pendek ini yang didukung untuk parameter ke prosedur tersimpan:

EXEC [dbo].[RPredictSingleRow] 'RTrainLogit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

Hasilnya menunjukkan bahwa kemungkinan mendapatkan tip adalah rendah (nol) pada 10 perjalanan teratas ini, karena semuanya adalah perjalanan penumpang tunggal dalam jarak yang relatif pendek.

Kesimpulan

Sekarang setelah Anda belajar menyematkan kode R dalam prosedur tersimpan, Anda dapat memperluas praktik ini untuk membangun model Anda sendiri. Integrasi dengan Transact-SQL memudahkan penyebaran model R untuk prediksi dan menggabungkan pelatihan ulang model sebagai bagian dari alur kerja data perusahaan.

Langkah berikutnya

Dalam artikel ini, Anda:

Prosedur tersimpan yang dibuat dan digunakan untuk penilaian batch
Prosedur tersimpan yang dibuat dan digunakan untuk menilai satu baris

Untuk informasi selengkapnya tentang R, lihat Ekstensi R di SQL Server.