Tutorial Python: Menjalankan prediksi menggunakan Python yang disematkan dalam prosedur tersimpan

Artikel
05/30/2023

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

Di bagian lima dari seri tutorial lima bagian ini, Anda akan mempelajari cara mengoperasikan model yang Anda latih dan simpan di bagian sebelumnya.

Dalam skenario ini, operasionalisasi berarti menyebarkan model ke produksi untuk penilaian. Integrasi dengan SQL Server membuat ini cukup mudah, karena Anda dapat menyematkan kode Python dalam prosedur tersimpan. Untuk mendapatkan prediksi dari model berdasarkan input baru, cukup panggil prosedur tersimpan dari aplikasi dan teruskan data baru.

Bagian tutorial ini menunjukkan dua metode untuk membuat prediksi berdasarkan model Python: penilaian batch dan penilaian baris demi baris.

Penilaian batch: Untuk menyediakan beberapa baris data input, teruskan kueri SELECT sebagai argumen ke prosedur tersimpan. Hasilnya adalah tabel pengamatan yang sesuai dengan kasus input.
Penilaian individual: Berikan sekumpulan nilai parameter individual sebagai input. Prosedur tersimpan mengembalikan satu baris atau nilai.

Semua kode Python yang diperlukan untuk penilaian disediakan sebagai bagian dari prosedur tersimpan.

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 menjelajahi 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 batch

Dua prosedur tersimpan pertama yang dibuat menggunakan skrip berikut mengilustrasikan sintaks dasar untuk membungkus panggilan prediksi Python dalam prosedur tersimpan. Kedua prosedur tersimpan memerlukan tabel data sebagai input.

Nama model yang akan digunakan disediakan sebagai parameter input ke prosedur tersimpan. Prosedur tersimpan memuat model berseri dari tabel nyc_taxi_modelsdatabase .table, menggunakan pernyataan SELECT dalam prosedur tersimpan.
Model berseri disimpan dalam variabel mod Python untuk pemrosesan lebih lanjut menggunakan Python.
Kasus baru yang perlu dinilai diperoleh dari kueri Transact-SQL yang ditentukan dalam @input_data_1. Saat data kueri dibaca, baris disimpan dalam bingkai data default, InputDataSet.
Kedua prosedur tersimpan menggunakan fungsi dari sklearn untuk menghitung metrik akurasi, AUC (area di bawah kurva). Metrik akurasi seperti AUC hanya dapat dihasilkan jika Anda juga menyediakan label target ( kolom berujung ). Prediksi tidak memerlukan label target (variabel y), tetapi perhitungan metrik akurasi tidak.

Oleh karena itu, jika Anda tidak memiliki label target untuk data yang akan dinilai, Anda dapat memodifikasi prosedur tersimpan untuk menghapus perhitungan AUC, dan hanya mengembalikan probabilitas tip dari fitur (variabel X dalam prosedur tersimpan).

PredictTipSciKitPy

Jalankan pernyataan T-SQL berikut untuk membuat prosedur PredictTipSciKitPytersimpan . Prosedur tersimpan ini memerlukan model berdasarkan paket scikit-learn, karena menggunakan fungsi khusus untuk paket tersebut.

Bingkai data yang berisi input diteruskan ke predict_proba fungsi model regresi logistik, mod. Fungsi predict_proba (probArray = mod.predict_proba(X)) mengembalikan float yang mewakili probabilitas bahwa tip (dari jumlah apa pun) akan diberikan.

DROP PROCEDURE IF EXISTS PredictTipSciKitPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSciKitPy] (@model varchar(50), @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'Python',
  @script = N'
import pickle;
import numpy;
from sklearn import metrics

mod = pickle.loads(lmodel2)
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

probArray = mod.predict_proba(X)
probList = []
for i in range(len(probArray)):
  probList.append((probArray[i])[1])

probArray = numpy.asarray(probList)
fpr, tpr, thresholds = metrics.roc_curve(y, probArray)
aucResult = metrics.auc(fpr, tpr)
print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',	
  @input_data_1 = @inquery,
  @input_data_1_name = N'InputDataSet',
  @params = N'@lmodel2 varbinary(max)',
  @lmodel2 = @lmodel2
WITH RESULT SETS ((Score float));
END
GO

PredictTipRxPy

Jalankan pernyataan T-SQL berikut untuk membuat prosedur PredictTipRxPytersimpan . Prosedur tersimpan ini menggunakan input yang sama dan membuat jenis skor yang sama dengan prosedur tersimpan sebelumnya, tetapi menggunakan fungsi dari paket pencabutan skala yang disediakan dengan pembelajaran mesin SQL Server.

DROP PROCEDURE IF EXISTS PredictTipRxPy;
GO

CREATE PROCEDURE [dbo].[PredictTipRxPy] (@model varchar(50), @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'Python',
  @script = N'
import pickle;
import numpy;
from sklearn import metrics
from revoscalepy.functions.RxPredict import rx_predict;

mod = pickle.loads(lmodel2)
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

probArray = rx_predict(mod, X)
probList = probArray["tipped_Pred"].values 

probArray = numpy.asarray(probList)
fpr, tpr, thresholds = metrics.roc_curve(y, probArray)
aucResult = metrics.auc(fpr, tpr)
print ("AUC on testing data is: " + str(aucResult))

OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @input_data_1 = @inquery,
  @input_data_1_name = N'InputDataSet',
  @params = N'@lmodel2 varbinary(max)',
  @lmodel2 = @lmodel2
WITH RESULT SETS ((Score float));
END
GO

Menjalankan penilaian batch menggunakan kueri SELECT

Prosedur tersimpan PredictTipSciKitPy dan PredictTipRxPy memerlukan dua parameter input:

Kueri yang mengambil data untuk penilaian
Nama model terlatih

Dengan meneruskan argumen tersebut ke prosedur tersimpan, Anda dapat memilih model tertentu atau mengubah data yang digunakan untuk penilaian.

Untuk menggunakan model scikit-learn untuk penilaian, panggil prosedur tersimpan PredictTipSciKitPy, meneruskan nama model dan string kueri sebagai input.
```
DECLARE @query_string nvarchar(max) -- Specify input query
  SET @query_string='
  select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance,
  dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
  from nyctaxi_sample_testing'
EXEC [dbo].[PredictTipSciKitPy] 'SciKit_model', @query_string;
```
Prosedur tersimpan mengembalikan probabilitas yang diprediksi untuk setiap perjalanan yang diteruskan sebagai bagian dari kueri input.

Jika Anda menggunakan SSMS (SQL Server Management Studio) untuk menjalankan kueri, probabilitas akan muncul sebagai tabel di panel Hasil . Panel Pesan menghasilkan metrik akurasi (AUC atau area di bawah kurva) dengan nilai sekitar 0,56.

Untuk menggunakan model pencabutan skala untuk penilaian, panggil prosedur tersimpan PredictTipRxPy, meneruskan nama model dan string kueri sebagai input.

DECLARE @query_string nvarchar(max) -- Specify input query
  SET @query_string='
  select tipped, fare_amount, passenger_count, trip_time_in_secs, trip_distance,
  dbo.fnCalculateDistance(pickup_latitude, pickup_longitude,  dropoff_latitude, dropoff_longitude) as direct_distance
  from nyctaxi_sample_testing'
EXEC [dbo].[PredictTipRxPy] 'revoscalepy_model', @query_string;

Penilaian baris tunggal

Terkadang, alih-alih penilaian batch, Anda mungkin ingin meneruskan dalam satu kasus, mendapatkan nilai dari aplikasi, dan mengembalikan satu hasil berdasarkan nilai tersebut. Misalnya, Anda dapat menyiapkan lembar kerja Excel, aplikasi web, atau laporan untuk memanggil prosedur tersimpan dan meneruskannya ke input yang di ketik atau dipilih oleh pengguna.

Di bagian ini, Anda akan mempelajari cara membuat prediksi tunggal dengan memanggil dua prosedur tersimpan:

PredictTipSingleModeSciKitPy dirancang untuk penilaian baris tunggal menggunakan model scikit-learn.
PredictTipSingleModeRxPy dirancang untuk penilaian baris tunggal menggunakan model revoscalepy.
Jika Anda belum melatih model, kembali ke bagian lima!

Kedua model mengambil sebagai input serangkaian nilai tunggal, seperti jumlah penumpang, jarak perjalanan, dan sebagainya. Fungsi bernilai tabel, fnEngineerFeatures, digunakan untuk mengonversi nilai garis lintang dan bujur dari input ke fitur baru, jarak langsung. Bagian empat berisi deskripsi fungsi bernilai tabel ini.

Kedua prosedur tersimpan membuat skor berdasarkan model Python.

Catatan

Penting bagi Anda untuk menyediakan semua fitur input yang diperlukan oleh model Python saat Anda memanggil prosedur tersimpan dari aplikasi eksternal. Untuk menghindari kesalahan, Anda mungkin perlu mentransmisikan atau mengonversi data input ke jenis data Python, selain memvalidasi jenis data dan panjang data.

PredictTipSingleModeSciKitPy

Prosedur PredictTipSingleModeSciKitPy tersimpan berikut melakukan penilaian menggunakan model scikit-learn .

DROP PROCEDURE IF EXISTS PredictTipSingleModeSciKitPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSingleModeSciKitPy] (@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'Python',
  @script = N'
import pickle;
import numpy;

# Load model and unserialize
mod = pickle.loads(model)

# Get features for scoring from input data
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)
probList = []
probList.append((mod.predict_proba(X)[0])[1])

# Create output data frame
OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @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
GO

PredictTipSingleModeRxPy

Prosedur PredictTipSingleModeRxPy tersimpan berikut melakukan penilaian menggunakan model pencabutan skala.

DROP PROCEDURE IF EXISTS PredictTipSingleModeRxPy;
GO

CREATE PROCEDURE [dbo].[PredictTipSingleModeRxPy] (@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'Python',
  @script = N'
import pickle;
import numpy;
from revoscalepy.functions.RxPredict import rx_predict;

# Load model and unserialize
mod = pickle.loads(model)

# Get features for scoring from input data
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]

# Score data to get tip prediction probability as a list (of float)

probArray = rx_predict(mod, X)

probList = []
probList = probArray["tipped_Pred"].values

# Create output data frame
OutputDataSet = pandas.DataFrame(data = probList, columns = ["predictions"])
',
  @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
GO

Hasilkan skor dari model

Setelah prosedur tersimpan dibuat, mudah untuk menghasilkan skor berdasarkan salah satu model. Buka jendela Kueri baru dan berikan parameter untuk setiap kolom fitur.

Tujuh nilai yang diperlukan untuk kolom fitur ini adalah, secara berurutan:

passenger_count
trip_distance
trip_time_in_secs
pickup_latitude
pickup_longitude
dropoff_latitude
dropoff_longitude

Contohnya:

Untuk menghasilkan prediksi dengan menggunakan model pencabutan skala , jalankan pernyataan ini:

EXEC [dbo].[PredictTipSingleModeRxPy] 'revoscalepy_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

Untuk menghasilkan skor dengan menggunakan model scikit-learn , jalankan pernyataan ini:

EXEC [dbo].[PredictTipSingleModeSciKitPy] 'SciKit_model', 1, 2.5, 631, 40.763958,-73.973373, 40.782139,-73.977303

Output dari kedua prosedur adalah probabilitas tip yang dibayar untuk perjalanan taksi dengan parameter atau fitur yang ditentukan.

Kesimpulan

Dalam seri tutorial ini, Anda telah mempelajari cara bekerja dengan kode Python yang disematkan dalam prosedur tersimpan. Integrasi dengan Transact-SQL membuatnya jauh lebih mudah untuk menyebarkan model Python 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 Python, lihat Ekstensi Python di SQL Server.

Bagikan melalui