Skip to contents

Export a database to Parquet files, and import it back

Source: R/parquet.R
export_parquet.Rd

  • export_parquet() writes every table to a folder of Parquet files (compressed with zstd), with the SQL that recreates the tables and a manifest of row counts. Parquet is an open, column-oriented format that any language reads: it is how to share a database with a co-author, or archive it independently of the DuckDB version.

  • import_parquet() recreates the tables in a database from such a folder, and checks the row counts against the manifest.

Usage

export_parquet(
  con,
  dir,
  tables = NULL,
  compression_level = 9L,
  file_size = "2GB"
)

import_parquet(con, dir, tables = NULL, overwrite = FALSE)

Arguments

con

A DBI connection (read access for the export, write access for the import).

dir

Character. Folder of the export.

tables

Character or NULL. Tables to export or import; NULL for all.

compression_level

Integer. zstd level, from 1 (fast) to 22 (small and slow).

file_size

Character. Approximate maximal size of each Parquet file (large tables are split into several files).

overwrite

Logical. Replace tables that already exist in the database.

Value

export_parquet(): the manifest (a data.table with table, n_rows, n_files, megabytes), invisibly. import_parquet(): the comparison of row counts with the manifest, invisibly.

Details

The export is resumable: each table is written to <table>.partial/ and renamed when complete, so an interrupted export restarts at the first unfinished table. The schema is exported as the CREATE TABLE statements stored by DuckDB (schema_tables.sql).

Memory: DuckDB exports within its memory limit (db_connect()). On a table of 10 million rows of 600-character texts, the export took 3 seconds with a peak of 5.5 GB under the default 6 GB limit; under a 2 GB limit it failed once out of three runs (out of memory). Keep at least 4 GB for exports of large text tables, or export them in parts.

Exporting is not publishing: check what the tables contain before sending them (licensed full texts, personal data), and exclude tables with tables.

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, "document", data.frame(doc_id = c("a:1", "a:2")))
dir <- file.path(tempdir(), "export_example")
export_parquet(con, dir)
#> ℹ Exporting document
#> ✔ Exporting document [14ms]
#> 
#> ✔ 1 table exported to /tmp/RtmpKqItGK/export_example (0 MB).
db_disconnect(con)

con2 <- 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.
import_parquet(con2, dir)
#> ✔ 1 table imported; row counts match the manifest.
DBI::dbGetQuery(con2, "SELECT * FROM document")
#>   doc_id
#> 1    a:1
#> 2    a:2
db_disconnect(con2)