rquery (version 0.5.0)

materialize: Materialize an optree as a table.

Description

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.

Usage

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

Arguments

db

database connecton.

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 statment 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.

precheck

logical if TRUE precheck existance of table and columns.

Value

table handle

See Also

db_td, execute, to_sql, rq_copy_to, mk_td

Examples

# NOT RUN {
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", precheck = TRUE)
  cat(format(res))
  sql <- to_sql(res, my_db)
  cat(sql)
  print(DBI::dbGetQuery(my_db, sql))

  # extra example, table that doesn't match declared structure
  rq_copy_to(my_db, 'd',
              data.frame(z = 1:5),
              temporary = TRUE, overwrite = TRUE)
  tryCatch(
     materialize(my_db, optree, "example", precheck = TRUE),
     error = function(e) { as.character(e) }) %.>%
     print(.)

  DBI::dbDisconnect(my_db)
}

# }