2.0 - Python Example - Teradata Vantage

Teradata Vantageā„¢ - Bring Your Own Model User Guide

Product
Teradata Vantage
Release Number
2.0
Release Date
October 2021
Content Type
User Guide
Publication ID
B700-1111-051K
Language
English (United States)

ModelTable: iris_db_glm_trans_function_model.pmml

from sklearn2pmml import sklearn2pmml
from sklearn2pmml.pipeline import PMMLPipeline
from sklearn.linear_model import LogisticRegression
from sklearn_pandas import DataFrameMapper
from sklearn.preprocessing import StandardScaler
import os
import time
from sklearn.preprocessing import FunctionTransformer
import numpy as np
from teradataml import *
 
display.print_sqlmr_query = True
passwd = "alice"
uid = "alice"
 
con = create_context(host="server123@mydomain.com", username=uid, password=passwd)
con
 
train_df = DataFrame.from_query("select * from iris_train")
traid_pd = train_df.to_pandas()
traid_pd
type(traid_pd)
 
X = traid_pd[['sepal_length','sepal_width','petal_length', 'petal_width' ]]
y=traid_pd[['species']]
 
mapper_alias = DataFrameMapper([
    (['sepal_width','petal_width','petal_length'],None),
    (['sepal_length'], FunctionTransformer(np.log1p,validate=False),
    {'alias': 'sepal_scaled'})
    ])
 
#pipeline
pipeline = PMMLPipeline([
    ("mapping",mapper_alias),
     ("classifier", LogisticRegression(random_state=0,solver='lbfgs',multi_class='multinomial'))
     ])
 
pipeline.fit(X,y.values.ravel())
sklearn2pmml(pipeline,"../sql/iris_db_glm_trans_function_model.pmml",with_repr=True)

Query that Calls PMMLPredict Function

SELECT * FROM mldb.PMMLPredict (
  ON iris_test
  ON (SELECT * FROM pmml_models WHERE model_id='iris_db_glm_trans_function_model') DIMENSION
  USING
    Accumulate ('id')
) AS td;

Query Output

 *** Query completed. 30 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.
 
 id prediction json_report
--- ---------- ------------------------------------------------------------------------------------------------------------------
 70            {"probability(2)":0.9487947271053611,"probability(1)":0.03167569892604183,"probability(3)":0.019529573968597048}
 55            {"probability(2)":0.7687887308918844,"probability(1)":0.006034837917934799,"probability(3)":0.22517643119018088}
 95            {"probability(2)":0.9175122830779481,"probability(1)":0.01809712579041765,"probability(3)":0.06439059113163426}
  5            {"probability(2)":0.018461763647857853,"probability(1)":0.9815381659625638,"probability(3)":7.038957834282637E-8}
 30            {"probability(2)":0.04437718803357112,"probability(1)":0.9556225113375069,"probability(3)":3.006289220797503E-7}
 20            {"probability(2)":0.01916919151903867,"probability(1)":0.9808306917446998,"probability(3)":1.167362615438517E-7}
115            {"probability(2)":0.059275415123526506,"probability(1)":1.5053384985819844E-4,"probability(3)":0.9405740510266154}
 85            {"probability(2)":0.8015516010716062,"probability(1)":0.010459943218685357,"probability(3)":0.18798845570970832}
 80            {"probability(2)":0.904144633529767,"probability(1)":0.09037020083300785,"probability(3)":0.005485165637225141}
 15            {"probability(2)":0.007732219271685014,"probability(1)":0.9922677645640159,"probability(3)":1.616429916906392E-8}
135            {"probability(2)":0.3320201392756134,"probability(1)":1.7098086892787024E-4,"probability(3)":0.6678088798554588}
 50            {"probability(2)":0.025035479040884143,"probability(1)":0.9749644162285335,"probability(3)":1.0473058225556595E-7}
145            {"probability(2)":0.015005855243442206,"probability(1)":1.3728306974232097E-5,"probability(3)":0.9849804164495836}
 60            {"probability(2)":0.9133132047129877,"probability(1)":0.03957819704179939,"probability(3)":0.04710859824521279}
130            {"probability(2)":0.16851529382802788,"probability(1)":7.769382003017571E-5,"probability(3)":0.8314070123519419}
 90            {"probability(2)":0.929932503980404,"probability(1)":0.024797028830030713,"probability(3)":0.04527046718956533}
125            {"probability(2)":0.05183748571942978,"probability(1)":4.5312056634287404E-5,"probability(3)":0.9481172022239358}
140            {"probability(2)":0.09031551060120137,"probability(1)":1.3563567612567517E-4,"probability(3)":0.909548853722673}
 45            {"probability(2)":0.05032117813705056,"probability(1)":0.9496778191327206,"probability(3)":1.002730228848799E-6}
 40            {"probability(2)":0.029067878914190822,"probability(1)":0.9709319755439045,"probability(3)":1.4554190472489897E-7}
 25            {"probability(2)":0.07479484451501492,"probability(1)":0.925204249153934,"probability(3)":9.063310511264311E-7}
 65            {"probability(2)":0.8863461817467817,"probability(1)":0.09770172271301178,"probability(3)":0.01595209554020663}
100            {"probability(2)":0.9237578287940943,"probability(1)":0.02589120875209655,"probability(3)":0.05035096245380913}
 75            {"probability(2)":0.911674205886805,"probability(1)":0.016605021762068888,"probability(3)":0.07172077235112624}
 10            {"probability(2)":0.039311938980028084,"probability(1)":0.9606879022512271,"probability(3)":1.5876874476009161E-7}
120            {"probability(2)":0.5374819297040777,"probability(1)":8.430967446201364E-4,"probability(3)":0.461674973551302}
110            {"probability(2)":0.007137937665082582,"probability(1)":3.189025872579834E-6,"probability(3)":0.9928588733090449}
150            {"probability(2)":0.3139101275662714,"probability(1)":9.121774950581918E-4,"probability(3)":0.6851776949386704}
 35            {"probability(2)":0.03888402774824238,"probability(1)":0.9611157561048745,"probability(3)":2.1614688296541417E-7}
105            {"probability(2)":0.028075184978854792,"probability(1)":1.4243195609491135E-5,"probability(3)":0.9719105718255356}