Wdrażanie i tworzenie przewidywań za pomocą modelu ONNX i uczenia maszynowego SQL

Artykuł
01/10/2024

Ważne

Usługa Azure SQL Edge nie obsługuje już platformy ARM64.

W tym przewodniku Szybki start dowiesz się, jak wytrenować model, przekonwertować go na ONNX, wdrożyć go w usłudze Azure SQL Edge, a następnie uruchomić natywną wartość PREDICT na danych przy użyciu przekazanego modelu ONNX.

Ten przewodnik Szybki start jest oparty na zestawie danych scikit-learn i korzysta z zestawu danych Boston Housing.

Zanim rozpoczniesz

Jeśli używasz usługi Azure SQL Edge i nie wdrożono modułu azure SQL Edge, wykonaj kroki wdrażania przeglądarki SQL Edge przy użyciu witryny Azure Portal.
Zainstaluj program Azure Data Studio.
Zainstaluj pakiety języka Python potrzebne w tym przewodniku Szybki start:
1. Otwórz nowy notes połączony z jądrem języka Python 3.
2. Wybieranie pozycji Zarządzaj pakietami
3. Na karcie Zainstalowane wyszukaj następujące pakiety języka Python na liście zainstalowanych pakietów. Jeśli którykolwiek z tych pakietów nie jest zainstalowany, wybierz kartę Dodaj nowy , wyszukaj pakiet i wybierz pozycję Zainstaluj.
  - scikit-learn
  - Numpy
  - onnxmltools
  - onnxruntime
  - pyodbc
  - setuptools
  - skl2onnx
  - sqlalchemy
Dla każdej części skryptu w poniższych sekcjach wprowadź ją w komórce w notesie usługi Azure Data Studio i uruchom komórkę.

Trenowanie potoku

Podziel zestaw danych, aby użyć funkcji do przewidywania mediany wartości domu.

import numpy as np
import onnxmltools
import onnxruntime as rt
import pandas as pd
import skl2onnx
import sklearn
import sklearn.datasets

from sklearn.datasets import load_boston
boston = load_boston()
boston

df = pd.DataFrame(data=np.c_[boston['data'], boston['target']], columns=boston['feature_names'].tolist() + ['MEDV'])

target_column = 'MEDV'

# Split the data frame into features and target
x_train = pd.DataFrame(df.drop([target_column], axis = 1))
y_train = pd.DataFrame(df.iloc[:,df.columns.tolist().index(target_column)])

print("\n*** Training dataset x\n")
print(x_train.head())

print("\n*** Training dataset y\n")
print(y_train.head())

Dane wyjściowe:

*** Training dataset x

        CRIM    ZN  INDUS  CHAS    NOX     RM   AGE     DIS  RAD    TAX  \
0  0.00632  18.0   2.31   0.0  0.538  6.575  65.2  4.0900  1.0  296.0
1  0.02731   0.0   7.07   0.0  0.469  6.421  78.9  4.9671  2.0  242.0
2  0.02729   0.0   7.07   0.0  0.469  7.185  61.1  4.9671  2.0  242.0
3  0.03237   0.0   2.18   0.0  0.458  6.998  45.8  6.0622  3.0  222.0
4  0.06905   0.0   2.18   0.0  0.458  7.147  54.2  6.0622  3.0  222.0

    PTRATIO       B  LSTAT
0     15.3  396.90   4.98
1     17.8  396.90   9.14
2     17.8  392.83   4.03
3     18.7  394.63   2.94
4     18.7  396.90   5.33

*** Training dataset y

0    24.0
1    21.6
2    34.7
3    33.4
4    36.2
Name: MEDV, dtype: float64

Tworzenie potoku w celu wytrenowania modelu LinearRegression. Można również użyć innych modeli regresji.

from sklearn.compose import ColumnTransformer
from sklearn.linear_model import LinearRegression
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import RobustScaler

continuous_transformer = Pipeline(steps=[('scaler', RobustScaler())])

# All columns are numeric - normalize them
preprocessor = ColumnTransformer(
    transformers=[
        ('continuous', continuous_transformer, [i for i in range(len(x_train.columns))])])

model = Pipeline(
    steps=[
        ('preprocessor', preprocessor),
        ('regressor', LinearRegression())])

# Train the model
model.fit(x_train, y_train)

Sprawdź dokładność modelu, a następnie oblicz wynik R2 i błąd średniokwadratowy.

# Score the model
from sklearn.metrics import r2_score, mean_squared_error
y_pred = model.predict(x_train)
sklearn_r2_score = r2_score(y_train, y_pred)
sklearn_mse = mean_squared_error(y_train, y_pred)
print('*** Scikit-learn r2 score: {}'.format(sklearn_r2_score))
print('*** Scikit-learn MSE: {}'.format(sklearn_mse))

Dane wyjściowe:

*** Scikit-learn r2 score: 0.7406426641094094
*** Scikit-learn MSE: 21.894831181729206

Konwertowanie modelu na ONNX

Przekonwertuj typy danych na obsługiwane typy danych SQL. Ta konwersja jest również wymagana dla innych ramek danych.

from skl2onnx.common.data_types import FloatTensorType, Int64TensorType, DoubleTensorType

def convert_dataframe_schema(df, drop=None, batch_axis=False):
    inputs = []
    nrows = None if batch_axis else 1
    for k, v in zip(df.columns, df.dtypes):
        if drop is not None and k in drop:
            continue
        if v == 'int64':
            t = Int64TensorType([nrows, 1])
        elif v == 'float32':
            t = FloatTensorType([nrows, 1])
        elif v == 'float64':
            t = DoubleTensorType([nrows, 1])
        else:
            raise Exception("Bad type")
        inputs.append((k, t))
    return inputs

Za pomocą polecenia skl2onnxprzekonwertuj model LinearRegression na format ONNX i zapisz go lokalnie.

# Convert the scikit model to onnx format
onnx_model = skl2onnx.convert_sklearn(model, 'Boston Data', convert_dataframe_schema(x_train), final_types=[('variable1',FloatTensorType([1,1]))])
# Save the onnx model locally
onnx_model_path = 'boston1.model.onnx'
onnxmltools.utils.save_model(onnx_model, onnx_model_path)

Uwaga

Może być konieczne ustawienie parametru target_opset dla funkcji skl2onnx.convert_sklearn, jeśli istnieje niezgodność między wersją środowiska uruchomieniowego ONNX w programie SQL Edge i pakietem skl2onnx. Aby uzyskać więcej informacji, zobacz informacje o wersji programu SQL Edge, aby uzyskać wersję środowiska uruchomieniowego ONNX odpowiadającą wersji wydania, a następnie wybierz target_opset środowisko uruchomieniowe ONNX na podstawie macierzy zgodności z poprzednimi wersjami onNX.

Testowanie modelu ONNX

Po przekonwertowaniu modelu na format ONNX należy ocenić model, aby nie wykazywać niewielkiego spadku wydajności.

Uwaga

Środowisko uruchomieniowe ONNX używa zmiennoprzecinków zamiast podwajanych, więc możliwe są małe rozbieżności.

import onnxruntime as rt
sess = rt.InferenceSession(onnx_model_path)

y_pred = np.full(shape=(len(x_train)), fill_value=np.nan)

for i in range(len(x_train)):
    inputs = {}
    for j in range(len(x_train.columns)):
        inputs[x_train.columns[j]] = np.full(shape=(1,1), fill_value=x_train.iloc[i,j])

    sess_pred = sess.run(None, inputs)
    y_pred[i] = sess_pred[0][0][0]

onnx_r2_score = r2_score(y_train, y_pred)
onnx_mse = mean_squared_error(y_train, y_pred)

print()
print('*** Onnx r2 score: {}'.format(onnx_r2_score))
print('*** Onnx MSE: {}\n'.format(onnx_mse))
print('R2 Scores are equal' if sklearn_r2_score == onnx_r2_score else 'Difference in R2 scores: {}'.format(abs(sklearn_r2_score - onnx_r2_score)))
print('MSE are equal' if sklearn_mse == onnx_mse else 'Difference in MSE scores: {}'.format(abs(sklearn_mse - onnx_mse)))
print()

Dane wyjściowe:

*** Onnx r2 score: 0.7406426691136831
*** Onnx MSE: 21.894830759270633

R2 Scores are equal
MSE are equal

Wstawianie modelu ONNX

Zapisz model w usłudze Azure SQL Edge w models tabeli w bazie danych onnx. W parametry połączenia określ adres serwera, nazwę użytkownika i hasło.

import pyodbc

server = '' # SQL Server IP address
username = '' # SQL Server username
password = '' # SQL Server password

# Connect to the master DB to create the new onnx database
connection_string = "Driver={ODBC Driver 17 for SQL Server};Server=" + server + ";Database=master;UID=" + username + ";PWD=" + password + ";"

conn = pyodbc.connect(connection_string, autocommit=True)
cursor = conn.cursor()

database = 'onnx'
query = 'DROP DATABASE IF EXISTS ' + database
cursor.execute(query)
conn.commit()

# Create onnx database
query = 'CREATE DATABASE ' + database
cursor.execute(query)
conn.commit()

# Connect to onnx database
db_connection_string = "Driver={ODBC Driver 17 for SQL Server};Server=" + server + ";Database=" + database + ";UID=" + username + ";PWD=" + password + ";"

conn = pyodbc.connect(db_connection_string, autocommit=True)
cursor = conn.cursor()

table_name = 'models'

# Drop the table if it exists
query = f'drop table if exists {table_name}'
cursor.execute(query)
conn.commit()

# Create the model table
query = f'create table {table_name} ( ' \
    f'[id] [int] IDENTITY(1,1) NOT NULL, ' \
    f'[data] [varbinary](max) NULL, ' \
    f'[description] varchar(1000))'
cursor.execute(query)
conn.commit()

# Insert the ONNX model into the models table
query = f"insert into {table_name} ([description], [data]) values ('Onnx Model',?)"

model_bits = onnx_model.SerializeToString()

insert_params  = (pyodbc.Binary(model_bits))
cursor.execute(query, insert_params)
conn.commit()

Ładowanie danych

Załaduj dane do bazy danych SQL.

Najpierw utwórz dwie tabele, funkcje i element docelowy, aby przechowywać podzestawy zestawu danych mieszkaniowych Boston.

Funkcje zawierają wszystkie dane używane do przewidywania wartości docelowej mediany.
Element docelowy zawiera medianę dla każdego rekordu w zestawie danych.

import sqlalchemy
from sqlalchemy import create_engine
import urllib

db_connection_string = "Driver={ODBC Driver 17 for SQL Server};Server=" + server + ";Database=" + database + ";UID=" + username + ";PWD=" + password + ";"

conn = pyodbc.connect(db_connection_string)
cursor = conn.cursor()

features_table_name = 'features'

# Drop the table if it exists
query = f'drop table if exists {features_table_name}'
cursor.execute(query)
conn.commit()

# Create the features table
query = \
    f'create table {features_table_name} ( ' \
    f'    [CRIM] float, ' \
    f'    [ZN] float, ' \
    f'    [INDUS] float, ' \
    f'    [CHAS] float, ' \
    f'    [NOX] float, ' \
    f'    [RM] float, ' \
    f'    [AGE] float, ' \
    f'    [DIS] float, ' \
    f'    [RAD] float, ' \
    f'    [TAX] float, ' \
    f'    [PTRATIO] float, ' \
    f'    [B] float, ' \
    f'    [LSTAT] float, ' \
    f'    [id] int)'

cursor.execute(query)
conn.commit()

target_table_name = 'target'

# Create the target table
query = \
    f'create table {target_table_name} ( ' \
    f'    [MEDV] float, ' \
    f'    [id] int)'

x_train['id'] = range(1, len(x_train)+1)
y_train['id'] = range(1, len(y_train)+1)

print(x_train.head())
print(y_train.head())

Na koniec użyj polecenia sqlalchemy , aby wstawić x_train ramki danych i y_train pandas do tabel features i target, odpowiednio.

db_connection_string = 'mssql+pyodbc://' + username + ':' + password + '@' + server + '/' + database + '?driver=ODBC+Driver+17+for+SQL+Server'
sql_engine = sqlalchemy.create_engine(db_connection_string)
x_train.to_sql(features_table_name, sql_engine, if_exists='append', index=False)
y_train.to_sql(target_table_name, sql_engine, if_exists='append', index=False)

Teraz możesz wyświetlić dane w bazie danych.

Uruchamianie funkcji PREDICT przy użyciu modelu ONNX

Korzystając z modelu w języku SQL, uruchom natywną wartość PREDICT na danych przy użyciu przekazanego modelu ONNX.

Uwaga

Zmień jądro notesu na SQL, aby uruchomić pozostałą komórkę.

USE onnx

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;

Następne kroki

Machine Edukacja and AI with ONNX in SQL Edge (Maszyny Edukacja i sztuczna inteligencja w usłudze ONNX w usłudze SQL Edge)

Wdrażanie i tworzenie przewidywań za pomocą modelu ONNX i uczenia maszynowego SQL

Zanim rozpoczniesz

Trenowanie potoku

Konwertowanie modelu na ONNX

Testowanie modelu ONNX

Wstawianie modelu ONNX

Ładowanie danych

Uruchamianie funkcji PREDICT przy użyciu modelu ONNX

Następne kroki

Dodatkowe zasoby