Save query without loading the data into R environment

[hope-data-science]

I have a pretty large duckdb file and I use a query to generate a larger-than-memory data. How can I save this results elegantly without using compute to load the data into R environment (which might not be possible)?

[krlmlr]

Thanks, we need to document this better, and/or provide utilities.

For now, you could use COPY TO like this:

data_sql <- "..."
output_sql <- paste0("COPY (", data_sql, ") TO 'out.parquet' (FORMAT PARQUET, ...)")
dbExecute(con, output_sql)

Upstream docs: https://duckdb.org/docs/sql/statements/copy#copy--to

[hope-data-science]

FYI, currently I am using:

## to parquet
duckdb_sink_parquet = \(conn,sql,path) {
  DBI::dbExecute(conn = conn,
                 statement =  stringr::str_glue("COPY ({sql})
          TO '{path}' (FORMAT PARQUET)"))
}

## to a table in the current database (duckdb)
duckdb_sink_table = \(conn,sql,table_name){
  DBI::dbExecute(conn = conn,
                 statement =  stringr::str_glue("CREATE TABLE {table_name} AS {sql}"))
}

I wonder if DBI package has some shortcuts for these functions.

[krlmlr]

Thanks. This is out of scope for DBI, could be in scope for duckdb of for a utility package on top of duckdb.

[hope-data-science]

Another practice is:

tbl(duckdb_connecntion, "table_name") %>%
    sql_render() %>%
    db_save_query(con = duckdb_connection,sql = ., name = "new_table_name",temporary = FALSE)

I think we really need this sort of functions to implement out-of-memory computation.

Thanks.

[lgaborini]

If you have a lazy table dt that can't be materialized in-memory, I have also just discovered this:

dt |>
  compute(
    name = "new_table_name",
    overwrite = TRUE,
    temporary = FALSE
  )