<jupyter_start><jupyter_text>Build a Model<jupyter_code>set.seed(1960)

data(BreastCancer)
# summary(BreastCancer)

bc = BreastCancer[,-1]

for(i in 1:(ncol(bc) - 1)){
  bc[, i] <- as.numeric(bc[, i])
  bc[is.na(bc[,i]), i] <- mean(bc[,i], na.rm = TRUE)
}
TGT_IDX = ncol(bc)

create_model  =  function() {

    model  <- train(Class ~ ., data = bc, method = "svmRadial", prob.model=TRUE)
    
    return(model)
}
# dataset
model = create_model()
pred <- predict(model, as.matrix(bc[, -TGT_IDX]) , type="prob")
pred_labels <- predict(model, as.matrix(bc[, -TGT_IDX]) , type="raw")
sum(pred_labels != bc$Class)/length(pred_labels)

pred[1:5,]<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 = "caret_svm_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 kernel_input_with_scaling AS 
(SELECT "ADS"."KEY" AS "KEY", (CAST("ADS"."Feature_0" AS FLOAT) - 4.4177396280400565) / 2.8157406585949314 AS "Feature_0", (CAST("ADS"."Feature_1" AS FLOAT) - 3.13447782546495) / 3.0514591099542008 AS "Feature_1", (CAST("ADS"."Feature_2" AS FLOAT) - 3.2074391988555084) / 2.971912767215713 AS "Feature_2", (CAST("ADS"."Feature_3" AS FLOAT) - 2.8068669527896994) / 2.8553792392170236 AS "Feature_3", (CAST("ADS"."Feature_4" AS FLOAT) - 3.2160228898426317) / 2.2142998866490484 AS "Feature_4", (CAST("ADS"."Feature_5" AS FLOAT) - 3.544655929721816) / 3.6018516398045315 AS "Feature_5", (CAST("ADS"."Feature_6" AS FLOAT) - 3.4377682403433485) / 2.438364252324251 AS "Feature_6", (CAST("ADS"."Feature_7" AS FLOAT) - 2.866952789699571) / 3.0536338936127745 AS "Feature_7", (CAST("ADS"."Feature_8" AS FLOAT) - 1.569384835479256) / 1.619802614296755 AS "Feature_8" 
FROM "INPUT_DATA" AS "ADS"), 
"SV_data" AS 
(SELECT "Values".sv_idx AS sv_idx, "Values".dual_coeff AS dual[...]<jupyter_text>Execute the SQL Code<jupyter_code>library(RODBC)
conn = odbcConnect("pgsql", uid="db", pwd="db", case="nochange")
odbcSetAutoCommit(conn , autoCommit = TRUE)
dataset = bc[, -TGT_IDX]

df_sql = as.data.frame(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)

head(df_sql)
# colnames(df_sql)
# odbcGetInfo(conn)
# sqlTables(conn)
df_sql_out = sqlQuery(conn, lModelSQL)
head(df_sql_out)<jupyter_output><empty_output><jupyter_text>R Caret SVM Output<jupyter_code>pred_proba  =  predict(model, as.matrix(dataset), type = "prob")
df_r_out = data.frame(pred_proba)
names(df_r_out) = sprintf("Proba_%s",model$levels)

df_r_out$KEY = seq.int(nrow(dataset))
df_r_out$Score_benign  =  NA
df_r_out$Score_malignant  =  NA
df_r_out$LogProba_benign  =  log(df_r_out$Proba_benign)
df_r_out$LogProba_malignant =  log(df_r_out$Proba_malignant)
df_r_out$Decision =   predict(model, as.matrix(dataset), type = "raw")
df_r_out$DecisionProba =  apply(pred_proba, 1, function(x) max(x))
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)
diffs_df = df_merge[df_merge$Decision_1 != df_merge$Decision_2,]
head(diffs_df)
print(c("DIFF_N_ROWS" , nrow(diffs_df)))
stopifnot(nrow(diffs_df) == 0)

summary(df_sql_out)
summary(df_r_out)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1639135959339263, 'fraction_numerical': 0.08792884371029225, 'mean_word_length': 6.2619926199261995, 'pattern_counts': {'":': 0, '<': 26, '<?xml version=': 0, '>': 20, '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': '215048', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1898, 'n_tokens_neox': 1660, 'n_words': 262, 'path': 'doc/caret2sql-svmRadial-BreastCancer.ipynb', 'repo_name': 'antoinecarme/caret2sql', 'size': '3935'}