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_function_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(pmmlTransformations)
library(pmml)
#data(iris)
irisBox <- xform_wrap(iris_df)
#function transformation
irisBox <- xform_function(irisBox,orig_field_name = "species",new_field_name = "species.setosa",new_field_data_type = "factor",expression = "if (species == '1') {'setosa'} else {'other'}")
#using species.setosa to predict if setosa or not
fit <- lm (petal_width ~ species.setosa, data=irisBox$data)
tpmml <- pmml(fit, transforms = irisBox)
save_pmml(tpmml,"../sql/iris_db_lm_map_function.pmml")

Query that Calls PMMLPredict Function

SELECT * FROM mldb.PMMLPredict (
 ON iris_test
 ON (SELECT * FROM pmml_models WHERE model_id='iris_f_t_lm_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
----------- ------------------------- -------------------------------------
         95 1.68125                   {"Predicted_petal_width":1.68125}
        115 1.68125                   {"Predicted_petal_width":1.68125}
         30 1.68125                   {"Predicted_petal_width":1.68125}
         80 1.68125                   {"Predicted_petal_width":1.68125}
         50 1.68125                   {"Predicted_petal_width":1.68125}
         90 1.68125                   {"Predicted_petal_width":1.68125}
         15 1.68125                   {"Predicted_petal_width":1.68125}
          5 1.68125                   {"Predicted_petal_width":1.68125}
         40 1.68125                   {"Predicted_petal_width":1.68125}
         70 1.68125                   {"Predicted_petal_width":1.68125}
         55 1.68125                   {"Predicted_petal_width":1.68125}
         65 1.68125                   {"Predicted_petal_width":1.68125}
         85 1.68125                   {"Predicted_petal_width":1.68125}
         20 1.68125                   {"Predicted_petal_width":1.68125}
        120 1.68125                   {"Predicted_petal_width":1.68125}
         45 1.68125                   {"Predicted_petal_width":1.68125}
        135 1.68125                   {"Predicted_petal_width":1.68125}
        125 1.68125                   {"Predicted_petal_width":1.68125}
         60 1.68125                   {"Predicted_petal_width":1.68125}
        110 1.68125                   {"Predicted_petal_width":1.68125}
        140 1.68125                   {"Predicted_petal_width":1.68125}
         75 1.68125                   {"Predicted_petal_width":1.68125}
        100 1.68125                   {"Predicted_petal_width":1.68125}
        130 1.68125                   {"Predicted_petal_width":1.68125}
        150 1.68125                   {"Predicted_petal_width":1.68125}
         10 1.68125                   {"Predicted_petal_width":1.68125}
         25 1.68125                   {"Predicted_petal_width":1.68125}
         35 1.68125                   {"Predicted_petal_width":1.68125}
        145 1.68125                   {"Predicted_petal_width":1.68125}
        105 1.68125                   {"Predicted_petal_width":1.68125}