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}