Materialize an optree as a table.

Run the data query as a CREATE TABLE AS . Think of as a function that can be applied to relop trees, not as a component to place in pipelines.

materialize(
  db,
  optree,
  table_name = mk_tmp_name_source("rquery_mat")(),
  ...,
  limit = NULL,
  source_limit = NULL,
  overwrite = TRUE,
  temporary = FALSE,
  qualifiers = NULL
)

Arguments

db	database connecton (rquery_db_info class or DBI connections preferred).
optree	relop operation tree.
table_name	character, name of table to create.
...	force later arguments to bind by name.
limit	numeric if not NULL result limit (to use this, last statement must not have a limit).
source_limit	numeric if not NULL limit sources to this many rows.
overwrite	logical if TRUE drop an previous table.
temporary	logical if TRUE try to create a temporary table.
qualifiers	optional named ordered vector of strings carrying additional db hierarchy terms, such as schema.

Value

table description

See also

db_td, execute, to_sql, rq_copy_to, mk_td

Examples

if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) {
  my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

  d <- rq_copy_to(my_db, 'd',
                   data.frame(AUC = 0.6, R2 = 0.2),
                   temporary = TRUE, overwrite = TRUE)
  optree <- extend_se(d, c("v" %:=% "AUC + R2", "x" %:=% "pmax(AUC,v)"))
  cat(format(optree))
  res <- materialize(my_db, optree, "example")
  cat(format(res))
  sql <- to_sql(res, my_db)
  cat(sql)
  print(DBI::dbGetQuery(my_db, sql))

  DBI::dbDisconnect(my_db)
}
#> mk_td("d", c(
#>   "AUC",
#>   "R2")) %.>%
#>  extend(.,
#>   v := AUC + R2) %.>%
#>  extend(.,
#>   x := pmax(AUC, v))
#> mk_td("example", c(
#>   "AUC",
#>   "R2",
#>   "v",
#>   "x"))
#> SELECT
#>  `AUC`,
#>  `R2`,
#>  `v`,
#>  `x`
#> FROM
#>  `example`
#>   AUC  R2   v   x
#> 1 0.6 0.2 0.8 0.8