Voorspellingen implementeren en doen met een ONNX-model en SQL Machine Learning

Artikel
09/21/2024

Belangrijk

Azure SQL Edge wordt op 30 september 2025 buiten gebruik gesteld. Zie de kennisgeving buitengebruikstelling voor meer informatie en migratieopties.

Notitie

Azure SQL Edge biedt geen ondersteuning meer voor het ARM64-platform.

In deze quickstart leert u hoe u een model traint, converteert naar ONNX, implementeert in Azure SQL Edge en vervolgens systeemeigen PREDICT uitvoert op gegevens met behulp van het geüploade ONNX-model.

Deze quickstart is gebaseerd op scikit-learn en maakt gebruik van de Boston Housing-gegevensset.

Voordat u begint

Als u Azure SQL Edge gebruikt en u nog geen Azure SQL Edge-module hebt geïmplementeerd, volgt u de stappen voor het implementeren van SQL Edge met behulp van Azure Portal.
Installeer Azure Data Studio.
Python-pakketten installeren die nodig zijn voor deze quickstart:
1. Open nieuw notebook dat is verbonden met de Python 3-kernel.
2. Pakketten beheren selecteren
3. Zoek op het tabblad Geïnstalleerd naar de volgende Python-pakketten in de lijst met geïnstalleerde pakketten. Als een van deze pakketten niet is geïnstalleerd, selecteert u het tabblad Nieuwe toevoegen, zoekt u het pakket en selecteert u Installeren.
  - scikit-learn
  - numpy
  - onnxmltools
  - onnxruntime
  - pyodbc
  - setuptools
  - skl2onnx
  - sqlalchemy
Voer voor elk scriptonderdeel in de volgende secties deze in een cel in het Azure Data Studio-notebook in en voer de cel uit.

Een pijplijn trainen

Splits de gegevensset om functies te gebruiken om de mediaanwaarde van een huis te voorspellen.

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

Uitvoer:

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

Maak een pijplijn om het LinearRegression-model te trainen. U kunt ook andere regressiemodellen gebruiken.

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)

Controleer de nauwkeurigheid van het model en bereken vervolgens de R2-score en gemiddelde kwadratische fout.

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

Uitvoer:

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

Het model converteren naar ONNX

Converteer de gegevenstypen naar de ondersteunde SQL-gegevenstypen. Deze conversie is ook vereist voor andere dataframes.

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

Converteer met behulp skl2onnxvan het LinearRegression-model naar de ONNX-indeling en sla het lokaal op.

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

Notitie

Mogelijk moet u de target_opset parameter voor de skl2onnx.convert_sklearn-functie instellen als er een niet-overeenkomende ONNX-runtimeversie in SQL Edge en skl2onnx packge is. Zie de releaseopmerkingen voor SQL Edge om de ONNX-runtimeversie op te halen die overeenkomt met de release en kies de target_opset onnx-runtime op basis van de ONNX-compatibiliteitsmatrix voor eerdere versies.

Het ONNX-model testen

Nadat het model is geconverteerd naar de ONNX-indeling, moet u het model beoordelen om weinig tot geen verslechtering van de prestaties weer te geven.

Notitie

ONNX Runtime maakt gebruik van floats in plaats van doubles, zodat kleine verschillen mogelijk zijn.

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

Uitvoer:

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

R2 Scores are equal
MSE are equal

Het ONNX-model invoegen

Sla het model op in Azure SQL Edge, in een models tabel in een database onnx. Geef in de verbindingsreeks het serveradres, de gebruikersnaam en het wachtwoord op.

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

De gegevens laden

Laad de gegevens in SQL.

Maak eerst twee tabellen, functies en doel om subsets van de Boston-gegevensset voor huisvesting op te slaan.

Functies bevatten alle gegevens die worden gebruikt om het doel, de mediaanwaarde te voorspellen.
Het doel bevat de mediaanwaarde voor elke record in de gegevensset.

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

Ten slotte kunt sqlalchemy u de gegevensframes van y_train pandas x_train invoegen in de tabellen features en target, respectievelijk.

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)

U kunt nu de gegevens in de database bekijken.

PREDICT uitvoeren met behulp van het ONNX-model

Voer met het model in SQL systeemeigen PREDICT uit op de gegevens met behulp van het geüploade ONNX-model.

Notitie

Wijzig de notebook-kernel in SQL om de resterende cel uit te voeren.

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;

Machine Learning en AI met ONNX in SQL Edge

Delen via

Voorspellingen implementeren en doen met een ONNX-model en SQL Machine Learning

Voordat u begint

Een pijplijn trainen

Het model converteren naar ONNX

Het ONNX-model testen

Het ONNX-model invoegen

De gegevens laden

PREDICT uitvoeren met behulp van het ONNX-model

Feedback

Aanvullende resources

Delen via

Voorspellingen implementeren en doen met een ONNX-model en SQL Machine Learning

Voordat u begint

Een pijplijn trainen

Het model converteren naar ONNX

Het ONNX-model testen

Het ONNX-model invoegen

De gegevens laden

PREDICT uitvoeren met behulp van het ONNX-model

Gerelateerde inhoud

Feedback

Aanvullende resources