call-pal-auto-with-connection.RmdIn this article, we show users how to use the CallPalAutoWithConnection function in hana.ml.r package to call native PAL procedures (like PAL_FAIRML and PAL_FAIRML_PREDICT in this case), especially those not implemented yet in hana.ml.r.
library(hana.ml.r)
library(uuid)
source("./config.R")
#> WARN [2024-01-11 15:19:56] The HANA version is 4.50.000.00.1704032026 and note that some classes, functions and their parameters are different between HANA versions and please refer to the documentation of the specific function and class!
# Assuming that the file config.R contains the lines of code for creating
# a jdbc/odbc connectionto some SAP HANA database server as follows --
#
# for jdbc:
# conn <- hanaml.ConnectionContext(dsn = <host>:<port>,
# username = 'xxxx',
# password = 'xxxx',
# odbc = FALSE,
# jdbcDriverPath = <path to jdbcDriver>,
# ...)
# for odbc:
# conn <- hanaml.ConnectionContext(dsn = <ODBC data source name>,
# username = 'xxxx',
# password = 'xxxx',
# odbc = TRUE,
# ...)
DATA.TBL.FIT <- 'CREATE COLUMN TABLE PAL_FIT_TBL
("ID" INTEGER,
"A1" NVARCHAR(5),
"A2" DOUBLE,
"Y" INTEGER)'
sqlQueryMix(conn$connection, "DROP TABLE PAL_FIT_TBL")
#> [1] "No Data"
sqlQueryMix(conn$connection, DATA.TBL.FIT)
#> [1] "No Data"
data.list <- list( c("1, 'A', 0.1, 0"),
c("2, 'B', 0.7, 1"),
c("3, 'B', 0.2, 0"),
c("4, 'A', 0.8, 1"),
c("5, 'A', 0.3, 0"),
c("6, 'B', 0.4, 0"),
c("7, 'A', 0.1, 0"),
c("8, 'B', 0.7, 1"),
c("9, 'B', 0.2, 0"),
c("10, 'A', 0.8, 1"),
c("11, 'A', 0.3, 0"),
c("12, 'B', 0.4, 0"))
for (table.data in data.list) {
sqlQueryMix(conn$connection, sprintf("INSERT INTO %s VALUES (%s)",
"PAL_FIT_TBL",
paste(table.data, collapse = ", ")))
}
data.fit <- conn$table("PAL_FIT_TBL")
print(data.fit$Head(5)$Collect())
#> ID A1 A2 Y
#> 1 1 A 0.1 0
#> 2 2 B 0.7 1
#> 3 3 B 0.2 0
#> 4 4 A 0.8 1
#> 5 5 A 0.3 0
DATA.TBL.PREDICT <- 'CREATE COLUMN TABLE PAL_PREDICT_TBL
("ID" INTEGER,
"A1" NVARCHAR(5),
"A2" DOUBLE)'
sqlQueryMix(conn$connection, "DROP TABLE PAL_PREDICT_TBL")
#> [1] "No Data"
sqlQueryMix(conn$connection, DATA.TBL.PREDICT)
#> [1] "No Data"
data.list <- list( c("1, 'A', 0.1"),
c("2, 'B', 0.7"),
c("3, 'B', 0.2"),
c("4, 'A', 0.8"),
c("5, 'A', 0.3"),
c("6, 'B', 0.4"),
c("7, 'A', 0.1"),
c("8, 'B', 0.7"),
c("9, 'B', 0.2"),
c("10, 'A', 0.8"),
c("11, 'A', 0.3"),
c("12, 'B', 0.4"))
for (table.data in data.list) {
sqlQueryMix(conn$connection, sprintf("INSERT INTO %s VALUES (%s)",
"PAL_PREDICT_TBL",
paste(table.data, collapse = ", ")))
}
data.predict <- conn$table("PAL_PREDICT_TBL")
print(data.predict$Head(5)$Collect())
#> ID A1 A2
#> 1 1 A 0.1
#> 2 2 B 0.7
#> 3 3 B 0.2
#> 4 4 A 0.8
#> 5 5 A 0.3
uid <- toupper(gsub("-", "_", uuid::UUIDgenerate()))
param.tbl.sql <- 'CREATE LOCAL TEMPORARY TABLE #PAL_PARAMETER_TAB (\"PARAM_NAME\" VARCHAR(100),
\"INT_VALUE\" INTEGER, \"DOUBLE_VALUE\" DOUBLE, \"STRING_VALUE\" VARCHAR(100));'
sqlQueryMix(conn$connection, "DROP TABLE #PAL_PARAMETER_TAB")
#> [1] "No Data"
sqlQueryMix(conn$connection, param.tbl.sql)
#> [1] "No Data"
param.list <- c(
"'HAS_ID', 1, NULL, NULL",
"'DEPENDENT_VARIABLE', NULL, NULL, 'Y'",
"'CATEGORICAL_VARIABLE', NULL, NULL, 'Y'",
"'FAIR_SENSITIVE_VARIABLE', NULL, NULL, 'A1'",
"'FAIR_SUBMODEL', NULL, NULL, 'HGBT'",
"'FAIR_CONSTRAINT', NULL, NULL, 'demographic_parity'",
"'FAIR_LOSS_FUNC', NULL, NULL, 'error_rate'"
)
for (table.data in param.list) {
sqlQueryMix(conn$connection, sprintf(
"INSERT INTO %s VALUES (%s)", "#PAL_PARAMETER_TAB", paste(table.data, collapse = ", ")))
}
param.tbl <- conn$table("#PAL_PARAMETER_TAB")
print(param.tbl$Collect())
#> PARAM_NAME INT_VALUE DOUBLE_VALUE STRING_VALUE
#> 1 HAS_ID 1 NA <NA>
#> 2 DEPENDENT_VARIABLE NA NA Y
#> 3 CATEGORICAL_VARIABLE NA NA Y
#> 4 FAIR_SENSITIVE_VARIABLE NA NA A1
#> 5 FAIR_SUBMODEL NA NA HGBT
#> 6 FAIR_CONSTRAINT NA NA demographic_parity
#> 7 FAIR_LOSS_FUNC NA NA error_rate
CallPalAutoWithConnection(conn=conn, funcname=proc.name, tableNames=in.tables, resultNames=out.tables)
model.tbl <- conn$table(out.tables[[1]])
print(model.tbl$Head(3)$Collect()) # model table
#> ROW_INDEX
#> 1 0
#> 2 1
#> 3 2
#> MODEL_CONTENT
#> 1 {"name":"eg","labels":["1","0"],"model":{"wts":[0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02,0.02],"submodels":[{"name":"hgbt","model":{"head":{"ver":"rapidjson","model_version":"2.0","nrow":12,"nclass":2,"var":{"repl":[0,0,0.25,0.75],"map":[["A","B"],[0,1]],"name":["A1","A2","Y"],"typ":[83,100,73],"cate":[[],[]]},"param":{"obj":"binary:logistic","bs":[0.0]}},"trees":[{"nodes":[{"id":0,"n":12,"gn":1.0,"w":-0.15,"sp":1,"md":82,"x":0.55,"ch":[1,2]},{"id":1,"n":8,"gn":5.333333333333333,"w":-0.39999999999999999,"ch":[]},{"id":2,"n":4,"gn":2.0,"w":0.3,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.5833132964558405,"w":-0.11602307925537738,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":2.3746484886763827,"w":-0.2958603876150163,"ch":[]},{"id":2,"n":6,"gn":0.32919968733377177,"w":0.10978138008106686,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.4175204659333183,"w":-0.09969236965135643,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.7089769066612495,"w":-0.2568108464195174,"ch":[]},{"id":2,"n":6,"gn":0.2235954010994021,"w":0.09065199294712141,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.30917708766175847,"w":-0.08743046286226508,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.2648125077717867,"w":-0.2271999104951825,"ch":[]},{"id":2,"n":6,"gn":0.15241888072691818,"w":0.07505746148224583,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.23586542317563448,"w":-0.07789524702254754,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":0.9569413223793747,"w":-0.20353926069695386,"ch":[]},{"id":2,"n":6,"gn":0.10417348648425884,"w":0.06224793828159015,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.18476445509906204,"w":-0.07028659004199688,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.005814619531209248,"w":-0.015810394190469903,"ch":[]},{"id":2,"n":6,"gn":0.20245373808934703,"w":-0.08954334844448858,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.1289691155286887,"w":-0.05882392486591282,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.004145497621944307,"w":-0.013362062382640197,"ch":[]},{"id":2,"n":6,"gn":0.14078361270863425,"w":-0.07479672065898335,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.09015115759772338,"w":-0.0492705036715286,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.002956283946417471,"w":-0.011292815982466235,"ch":[]},{"id":2,"n":6,"gn":0.09807858710461814,"w":-0.06255274696815409,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.06309081488729733,"w":-0.04129161536727175,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0021086865959448039,"w":-0.00954395339534965,"ch":[]},{"id":2,"n":6,"gn":0.06842631479127734,"w":-0.05235420025794902,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.04419591115976562,"w":-0.03461804328206986,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0015043864515895962,"w":-0.008065873435220287,"ch":[]},{"id":2,"n":6,"gn":0.04779362086397101,"w":-0.04384091590750182,"ch":[]}],"height":1,"nnode":3}]}},{"name":"hgbt","model":{"head":{"ver":"rapidjson","model_version":"2.0","nrow":12,"nclass":2,"var":{"repl":[0,0,0.25,0.75],"map":[["A","B"],[0,1]],"name":["A1","A2","Y"],"typ":[83,100,73],"cate":[[],[]]},"param":{"obj":"binary:logistic","bs":[0.0]}},"trees":[{"nodes":[{"id":0,"n":12,"gn":1.0,"w":-0.15,"sp":1,"md":82,"x":0.55,"ch":[1,2]},{"id":1,"n":8,"gn":5.333333333333333,"w":-0.39999999999999999,"ch":[]},{"id":2,"n":4,"gn":2.0,"w":0.3,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.5833132964558405,"w":-0.11602307925537738,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":2.3746484886763827,"w":-0.2958603876150163,"ch":[]},{"id":2,"n":6,"gn":0.32919968733377177,"w":0.10978138008106686,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.4175204659333183,"w":-0.09969236965135643,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.7089769066612495,"w":-0.2568108464195174,"ch":[]},{"id":2,"n":6,"gn":0.2235954010994021,"w":0.09065199294712141,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.30917708766175847,"w":-0.08743046286226508,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.2648125077717867,"w":-0.2271999104951825,"ch":[]},{"id":2,"n":6,"gn":0.15241888072691818,"w":0.07505746148224583,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.23586542317563448,"w":-0.07789524702254754,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":0.9569413223793747,"w":-0.20353926069695386,"ch":[]},{"id":2,"n":6,"gn":0.10417348648425884,"w":0.06224793828159015,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.18476445509906204,"w":-0.07028659004199688,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0058
#> 2 14619531209248,"w":-0.015810394190469903,"ch":[]},{"id":2,"n":6,"gn":0.20245373808934703,"w":-0.08954334844448858,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.1289691155286887,"w":-0.05882392486591282,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.004145497621944307,"w":-0.013362062382640197,"ch":[]},{"id":2,"n":6,"gn":0.14078361270863425,"w":-0.07479672065898335,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.09015115759772338,"w":-0.0492705036715286,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.002956283946417471,"w":-0.011292815982466235,"ch":[]},{"id":2,"n":6,"gn":0.09807858710461814,"w":-0.06255274696815409,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.06309081488729733,"w":-0.04129161536727175,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0021086865959448039,"w":-0.00954395339534965,"ch":[]},{"id":2,"n":6,"gn":0.06842631479127734,"w":-0.05235420025794902,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.04419591115976562,"w":-0.03461804328206986,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0015043864515895962,"w":-0.008065873435220287,"ch":[]},{"id":2,"n":6,"gn":0.04779362086397101,"w":-0.04384091590750182,"ch":[]}],"height":1,"nnode":3}]}},{"name":"hgbt","model":{"head":{"ver":"rapidjson","model_version":"2.0","nrow":12,"nclass":2,"var":{"repl":[0,0,0.25,0.75],"map":[["A","B"],[0,1]],"name":["A1","A2","Y"],"typ":[83,100,73],"cate":[[],[]]},"param":{"obj":"binary:logistic","bs":[0.0]}},"trees":[{"nodes":[{"id":0,"n":12,"gn":1.0,"w":-0.15,"sp":1,"md":82,"x":0.55,"ch":[1,2]},{"id":1,"n":8,"gn":5.333333333333333,"w":-0.39999999999999999,"ch":[]},{"id":2,"n":4,"gn":2.0,"w":0.3,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.5833132964558405,"w":-0.11602307925537738,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":2.3746484886763827,"w":-0.2958603876150163,"ch":[]},{"id":2,"n":6,"gn":0.32919968733377177,"w":0.10978138008106686,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.4175204659333183,"w":-0.09969236965135643,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.7089769066612495,"w":-0.2568108464195174,"ch":[]},{"id":2,"n":6,"gn":0.2235954010994021,"w":0.09065199294712141,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.30917708766175847,"w":-0.08743046286226508,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.2648125077717867,"w":-0.2271999104951825,"ch":[]},{"id":2,"n":6,"gn":0.15241888072691818,"w":0.07505746148224583,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.23586542317563448,"w":-0.07789524702254754,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":0.9569413223793747,"w":-0.20353926069695386,"ch":[]},{"id":2,"n":6,"gn":0.10417348648425884,"w":0.06224793828159015,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.18476445509906204,"w":-0.07028659004199688,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.005814619531209248,"w":-0.015810394190469903,"ch":[]},{"id":2,"n":6,"gn":0.20245373808934703,"w":-0.08954334844448858,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.1289691155286887,"w":-0.05882392486591282,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.004145497621944307,"w":-0.013362062382640197,"ch":[]},{"id":2,"n":6,"gn":0.14078361270863425,"w":-0.07479672065898335,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.09015115759772338,"w":-0.0492705036715286,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.002956283946417471,"w":-0.011292815982466235,"ch":[]},{"id":2,"n":6,"gn":0.09807858710461814,"w":-0.06255274696815409,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.06309081488729733,"w":-0.04129161536727175,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0021086865959448039,"w":-0.00954395339534965,"ch":[]},{"id":2,"n":6,"gn":0.06842631479127734,"w":-0.05235420025794902,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.04419591115976562,"w":-0.03461804328206986,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0015043864515895962,"w":-0.008065873435220287,"ch":[]},{"id":2,"n":6,"gn":0.04779362086397101,"w":-0.04384091590750182,"ch":[]}],"height":1,"nnode":3}]}},{"name":"hgbt","model":{"head":{"ver":"rapidjson","model_version":"2.0","nrow":12,"nclass":2,"var":{"repl":[0,0,0.25,0.75],"map":[["A","B"],[0,1]],"name":["A1","A2","Y"],"typ":[83,100,73],"cate":[[],[]]},"param":{"obj":"binary:logistic","bs":[0.0]}},"trees":[{"nodes":[{"id":0,"n":12,"gn":1.0,"w":-0.15,"sp":1,"md":82,"x":0.55,"ch":[1,2]},{"id":1,"n":8,"gn":5.333333333333333,"w":-0.39999999999999999,"ch":[]},{"id":2,"n":4,"gn":2.0,"w":0.3,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.5833132964558405,"w":-0.11602307925537738,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":2.3746484886763827,"w":-0.2958603876150163,"ch":[]},{"id":2,"n":6,"gn":0.32919968733377177,"w":0.10978138008106686,"ch":[]}],"height":1,"nnode":3},{"nodes":[{
#> 3 "id":0,"n":12,"gn":0.4175204659333183,"w":-0.09969236965135643,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.7089769066612495,"w":-0.2568108464195174,"ch":[]},{"id":2,"n":6,"gn":0.2235954010994021,"w":0.09065199294712141,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.30917708766175847,"w":-0.08743046286226508,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.2648125077717867,"w":-0.2271999104951825,"ch":[]},{"id":2,"n":6,"gn":0.15241888072691818,"w":0.07505746148224583,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.23586542317563448,"w":-0.07789524702254754,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":0.9569413223793747,"w":-0.20353926069695386,"ch":[]},{"id":2,"n":6,"gn":0.10417348648425884,"w":0.06224793828159015,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.18476445509906204,"w":-0.07028659004199688,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.005814619531209248,"w":-0.015810394190469903,"ch":[]},{"id":2,"n":6,"gn":0.20245373808934703,"w":-0.08954334844448858,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.1289691155286887,"w":-0.05882392486591282,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.004145497621944307,"w":-0.013362062382640197,"ch":[]},{"id":2,"n":6,"gn":0.14078361270863425,"w":-0.07479672065898335,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.09015115759772338,"w":-0.0492705036715286,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.002956283946417471,"w":-0.011292815982466235,"ch":[]},{"id":2,"n":6,"gn":0.09807858710461814,"w":-0.06255274696815409,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.06309081488729733,"w":-0.04129161536727175,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0021086865959448039,"w":-0.00954395339534965,"ch":[]},{"id":2,"n":6,"gn":0.06842631479127734,"w":-0.05235420025794902,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.04419591115976562,"w":-0.03461804328206986,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0015043864515895962,"w":-0.008065873435220287,"ch":[]},{"id":2,"n":6,"gn":0.04779362086397101,"w":-0.04384091590750182,"ch":[]}],"height":1,"nnode":3}]}},{"name":"hgbt","model":{"head":{"ver":"rapidjson","model_version":"2.0","nrow":12,"nclass":2,"var":{"repl":[0,0,0.25,0.75],"map":[["A","B"],[0,1]],"name":["A1","A2","Y"],"typ":[83,100,73],"cate":[[],[]]},"param":{"obj":"binary:logistic","bs":[0.0]}},"trees":[{"nodes":[{"id":0,"n":12,"gn":1.0,"w":-0.15,"sp":1,"md":82,"x":0.55,"ch":[1,2]},{"id":1,"n":8,"gn":5.333333333333333,"w":-0.39999999999999999,"ch":[]},{"id":2,"n":4,"gn":2.0,"w":0.3,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.5833132964558405,"w":-0.11602307925537738,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":2.3746484886763827,"w":-0.2958603876150163,"ch":[]},{"id":2,"n":6,"gn":0.32919968733377177,"w":0.10978138008106686,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.4175204659333183,"w":-0.09969236965135643,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.7089769066612495,"w":-0.2568108464195174,"ch":[]},{"id":2,"n":6,"gn":0.2235954010994021,"w":0.09065199294712141,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.30917708766175847,"w":-0.08743046286226508,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":1.2648125077717867,"w":-0.2271999104951825,"ch":[]},{"id":2,"n":6,"gn":0.15241888072691818,"w":0.07505746148224583,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.23586542317563448,"w":-0.07789524702254754,"sp":1,"md":82,"x":0.35,"ch":[1,2]},{"id":1,"n":6,"gn":0.9569413223793747,"w":-0.20353926069695386,"ch":[]},{"id":2,"n":6,"gn":0.10417348648425884,"w":0.06224793828159015,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.18476445509906204,"w":-0.07028659004199688,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.005814619531209248,"w":-0.015810394190469903,"ch":[]},{"id":2,"n":6,"gn":0.20245373808934703,"w":-0.08954334844448858,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.1289691155286887,"w":-0.05882392486591282,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.004145497621944307,"w":-0.013362062382640197,"ch":[]},{"id":2,"n":6,"gn":0.14078361270863425,"w":-0.07479672065898335,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.09015115759772338,"w":-0.0492705036715286,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.002956283946417471,"w":-0.011292815982466235,"ch":[]},{"id":2,"n":6,"gn":0.09807858710461814,"w":-0.06255274696815409,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.06309081488729733,"w":-0.04129161536727175,"sp":0,"md":82,"x":0,"ch":[1,2]},{"id":1,"n":6,"gn":0.0021086865959448039,"w":-0.00954395339534965,"ch":[]},{"id":2,"n":6,"gn":0.06842631479127734,"w":-0.05235420025794902,"ch":[]}],"height":1,"nnode":3},{"nodes":[{"id":0,"n":12,"gn":0.04419591115976562,"w":-0.03461804328206986,"sp":0,"md":82,"
predict.proc.name <- 'PAL_FAIRML_PREDICT'
uid2 <- toupper(gsub("-", "_", uuid::UUIDgenerate()))
res.tbl <- sprintf('#PAL_FAIRML_RESULT_TBL_%s', uid2)
param.tbl.sql2 <- 'CREATE LOCAL TEMPORARY TABLE #PAL_PARAMETER_TAB2 (\"PARAM_NAME\" VARCHAR(100),
\"INT_VALUE\" INTEGER, \"DOUBLE_VALUE\" DOUBLE, \"STRING_VALUE\" VARCHAR(100));'
sqlQueryMix(conn$connection, "DROP TABLE #PAL_PARAMETER_TAB2")
sqlQueryMix(conn$connection, param.tbl.sql2)
param.tbl2 <- conn$table("#PAL_PARAMETER_TAB2")
in.tables <- list(data.predict, model.tbl, param.tbl2)
out.tables <- res.tbl
CallPalAutoWithConnection(conn=conn, funcname=predict.proc.name, tableNames=in.tables, resultNames=out.tables)
res <- conn$table(res.tbl)
print(res$Collect()) # result table
#> ID SCORE
#> 1 1 0
#> 2 2 1
#> 3 3 0
#> 4 4 1
#> 5 5 0
#> 6 6 0
#> 7 7 0
#> 8 8 1
#> 9 9 0
#> 10 10 1
#> 11 11 0
#> 12 12 0