Mulai cepat: Membuat dan menilai model prediktif di Python dengan pembelajaran mesin SQL

Artikel
05/30/2023

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

Dalam mulai cepat ini, Anda akan membuat dan melatih model prediktif menggunakan Python. Anda akan menyimpan model ke tabel di instans SQL Server Anda, lalu menggunakan model untuk memprediksi nilai dari data baru menggunakan SQL Server Machine Learning Services, Azure SQL Managed Instance Machine Learning Services, atau SQL Server Kluster Big Data .

Anda akan membuat dan menjalankan dua prosedur tersimpan yang berjalan di SQL. Yang pertama menggunakan himpunan data bunga Iris klasik dan menghasilkan model Naïve Bayes untuk memprediksi spesies Iris berdasarkan karakteristik bunga. Prosedur kedua adalah untuk penilaian - ini memanggil model yang dihasilkan dalam prosedur pertama untuk menghasilkan serangkaian prediksi berdasarkan data baru. Dengan menempatkan kode Python dalam prosedur tersimpan SQL, operasi terkandung dalam SQL, dapat digunakan kembali, dan dapat dipanggil oleh prosedur tersimpan lainnya dan aplikasi klien.

Dengan menyelesaikan mulai cepat ini, Anda akan mempelajari:

Cara menyematkan kode Python dalam prosedur tersimpan
Cara meneruskan input ke kode Anda melalui input pada prosedur tersimpan
Bagaimana prosedur tersimpan digunakan untuk mengoprasionalkan model

Prasyarat

Anda memerlukan prasyarat berikut untuk menjalankan mulai cepat ini.

Database SQL pada salah satu platform ini:
- SQL Server Layanan Pembelajaran Mesin. Untuk menginstal, lihat panduan penginstalan Windows atau panduan penginstalan Linux.
- SQL Server Kluster Big Data. Lihat cara mengaktifkan Layanan Pembelajaran Mesin di SQL Server Kluster Big Data.
- Azure SQL Managed Instance Layanan Pembelajaran Mesin. Untuk informasi, lihat ringkasan Azure SQL Managed Instance Machine Learning Services.
Alat untuk menjalankan kueri SQL yang berisi skrip Python. Mulai cepat ini menggunakan Azure Data Studio.
Data sampel yang digunakan dalam latihan ini adalah data sampel Iris. Ikuti instruksi dalam data demo Iris untuk membuat contoh database irissql.

Membuat prosedur tersimpan yang menghasilkan model

Dalam langkah ini, Anda akan membuat prosedur tersimpan yang menghasilkan model untuk memprediksi hasil.

Buka Azure Data Studio, sambungkan ke instans SQL Anda, dan buka jendela kueri baru.
Sambungkan ke database irissql.
```
USE irissql
GO
```
Salin dalam kode berikut untuk membuat prosedur tersimpan baru.

Ketika dijalankan, prosedur ini memanggil sp_execute_external_script untuk memulai sesi Python.

Input yang diperlukan oleh kode Python Anda diteruskan sebagai parameter input pada prosedur tersimpan ini. Output akan menjadi model terlatih, berdasarkan pustaka scikit-learn Python untuk algoritma pembelajaran mesin.

Kode ini menggunakan acar untuk menserialisasikan model. Model akan dilatih menggunakan data dari kolom 0 hingga 4 dari tabel iris_data .

Parameter yang Anda lihat di bagian kedua dari prosedur mengartikulasikan input data dan output model. Sebanyak mungkin, Anda ingin kode Python berjalan dalam prosedur tersimpan memiliki input dan output yang ditentukan dengan jelas yang memetakan input dan output prosedur tersimpan yang diteruskan pada durasi.
```
CREATE PROCEDURE generate_iris_model (@trained_model VARBINARY(max) OUTPUT)
AS
BEGIN
    EXECUTE sp_execute_external_script @language = N'Python'
        , @script = N'
import pickle
from sklearn.naive_bayes import GaussianNB
GNB = GaussianNB()
trained_model = pickle.dumps(GNB.fit(iris_data[["Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"]], iris_data[["SpeciesId"]].values.ravel()))
'
        , @input_data_1 = N'select "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
        , @input_data_1_name = N'iris_data'
        , @params = N'@trained_model varbinary(max) OUTPUT'
        , @trained_model = @trained_model OUTPUT;
END;
GO
```
Verifikasi prosedur tersimpan ada.

Jika skrip T-SQL dari langkah sebelumnya berjalan tanpa kesalahan, prosedur tersimpan baru yang disebut generate_iris_model dibuat dan ditambahkan ke database irissql . Anda dapat menemukan prosedur tersimpan di Object Explorer Azure Data Studio, di bawah Keterprograman.

Jalankan prosedur untuk membuat dan melatih model

Dalam langkah ini, Anda menjalankan prosedur untuk menjalankan kode yang disematkan, membuat model terlatih dan berseri sebagai output.

Model yang disimpan untuk digunakan kembali dalam database Anda diserialisasikan sebagai aliran byte dan disimpan dalam kolom VARBINARY (MAX) dalam tabel database. Setelah model dibuat, dilatih, diserialisasikan, dan disimpan ke database, model dapat dipanggil oleh prosedur lain atau oleh fungsi PREDICT T-SQL dalam menilai beban kerja.

Jalankan skrip berikut untuk menjalankan prosedur. Pernyataan khusus untuk menjalankan prosedur tersimpan ada EXECUTE di baris keempat.

Skrip khusus ini menghapus model yang ada dengan nama yang sama ("Naive Bayes") untuk memberi ruang bagi yang baru yang dibuat dengan menjalankan ulang prosedur yang sama. Tanpa penghapusan model, terjadi kesalahan yang menyatakan objek sudah ada. Model disimpan dalam tabel yang disebut iris_models, disediakan saat Anda membuat database irissql .
```
DECLARE @model varbinary(max);
DECLARE @new_model_name varchar(50)
SET @new_model_name = 'Naive Bayes'
EXECUTE generate_iris_model @model OUTPUT;
DELETE iris_models WHERE model_name = @new_model_name;
INSERT INTO iris_models (model_name, model) values(@new_model_name, @model);
GO
```
Verifikasi bahwa model telah disisipkan.
```
SELECT * FROM dbo.iris_models
```
Hasil

model_name model

Naive Bayes 0x800363736B6C6561726E2E6E616976655F62617965730A...

model_name	model
Naive Bayes	0x800363736B6C6561726E2E6E616976655F62617965730A...

Membuat dan menjalankan prosedur tersimpan untuk menghasilkan prediksi

Sekarang setelah Anda membuat, melatih, dan menyimpan model, lanjutkan ke langkah berikutnya: membuat prosedur tersimpan yang menghasilkan prediksi. Anda akan melakukan ini dengan memanggil sp_execute_external_script untuk menjalankan skrip Python yang memuat model berseri dan memberinya input data baru untuk mencetak skor.

Jalankan kode berikut untuk membuat prosedur tersimpan yang melakukan penilaian. Pada durasi, prosedur ini akan memuat model biner, menggunakan kolom [1,2,3,4] sebagai input, dan menentukan kolom [0,5,6] sebagai output.

CREATE PROCEDURE predict_species (@model VARCHAR(100))
AS
BEGIN
    DECLARE @nb_model VARBINARY(max) = (
            SELECT model
            FROM iris_models
            WHERE model_name = @model
            );

    EXECUTE sp_execute_external_script @language = N'Python'
        , @script = N'
import pickle
irismodel = pickle.loads(nb_model)
species_pred = irismodel.predict(iris_data[["Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width"]])
iris_data["PredictedSpecies"] = species_pred
OutputDataSet = iris_data[["id","SpeciesId","PredictedSpecies"]] 
print(OutputDataSet)
'
        , @input_data_1 = N'select id, "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "SpeciesId" from iris_data'
        , @input_data_1_name = N'iris_data'
        , @params = N'@nb_model varbinary(max)'
        , @nb_model = @nb_model
    WITH RESULT SETS((
                "id" INT
              , "SpeciesId" INT
              , "SpeciesId.Predicted" INT
                ));
END;
GO

Jalankan prosedur tersimpan, memberikan nama model "Naive Bayes" sehingga prosedur mengetahui model mana yang akan digunakan.
```
EXECUTE predict_species 'Naive Bayes';
GO
```
Saat Anda menjalankan prosedur tersimpan, prosedur tersebut mengembalikan data.frame Python. Baris T-SQL ini menentukan skema untuk hasil yang dikembalikan: WITH RESULT SETS ( ("id" int, "SpeciesId" int, "SpeciesId.Predicted" int));. Anda dapat menyisipkan hasilnya ke dalam tabel baru, atau mengembalikannya ke aplikasi.

Hasilnya adalah 150 prediksi tentang spesies menggunakan karakteristik bunga sebagai input. Untuk sebagian besar pengamatan, spesies yang diprediksi cocok dengan spesies yang sebenarnya.

Contoh ini telah dibuat sederhana dengan menggunakan himpunan data Python iris untuk pelatihan dan penilaian. Pendekatan yang lebih umum akan melibatkan menjalankan kueri SQL untuk mendapatkan data baru, dan meneruskan itu ke Python sebagai InputDataSet.

Kesimpulan

Dalam latihan ini, Anda mempelajari cara membuat prosedur tersimpan yang didedikasikan untuk tugas yang berbeda, di mana setiap prosedur tersimpan menggunakan prosedur sp_execute_external_script tersimpan sistem untuk memulai proses Python. Input ke proses Python diteruskan sebagai sp_execute_external parameter. Skrip Python itu sendiri dan variabel data dalam database diteruskan sebagai input.

Umumnya, Anda hanya boleh berencana menggunakan Azure Data Studio dengan kode Python yang dipoluskan, atau kode Python sederhana yang mengembalikan output berbasis baris. Sebagai alat, Azure Data Studio mendukung bahasa kueri seperti T-SQL dan mengembalikan set baris yang diratakan. Jika kode Anda menghasilkan output visual seperti scatterplot atau histogram, Anda memerlukan alat terpisah atau aplikasi pengguna akhir yang dapat merender gambar di luar prosedur tersimpan.

Untuk beberapa pengembang Python yang terbiasa menulis skrip all-inclusive yang menangani berbagai operasi, mengatur tugas ke dalam prosedur terpisah mungkin tampak tidak perlu. Tetapi pelatihan dan penilaian memiliki kasus penggunaan yang berbeda. Dengan memisahkannya, Anda dapat menempatkan setiap tugas pada jadwal dan izin cakupan yang berbeda untuk setiap operasi.

Manfaat akhir adalah bahwa proses dapat dimodifikasi menggunakan parameter. Dalam latihan ini, kode Python yang membuat model (bernama "Naive Bayes" dalam contoh ini) diteruskan sebagai input ke prosedur tersimpan kedua yang memanggil model dalam proses penilaian. Latihan ini hanya menggunakan satu model, tetapi Anda dapat membayangkan bagaimana parameterisasi model dalam tugas penilaian akan membuat skrip tersebut lebih berguna.

Langkah berikutnya

Untuk informasi selengkapnya tentang tutorial untuk Python dengan pembelajaran mesin SQL, lihat:

Tutorial Python