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

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