Bagikan melalui

Tutorial Python: Melatih dan menyimpan model Python menggunakan T-SQL

  • Artikel

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

Di bagian empat dari seri tutorial lima bagian ini, Anda akan mempelajari cara melatih model pembelajaran mesin menggunakan paket Python scikit-learn dan revoscalepy. Pustaka Python ini sudah diinstal dengan pembelajaran mesin SQL Server.

Anda akan memuat modul dan memanggil fungsi yang diperlukan untuk membuat dan melatih model menggunakan prosedur tersimpan SQL Server. Model ini memerlukan fitur data yang Anda rekayasa di bagian sebelumnya dari seri tutorial ini. Terakhir, Anda akan menyimpan model terlatih ke tabel SQL Server.

Dalam artikel ini, Anda akan:

  • Membuat dan melatih model menggunakan prosedur tersimpan SQL
  • Menyimpan model terlatih ke tabel SQL

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 lima, Anda akan mempelajari cara mengoperalisasi model yang Anda latih dan simpan di bagian empat.

Membagi data sampel menjadi set pelatihan dan pengujian

  1. Buat prosedur tersimpan yang disebut PyTrainTestSplit untuk membagi data dalam tabel nyctaxi_sample menjadi dua bagian: nyctaxi_sample_training dan nyctaxi_sample_testing.

    Jalankan kode berikut untuk membuatnya:

    DROP PROCEDURE IF EXISTS PyTrainTestSplit;
GO

CREATE PROCEDURE [dbo].[PyTrainTestSplit] (@pct int)
AS

DROP TABLE IF EXISTS dbo.nyctaxi_sample_training
SELECT * into nyctaxi_sample_training FROM nyctaxi_sample WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) < @pct

DROP TABLE IF EXISTS dbo.nyctaxi_sample_testing
SELECT * into nyctaxi_sample_testing FROM nyctaxi_sample
WHERE (ABS(CAST(BINARY_CHECKSUM(medallion,hack_license)  as int)) % 100) > @pct
GO

  2. Untuk membagi data Anda menggunakan pemisahan kustom, jalankan prosedur tersimpan, dan berikan parameter bilangan bulat yang mewakili persentase data untuk dialokasikan ke set pelatihan. Misalnya, pernyataan berikut akan mengalokasikan 60% data ke set pelatihan.

    EXEC PyTrainTestSplit 60
GO

Membangun model regresi logistik

Setelah data disiapkan, Anda dapat menggunakannya untuk melatih model. Anda melakukan ini dengan memanggil prosedur tersimpan yang menjalankan beberapa kode Python, mengambil sebagai input tabel data pelatihan. Untuk tutorial ini, Anda membuat dua model, kedua model klasifikasi biner:

  • Prosedur tersimpan PyTrainScikit membuat model prediksi tip menggunakan paket scikit-learn.
  • Prosedur tersimpan TrainTipPredictionModelRxPy membuat model prediksi tip menggunakan paket revoscalepy.

Setiap prosedur tersimpan menggunakan data input yang Anda berikan untuk membuat dan melatih model regresi logistik. Semua kode Python dibungkus dalam prosedur tersimpan sistem, sp_execute_external_script.

Untuk mempermudah melatih kembali model pada data baru, Anda membungkus panggilan ke sp_execute_external_script dalam prosedur tersimpan lain, dan meneruskan data pelatihan baru sebagai parameter. Bagian ini akan memanding Anda melalui proses tersebut.

PyTrainScikit

  1. Di Management Studio, buka jendela Kueri baru dan jalankan pernyataan berikut untuk membuat prosedur tersimpan PyTrainScikit. Prosedur tersimpan berisi definisi data input, sehingga Anda tidak perlu memberikan kueri input.

    DROP PROCEDURE IF EXISTS PyTrainScikit;
GO

CREATE PROCEDURE [dbo].[PyTrainScikit] (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'
import numpy
import pickle
from sklearn.linear_model import LogisticRegression

##Create SciKit-Learn logistic regression model
X = InputDataSet[["passenger_count", "trip_distance", "trip_time_in_secs", "direct_distance"]]
y = numpy.ravel(InputDataSet[["tipped"]])

SKLalgo = LogisticRegression()
logitObj = SKLalgo.fit(X, y)

##Serialize model
trained_model = pickle.dumps(logitObj)
',
@input_data_1 = N'
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_training
',
@input_data_1_name = N'InputDataSet',
@params = N'@trained_model varbinary(max) OUTPUT',
@trained_model = @trained_model OUTPUT;
;
END;
GO

  2. Jalankan pernyataan SQL berikut untuk menyisipkan model terlatih ke dalam tabel nyc_taxi_models.

    DECLARE @model VARBINARY(MAX);
EXEC PyTrainScikit @model OUTPUT;
INSERT INTO nyc_taxi_models (name, model) VALUES('SciKit_model', @model);

    Pemrosesan data dan pas model mungkin memakan waktu beberapa menit. Pesan yang akan disalurkan ke aliran stdout Python ditampilkan di jendela Pesan Management Studio. Contohnya:

    STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

  3. Buka tabel nyc_taxi_models. Anda dapat melihat bahwa satu baris baru telah ditambahkan, yang berisi model berseri dalam model kolom.

    SciKit_model
0x800363736B6C6561726E2E6C696E6561....

TrainTipPredictionModelRxPy

Prosedur tersimpan ini menggunakan paket Python revoscalepy . Ini berisi objek, transformasi, dan algoritma yang mirip dengan yang disediakan untuk paket RevoScaleR bahasa R.

Dengan menggunakan revoscalepy, Anda dapat membuat konteks komputasi jarak jauh, memindahkan data antara konteks komputasi, mengubah data, dan melatih model prediktif menggunakan algoritma populer seperti regresi logistik dan linier, pohon keputusan, dan banyak lagi. Untuk informasi selengkapnya, lihat modul pencabutan skala di SQL Server dan referensi fungsi pencabutan skala.

  1. Di Management Studio, buka jendela Kueri baru dan jalankan pernyataan berikut untuk membuat prosedur tersimpan TrainTipPredictionModelRxPy. Karena prosedur tersimpan sudah menyertakan definisi data input, Anda tidak perlu memberikan kueri input.

    DROP PROCEDURE IF EXISTS TrainTipPredictionModelRxPy;
GO

CREATE PROCEDURE [dbo].[TrainTipPredictionModelRxPy] (@trained_model varbinary(max) OUTPUT)
AS
BEGIN
EXEC sp_execute_external_script 
  @language = N'Python',
  @script = N'
import numpy
import pickle
from revoscalepy.functions.RxLogit import rx_logit

## Create a logistic regression model using rx_logit function from revoscalepy package
logitObj = rx_logit("tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance", data = InputDataSet);

## Serialize model
trained_model = pickle.dumps(logitObj)
',
@input_data_1 = N'
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_training
',
@input_data_1_name = N'InputDataSet',
@params = N'@trained_model varbinary(max) OUTPUT',
@trained_model = @trained_model OUTPUT;
;
END;
GO

    Prosedur tersimpan ini melakukan langkah-langkah berikut sebagai bagian dari pelatihan model:

    • Kueri SELECT menerapkan fungsi skalar kustom fnCalculateDistance untuk menghitung jarak langsung antara lokasi penjemputan dan pengantaran. Hasil kueri disimpan dalam variabel input Python default, InputDataset.
    • Variabel biner yang diberi tip digunakan sebagai label atau kolom hasil, dan model cocok menggunakan kolom fitur ini: passenger_count, trip_distance, trip_time_in_secs, dan direct_distance.
    • Model terlatih diserialisasikan dan disimpan dalam variabel logitObjPython . Dengan menambahkan OUTPUT kata kunci T-SQL, Anda dapat menambahkan variabel sebagai output dari prosedur tersimpan. Pada langkah berikutnya, variabel tersebut digunakan untuk menyisipkan kode biner model ke dalam tabel database nyc_taxi_models. Mekanisme ini memudahkan untuk menyimpan dan menggunakan kembali model.

  2. Jalankan prosedur tersimpan sebagai berikut untuk menyisipkan model revoscalepy terlatih ke dalam tabel nyc_taxi_models.

    DECLARE @model VARBINARY(MAX);
EXEC TrainTipPredictionModelRxPy @model OUTPUT;
INSERT INTO nyc_taxi_models (name, model) VALUES('revoscalepy_model', @model);

    Pemrosesan data dan pas model mungkin memakan waktu cukup lama. Pesan yang akan disalurkan ke aliran stdout Python ditampilkan di jendela Pesan Management Studio. Contohnya:

    STDOUT message(s) from external script:
C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\lib\site-packages\revoscalepy

  3. Buka tabel nyc_taxi_models. Anda dapat melihat bahwa satu baris baru telah ditambahkan, yang berisi model berseri dalam model kolom.

    revoscalepy_model
0x8003637265766F7363616c....

Di bagian berikutnya dari tutorial ini, Anda akan menggunakan model terlatih untuk membuat prediksi.

Langkah berikutnya

Dalam artikel ini, Anda:

  • Membuat dan melatih model menggunakan prosedur tersimpan SQL
  • Menyimpan model terlatih ke tabel SQL

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