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: boston_trans_normcont_model.R

library(pmml)                                                                           library(MASS)
library(randomForest)
dataBox <- xform_wrap(Boston)
# Normalize all numeric variables of the loaded boston dataset to lie between 0 and 1.
dataBox <- xform_min_max(dataBox)
#include only derived fields for modeling
#last element derived_medv index
last = length(names(dataBox$data))
#create dataset without original predictors 1-13 and last derived_medv
#to train model
dataset <- dataBox$data[-c(1:13, eval(last))]
names(dataset)
fit <- randomForest(medv ~ ., data=na.omit(dataset), ntree=50)
tpmml <- pmml(fit, transforms = dataBox)
save_pmml(tpmml,"../sql/boston_trans_normcont_model.pmml")

Query that Calls PMMLPredict Function

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

Query Output

*** Query completed. 101 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.
 
         id prediction                json_report
----------- ------------------------- -------------------------------------
        265 35.28476666666667         {"Predicted_medv":35.28476666666667}
         80 20.683633333333333        {"Predicted_medv":20.683633333333333}
        305 34.60083333333333         {"Predicted_medv":34.60083333333333}
        345 30.106199999999998        {"Predicted_medv":30.106199999999998}
        120 19.648199999999992        {"Predicted_medv":19.648199999999992}
        385 8.790796296296296         {"Predicted_medv":8.790796296296296}
        160 24.611733333333323        {"Predicted_medv":24.611733333333323}
        200 33.89993333333334         {"Predicted_medv":33.89993333333334}
        425 13.232299999999999        {"Predicted_medv":13.232299999999999}
        240 24.894433333333332        {"Predicted_medv":24.894433333333332}
        465 21.6355619047619          {"Predicted_medv":21.6355619047619}
        ...