2.0 - R 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_rf_value_transformation.R

library(dplyr)
library(tdplyr)
# Create connection
con <- td_create_context(host='server123@mydomain.com', uid='alice', pwd='alice', dType='native')
# Reference table from DBS
iris <- tbl(con, "iris_train")
# Convert iris to a local dataframe
iris_df <- as.data.frame(iris)
# Print the iris dataframe
iris_df
library(randomForest)
library(pmmlTransformations)
library(pmml)
#data(iris)
irisBox <- xform_wrap(iris_df)
irisBox <- xform_map(irisBox,
       xform_info = "[species -> derived_species][string->integer]",
       table = "./MapSpecies.csv", map_missing_to = "2"
     )
irisrf <- randomForest(as.factor(species) ~ ., data=irisBox$data[c(1:6)], importance=TRUE,
                       proximity=TRUE)
irisBox$data[c(1:6)]
tpmml <- pmml(irisrf, transforms = irisBox)
save_pmml(tpmml,"iris_db_df_map_value.pmml")

MapSpecies.csv

1,2
2,3
3,4

Query that Calls PMMLPredict Function

SELECT * FROM mldb.PMMLPredict (
  ON iris_test
  ON (SELECT * FROM pmml_models WHERE model_id='iris_db_df_map_value') 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
--- ---------- -----------------------------------------------------------------------------------------
 40 1          {"Predicted_species":"1","Probability_1":1.0,"Probability_3":0.0,"Probability_2":0.0}
120 3          {"Predicted_species":"3","Probability_1":0.0,"Probability_3":0.726,"Probability_2":0.274}
 80 2          {"Predicted_species":"2","Probability_1":0.0,"Probability_3":0.002,"Probability_2":0.998}
 15 1          {"Predicted_species":"1","Probability_1":0.972,"Probability_3":0.0,"Probability_2":0.028}
 55 2          {"Predicted_species":"2","Probability_1":0.0,"Probability_3":0.006,"Probability_2":0.994}
135 3          {"Predicted_species":"3","Probability_1":0.0,"Probability_3":0.75,"Probability_2":0.25}
 95 2          {"Predicted_species":"2","Probability_1":0.0,"Probability_3":0.002,"Probability_2":0.998}
 30 1          {"Predicted_species":"1","Probability_1":1.0,"Probability_3":0.0,"Probability_2":0.0}
110 3          {"Predicted_species":"3","Probability_1":0.0,"Probability_3":1.0,"Probability_2":0.0}
 70 2          {"Predicted_species":"2","Probability_1":0.0,"Probability_3":0.002,"Probability_2":0.998}