<jupyter_start><jupyter_text>INSTALL DEPENDENCIES<jupyter_code>!pip install pandas
!pip install scikit-learn
!pip install numpy
!pip install onnxmltools
!pip install onnxruntime
!pip install pyodbc
!pip install setuptools
!pip install skl2onnx
!pip install sqlalchemy<jupyter_output><empty_output><jupyter_text>SET VALUES<jupyter_code>import pyodbc
import skl2onnx
import sklearn
import sklearn.datasets
import numpy as np
import onnxmltools
import onnxruntime as rt
import pandas as pd
from onnxruntime.capi.onnxruntime_pybind11_state import InvalidArgument


server = '' # SQL Server IP address
username = '' # SQL Server username
password = '' # SQL Server password
database = 'IoTEdgeDB'
data_table = 'OpcNodes'
models_table = 'Models'

master_connection_string = "Driver={ODBC Driver 17 for SQL Server};Server=" + server + ";Database=master;UID=" + username + ";PWD=" + password + ";"
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)<jupyter_output><empty_output><jupyter_text>CREATE X TRAIN DATASET<jupyter_code>query = 'SELECT ApplicationUri, DipData, SpikeData, RandomSignedInt32 FROM OpcNodes WHERE DipData IS NOT NULL AND SpikeData IS NOT NULL AND RandomSignedInt32 IS NOT NULL'
sql_query = pd.read_sql_query(query, conn)
x_train = pd.DataFrame(sql_query, columns=['DipData', 'SpikeData', 'RandomSignedInt32'])

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

     DipData  SpikeData  RandomSignedInt32
0 -36.812455 -36.812455      -1.574417e+08
1 -36.812455 -36.812455      -1.574417e+08
2 -68.454711 -68.454711      -1.734891e+09
3 -68.454711 -68.454711      -1.734891e+09
4  95.105652  95.105652       4.466349e+08<jupyter_text>CREATE Y TRAIN DATASET<jupyter_code>target_column = 'MEDV'
y_train = pd.DataFrame()

y_train[target_column] = np.random.randint(100, size=(len(x_train)))

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

   MEDV
0    90
1    26
2     2
3     3
4    22<jupyter_text>CREATE PIPELINE TO TRAIN THE LINEAR REGRESSION MODEL<jupyter_code>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)<jupyter_output><empty_output><jupyter_text>CHECK MODEL ACCURACY<jupyter_code># 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))<jupyter_output>*** Scikit-learn r2 score: 0.0007844648024204481
*** Scikit-learn MSE: 837.4150066212547<jupyter_text>DEFINE FUNCTION TO CONVERT MODEL TO ONNX<jupyter_code>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<jupyter_output><empty_output><jupyter_text>SAVE MODEL IN ONNX FORMAT<jupyter_code># Convert the scikit model to onnx format
onnx_model = skl2onnx.convert_sklearn(model, 'OPC tags data', convert_dataframe_schema(x_train), final_types=[('variable1',FloatTensorType([1,1]))], target_opset=11)
# Save the onnx model locally
onnx_model_path = 'opcnodes.model.onnx'
onnxmltools.utils.save_model(onnx_model, onnx_model_path)<jupyter_output><empty_output><jupyter_text>TEST ONNX MODEL<jupyter_code>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)))<jupyter_output>*** Onnx r2 score: 0.0007844624414192758
*** Onnx MSE: 837.4150085999447

Difference in R2 scores: 2.3610011723462776e-09
Difference in MSE scores: 1.978689965653757e-06<jupyter_text>INSERT MODEL IN DATABASE<jupyter_code># Insert the ONNX model into the models table for each value of ApplicationUri
query = f'SELECT DISTINCT ApplicationUri FROM {data_table}'
cursor = conn.cursor()
cursor.execute(query)
application_uris = cursor.fetchall()

for app in application_uris:
    query = f"insert into {models_table} ([applicationUri], [description], [data]) values ('{app[0]}', 'Onnx Model', ?)"
    model_bits = onnx_model.SerializeToString()
    insert_params  = (pyodbc.Binary(model_bits))
    cursor.execute(query, insert_params)
    conn.commit()<jupyter_output><empty_output><jupyter_text>RUN PREDICTION<jupyter_code>import pandas as pd

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

# get ONNX model
app_uri = 'urn:OpcPlc:opcserver1'
query = f'SELECT data FROM {models_table} WHERE applicationUri= \'{app_uri}\''
cursor.execute(query)
onnx_model = cursor.fetchall()[0][0]

# get feature data
row_count = 5
query = f'SELECT top {row_count} DipData, SpikeData, RandomSignedInt32 FROM {data_table} WHERE ApplicationUri= \'{app_uri}\' AND DipData IS NOT NULL AND SpikeData IS NOT NULL AND RandomSignedInt32 IS NOT NULL ORDER BY SourceTimestamp DESC'
sql_query = pd.read_sql_query(query, conn)

x_train = pd.DataFrame(sql_query, columns=['DipData', 'SpikeData', 'RandomSignedInt32'])

sess = rt.InferenceSession(onnx_model)

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]

print(y_pred)
out.name<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11586500132872708, 'fraction_numerical': 0.04398086633005581, 'mean_word_length': 4.638202247191011, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 3}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '231595', 'license': 'MIT', 'n_tokens_mistral': 2959, 'n_tokens_neox': 2703, 'n_words': 602, 'path': 'ML/Notebooks/train-opc-nodes-model.ipynb', 'repo_name': 'marvin-garcia/EdgeStreamAnalytics', 'size': '7526'}