<jupyter_start><jupyter_text>Build a Model<jupyter_code>## multiclass classification in iris dataset:

data(BostonHousing)
BostonHousing$chas = as.numeric(BostonHousing$chas)

set.seed(1960)

dataset = BostonHousing[, -14] 

create_model  =  function() {

    model <- train(medv ~ ., data = BostonHousing, method = "xgbTree")    

    return(model)
}

model = create_model()
# cat(model$feature_names)
# print(model)
pred_labels <- predict(model, BostonHousing[, -14] , type="raw")
df = data.frame(BostonHousing[,14])
names(df) = c("medv")
df$Estimator = pred_labels
df$Error = df$Estimator - df$medv
MAPE = mean(abs(df$Error / df$medv))
summary(df)
MAPE<jupyter_output><empty_output><jupyter_text>SQL Code Generation<jupyter_code>test_ws_sql_gen = function(mod) {
    WS_URL = "https://sklearn2sql.herokuapp.com/model"
    WS_URL = "http://localhost:1888/model"
    model_serialized <- serialize(mod, NULL)
    b64_data = base64encode(model_serialized)
    data = list(Name = "xgboost_test_model", SerializedModel = b64_data , SQLDialect = "postgresql" , Mode="caret")
    r = POST(WS_URL, body = data, encode = "json")
    # print(r)
    content = content(r)
    # print(content)
    lSQL = content$model$SQLGenrationResult[[1]]$SQL # content["model"]["SQLGenrationResult"][0]["SQL"]
    return(lSQL);
}
lModelSQL = test_ws_sql_gen(model)
cat(lModelSQL)<jupyter_output>WITH "XGB_0" AS 
(WITH "DT_node_lookup" AS 
(SELECT "ADS"."KEY" AS "KEY", CASE WHEN ("ADS"."Feature_5" < 6.79699993) THEN CASE WHEN ("ADS"."Feature_0" < 9.28069496) THEN CASE WHEN ("ADS"."Feature_5" < 6.14300013) THEN 7 ELSE 8 END ELSE CASE WHEN ("ADS"."Feature_0" < 59.5282974) THEN 9 ELSE 10 END END ELSE CASE WHEN ("ADS"."Feature_5" < 7.43700027) THEN CASE WHEN ("ADS"."Feature_11" < 130.345001) THEN 11 ELSE 12 END ELSE 6 END END AS node_id_2 
FROM "INPUT_DATA" AS "ADS"), 
"DT_node_data" AS 
(SELECT "Values".nid AS nid, "Values"."Estimator" AS "Estimator" 
FROM (SELECT 6 AS nid, 12.9722738 AS "Estimator" UNION ALL SELECT 7 AS nid, 5.41195965 AS "Estimator" UNION ALL SELECT 8 AS nid, 6.82309532 AS "Estimator" UNION ALL SELECT 9 AS nid, 3.57075024 AS "Estimator" UNION ALL SELECT 10 AS nid, 1.28000009 AS "Estimator" UNION ALL SELECT 11 AS nid, 2.0400002 AS "Estimator" UNION ALL SELECT 12 AS nid, 9.28163338 AS "Estimator") AS "Values"), 
"DT_Output" AS 
(SELECT "DT_node_lookup"."KEY" AS "K[...]<jupyter_text>Execute the SQL Code<jupyter_code>library(RODBC)
conn = odbcConnect("pgsql", uid="db", pwd="db", case="nochange")
odbcSetAutoCommit(conn , autoCommit = TRUE)
df_sql = dataset
names(df_sql) = sprintf("Feature_%d",0:(ncol(df_sql)-1))
df_sql$KEY = seq.int(nrow(dataset))

sqlDrop(conn , "INPUT_DATA" , errors = FALSE)
sqlSave(conn, df_sql, tablename = "INPUT_DATA", verbose = FALSE)

# df_sql
colnames(df_sql)
# odbcGetInfo(conn)
# sqlTables(conn)
df_sql_out = sqlQuery(conn, lModelSQL)
head(df_sql_out[order(df_sql_out$KEY),])
# df_sql_out<jupyter_output><empty_output><jupyter_text>R XGBoost Output<jupyter_code>estimator  =  predict(model, dataset, type = "raw")
df_r_out = data.frame(estimator)
names(df_r_out) = c("Estimator")

df_r_out$KEY = seq.int(nrow(dataset))
head(df_r_out)<jupyter_output><empty_output><jupyter_text>Compare R and SQL output<jupyter_code>df_merge = merge(x = df_r_out, y = df_sql_out, by = "KEY", all = TRUE, , suffixes = c("_1","_2"))
head(df_merge)
df_merge$Error = df_merge$Estimator_1 - df_merge$Estimator_2
df_merge$AbsError = abs(df_merge$Error)
head(df_merge)

df_merge_largest_errors = df_merge[df_merge$AbsError > 0.0001,]
df_merge_largest_errors
stopifnot(nrow(df_merge_largest_errors) == 0)
summary(df_sql_out)
summary(df_r_out)
summary(df_merge)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14308855291576675, 'fraction_numerical': 0.04859611231101512, 'mean_word_length': 6.111324376199616, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 21, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '535800', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1632, 'n_tokens_neox': 1435, 'n_words': 306, 'path': 'doc/caret2sql-xgboost-boston.ipynb', 'repo_name': 'antoinecarme/caret2sql', 'size': '3704'}