Nasazení a vytváření předpovědí pomocí modelu ONNX a strojového učení SQL

Článek
09/23/2024

Důležité

Azure SQL Edge bude vyřazeno 30. září 2025. Další informace a možnosti migrace najdete v oznámení o vyřazení.

Poznámka:

Azure SQL Edge už nepodporuje platformu ARM64.

V tomto rychlém startu se dozvíte, jak vytrénovat model, převést ho na ONNX, nasadit ho do Azure SQL Edge a pak spustit nativní funkci PREDICT na datech pomocí nahraného modelu ONNX.

Tento rychlý start je založený na scikit-learn a používá datovou sadu bostonského bydlení.

Než začnete

Pokud používáte Azure SQL Edge a nenasadili jste modul Azure SQL Edge, postupujte podle kroků nasazení SQL Edge pomocí webu Azure Portal.
Nainstalujte Azure Data Studio.
Pro účely tohoto rychlého startu nainstalujte balíčky Pythonu:
1. Otevřete nový poznámkový blok připojený k jádru Pythonu 3.
2. Výběr možnosti Spravovat balíčky
3. Na kartě Nainstalované vyhledejte v seznamu nainstalovaných balíčků následující balíčky Pythonu. Pokud některý z těchto balíčků není nainstalovaný, vyberte kartu Přidat nový , vyhledejte balíček a vyberte Nainstalovat.
  - scikit-learn
  - numpy
  - onnxmltools
  - onnxruntime
  - pyodbc
  - setuptools
  - skl2onnx
  - sqlalchemy
Pro každou část skriptu v následujících částech ji zadejte do buňky v poznámkovém bloku Azure Data Studio a spusťte buňku.

Trénování kanálu

Rozdělte datovou sadu tak, aby používala funkce k predikci mediánu hodnoty 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())

Výstup:

*** 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

Vytvořte kanál pro trénování modelu LinearRegression. Můžete také použít jiné regresní modely.

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)

Zkontrolujte přesnost modelu a pak vypočítejte skóre R2 a střední kvadratická chyba.

# 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))

Výstup:

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

Převod modelu na ONNX

Převeďte datové typy na podporované datové typy SQL. Tento převod se vyžaduje i pro jiné datové rámce.

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

Pomocí skl2onnxfunkce Převeďte model LinearRegression do formátu ONNX a uložte ho místně.

# 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)

Poznámka:

Pokud se neshoduje verze modulu runtime ONNX v SQL Edgi a skl2onnx packge, možná budete muset nastavit target_opset parametr pro funkci skl2onnx.convert_sklearn. Další informace najdete v poznámkách k verzi SQL Edge, abyste získali verzi modulu runtime ONNX odpovídající verzi verze a vybrali target_opset modul runtime ONNX na základě matice zpětné kompatibility ONNX.

Testování modelu ONNX

Po převodu modelu na formát ONNX vyhodnotit model tak, aby zobrazoval malé nebo žádné snížení výkonu.

Poznámka:

MODUL RUNTIME ONNX používá místo dvojitých hodnot plovoucí hodnoty, takže jsou možné malé nesrovnalosti.

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()

Výstup:

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

R2 Scores are equal
MSE are equal

Vložení modelu ONNX

Uložte model v Azure SQL Edge v models tabulce v databázi onnx. V připojovací řetězec zadejte adresu serveru, uživatelské jméno a heslo.

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()

Načtení dat

Načtěte data do SQL.

Nejprve vytvořte dvě tabulky, funkce a cíl pro ukládání podmnožina datové sady bydlení Bostonu.

Funkce obsahují všechna data, která se používají k predikci cíle, mediánu hodnoty.
Cíl obsahuje medián hodnoty pro každý záznam v datové sadě.

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())

Nakonec použijte sqlalchemy k vložení x_train datových rámců a y_train pandas do tabulek features a targetv uvedeném pořadí.

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)

Teď můžete zobrazit data v databázi.

Spuštění funkce PREDICT pomocí modelu ONNX

S modelem v SQL spusťte nativní predict na datech pomocí nahraného modelu ONNX.

Poznámka:

Změňte jádro poznámkového bloku na SQL a spusťte zbývající buňku.

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;

Strojové učení a AI s ONNX v SQL Edgi

Sdílet prostřednictvím

Nasazení a vytváření předpovědí pomocí modelu ONNX a strojového učení SQL

Než začnete

Trénování kanálu

Převod modelu na ONNX

Testování modelu ONNX

Vložení modelu ONNX

Načtení dat

Spuštění funkce PREDICT pomocí modelu ONNX

Váš názor

Další materiály

Sdílet prostřednictvím

Nasazení a vytváření předpovědí pomocí modelu ONNX a strojového učení SQL

Než začnete

Trénování kanálu

Převod modelu na ONNX

Testování modelu ONNX

Vložení modelu ONNX

Načtení dat

Spuštění funkce PREDICT pomocí modelu ONNX

Související obsah

Váš názor

Další materiály