Penilaian asli menggunakan fungsi PREDICT T-SQL dengan pembelajaran mesin SQL

  • Artikel

Berlaku untuk: SQL Server 2017 (14.x) dan yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Pelajari cara menggunakan penilaian asli dengan fungsi PREDICT T-SQL untuk menghasilkan nilai prediksi untuk input data baru dalam waktu dekat secara real-time. Penilaian asli mengharuskan Anda memiliki model yang sudah dilatih.

Fungsi ini PREDICT menggunakan kemampuan ekstensi C++ asli dalam pembelajaran mesin SQL. Metodologi ini menawarkan kecepatan pemrosesan beban kerja prakiraan dan prediksi tercepat dan model dukungan dalam format atau model Open Neural Network Exchange (ONNX) yang dilatih menggunakan paket RevoScaleR dan revoscalepy .

Cara kerja penilaian asli

Penilaian asli menggunakan pustaka yang dapat membaca model di ONNX atau format biner yang telah ditentukan sebelumnya, dan menghasilkan skor untuk input data baru yang Anda berikan. Karena model dilatih, disebarkan, dan disimpan, model dapat digunakan untuk penilaian tanpa harus memanggil interpreter R atau Python. Ini berarti bahwa overhead dari beberapa interaksi proses berkurang, menghasilkan performa prediksi yang lebih cepat.

Untuk menggunakan penilaian asli, panggil PREDICT fungsi T-SQL dan berikan input yang diperlukan berikut:

  • Model yang kompatibel berdasarkan model dan algoritma yang didukung.
  • Input data, biasanya didefinisikan sebagai kueri T-SQL.

Fungsi mengembalikan prediksi untuk data input, bersama dengan kolom data sumber apa pun yang ingin Anda lewati.

Prasyarat

PREDICT tersedia pada:

  • Semua edisi SQL Server 2017 dan yang lebih baru di Windows dan Linux
  • Instans Terkelola Azure SQL
  • Azure SQL Database
  • Azure SQL Edge
  • Azure Synapse Analytics

Fungsi diaktifkan secara default. Anda tidak perlu menginstal R atau Python, atau mengaktifkan fitur tambahan.

Model yang didukung

Format model yang didukung oleh PREDICT fungsi tergantung pada platform SQL tempat Anda melakukan penilaian asli. Lihat tabel di bawah ini untuk melihat format model mana yang didukung di platform mana.

Platform Format model ONNX Format model RevoScale
SQL Server Tidak Ya
Instans Terkelola Azure SQL Ya Ya
Azure SQL Database Tidak Ya
Azure SQL Edge Ya Tidak
Azure Synapse Analytics Ya Tidak

Model ONNX

Model harus dalam format model Open Neural Network Exchange (ONNX).

Model revoScale

Model harus dilatih terlebih dahulu menggunakan salah satu algoritma rx yang didukung yang tercantum di bawah ini menggunakan paket RevoScaleR atau revoscalepy .

Serialisasi model menggunakan rxSerialize untuk R, dan rx_serialize_model untuk Python. Fungsi serialisasi ini telah dioptimalkan untuk mendukung penilaian cepat.

Algoritma RevoScale yang didukung

Algoritma berikut didukung dalam revoscalepy dan RevoScaleR.

Jika Anda perlu menggunakan algoritma dari MicrosoftML atau microsoftml, gunakan penilaian real time dengan sp_rxPredict.

Jenis model yang tidak didukung mencakup jenis berikut:

  • Model yang berisi transformasi lain
  • Model yang menggunakan rxGlm algoritma atau rxNaiveBayes di RevoScaleR atau setara dengan revoscalepy
  • Model PMML
  • Model yang dibuat menggunakan pustaka sumber terbuka atau pihak ketiga lainnya

Contoh

PREDICT dengan model ONNX

Contoh ini menunjukkan cara menggunakan model ONNX yang disimpan dalam dbo.models tabel untuk penilaian asli.

DECLARE @model VARBINARY(max) = (
        SELECT DATA
        FROM dbo.models
        WHERE id = 1
        );

WITH predict_input
AS (
    SELECT TOP (1000) [id]
        , CRIM
        , ZN
        , INDUS
        , CHAS
        , NOX
        , RM
        , AGE
        , DIS
        , RAD
        , TAX
        , PTRATIO
        , B
        , LSTAT
    FROM [dbo].[features]
    )
SELECT predict_input.id
    , p.variable1 AS MEDV
FROM PREDICT(MODEL = @model, DATA = predict_input, RUNTIME=ONNX) WITH (variable1 FLOAT) AS p;

Catatan

Karena kolom dan nilai yang dikembalikan oleh PREDICT dapat bervariasi menurut jenis model, Anda harus menentukan skema data yang dikembalikan dengan menggunakan klausa WITH .

PREDICT dengan model RevoScale

Dalam contoh ini, Anda membuat model menggunakan RevoScaleR di R, lalu memanggil fungsi prediksi real-time dari T-SQL.

Langkah 1. Menyiapkan dan menyimpan model

Jalankan kode berikut untuk membuat database sampel dan tabel yang diperlukan.

CREATE DATABASE NativeScoringTest;
GO
USE NativeScoringTest;
GO
DROP TABLE IF EXISTS iris_rx_data;
GO
CREATE TABLE iris_rx_data (
    "Sepal.Length" float not null, "Sepal.Width" float not null
  , "Petal.Length" float not null, "Petal.Width" float not null
  , "Species" varchar(100) null
);
GO

Gunakan pernyataan berikut untuk mengisi tabel data dengan data dari himpunan data iris .

INSERT INTO iris_rx_data ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width" , "Species")
EXECUTE sp_execute_external_script
  @language = N'R'
  , @script = N'iris_data <- iris;'
  , @input_data_1 = N''
  , @output_data_1_name = N'iris_data';
GO

Sekarang, buat tabel untuk menyimpan model.

DROP TABLE IF EXISTS ml_models;
GO
CREATE TABLE ml_models ( model_name nvarchar(100) not null primary key
  , model_version nvarchar(100) not null
  , native_model_object varbinary(max) not null);
GO

Kode berikut membuat model berdasarkan himpunan data iris dan menyimpannya ke tabel bernama model.

DECLARE @model varbinary(max);
EXECUTE sp_execute_external_script
  @language = N'R'
  , @script = N'
    iris.sub <- c(sample(1:50, 25), sample(51:100, 25), sample(101:150, 25))
    iris.dtree <- rxDTree(Species ~ Sepal.Length + Sepal.Width + Petal.Length + Petal.Width, data = iris[iris.sub, ])
    model <- rxSerializeModel(iris.dtree, realtimeScoringOnly = TRUE)
    '
  , @params = N'@model varbinary(max) OUTPUT'
  , @model = @model OUTPUT
  INSERT [dbo].[ml_models]([model_name], [model_version], [native_model_object])
  VALUES('iris.dtree','v1', @model) ;

Catatan

Pastikan untuk menggunakan fungsi rxSerializeModel dari RevoScaleR untuk menyimpan model. Fungsi R serialize standar tidak dapat menghasilkan format yang diperlukan.

Anda dapat menjalankan pernyataan seperti berikut ini untuk melihat model yang disimpan dalam format biner:

SELECT *, datalength(native_model_object)/1024. as model_size_kb
FROM ml_models;

Langkah 2. Menjalankan PREDICT pada model

Pernyataan PREDICT sederhana berikut mendapatkan klasifikasi dari model pohon keputusan menggunakan fungsi penilaian asli . Ini memprediksi spesies iris berdasarkan atribut yang Anda berikan, panjang dan lebar kelopak.

DECLARE @model varbinary(max) = (
  SELECT native_model_object
  FROM ml_models
  WHERE model_name = 'iris.dtree'
  AND model_version = 'v1');
SELECT d.*, p.*
  FROM PREDICT(MODEL = @model, DATA = dbo.iris_rx_data as d)
  WITH(setosa_Pred float, versicolor_Pred float, virginica_Pred float) as p;
go

Jika Anda mendapatkan kesalahan, "Terjadi kesalahan selama eksekusi fungsi PREDICT. Model rusak atau tidak valid", biasanya berarti kueri Anda tidak mengembalikan model. Periksa apakah Anda mengetik nama model dengan benar, atau apakah tabel model kosong.

Catatan

Karena kolom dan nilai yang dikembalikan oleh PREDICT dapat bervariasi menurut jenis model, Anda harus menentukan skema data yang dikembalikan dengan menggunakan klausa WITH .

Langkah berikutnya