Process a large query chunk by chunk

Runs a query and passes its result to a function in chunks of chunk_size rows, so that results larger than the memory (the texts of a whole database) can be processed: only one chunk is held in R at a time.

Usage

db_query_chunks(
  con,
  sql,
  fun,
  chunk_size = 100000L,
  params = NULL,
  combine = TRUE
)

Arguments

con

A DBI connection.

sql

Character. One SQL query.

fun

Function applied to each chunk (a data.table); its results are collected.

chunk_size

Integer. Rows per chunk.

params

List or NULL. Values bound to ? placeholders.

combine

Logical. When every result of fun is a data frame, bind them into one data.table; otherwise return the list.

Value

The results of fun, bound into one data.table or as a list (an empty list when the query returns no row).

Details

When the arrow package is installed and no parameters are bound, DuckDB streams the result as Arrow record batches; otherwise chunks are fetched with DBI::dbFetch(), which saves R memory but lets DuckDB materialise the whole result first. Benchmark (bench/results_memory.csv), 2 million rows of 600-character texts in chunks of 100,000: 10.5 s and a peak of 3.6 GB with dbFetch() (no better than fetching everything at once), 5.6 s and 2.7 GB with Arrow batches. Aggregate in SQL whenever possible; use chunks for what SQL cannot do (R text functions, writing files).

See also

Other memory: db_check_unique(), free_memory_gb()

Examples

con <- db_connect(tempfile(fileext = ".duckdb"))
#> duckdb is storing downloaded extensions and secrets under ~/.duckdb:
#> ℹ /home/agoutsmedt/.duckdb
#> This persists across sessions and is shared with the DuckDB CLI and other clients.
#> ℹ Run duckdb(shared_home = FALSE) to use a temporary directory instead.
#> ℹ See ?duckdb_storage for details and alternatives.
DBI::dbWriteTable(con, "text", data.frame(doc_id = 1:10, text = strrep("a", 1:10)))
db_query_chunks(con, "SELECT * FROM text", function(chunk) {
  chunk[, .(n_docs = .N, n_chars = sum(nchar(text)))]
}, chunk_size = 4)
#>    n_docs n_chars
#>     <int>   <int>
#> 1:      4      10
#> 2:      4      26
#> 3:      2      19
db_disconnect(con)