Package 'ducklake'

Description

Adds Parquet files that already exist on disk (or object storage) to a DuckLake table without copying or rewriting them. This is the migration path for data that is already in Parquet: the files are recorded in the catalog in place.

Usage

add_data_files(
  table_name,
  files,
  schema_name = NULL,
  allow_missing = FALSE,
  ignore_extra_columns = FALSE,
  create = FALSE,
  ducklake_name = NULL
)

Arguments

Details

Runs ⁠CALL ducklake_add_data_files(...)⁠ once per file. The complete vector is atomic: outside an existing transaction the function opens one, so the batch creates one snapshot and any failure rolls back every registration. Inside with_transaction() the registrations join the caller's snapshot.

With create = TRUE, the table schema is read from the complete file list with read_parquet() and created with zero rows before registration. Neither this path nor registration copies the data or materializes it in R.

Ownership of each file transfers to DuckLake: compaction (e.g. merge_adjacent_files()) may later rewrite and delete it, so do not add files that something else still relies on.

Value

Invisibly returns the character vector of files added.

See Also

list_ducklake_files(), create_table()

Other table operations: create_schema(), create_table(), create_view(), drop_schema(), drop_view(), ducklake_exec(), get_ducklake_table(), get_metadata_table(), list_ducklake_tables(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("addfiles_lake_")
dir.create(lake_dir)
attach_ducklake("addfiles_lake", lake_path = lake_dir)

# Write a couple of Parquet extracts to register. Cast explicitly: DuckDB
# reads a bare 1.5 as DECIMAL, which will not map onto a DOUBLE column.
extract_dir <- tempfile("extracts_")
dir.create(extract_dir)
conn <- get_ducklake_connection()
jan <- file.path(extract_dir, "jan.parquet")
feb <- file.path(extract_dir, "feb.parquet")
DBI::dbExecute(conn, sprintf(
  "COPY (SELECT 1::INTEGER AS id, 1.5::DOUBLE AS value)
   TO '%s' (FORMAT PARQUET);", jan
))
DBI::dbExecute(conn, sprintf(
  "COPY (SELECT 2::INTEGER AS id, 2.5::DOUBLE AS value)
   TO '%s' (FORMAT PARQUET);", feb
))

# Bring an existing Parquet extract into the lake without copying it
create_table(data.frame(id = integer(), value = numeric()), "readings")
add_data_files("readings", jan)

# Register another, tolerating a column the table doesn't have
add_data_files("readings", feb, ignore_extra_columns = TRUE)

# Create a new table and register an existing Parquet batch atomically
add_data_files("staging", feb, create = TRUE)

unlink(extract_dir, recursive = TRUE)

detach_ducklake("addfiles_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Adds a column in place with ⁠ALTER TABLE ... ADD COLUMN⁠. This is a metadata-only change: no data files are rewritten, history is preserved, and earlier snapshots still show the old schema. Compare replace_table(), which rewrites the whole table.

Usage

add_table_column(table_name, column_name, type, default = NULL)

Arguments

Value

Invisibly returns NULL.

See Also

drop_table_column(), rename_table_column(), set_column_type(), rename_ducklake_table()

Other schema evolution: drop_table_column(), rename_ducklake_table(), rename_table_column(), set_column_not_null(), set_column_type()

Examples

lake_dir <- tempfile("addcol_lake_")
dir.create(lake_dir)
attach_ducklake("addcol_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

add_table_column("cars", "grade", "VARCHAR")
add_table_column("cars", "discount", "DECIMAL(5,2)", default = 0)

detach_ducklake("addcol_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Wrapper for the ducklake ATTACH command. Creates a new DuckLake if the specified name does not exist, or connects to an existing one, and labels the creation snapshot of a lake it creates. The lake can be detached with detach_ducklake().

Usage

attach_ducklake(
  ducklake_name,
  lake_path,
  backend = c("duckdb", "postgres", "sqlite", "mysql"),
  catalog_connection_string = NULL,
  read_only = FALSE,
  override_data_path = FALSE,
  data_inlining_row_limit = NULL,
  encrypted = FALSE,
  meta_encryption_key = NULL,
  snapshot_version = NULL,
  snapshot_time = NULL,
  automatic_migration = FALSE,
  create = TRUE,
  metadata_schema = NULL,
  author = NULL,
  commit_message = "Create lake"
)

Arguments

Details

By default DuckDB is used as the catalog database. Alternative backends (PostgreSQL, SQLite, MySQL) can be selected with the backend parameter, which enables concurrent multi-client access. See https://ducklake.select/docs/stable/duckdb/usage/choosing_a_catalog_database.

The ducklake extension, and the extension for a non-DuckDB backend, are loaded on each attach and downloaded the first time they are needed; a message names the extension and the directory before any download. Where that directory is depends on duckdb (see ?duckdb::duckdb_storage): in an interactive session duckdb offers to create ⁠~/.duckdb⁠ the first time it connects, and with that the download happens once per machine. To fetch the extensions ahead of time, for a container image or a machine that is offline when the lake is attached, use install_ducklake().

DuckLake writes snapshot 0 itself when it creates a lake, with no author and no commit message. When this call creates the lake, it fills those two fields on snapshot 0 from author and commit_message, the way set_snapshot_metadata() labels a snapshot after the fact, so the history from list_table_snapshots() starts with a labeled entry. Nothing is written when the lake already exists, when read_only is set, when the attach is pinned with snapshot_version or snapshot_time, or when the call runs inside an open transaction; set_snapshot_metadata(snapshot_id = 0) labels such a lake later. The write is silent, and a failure to write is a warning, not an error. For a PostgreSQL or MySQL catalog, whether the lake already existed is read from the catalog after the attach (one snapshot, id 0, no metadata), so a lake another client created and never wrote to is labeled too.

For credential management with PostgreSQL or MySQL, consider DuckDB's built-in secrets manager instead of embedding credentials in the connection string:

conn <- get_ducklake_connection()
DBI::dbExecute(conn, "CREATE SECRET (
    TYPE postgres,
    HOST '127.0.0.1',
    PORT 5432,
    DATABASE ducklake_catalog,
    USER 'analyst',
    PASSWORD 'secret'
)")

Then pass an empty or partial catalog_connection_string; DuckDB fills in the rest from the secret. See https://duckdb.org/docs/stable/configuration/secrets_manager.

Windows limitation: The postgres and mysql DuckDB extensions are not available on Windows (MinGW toolchain). Only duckdb and sqlite backends work there. Use Linux, macOS, or WSL for PostgreSQL/MySQL backends. See https://github.com/duckdb/duckdb/issues/7892. The aws and azure extensions are missing on Windows too, so create_storage_secret() with provider = "credential_chain" or type = "azure" does not work there; explicit S3 keys do.

Value

Invisibly, NULL. Called for its side effect of attaching the DuckLake catalog to the package's DuckDB connection and, for a lake it creates, of labeling snapshot 0.

See Also

detach_ducklake(), install_ducklake(), create_storage_secret()

Other connection management: create_storage_secret(), detach_ducklake(), ducklake_extension_available(), get_ducklake_backend(), get_ducklake_connection(), get_ducklake_info(), install_ducklake(), set_ducklake_connection()

Examples

# DuckDB catalog (default)
lake_dir <- tempfile("my_lake_")
dir.create(lake_dir)
attach_ducklake("my_lake", lake_path = lake_dir, author = "Data Engineer")
detach_ducklake("my_lake")

# Custom inlining threshold for a streaming workload
stream_dir <- tempfile("streaming_lake_")
dir.create(stream_dir)
attach_ducklake(
  "streaming_lake",
  lake_path = stream_dir,
  data_inlining_row_limit = 100
)

detach_ducklake("streaming_lake", shutdown = TRUE)
unlink(c(lake_dir, stream_dir), recursive = TRUE)

# The remaining forms need a catalog server, or extensions that are
# downloaded on first use, so they are not run here.
## Not run: 
# PostgreSQL catalog
attach_ducklake(
  "my_lake",
  backend = "postgres",
  catalog_connection_string = "dbname=ducklake_catalog host=localhost",
  lake_path = "/shared/lake/data/"
)

# SQLite catalog
attach_ducklake(
  "my_lake",
  backend = "sqlite",
  catalog_connection_string = "metadata.sqlite",
  lake_path = "data_files/"
)

# MySQL catalog
attach_ducklake(
  "my_lake",
  backend = "mysql",
  catalog_connection_string = "db=ducklake_catalog host=localhost",
  lake_path = "data_files/"
)

# DuckDB catalog on local disk, Parquet data on S3
create_storage_secret("s3", provider = "credential_chain")
attach_ducklake(
  "trial_lake",
  lake_path = "s3://my-trial-lake/data",
  catalog_connection_string = "trial_lake.ducklake"
)

# Read-only attach of a .ducklake catalog straight from object storage
attach_ducklake(
  "trial_lake",
  lake_path = "s3://my-trial-lake/data",
  catalog_connection_string = "s3://my-trial-lake/trial_lake.ducklake",
  read_only = TRUE
)

# Encrypted Parquet files (keys live in the catalog); needs httpfs
attach_ducklake("secure_lake", lake_path = "path/to/lake", encrypted = TRUE)

# Encrypt the catalog database too -- it is where the Parquet keys live.
# askpass prompts for the key so it never sits in code.
attach_ducklake(
  "secure_lake",
  lake_path = "path/to/lake",
  encrypted = TRUE,
  meta_encryption_key = askpass::askpass("Catalog encryption key")
)

# A frozen view of the lake as of snapshot 12, e.g. to reproduce a report
attach_ducklake("lake_v12", lake_path = "path/to/lake", snapshot_version = 12)

# A lake created with DuckDB 1.5.1 (catalog format 0.4), opened after
# upgrading: migrate it once, then attach as usual
attach_ducklake("old_lake", lake_path = "path/to/lake", automatic_migration = TRUE)

# Open an existing lake, and error if it is not there
attach_ducklake("prod_lake", lake_path = "/lakes/prod", create = FALSE)

# Several lakes in one PostgreSQL database, one schema each
attach_ducklake(
  "study_a",
  backend = "postgres",
  catalog_connection_string = "dbname=lakes host=db.example.org",
  lake_path = "s3://lakes/study_a",
  metadata_schema = "study_a"
)

## End(Not run)

Description

Attaches a remote Quack server as a catalog in the current session. Tables in the server's default database are then reachable as quack_name.table_name and can be queried with get_ducklake_table() or dplyr::tbl().

Usage

attach_quack(quack_name, uri, token = NULL, disable_ssl = FALSE)

Arguments

Details

A DuckLake served over Quack lives in its own catalog on the server rather than in the default database, so its tables are not exposed under quack_name. Query a served DuckLake with quack_query(), naming the lake's catalog, for example quack_query(uri, "SELECT * FROM trial.adsl").

Value

Invisibly, NULL. Called for its side effect of attaching the remote catalog to the package's DuckDB connection.

See Also

detach_quack(), quack_query(), quack_serve()

Other quack: detach_quack(), install_quack(), quack_query(), quack_serve(), quack_stop()

Examples

## Not run: 
# A remote DuckDB database, queried through the attached catalog
attach_quack("warehouse", "quack:data.example.org", token = "super_secret")

get_ducklake_table("warehouse.sales") |>
  dplyr::filter(region == "EMEA") |>
  dplyr::collect()

detach_quack("warehouse")

## End(Not run)

Description

Creates a timestamped backup of the Parquet data files and, for file-based backends (DuckDB, SQLite), the catalog database. For PostgreSQL/MySQL backends only data files are copied; use pg_dump / mysqldump for the catalog.

Usage

backup_ducklake(ducklake_name, lake_path, backup_path)

Arguments

Details

The catalog is copied with DuckDB's ⁠COPY FROM DATABASE⁠ while the lake stays attached. The copy is taken inside one transaction, so it is a consistent snapshot of the metadata, and nothing is detached or shut down along the way: other attached lakes, in-memory secrets, and a connection you registered with set_ducklake_connection() are left as they are. The data directories (one per schema) are copied as files.

To work with the backup, attach it with lake_path pointing at the backup directory. Pass override_data_path = TRUE, since the copied catalog remembers the original data location, and create = FALSE, so a mistyped path is an error rather than a new, empty lake. When the catalog file was not named after the lake (a split layout), name it with catalog_connection_string.

Important notes:

• Transactions committed after a backup won't be tracked when recovering. The data will exist in the Parquet files, but the backup will point to an earlier snapshot.

• Run compaction and cleanup before a backup, not after: they rewrite and remove data files that the copied catalog refers to.

• For production systems, schedule backups using {cronR} or {taskscheduleR}.

Value

Invisibly returns the path to the created backup directory

See Also

Other maintenance: checkpoint_ducklake(), cleanup_old_files(), delete_orphaned_files(), expire_snapshots(), flush_inlined_data(), get_table_info(), list_ducklake_files(), merge_adjacent_files(), plot_table_files(), rewrite_data_files()

Examples

# Create a DuckLake
lake_dir <- tempfile("my_lake")
dir.create(lake_dir)
attach_ducklake("my_lake", lake_path = lake_dir)

# Add some data
with_transaction(
  create_table(mtcars, "cars"),
  author = "User",
  commit_message = "Initial data"
)

# Create a backup; the lake stays attached throughout
backup_dir <- backup_ducklake(
  ducklake_name = "my_lake",
  lake_path = lake_dir,
  backup_path = file.path(lake_dir, "backups")
)

# Restore (override_data_path needed when location differs):
# detach_ducklake("my_lake")
# attach_ducklake("my_lake", lake_path = backup_dir,
#                 override_data_path = TRUE, create = FALSE)

detach_ducklake("my_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Starts a new transaction in the DuckDB connection. All subsequent operations will be part of this transaction until it is committed or rolled back.

Usage

begin_transaction(conn = NULL)

Arguments

Details

Transactions allow you to group multiple operations together and ensure they either all succeed or all fail. Use commit_transaction() to apply the changes or rollback_transaction() to discard them.

DuckDB supports full ACID transactions with multiple isolation levels.

Value

Invisibly returns TRUE on success

See Also

Other transactions: commit_transaction(), rollback_transaction(), set_ducklake_retry(), set_snapshot_metadata(), with_transaction()

Examples

lake_dir <- tempfile("begin_lake_")
dir.create(lake_dir)
attach_ducklake("begin_lake", lake_path = lake_dir)
create_table(data.frame(id = 1:3, status = "pending"), "jobs")

# Start a transaction
begin_transaction()

# Make some changes
get_ducklake_table("jobs") |>
  dplyr::filter(status == "pending") |>
  dplyr::mutate(status = "processed") |>
  ducklake_exec()

# Commit if everything looks good
commit_transaction()

# Or rollback if something went wrong
# rollback_transaction()

detach_ducklake("begin_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Runs DuckLake's maintenance operations on the catalog in one call: flushes inlined data, merges small files, rewrites heavily deleted files, and, when the lake carries a retention policy, expires old snapshots and deletes the files they released.

Usage

checkpoint_ducklake(ducklake_name = NULL)

Arguments

Details

CHECKPOINT runs, in order, the equivalents of flush_inlined_data(), expire_snapshots(), merge_adjacent_files(), rewrite_data_files(), cleanup_old_files(), and delete_orphaned_files(). The two retention steps do nothing until the lake carries a policy: snapshots are expired only when the expire_older_than option is set, and released files are deleted only when delete_older_than is set. A lake with neither keeps every snapshot and every file however often it is checkpointed.

A typical policy, set once with set_ducklake_option() and persisted in the catalog:

set_ducklake_option("expire_older_than", "90 days")
set_ducklake_option("delete_older_than", "7 days")

Run checkpoints periodically (e.g., after a batch of streaming inserts) to consolidate inlined data and keep query performance optimal.

Value

Invisibly returns NULL.

Note

On Windows with a DuckDB-file catalog, the file-cleanup step of CHECKPOINT can fail because Windows does not allow the catalog file to be opened a second time while the lake is attached (a current DuckDB limitation). flush_inlined_data() is unaffected; on Windows, prefer it for routine use and run full checkpoints from a fresh session, or use a PostgreSQL/SQLite catalog.

See Also

flush_inlined_data(), set_ducklake_option(), expire_snapshots()

Other data inlining: flush_inlined_data(), get_inlining_row_limit(), set_inlining_row_limit()

Other maintenance: backup_ducklake(), cleanup_old_files(), delete_orphaned_files(), expire_snapshots(), flush_inlined_data(), get_table_info(), list_ducklake_files(), merge_adjacent_files(), plot_table_files(), rewrite_data_files()

Examples

lake_dir <- tempfile("checkpoint_lake_")
dir.create(lake_dir)
attach_ducklake("checkpoint_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

# Run all maintenance
checkpoint_ducklake()

# Or specify a named lake
checkpoint_ducklake("checkpoint_lake")

detach_ducklake("checkpoint_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Physically deletes data files that are no longer referenced by any snapshot – typically files orphaned by expire_snapshots() or replaced by merge_adjacent_files().

Usage

cleanup_old_files(
  ducklake_name = NULL,
  older_than = NULL,
  cleanup_all = FALSE,
  dry_run = FALSE
)

Arguments

Details

As an alternative to calling this manually, set a retention policy once with set_ducklake_option("delete_older_than", "7 days"); every later checkpoint_ducklake() then deletes the files that have been released for at least that long.

Value

A data frame listing the deleted (or deletable) files.

See Also

expire_snapshots(), delete_orphaned_files(), checkpoint_ducklake()

Other maintenance: backup_ducklake(), checkpoint_ducklake(), delete_orphaned_files(), expire_snapshots(), flush_inlined_data(), get_table_info(), list_ducklake_files(), merge_adjacent_files(), plot_table_files(), rewrite_data_files()

Examples

lake_dir <- tempfile("cleanup_lake_")
dir.create(lake_dir)
attach_ducklake("cleanup_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

expire_snapshots(older_than = Sys.time())

# Preview, then delete everything that is scheduled
cleanup_old_files(dry_run = TRUE, cleanup_all = TRUE)
cleanup_old_files(cleanup_all = TRUE)

# Only delete files scheduled more than a week ago
cleanup_old_files(older_than = Sys.time() - 7 * 24 * 60 * 60)

detach_ducklake("cleanup_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Commits the current transaction, making all changes permanent. Optionally adds metadata (author, commit message, and extra info) to the snapshot.

Usage

commit_transaction(
  conn = NULL,
  author = NULL,
  commit_message = NULL,
  commit_extra_info = NULL
)

Arguments

Details

This function commits all changes made since begin_transaction() was called, making them permanent in the database.

If author, commit_message, or commit_extra_info are provided, they will be set using CALL ducklake.set_commit_message() within the transaction before the COMMIT statement, as required by the DuckLake v1.0 specification. An author set once for the session with options(ducklake.author = "...") is recorded on every commit that does not name one; the author argument wins when both are given.

The commit is confirmed with one message naming the snapshot it created, with the author and commit message when they were given. The id comes from DuckLake's last_committed_snapshot(), which tracks this connection's own commits, so it is right even when other sessions commit to the same lake at the same time. A transaction that changed nothing creates no snapshot, and the message says so, naming the snapshot the lake stands at. options(ducklake.verbose = FALSE) silences these confirmations.

Value

Invisibly returns TRUE on success

See Also

Other transactions: begin_transaction(), rollback_transaction(), set_ducklake_retry(), set_snapshot_metadata(), with_transaction()

Examples

lake_dir <- tempfile("commit_lake_")
dir.create(lake_dir)
attach_ducklake("commit_lake", lake_path = lake_dir)

# Basic commit
begin_transaction()
create_table(iris, "flowers")
commit_transaction()

# Commit with metadata
begin_transaction()
create_table(mtcars, "cars")
commit_transaction(
  author = "John Doe",
  commit_message = "Add cars dataset"
)

detach_ducklake("commit_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Experimental. Stores a rule as a check: a view, in a schema set aside for checks, that returns the rows breaking the rule, with the rule's label as its comment. State the rule the way you would say it, as the condition every row should meet. create_check() keeps the rows where it is false, so the code reads like the label and a rule such as "dose is not 0" is written dose != 0, with no double negative. run_checks() then counts the failing rows of every check in the schema.

Usage

create_check(
  .data,
  check_name,
  rule,
  label,
  listing = dplyr::everything(),
  schema_name = NULL,
  replace = TRUE
)

Arguments

Details

A row where the rule evaluates to NA passes, as it does under a SQL CHECK constraint: NOT (rule) is not true for it. When a missing value should fail, say so in the rule (!is.na(dose) & dose != 0) or give it a check of its own.

The schema, the view, and its label are written in one transaction, so a new or revised check is one snapshot. Inside with_transaction() they join the open transaction, which is how a change of rules gets an author and a commit message.

A check is an ordinary view, and create_check() is a convenience for the common case of one rule about each row. A rule that is easier to state as its failure (models that appear twice, visits without a subject) is a pipeline that returns those rows, stored with create_view() in the same schema and labelled with set_table_comment(). Both forms select the same rows, since three-valued logic treats NOT (dose != 0) and dose = 0 alike, so choose the one that reads better.

Value

Invisibly returns NULL.

See Also

run_checks() to run the checks, create_view() for a check written as its failure, and vignette("data-checks").

Examples

lake_dir <- tempfile("create_check_lake_")
dir.create(lake_dir)
attach_ducklake("create_check_lake", lake_path = lake_dir)
create_table(
  data.frame(model = rownames(mtcars), mtcars, row.names = NULL),
  "cars"
)

get_ducklake_table("main.cars") |>
  create_check(
    "cyl_known", cyl %in% c(4, 6, 8),
    label = "cyl is 4, 6, or 8",
    listing = c(model, cyl)
  )

# A rule that forbids a value is stated as it is said
get_ducklake_table("main.cars") |>
  create_check("hp_not_zero", hp != 0, label = "hp is not 0")

run_checks()

detach_ducklake("create_check_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Schemas group the tables of a lake, and are the natural home for medallion layers (bronze, silver, gold) or per-study areas. Every function that takes a table name accepts "schema.table", and list_ducklake_tables() shows each table's schema. DuckLake writes a schema's data files under a directory of their own, which is what path-based access control on object storage keys on.

Usage

create_schema(schema_name, if_not_exists = TRUE, ducklake_name = NULL)

Arguments

Value

Invisibly, NULL.

See Also

drop_schema(), list_ducklake_tables()

Other table operations: add_data_files(), create_table(), create_view(), drop_schema(), drop_view(), ducklake_exec(), get_ducklake_table(), get_metadata_table(), list_ducklake_tables(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("schema_lake_")
dir.create(lake_dir)
attach_ducklake("schema_lake", lake_path = lake_dir)

# One schema per medallion layer
create_schema("bronze")
create_schema("silver")

create_table(mtcars, "bronze.cars")
get_ducklake_table("bronze.cars") |>
  dplyr::filter(cyl == 4) |>
  create_table("silver.cars")

list_ducklake_tables()

detach_ducklake("schema_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Registers credentials with DuckDB's secrets manager so a DuckLake can read and write data files on object storage (lake_path = "s3://..." and friends). Wraps ⁠CREATE SECRET⁠.

Usage

create_storage_secret(
  type = c("s3", "gcs", "r2", "azure"),
  ...,
  provider = NULL,
  scope = NULL,
  name = NULL,
  persistent = FALSE
)

Arguments

Details

The httpfs extension (or the azure extension for type = "azure") is loaded automatically. With provider = "credential_chain", the aws extension is loaded too: it supplies that provider for "s3", "gcs", and "r2" secrets, and DuckDB's automatic mid-statement install of it can fail, so the package loads it up front instead. If you pre-install extensions (say, when baking a container image), include aws alongside httpfs. The azure extension provides its own credential chain.

On Windows neither the aws nor the azure extension is available for the duckdb R package, so provider = "credential_chain" and type = "azure" fail there; explicit keys for "s3", "gcs", and "r2" work.

Prefer provider = "credential_chain" over embedding long-lived keys in scripts. The secret's values are visible in the session via duckdb_secrets() (redacted) and travel with persistent storage unencrypted, so treat persistent = TRUE with the same care as a credentials file.

Value

Invisibly returns the secret's name (or NA_character_ for an unnamed secret).

See Also

attach_ducklake()

Other connection management: attach_ducklake(), detach_ducklake(), ducklake_extension_available(), get_ducklake_backend(), get_ducklake_connection(), get_ducklake_info(), install_ducklake(), set_ducklake_connection()

Examples

## Not run: 
# Explicit keys, scoped to one bucket
create_storage_secret(
  "s3",
  key_id = Sys.getenv("AWS_ACCESS_KEY_ID"),
  secret = Sys.getenv("AWS_SECRET_ACCESS_KEY"),
  region = "us-east-1",
  scope = "s3://my-trial-lake"
)

# Let the AWS credential chain find credentials
create_storage_secret("s3", provider = "credential_chain")

# Then attach a lake whose data lives on S3. The catalog file stays on
# local disk; lake_path only sets where the Parquet data goes.
attach_ducklake(
  "trial_lake",
  lake_path = "s3://my-trial-lake/data",
  catalog_connection_string = "trial_lake.ducklake"
)

## End(Not run)

Description

Create a DuckLake table

Usage

create_table(data_source, table_name, labels = TRUE)

Arguments

Value

Invisibly, NULL. Called for its side effect of creating the table in the lake.

See Also

Other table operations: add_data_files(), create_schema(), create_view(), drop_schema(), drop_view(), ducklake_exec(), get_ducklake_table(), get_metadata_table(), list_ducklake_tables(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("create_lake_")
dir.create(lake_dir)
attach_ducklake("create_lake", lake_path = lake_dir)

# From data.frame
create_table(mtcars, "cars")

# From a local file
csv_path <- tempfile(fileext = ".csv")
utils::write.csv(mtcars, csv_path, row.names = FALSE)
create_table(csv_path, "cars_from_csv")

# From a lazy table: the query runs inside DuckDB and writes straight
# into the lake, without collecting into R
get_ducklake_table("cars") |>
  dplyr::filter(cyl > 4) |>
  create_table("big_cars")

# From a URL -- needs network access and the httpfs extension
## Not run: 
create_table("https://example.com/data.csv", "remote_table")

## End(Not run)

unlink(csv_path)

detach_ducklake("create_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Stores a dplyr pipeline in the lake as a SQL view: the query runs fresh every time the view is read, so it always reflects the current data. Views live in the DuckLake catalog itself, which makes them a good home for shared business logic – a Python or SQL client of the same lake sees exactly the same definition.

Usage

create_view(.data, view_name, replace = TRUE)

Arguments

Details

Read a view back with get_ducklake_table(), which works for views and tables alike, and keep piping dplyr verbs onto it. Like tables, views are versioned: dropping or replacing one is a snapshot like any other.

DuckLake stores a replaced view as a new catalog entry and keys the comment to the entry, so ⁠CREATE OR REPLACE VIEW⁠ by itself drops the comment. create_view() reads the comment first and sets it again, in the same snapshot as the replacement, the way replace_table() carries a table's comments over. Reword it with set_table_comment(), or clear it with NULL.

A view kept in a schema of its own should read its tables by schema-qualified name: get_ducklake_table("main.cars"), not "cars". DuckDB resolves an unqualified name from the view's schema and the session's current database, so the view can fail to bind in a session where another database is current, and a view that shares its name with the table it reads (checks.cars over cars) reads itself and fails with a recursion error.

Value

Invisibly returns NULL.

See Also

drop_view(), list_ducklake_tables(), replace_table() to materialize a pipeline as data instead.

Other table operations: add_data_files(), create_schema(), create_table(), drop_schema(), drop_view(), ducklake_exec(), get_ducklake_table(), get_metadata_table(), list_ducklake_tables(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("view_lake_")
dir.create(lake_dir)
attach_ducklake("view_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

# Encapsulate filtering logic the whole team should share
get_ducklake_table("cars") |>
  dplyr::filter(cyl == 4) |>
  dplyr::select(mpg, cyl, gear) |>
  create_view("v_efficient_cars")

# Reads run the stored query against current data
get_ducklake_table("v_efficient_cars") |> dplyr::collect()

detach_ducklake("view_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Deletes files sitting in the lake's data path that are not tracked in the DuckLake metadata at all – for example, leftovers from a crashed write.

Usage

delete_orphaned_files(
  ducklake_name = NULL,
  older_than = NULL,
  cleanup_all = FALSE,
  dry_run = FALSE
)

Arguments

Details

This differs from cleanup_old_files(), which removes files that were tracked but are scheduled for deletion.

Always run with dry_run = TRUE first and check the file list. Anything in the data path that DuckLake does not recognise is fair game, and the comparison is by exact path string: a data path registered with an irregularity such as a doubled slash (as R's tempdir() produces on macOS) makes live files look orphaned, and deleting them breaks the lake.

Value

A data frame listing the deleted (or deletable) files.

See Also

cleanup_old_files(), checkpoint_ducklake()

Other maintenance: backup_ducklake(), checkpoint_ducklake(), cleanup_old_files(), expire_snapshots(), flush_inlined_data(), get_table_info(), list_ducklake_files(), merge_adjacent_files(), plot_table_files(), rewrite_data_files()

Examples

lake_dir <- tempfile("orphan_lake_")
dir.create(lake_dir)
attach_ducklake("orphan_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

# Always preview orphan deletion first
delete_orphaned_files(dry_run = TRUE, cleanup_all = TRUE)

detach_ducklake("orphan_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Detaches the DuckLake database but keeps the DuckDB connection alive by default. Use shutdown = TRUE to also close the connection and release file locks.

Usage

detach_ducklake(ducklake_name = NULL, shutdown = FALSE)

Arguments

Value

Invisibly, NULL. Called for its side effect of detaching the catalog from the package's DuckDB connection.

See Also

Other connection management: attach_ducklake(), create_storage_secret(), ducklake_extension_available(), get_ducklake_backend(), get_ducklake_connection(), get_ducklake_info(), install_ducklake(), set_ducklake_connection()

Examples

lake_dir <- tempfile("detach_lake_")
dir.create(lake_dir)
attach_ducklake("detach_lake", lake_path = lake_dir)
# ... do work ...
detach_ducklake("detach_lake")

# Full shutdown when completely done
attach_ducklake("detach_lake", lake_path = lake_dir)
detach_ducklake("detach_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Detaches a remote catalog previously attached with attach_quack(). The DuckDB connection itself stays alive.

Usage

detach_quack(quack_name = NULL)

Arguments

Value

Invisibly, NULL. Called for its side effect of detaching the remote catalog.

See Also

attach_quack()

Other quack: attach_quack(), install_quack(), quack_query(), quack_serve(), quack_stop()

Examples

## Not run: 
detach_quack("team")

## End(Not run)

Description

Removes a schema with ⁠DROP SCHEMA⁠. By default the schema must be empty; cascade = TRUE drops its tables and views with it. Like every change, the drop is a snapshot, so the tables stay reachable through time travel.

Usage

drop_schema(schema_name, cascade = FALSE, ducklake_name = NULL)

Arguments

Value

Invisibly, NULL.

See Also

create_schema()

Other table operations: add_data_files(), create_schema(), create_table(), create_view(), drop_view(), ducklake_exec(), get_ducklake_table(), get_metadata_table(), list_ducklake_tables(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("dropschema_lake_")
dir.create(lake_dir)
attach_ducklake("dropschema_lake", lake_path = lake_dir)

create_schema("scratch")
create_table(mtcars, "scratch.cars")
drop_schema("scratch", cascade = TRUE)

detach_ducklake("dropschema_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Removes a column in place with ⁠ALTER TABLE ... DROP COLUMN⁠. This is a metadata-only change: the column disappears from the current schema, but earlier snapshots still contain it and remain queryable through time travel.

Usage

drop_table_column(table_name, column_name)

Arguments

Value

Invisibly returns NULL.

See Also

add_table_column(), rename_table_column(), get_ducklake_table_version() to read snapshots that still have the column

Other schema evolution: add_table_column(), rename_ducklake_table(), rename_table_column(), set_column_not_null(), set_column_type()

Examples

lake_dir <- tempfile("dropcol_lake_")
dir.create(lake_dir)
attach_ducklake("dropcol_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

drop_table_column("cars", "carb")

detach_ducklake("dropcol_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Removes a view from the lake with ⁠DROP VIEW⁠. Only the stored query is dropped; the tables it reads are untouched.

Usage

drop_view(view_name)

Arguments

Value

Invisibly returns NULL.

See Also

create_view()

Other table operations: add_data_files(), create_schema(), create_table(), create_view(), drop_schema(), ducklake_exec(), get_ducklake_table(), get_metadata_table(), list_ducklake_tables(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("dropview_lake_")
dir.create(lake_dir)
attach_ducklake("dropview_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

get_ducklake_table("cars") |>
  dplyr::filter(cyl == 4) |>
  create_view("v_efficient_cars")

drop_view("v_efficient_cars")

detach_ducklake("dropview_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Execute DuckLake operations from dplyr queries

Usage

ducklake_exec(.data, table_name = NULL, .quiet = TRUE)

Arguments

Details

This function automatically detects the type of operation based on dplyr verbs:

• Filter-only queries on table_name generate DELETE operations (removes rows that DON'T match filter)

• Queries with mutate() on table_name generate UPDATE operations

• Reads from other tables generate INSERT operations, appending their result into table_name with columns matched by name; filter() and joins are fine here, since the whole query just feeds the INSERT

A plain read from table_name itself is refused, since inserting a table's own rows back into it would duplicate them. Pipelines that compile to a subquery over table_name (grouped filters, mutate() followed by filter()) are also refused rather than mistranslated. Use show_ducklake_query() to preview the generated SQL without running it.

Value

The result from db_execute()

See Also

Other table operations: add_data_files(), create_schema(), create_table(), create_view(), drop_schema(), drop_view(), get_ducklake_table(), get_metadata_table(), list_ducklake_tables(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("exec_lake_")
dir.create(lake_dir)
attach_ducklake("exec_lake", lake_path = lake_dir)
create_table(data.frame(id = 1:3, status = "pending"), "jobs")

# Update specific rows (table name inferred)
get_ducklake_table("jobs") |>
  dplyr::filter(id == 1) |>
  dplyr::mutate(status = "updated") |>
  ducklake_exec()

# Delete rows matching a filter
get_ducklake_table("jobs") |>
  dplyr::filter(status == "pending") |>
  ducklake_exec()

# Or provide the table name explicitly
get_ducklake_table("jobs") |>
  dplyr::mutate(status = "done") |>
  ducklake_exec("jobs")

detach_ducklake("exec_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Reports whether the ducklake DuckDB extension is already installed and can be loaded. The check opens a throwaway in-memory DuckDB connection, asks DuckDB's extension catalog whether the extension is installed, and loads it only if so. Reading the catalog cannot download anything, and loading an installed extension does not reach the network, so the probe never downloads and never writes to the extension directory. The connection is opened on the directory duckdb would resolve for a new connection, so an existing ⁠~/.duckdb⁠ is found, and the offer duckdb makes to create one is not triggered. The package's own connection is left untouched.

Usage

ducklake_extension_available()

Details

Use it to guard code that should degrade gracefully when the extension is absent: examples, vignettes, tests, and conditional branches in scripts. The package's own examples are gated on it. There is no need to call it before attach_ducklake(), which installs the extension the first time it is needed; install_ducklake() downloads it ahead of time.

A FALSE on a machine where the extension was installed earlier usually means it went into a temporary directory. duckdb keeps extensions under a "home" directory: the duckdb.home option or DUCKDB_R_HOME when set, otherwise ⁠~/.duckdb⁠ when it exists (in an interactive session duckdb offers to create it the first time it connects), otherwise a per-session temporary directory. See ?duckdb::duckdb_storage.

The result is cached for the rest of the session, since spinning up DuckDB to re-answer the same question is wasteful when dozens of examples ask it. install_ducklake() and a first-use install by attach_ducklake() clear the cache, so a FALSE answer becomes TRUE as soon as the extension is there.

Value

A single logical: TRUE when the extension loads, FALSE otherwise (including when a connection cannot be opened).

See Also

Other connection management: attach_ducklake(), create_storage_secret(), detach_ducklake(), get_ducklake_backend(), get_ducklake_connection(), get_ducklake_info(), install_ducklake(), set_ducklake_connection()

Examples

if (ducklake_extension_available()) {
  message("ducklake extension is ready to use")
} else {
  message("attach_ducklake() will download it on first use")
}

Description

Removes old snapshots from the DuckLake catalog. Expiring snapshots gives up the ability to time-travel to them, and schedules the data files that only they referenced for deletion.

Usage

expire_snapshots(
  ducklake_name = NULL,
  older_than = NULL,
  versions = NULL,
  dry_run = FALSE
)

Arguments

Details

Expiring snapshots does not delete any files by itself: files that are no longer referenced are merely scheduled for deletion. Run cleanup_old_files() afterwards to reclaim the storage, or let checkpoint_ducklake() handle both steps.

The most recent snapshot can never be expired.

For a standing policy, set set_ducklake_option("expire_older_than", "90 days") once; checkpoint_ducklake() then expires eligible snapshots on every run. Without that option a checkpoint expires nothing.

Value

A data frame listing the expired (or, with dry_run = TRUE, expirable) snapshots.

See Also

cleanup_old_files(), checkpoint_ducklake(), list_table_snapshots()

Other maintenance: backup_ducklake(), checkpoint_ducklake(), cleanup_old_files(), delete_orphaned_files(), flush_inlined_data(), get_table_info(), list_ducklake_files(), merge_adjacent_files(), plot_table_files(), rewrite_data_files()

Examples

lake_dir <- tempfile("expire_lake_")
dir.create(lake_dir)
attach_ducklake("expire_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

rows_insert(
  get_ducklake_table("cars"),
  data.frame(mpg = 30, cyl = 4),
  by = "mpg"
)

# Preview what a one-week retention policy would remove
expire_snapshots(older_than = Sys.time() - 7 * 24 * 60 * 60, dry_run = TRUE)

# Expire everything older than now, then reclaim the storage
expire_snapshots(older_than = Sys.time())
cleanup_old_files(cleanup_all = TRUE)

detach_ducklake("expire_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Materialises data that has been stored inline in the catalog database into Parquet files on the data path. This includes both inlined inserts and inlined deletions.

Usage

flush_inlined_data(ducklake_name = NULL, table_name = NULL, schema_name = NULL)

Arguments

Details

Flushing writes inlined rows to consolidated Parquet files and cleans up the inlined data tables. Time-travel information is preserved: flushed rows that had been deleted will produce a partial deletion file with snapshot metadata.

Tables with auto_compact set to FALSE are skipped when flushing an entire lake or schema. Use an explicit table_name to flush those tables.

If a table has a sort order defined, the flushed Parquet file will be sorted by those keys.

Value

A data frame with columns schema_name, table_name, and rows_flushed. Tables with no inlined data are omitted.

See Also

set_inlining_row_limit(), checkpoint_ducklake()

Other data inlining: checkpoint_ducklake(), get_inlining_row_limit(), set_inlining_row_limit()

Other maintenance: backup_ducklake(), checkpoint_ducklake(), cleanup_old_files(), delete_orphaned_files(), expire_snapshots(), get_table_info(), list_ducklake_files(), merge_adjacent_files(), plot_table_files(), rewrite_data_files()

Examples

lake_dir <- tempfile("flush_lake_")
dir.create(lake_dir)
attach_ducklake("flush_lake", lake_path = lake_dir,
                data_inlining_row_limit = 10)
create_table(head(mtcars, 3), "cars")

# Flush everything
flush_inlined_data()

# Flush a specific table
flush_inlined_data(table_name = "cars")

detach_ducklake("flush_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Get the catalog backend type of an attached lake

Usage

get_ducklake_backend(ducklake_name = NULL)

Arguments

Value

One of "duckdb", "postgres", "sqlite", or "mysql". Defaults to "duckdb" when the lake is unknown.

See Also

Other connection management: attach_ducklake(), create_storage_secret(), detach_ducklake(), ducklake_extension_available(), get_ducklake_connection(), get_ducklake_info(), install_ducklake(), set_ducklake_connection()

Examples

lake_dir <- tempfile("backend_lake_")
dir.create(lake_dir)
attach_ducklake("backend_lake", lake_path = lake_dir)

get_ducklake_backend()

# With several lakes attached, look one up by name
get_ducklake_backend("backend_lake")

detach_ducklake("backend_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Returns the DuckDB connection that all ducklake functions share. The first call creates the connection automatically, so you never need to set one up yourself. If you want ducklake to use a connection you have created (for example, one shared with other tools), register it first with set_ducklake_connection().

Usage

get_ducklake_connection()

Details

The automatically created connection is backed by a temporary database file (not ⁠:memory:⁠) with a spill directory configured, so larger-than-memory operations work out of the box. It is closed automatically when the R session ends.

Value

A DuckDB connection object (a duckdb_connection).

See Also

Other connection management: attach_ducklake(), create_storage_secret(), detach_ducklake(), ducklake_extension_available(), get_ducklake_backend(), get_ducklake_info(), install_ducklake(), set_ducklake_connection()

Examples

conn <- get_ducklake_connection()
DBI::dbGetQuery(conn, "SELECT version()")

detach_ducklake(shutdown = TRUE)

Description

One row of facts about a lake: which catalog backend it uses and where the catalog and data live, the DuckLake format version the catalog was written in, the extension version reading it, whether its Parquet files are encrypted, and the current snapshot id. Handy at the top of a report or a pipeline log, and the first thing to look at when a lake behaves unexpectedly.

Usage

get_ducklake_info(ducklake_name = NULL)

Arguments

Value

A one-row data frame with ducklake_name, backend, catalog (the catalog file or connection string), data_path, format_version, extension_version, encrypted, and current_snapshot.

See Also

get_ducklake_options() for the options set on the lake, list_ducklake_tables() for what it holds.

Other connection management: attach_ducklake(), create_storage_secret(), detach_ducklake(), ducklake_extension_available(), get_ducklake_backend(), get_ducklake_connection(), install_ducklake(), set_ducklake_connection()

Examples

lake_dir <- tempfile("info_lake_")
dir.create(lake_dir)
attach_ducklake("info_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

get_ducklake_info()

detach_ducklake("info_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Reads the configuration options recorded in the metadata catalog, including their scope (global, schema, or table).

Usage

get_ducklake_options(ducklake_name = NULL)

Arguments

Value

A data frame with one row per option setting, including option_name, value, scope (GLOBAL, SCHEMA, or TABLE), and scope_entry. Options left at their defaults are not listed.

See Also

set_ducklake_option()

Other options: set_ducklake_option()

Examples

lake_dir <- tempfile("getopt_lake_")
dir.create(lake_dir)
attach_ducklake("getopt_lake", lake_path = lake_dir)

set_ducklake_option("parquet_compression", "zstd")
get_ducklake_options()

detach_ducklake("getopt_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Returns a lazy reference to a table in the attached DuckLake. Like dplyr::tbl(), nothing is read until you collect(): build up your filter()/mutate()/summarise() pipeline first and DuckDB executes it as a single query, only pulling the rows you asked for into R.

Usage

get_ducklake_table(tbl_name)

Arguments

Value

A lazy table (class tbl_ducklake) that works with dplyr verbs. The table name is stored in the ducklake_table_name attribute.

See Also

create_table() to create tables, get_ducklake_table_asof() and get_ducklake_table_version() for time-travel reads.

Other table operations: add_data_files(), create_schema(), create_table(), create_view(), drop_schema(), drop_view(), ducklake_exec(), get_metadata_table(), list_ducklake_tables(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("cars_lake_")
dir.create(lake_dir)
attach_ducklake("cars_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

# Query lazily with dplyr, then collect
get_ducklake_table("cars") |>
  dplyr::filter(cyl > 4) |>
  dplyr::summarise(avg_mpg = mean(mpg), .by = cyl) |>
  dplyr::collect()

detach_ducklake("cars_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Retrieves data from a DuckLake table as it existed at a specific point in time using DuckLake's AT (TIMESTAMP => ...) syntax.

Usage

get_ducklake_table_asof(table_name, timestamp, conn = NULL)

Arguments

Details

DuckLake supports time-travel queries, allowing you to query historical data as it existed at a specific timestamp. This uses the syntax: SELECT * FROM table AT (TIMESTAMP => 'timestamp')

This is useful for:

• Auditing changes over time

• Recovering accidentally deleted or modified data

• Comparing data states across different time points

• Regulatory compliance and data lineage documentation

The timestamp must be within the range of available snapshots for the table. Use list_table_snapshots() to see available snapshot times.

Important: When querying at a snapshot's exact timestamp, you may need to add a small time buffer (e.g., +1 second) to ensure the snapshot is found. This is because the time-travel query looks for snapshots created at or before the specified timestamp.

Value

A lazy table (class tbl_ducklake) that works with dplyr verbs. Like get_ducklake_table(), collecting it restores stored column labels: the ones in force at that snapshot, which a later set_column_comments() may have changed. A table written from the read (create_table(), replace_table()) takes the present-day comments, as restore_table_version() does.

See Also

Other time travel: get_ducklake_table_version(), get_table_changes(), list_table_snapshots(), plot_snapshots(), plot_table_changes(), restore_table_version(), view_table_changes()

Examples

lake_dir <- tempfile("asof_lake_")
dir.create(lake_dir)
attach_ducklake("asof_lake", lake_path = lake_dir)
create_table(data.frame(id = 1:3, amount = c(10, 20, 30)), "orders")

rows_insert(
  get_ducklake_table("orders"),
  data.frame(id = 4L, amount = 40),
  by = "id"
)

# Query data at a specific snapshot time
snapshots <- list_table_snapshots("orders")
# Add 1 second to ensure the snapshot is found
get_ducklake_table_asof("orders", snapshots$snapshot_time[1] + 1) |>
  dplyr::summarise(total = sum(amount)) |>
  dplyr::collect()

detach_ducklake("asof_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Retrieves data from a DuckLake table at a specific snapshot ID using DuckLake's AT (VERSION => ...) syntax.

Usage

get_ducklake_table_version(table_name, version, conn = NULL)

Arguments

Details

This function allows you to query a specific snapshot of a table using its snapshot_id. This uses the syntax: SELECT * FROM table AT (VERSION => snapshot_id)

Each time you create or modify a table within a transaction, DuckLake creates a new snapshot with a unique snapshot_id. Note that snapshot_id and schema_version are typically the same value - both represent the snapshot identifier.

Use list_table_snapshots(table_name) to see all available snapshots and their IDs.

Value

A lazy table (class tbl_ducklake) that works with dplyr verbs. Like get_ducklake_table(), collecting it restores stored column labels: the ones in force at that snapshot, which a later set_column_comments() may have changed. A table written from the read (create_table(), replace_table()) takes the present-day comments, as restore_table_version() does.

See Also

Other time travel: get_ducklake_table_asof(), get_table_changes(), list_table_snapshots(), plot_snapshots(), plot_table_changes(), restore_table_version(), view_table_changes()

Examples

lake_dir <- tempfile("version_lake_")
dir.create(lake_dir)
attach_ducklake("version_lake", lake_path = lake_dir)
create_table(data.frame(id = 1:3, amount = c(10, 20, 30)), "orders")

rows_insert(
  get_ducklake_table("orders"),
  data.frame(id = 4L, amount = 40),
  by = "id"
)

# Get available snapshots
snapshots <- list_table_snapshots("orders")

# Query the first snapshot version
get_ducklake_table_version("orders", snapshots$snapshot_id[1]) |>
  dplyr::collect()

detach_ducklake("version_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Returns the effective data inlining row limit. When no table- or schema-level override is configured, the global DuckDB default is returned.

Usage

get_inlining_row_limit(
  table_name = NULL,
  schema_name = NULL,
  ducklake_name = NULL
)

Arguments

Value

An integer: the effective inlining row limit.

See Also

set_inlining_row_limit()

Other data inlining: checkpoint_ducklake(), flush_inlined_data(), set_inlining_row_limit()

Examples

lake_dir <- tempfile("inline_get_lake_")
dir.create(lake_dir)
attach_ducklake("inline_get_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

set_inlining_row_limit(100, table_name = "cars")

# Global default
get_inlining_row_limit()

# Table-specific limit
get_inlining_row_limit(table_name = "cars")

detach_ducklake("inline_get_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

DuckLake keeps all of its bookkeeping – snapshots, table schemas, data file locations, and more – in ordinary tables inside the catalog database. This function gives you a lazy reference to any of them, which is handy for auditing and for understanding how your lake evolves.

Usage

get_metadata_table(tbl_name, ducklake_name = NULL)

Arguments

Details

Commonly useful tables include ducklake_snapshot (one row per snapshot), ducklake_table (registered tables), and ducklake_data_file (the Parquet files backing each table). The full list is in the DuckLake specification.

Value

A lazy table that works with dplyr verbs.

See Also

list_table_snapshots() for a friendlier view of snapshot history.

Other table operations: add_data_files(), create_schema(), create_table(), create_view(), drop_schema(), drop_view(), ducklake_exec(), get_ducklake_table(), list_ducklake_tables(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("meta_lake_")
dir.create(lake_dir)
attach_ducklake("meta_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

# Every snapshot ever taken
get_metadata_table("ducklake_snapshot") |> dplyr::collect()

# Which Parquet files back the lake?
get_metadata_table("ducklake_data_file") |>
  dplyr::select(data_file_id, path) |>
  dplyr::collect()

detach_ducklake("meta_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Returns the exact rows that were inserted, deleted, or updated in a table between two snapshots (inclusive), using DuckLake's data change feed. Useful for auditing and for change-data-capture style pipelines.

Usage

get_table_changes(
  table_name,
  start = NULL,
  end = NULL,
  ducklake_name = NULL,
  conn = NULL
)

Arguments

Details

Both bounds must be given or both left NULL. When given, they must be of the same kind: two snapshot ids or two timestamps. POSIXct bounds are converted to UTC, matching the snapshot times DuckLake records; character bounds are passed through as-is and must already be in UTC. Bounds before the lake's first snapshot are rejected by DuckLake, so prefer snapshot times from list_table_snapshots(). Updates appear as two rows – the row as it looked before the change (update_preimage) and after it (update_postimage).

The feed is read with the table's schema as of the end snapshot: a column added inside the range is NA in rows from before it existed, and a renamed column appears under its newer name. A table rebuilt by replace_table() or restore_table_version() gets a new table id, and the feed covers the current id only, so changes from before the rebuild are not in it.

This wraps DuckLake's table_changes() function.

Value

A dplyr lazy query object (tbl_lazy). In addition to the table's own columns it carries snapshot_id (the snapshot that made the change), rowid (the changed row's identifier), and change_type ("insert", "delete", "update_preimage", or "update_postimage"). It also carries a ducklake_changes attribute naming the table, the lake, and the requested range, which view_table_changes() reads; the attribute survives dplyr verbs on the lazy table and is dropped by collect().

See Also

view_table_changes() to browse the feed interactively, plot_table_changes(), list_table_snapshots(), get_ducklake_table_version(), get_ducklake_table_asof()

Other time travel: get_ducklake_table_asof(), get_ducklake_table_version(), list_table_snapshots(), plot_snapshots(), plot_table_changes(), restore_table_version(), view_table_changes()

Examples

lake_dir <- tempfile("changes_lake_")
dir.create(lake_dir)
attach_ducklake("changes_lake", lake_path = lake_dir)
create_table(data.frame(id = 1:3, amount = c(10, 20, 30)), "orders")

rows_delete(
  get_ducklake_table("orders"),
  data.frame(id = 1L),
  by = "id"
)
snaps <- list_table_snapshots("orders")

# Everything that ever happened to the table
get_table_changes("orders") |> dplyr::collect()

# What changed in the most recent snapshot?
latest <- max(snaps$snapshot_id)
get_table_changes("orders", latest, latest) |> dplyr::collect()

# Every change across the table's full history, by timestamp
get_table_changes(
  "orders",
  min(snaps$snapshot_time), max(snaps$snapshot_time) + 1
) |>
  dplyr::filter(change_type == "delete") |>
  dplyr::collect()

detach_ducklake("changes_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Returns the current comments stored in the lake – via set_table_comment(), set_column_comments(), create_table()'s label sync, or any other client – as a tidy data frame.

Usage

get_table_comments(table_name = NULL, ducklake_name = NULL)

Arguments

Details

The comments are read through DuckDB's catalog functions, so they match what the session sees. On a lake attached with snapshot_version or snapshot_time they are the comments as of that snapshot, and inside an open transaction they include the ones it has set and not yet committed.

Value

A data frame with one row per comment: object_type ("table", "view", or "column"), schema_name, table_name, column_name (NA for tables and views), and comment. Zero rows when nothing is commented.

See Also

get_metadata_table() for the raw ducklake_tag and ducklake_column_tag catalog tables, which keep every version of a comment.

Other table documentation: set_column_comments(), set_table_comment()

Examples

lake_dir <- tempfile("readcomment_lake_")
dir.create(lake_dir)
attach_ducklake("readcomment_lake", lake_path = lake_dir)
create_table(mtcars, "cars")
set_table_comment("cars", "Motor Trend road tests")
set_column_comments("cars", mpg = "Miles per US gallon")

# Everything documented in the lake
get_table_comments()

# One table's documentation
get_table_comments("cars")

detach_ducklake("readcomment_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Returns per-table storage statistics from the DuckLake catalog: how many Parquet data files each table has and their total size, plus the same for delete files.

Usage

get_table_info(table_name = NULL, ducklake_name = NULL, conn = NULL)

Arguments

Details

These statistics are the raw material for storage maintenance decisions: many small files are worth compacting with merge_adjacent_files(), and a growing delete-file share is a sign to run rewrite_data_files(). Rows that are still inlined in the catalog (see set_inlining_row_limit()) are not in any Parquet file yet, so small recent writes may not show up in the counts until flush_inlined_data() writes them out.

This wraps DuckLake's ducklake_table_info() function.

Value

A data frame with one row per table and columns table_name, schema_id, table_id, table_uuid, file_count, file_size_bytes, delete_file_count, delete_file_size_bytes, and schema_name.

See Also

plot_table_files(), merge_adjacent_files(), rewrite_data_files()

Other maintenance: backup_ducklake(), checkpoint_ducklake(), cleanup_old_files(), delete_orphaned_files(), expire_snapshots(), flush_inlined_data(), list_ducklake_files(), merge_adjacent_files(), plot_table_files(), rewrite_data_files()

Examples

lake_dir <- tempfile("info_lake_")
dir.create(lake_dir)
attach_ducklake("info_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

# File statistics for every table in the lake
get_table_info()

# Just one table
get_table_info("cars")

detach_ducklake("info_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Reads the current partitioning keys from the DuckLake metadata catalog.

Usage

get_table_partitions(table_name = NULL, ducklake_name = NULL)

Arguments

Value

A data frame with one row per partition key: schema_name, table_name, partition_key_index, column_name, and transform (e.g. "identity", "year", or "bucket(4)"). Zero rows when nothing is partitioned.

See Also

set_table_partitioning(), get_metadata_table()

Other partitioning: reset_table_partitioning(), set_table_partitioning()

Examples

lake_dir <- tempfile("getpart_lake_")
dir.create(lake_dir)
attach_ducklake("getpart_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

set_table_partitioning("cars", "cyl")

# All partitioned tables in the lake
get_table_partitions()

# Keys for one table
get_table_partitions("cars")

detach_ducklake("getpart_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Reads the current sort order from the DuckLake metadata catalog, the counterpart of get_table_partitions() for set_table_sorting().

Usage

get_table_sorting(table_name = NULL, ducklake_name = NULL)

Arguments

Value

A data frame with one row per sort key: schema_name, table_name, sort_key_index, expression, sort_direction ("ASC" or "DESC"), and null_order ("NULLS_FIRST" or "NULLS_LAST"). Zero rows when nothing is sorted.

See Also

set_table_sorting(), get_table_partitions()

Other sorting: reset_table_sorting(), set_table_sorting()

Examples

lake_dir <- tempfile("getsort_lake_")
dir.create(lake_dir)
attach_ducklake("getsort_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

set_table_sorting("cars", c("cyl ASC", "mpg DESC"))

# All sorted tables in the lake
get_table_sorting()

# Keys for one table
get_table_sorting("cars")

detach_ducklake("getsort_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

attach_ducklake() installs the ducklake DuckDB extension the first time it is needed, so most sessions never call this function. It downloads the extension now, and optionally the extensions for the other catalog backends (postgres, sqlite, mysql), for the cases where the download has to happen before a lake is attached: baking a container image, a CI setup step, or a machine that is offline at attach time.

Usage

install_ducklake(backend = NULL)

Arguments

Details

Where the extension lands is decided by the duckdb R package (see ?duckdb::duckdb_storage). Extensions live under a "home" directory: the duckdb.home option or DUCKDB_R_HOME when set, otherwise ⁠~/.duckdb⁠ when it exists (in an interactive session duckdb offers to create it the first time it connects), otherwise a per-session temporary directory. With the temporary directory the extension is gone when R exits and the next session downloads it again. install_ducklake() reports the directory it installed into and says so when that directory is temporary. For scripts and CI, set DUCKDB_R_HOME in ⁠~/.Renviron⁠ or the job's environment to a directory that survives the session.

Value

Invisibly, NULL. Called for its side effect of installing the DuckDB extensions into the local extension cache.

Note

On Windows the postgres and mysql extensions are not available (MinGW toolchain). See attach_ducklake() for details.

See Also

Other connection management: attach_ducklake(), create_storage_secret(), detach_ducklake(), ducklake_extension_available(), get_ducklake_backend(), get_ducklake_connection(), get_ducklake_info(), set_ducklake_connection()

Examples

## Not run: 
install_ducklake()
install_ducklake(backend = "postgres")
install_ducklake(backend = c("postgres", "sqlite", "mysql"))

## End(Not run)

Description

Installs the Quack DuckDB extension, which provides the ⁠quack:⁠ client-server protocol. Quack is a core extension from DuckDB 1.5.3 onward, so it is autoloaded the first time it is used. Installing it ahead of time is useful on machines that have no internet access at query time.

Usage

install_quack(load = TRUE)

Arguments

Value

Invisibly, NULL. Called for its side effect of installing the quack DuckDB extension into the local extension cache.

See Also

attach_quack(), quack_serve()

Other quack: attach_quack(), detach_quack(), quack_query(), quack_serve(), quack_stop()

Examples

## Not run: 
install_quack()

## End(Not run)

Description

Returns the Parquet data files (and any delete files) that make up a table, optionally as of a past snapshot.

Usage

list_ducklake_files(
  table_name,
  schema_name = NULL,
  snapshot_version = NULL,
  snapshot_time = NULL,
  ducklake_name = NULL
)

Arguments

Details

Wraps ducklake_list_files(). For per-table file counts and sizes across the whole lake, see get_table_info(); for a picture of storage layout, see plot_table_files().

Value

A data frame with one row per data file, including data_file, data_file_size_bytes, and the associated delete_file columns (NA when a file has no deletes).

See Also

add_data_files(), get_table_info()

Other maintenance: backup_ducklake(), checkpoint_ducklake(), cleanup_old_files(), delete_orphaned_files(), expire_snapshots(), flush_inlined_data(), get_table_info(), merge_adjacent_files(), plot_table_files(), rewrite_data_files()

Examples

lake_dir <- tempfile("listfiles_lake_")
dir.create(lake_dir)
attach_ducklake("listfiles_lake", lake_path = lake_dir)
create_table(mtcars, "cars")
rows_insert(
  get_ducklake_table("cars"),
  data.frame(mpg = 30, cyl = 4),
  by = "mpg"
)

# Files behind a table right now
list_ducklake_files("cars")

# Files as of an earlier snapshot
first <- min(list_table_snapshots("cars")$snapshot_id)
list_ducklake_files("cars", snapshot_version = first)

detach_ducklake("listfiles_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Returns every table and view in the lake as a tidy data frame – the quick answer to "what is in here?".

Usage

list_ducklake_tables(ducklake_name = NULL)

Arguments

Value

A data frame with one row per object: schema_name, table_name, and type ("table" or "view").

See Also

get_table_info() for per-table file statistics, get_table_comments() for stored documentation, get_ducklake_table() to read any listed object.

Other table operations: add_data_files(), create_schema(), create_table(), create_view(), drop_schema(), drop_view(), ducklake_exec(), get_ducklake_table(), get_metadata_table(), replace_table(), show_ducklake_query()

Examples

lake_dir <- tempfile("list_lake_")
dir.create(lake_dir)
attach_ducklake("list_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

list_ducklake_tables()

detach_ducklake("list_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Retrieves information about available snapshots/versions for a table.

Usage

list_table_snapshots(table_name = NULL, ducklake_name = NULL, conn = NULL)

Arguments

Details

This function queries the snapshot history of a table, showing available versions and their timestamps. This is useful for understanding what historical versions are available for time-travel queries.

A table's snapshots are matched by its name and by every table id the name has had in its schema, so the history stays complete across replace_table() and restore_table_version(), which give the table a new id.

Value

A data frame with snapshot information (version, timestamp, etc.)

See Also

Other time travel: get_ducklake_table_asof(), get_ducklake_table_version(), get_table_changes(), plot_snapshots(), plot_table_changes(), restore_table_version(), view_table_changes()

Examples

lake_dir <- tempfile("snaplist_lake_")
dir.create(lake_dir)
attach_ducklake("snaplist_lake", lake_path = lake_dir)
create_table(data.frame(id = 1:3, amount = c(10, 20, 30)), "orders")

# List all snapshots for a table
list_table_snapshots("orders")

detach_ducklake("snaplist_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Compacts small adjacent Parquet files into larger ones. Frequent small inserts each write their own file; merging keeps file counts down and scans fast.

Usage

merge_adjacent_files(
  ducklake_name = NULL,
  table_name = NULL,
  schema_name = NULL,
  max_compacted_files = NULL,
  min_file_size = NULL,
  max_file_size = NULL
)

Arguments

Details

Merging does not delete the original small files – they may still be referenced by older snapshots. They are scheduled for deletion once no snapshot references them; run cleanup_old_files() to remove them.

Value

A data frame with one row per output file (columns schema_name, table_name, files_processed, files_created).

See Also

cleanup_old_files(), checkpoint_ducklake(), flush_inlined_data()

Other maintenance: backup_ducklake(), checkpoint_ducklake(), cleanup_old_files(), delete_orphaned_files(), expire_snapshots(), flush_inlined_data(), get_table_info(), list_ducklake_files(), plot_table_files(), rewrite_data_files()

Examples

lake_dir <- tempfile("merge_files_lake_")
dir.create(lake_dir)
attach_ducklake("merge_files_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

rows_insert(
  get_ducklake_table("cars"),
  data.frame(mpg = 30, cyl = 4),
  by = "mpg"
)

# Compact the whole lake
merge_adjacent_files()

# Compact one table, only touching files under 10 MB
merge_adjacent_files(table_name = "cars", max_file_size = 10e6)

detach_ducklake("merge_files_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Runs a SQL ⁠MERGE INTO⁠ statement: rows of target are matched against rows of source on the by columns, then updated, deleted, or left alone, while unmatched source rows can be inserted and target rows missing from the source can be removed. The whole operation is atomic – one snapshot, with row lineage preserved in the change feed.

Usage

merge_into(
  target,
  source,
  by,
  when_matched = c("update", "delete", "nothing"),
  when_not_matched = c("insert", "nothing"),
  matched_condition = NULL,
  not_matched_condition = NULL,
  delete_missing = FALSE,
  .quiet = TRUE
)

Arguments

Details

This is not a join. Joins (left_join() and friends) read from the lake and build a new result without touching either table; merge_into() changes the rows of target in place. For the common update-or-insert case, reach for rows_upsert() first – merge_into() is for the cases it cannot express: conditional clauses, deletes of matched rows, and synchronizing a table with a staging source.

Choosing how to change a table

• To look up or combine data for analysis, use dplyr joins (left_join() and friends). Joins read from the lake and build a new result; they never modify a lake table.

• To append, correct, or remove specific rows, use rows_insert(), rows_update(), or rows_delete(). Each call is a single SQL statement against the existing table – no data leaves the database, and with data inlining enabled (DuckLake's default) small changes land in the catalog without creating tiny Parquet files.

• To update rows that exist and insert the ones that don't in one atomic statement, use rows_upsert().

• For conditional merge logic or deletes driven by a staging table, use merge_into().

• To change a table's shape without touching its data – add, drop, or rename columns, widen a type – use the schema evolution family (add_table_column() and friends): metadata-only changes that rewrite nothing.

• For bulk transformations that touch most rows, use replace_table(). It rewrites the whole table inside DuckDB – heavier than the row operations, and it resets the row lineage that the in-place operations preserve in the change feed.

A tempting alternative – joining the source to the table and calling replace_table() on the result – rewrites every row and records the change as a wholesale replacement. merge_into() touches only the affected rows, so get_table_changes() afterward shows exactly which rows were inserted, updated, or deleted.

Conditions are SQL

matched_condition and not_matched_condition are raw SQL expressions, not dplyr code. They are pasted into the statement as written (only a ⁠;⁠ is rejected), so build them from trusted input only.

DuckLake-specific behavior

• Update sets the columns present in both tables (minus by); inserted rows receive the column default (NULL when none is defined) in target columns the source lacks.

• ⁠MERGE ... RETURNING⁠ is not implemented by DuckLake.

• DuckLake currently supports one update/delete action per MERGE statement. delete_missing = TRUE together with a when_matched action therefore runs as a MERGE plus a DELETE of the unmatched rows, wrapped in one transaction (a single snapshot). When you already opened a transaction, both statements simply join it.

• When several source rows share a by key, matched updates apply in an unspecified order; keep source keys unique.

Value

The number of affected rows, invisibly.

See Also

rows_upsert() for the plain update-or-insert case; get_table_changes() to inspect what a merge did.

Other row operations: rows_delete(), rows_insert(), rows_update(), rows_upsert()

Examples

lake_dir <- tempfile("merge_lake_")
dir.create(lake_dir)
attach_ducklake("merge_lake", lake_path = lake_dir)
create_table(data.frame(sls_id = 1:3, sls_amt = c(10, 20, 30)), "sales")

# Update only when the source amount is higher; insert new ids
new_sales <- data.frame(sls_id = c(2L, 4L), sls_amt = c(25, 40))
merge_into(
  get_ducklake_table("sales"), new_sales, by = "sls_id",
  matched_condition = "source.sls_amt > target.sls_amt"
)

# Synchronize to a staging table: upsert + drop rows gone from the source
staging_sales <- data.frame(sls_id = c(1L, 2L), sls_amt = c(11, 26))
merge_into("sales", staging_sales, by = "sls_id", delete_missing = TRUE)

# Remove rows flagged in the source
withdrawn <- data.frame(sls_id = 1L, sls_amt = 11)
merge_into(
  "sales", withdrawn, by = "sls_id",
  when_matched = "delete", when_not_matched = "nothing"
)

detach_ducklake("merge_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Draws snapshot history in one of two layouts. With a table_name, a commit-log timeline: one row per snapshot (newest at top) on an ordinal spine, with the timestamp, author, and commit message as aligned text and long idle stretches marked inline (e.g. "103 days later") instead of stretching an axis. Without a table_name, a lake-wide swimlane: one row per table, one point per snapshot, evenly spaced in snapshot order, so active and stale tables read at a glance.

Usage

plot_snapshots(table_name = NULL, ducklake_name = NULL, conn = NULL)

Arguments

Details

Requires the ggplot2 package (listed in Suggests). Snapshot data comes from list_table_snapshots(); each snapshot is classified from the entries of its changes column that name the table into one of: created, schema change, data change, maintenance, or other. A transaction that updates one table in place and rebuilds another therefore shows a data change on the first and a creation on the second. Authors and commit messages appear where they were recorded (see set_snapshot_metadata() and commit_transaction()).

Both layouts position snapshots by order rather than by clock time, so a history with months of silence between bursts of activity stays readable. In the swimlane, snapshots that touch no table (like the initial schema creation) appear in a (lake) lane, and the x axis labels show each snapshot's date. Lanes carry schema-qualified names when the lake keeps tables outside main, so bronze.dm and silver.dm stay apart.

Value

A ggplot object, which can be further customized with ggplot2 functions

See Also

Other time travel: get_ducklake_table_asof(), get_ducklake_table_version(), get_table_changes(), list_table_snapshots(), plot_table_changes(), restore_table_version(), view_table_changes()

Examples

lake_dir <- tempfile("plotsnap_lake_")
dir.create(lake_dir)
attach_ducklake("plotsnap_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

create_table(iris, "flowers")

# Commit-log timeline of one table's history
plot_snapshots("cars")

# Swimlane of every table in the lake
plot_snapshots()

# Customize the result like any ggplot
plot_snapshots("cars") +
  ggplot2::labs(title = "Audit trail")

detach_ducklake("plotsnap_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Draws a table's change volume as a diverging bar chart: one bar per snapshot, with rows inserted or updated above the axis and rows deleted below it. A companion to plot_snapshots(), which shows when and what kind of changes happened; this shows how much changed each time.

Usage

plot_table_changes(table_name, ducklake_name = NULL, conn = NULL)

Arguments

Details

Requires the ggplot2 package (listed in Suggests). Row counts come from DuckLake's data change feed via get_table_changes(). An update appears in the feed as a before and an after image of the row; it is counted once here. Snapshots that touched the table without changing rows (a schema change, for example) keep their slot on the axis with no bar.

Value

A ggplot object, which can be further customized with ggplot2 functions

See Also

Other time travel: get_ducklake_table_asof(), get_ducklake_table_version(), get_table_changes(), list_table_snapshots(), plot_snapshots(), restore_table_version(), view_table_changes()

Examples

lake_dir <- tempfile("plotchg_lake_")
dir.create(lake_dir)
attach_ducklake("plotchg_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

rows_update(
  get_ducklake_table("cars"),
  data.frame(mpg = 21, gear = 5),
  by = "mpg"
)

# Rows inserted, updated, and deleted per snapshot
plot_table_changes("cars")

# Customize the result like any ggplot
plot_table_changes("cars") +
  ggplot2::theme_classic()

detach_ducklake("plotchg_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Draws each table's storage footprint as a horizontal bar: total bytes of Parquet data files, with delete files stacked in a second color, and a label giving the file count and average file size. Useful for spotting fragmentation – many small files – before it slows scans down.

Usage

plot_table_files(ducklake_name = NULL, conn = NULL)

Arguments

Details

Requires the ggplot2 package (listed in Suggests). File statistics come from get_table_info(). Tables whose rows are still inlined in the catalog have no data files yet and show an empty bar; run flush_inlined_data() to write them out. Many small files can be compacted with merge_adjacent_files(), and a large delete-file share is a sign to run rewrite_data_files().

Value

A ggplot object, which can be further customized with ggplot2 functions

See Also

Other maintenance: backup_ducklake(), checkpoint_ducklake(), cleanup_old_files(), delete_orphaned_files(), expire_snapshots(), flush_inlined_data(), get_table_info(), list_ducklake_files(), merge_adjacent_files(), rewrite_data_files()

Examples

lake_dir <- tempfile("plotfiles_lake_")
dir.create(lake_dir)
attach_ducklake("plotfiles_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

create_table(iris, "flowers")

# File counts and sizes for every table in the lake
plot_table_files()

# Customize the result like any ggplot
plot_table_files() +
  ggplot2::theme_classic()

detach_ducklake("plotfiles_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Sends a single SQL query to a Quack server and returns the result as a data.frame. Unlike attach_quack(), this does not attach the remote catalog, so it is a quick way to pull a result without changing the session state.

Usage

quack_query(uri, query, token = NULL, disable_ssl = FALSE)

Arguments

Value

A data.frame with the query result.

See Also

attach_quack()

Other quack: attach_quack(), detach_quack(), install_quack(), quack_serve(), quack_stop()

Examples

## Not run: 
quack_query(
  "quack:data.example.org",
  "SELECT count(*) FROM adsl",
  token = "super_secret"
)

## End(Not run)

Description

Starts a Quack server in the current DuckDB instance. Everything attached to the session, including a DuckLake attached with attach_ducklake(), becomes reachable by other DuckDB clients over the ⁠quack:⁠ protocol. The server runs in the background of the DuckDB instance, so the R session stays usable.

Usage

quack_serve(
  uri = "quack:localhost",
  token = NULL,
  allow_other_hostname = FALSE,
  disable_ssl = FALSE
)

Arguments

Value

The server uri, invisibly.

See Also

quack_stop(), attach_quack()

Other quack: attach_quack(), detach_quack(), install_quack(), quack_query(), quack_stop()

Examples

## Not run: 
attach_ducklake("trial", lake_path = "path/to/lake")
quack_serve(token = "super_secret")
# ... colleagues connect with attach_quack() ...
quack_stop()

## End(Not run)

Description

Stops a Quack server started with quack_serve().

Usage

quack_stop(uri = "quack:localhost")

Arguments

Value

TRUE, invisibly.

See Also

quack_serve()

Other quack: attach_quack(), detach_quack(), install_quack(), quack_query(), quack_serve()

Examples

## Not run: 
quack_stop()

## End(Not run)

Description

Renames a table in place with ⁠ALTER TABLE ... RENAME TO⁠, a metadata-only change.

Usage

rename_ducklake_table(from, to)

Arguments

Details

History survives the rename, but snapshots from before it stay associated with the old name: get_ducklake_table_version() on the new name reaches back only as far as the rename, while the old name still serves the earlier snapshots.

Value

Invisibly returns NULL.

See Also

rename_table_column()

Other schema evolution: add_table_column(), drop_table_column(), rename_table_column(), set_column_not_null(), set_column_type()

Examples

lake_dir <- tempfile("renametbl_lake_")
dir.create(lake_dir)
attach_ducklake("renametbl_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

rename_ducklake_table("cars", "cars_daily")

detach_ducklake("renametbl_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Renames a column in place with ⁠ALTER TABLE ... RENAME COLUMN⁠, a metadata-only change.

Usage

rename_table_column(table_name, from, to)

Arguments

Value

Invisibly returns NULL.

See Also

add_table_column(), drop_table_column(), rename_ducklake_table()

Other schema evolution: add_table_column(), drop_table_column(), rename_ducklake_table(), set_column_not_null(), set_column_type()

Examples

lake_dir <- tempfile("renamecol_lake_")
dir.create(lake_dir)
attach_ducklake("renamecol_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

rename_table_column("cars", from = "mpg", to = "miles_per_gallon")

detach_ducklake("renamecol_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Replace a table with modified data and create a new snapshot

Usage

replace_table(.data, table_name, .quiet = TRUE)

Arguments

Details

This function is designed for bulk transformations that should create a new versioned snapshot. A dplyr pipeline on the package's connection runs inside DuckDB: its result is materialized in DuckDB's temporary storage (the query may read the table being replaced), the table is dropped and recreated from it, and the metadata DuckLake keeps against the table is put back. No rows pass through R. A data frame, or a lazy table on another connection, is loaded the way create_table() loads it.

The drop and create run atomically: when no transaction is open, replace_table() wraps them in one of its own, so a failed create never leaves the table dropped. Wrap the call in with_transaction() (or begin_transaction()/commit_transaction()) when you want to record an author and commit message on the snapshot, or to group the replacement with other changes.

DuckLake gives the replacement a new table id, as it does for any DROP + CREATE, and it stores comments, partition keys, sort order, and table-scoped options against that id. replace_table() carries them over: the table comment, column comments (and so variable labels) for columns that still exist, partition and sort keys whose columns still exist (set before the rows are written, so the rewrite itself lands partitioned and sorted), and options set with set_ducklake_option() at table scope. DuckLake cannot set options on a table created in the open transaction, so options are re-set right after the rewrite commits, as a small follow-up snapshot; inside a transaction you opened yourself they cannot be re-set at all, and a warning lists the calls to make after your commit. Earlier snapshots keep the earlier id and stay reachable by name through time travel.

What the open transaction has done to the table counts. Comments set earlier in it are carried over, and so are keys set or reset there with set_table_partitioning(), set_table_sorting(), and their reset_ counterparts. DuckLake shows pending keys nowhere, so ducklake notes them as those functions run: keys changed in the same transaction with a raw ⁠ALTER TABLE⁠ statement are not seen, and the rewrite then carries the committed keys over instead.

When to use replace_table():

• Bulk transformations - a dplyr pipeline that recomputes, reshapes, or filters most of the table

When to reach elsewhere:

• Schema-only changes - add_table_column(), drop_table_column(), rename_table_column(), and set_column_type() alter the table in place; nothing is collected or rewritten

• Derived columns - add_table_column() followed by a mutate() pipeline through ducklake_exec() fills the new column with an in-database UPDATE

• Targeted row changes - rows_update(), rows_upsert(), or ducklake_exec() modify only the affected rows

Both paths create a snapshot: replace_table() via DROP + CREATE, and ducklake_exec() via the in-place UPDATE/DELETE/INSERT it runs, so either way the change is available for time travel.

Value

Invisibly returns NULL

See Also

Other table operations: add_data_files(), create_schema(), create_table(), create_view(), drop_schema(), drop_view(), ducklake_exec(), get_ducklake_table(), get_metadata_table(), list_ducklake_tables(), show_ducklake_query()

Examples

lake_dir <- tempfile("replace_lake_")
dir.create(lake_dir)
attach_ducklake("replace_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

# Add new derived columns (atomic on its own; creates a new snapshot)
get_ducklake_table("cars") |>
  dplyr::mutate(
    thirsty = dplyr::if_else(mpg < 20, "Y", "N"),
    mpg_band = dplyr::case_when(
      mpg < 15 ~ "<15",
      mpg < 25 ~ "15-24",
      TRUE ~ ">=25"
    )
  ) |>
  replace_table("cars")

# Wrap in with_transaction() to record audit metadata on the snapshot
with_transaction(
  get_ducklake_table("cars") |>
    dplyr::select(-thirsty, -mpg_band) |>
    replace_table("cars"),
  author = "Data Engineer",
  commit_message = "Drop derived columns"
)

# Partition keys, sort order, comments, and table options survive the rewrite
set_table_partitioning("cars", "cyl")
get_ducklake_table("cars") |>
  dplyr::filter(mpg > 15) |>
  replace_table("cars")
get_table_partitions("cars")

detach_ducklake("replace_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Clears a table's partitioning keys so newly written data files are no longer split along them. Existing files are unaffected.

Usage

reset_table_partitioning(table_name)

Arguments

Value

Invisibly returns NULL.

See Also

set_table_partitioning(), get_table_partitions()

Other partitioning: get_table_partitions(), set_table_partitioning()

Examples

lake_dir <- tempfile("unpart_lake_")
dir.create(lake_dir)
attach_ducklake("unpart_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

set_table_partitioning("cars", "cyl")
reset_table_partitioning("cars")

detach_ducklake("unpart_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Clears a table's declared sort order so newly written data files are no longer sorted. Existing files are unaffected.

Usage

reset_table_sorting(table_name)

Arguments

Value

Invisibly returns NULL.

See Also

set_table_sorting()

Other sorting: get_table_sorting(), set_table_sorting()

Examples

lake_dir <- tempfile("unsort_lake_")
dir.create(lake_dir)
attach_ducklake("unsort_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

set_table_sorting("cars", "mpg")
reset_table_sorting("cars")

detach_ducklake("unsort_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Rolls a table back to the state it had at an earlier snapshot or point in time, by recreating it from a time-travel read of itself. History is preserved: the restore is recorded as a new snapshot (with a commit message noting the restore), so nothing is rewritten or lost and you can still time-travel to any snapshot, including those after the restore point.

Usage

restore_table_version(
  table_name,
  version = NULL,
  timestamp = NULL,
  author = NULL,
  commit_message = NULL,
  conn = NULL
)

Arguments

Details

You must specify either version or timestamp, but not both.

Under the hood this reads SELECT * FROM t AT (VERSION => n) into a temporary DuckDB table, then drops and recreates t from it inside a transaction. Because the restore creates a new snapshot, it is itself reversible with another restore_table_version() call.

The restore commits for itself, with its own author and message, so it cannot join a transaction that is already open and says so before it touches anything. To restore to a version and change it in one snapshot, pipe get_ducklake_table_version() into replace_table() inside with_transaction().

The restored table gets a new table id, and DuckLake keeps comments, partition keys, sort order, and table-scoped options against the id, so they are captured beforehand and put back: comments and keys for the columns the restored version still has, inside the restore transaction; table-scoped options right after it commits, as a small follow-up snapshot, since DuckLake cannot set options on a table created in the open transaction.

Value

Invisibly returns TRUE on success

See Also

get_ducklake_table_version(), get_ducklake_table_asof(), list_table_snapshots()

Other time travel: get_ducklake_table_asof(), get_ducklake_table_version(), get_table_changes(), list_table_snapshots(), plot_snapshots(), plot_table_changes(), view_table_changes()

Examples

lake_dir <- tempfile("restore_lake_")
dir.create(lake_dir)
attach_ducklake("restore_lake", lake_path = lake_dir)
create_table(data.frame(id = 1:3, amount = c(10, 20, 30)), "orders")

rows_delete(
  get_ducklake_table("orders"),
  data.frame(id = 1L),
  by = "id"
)
snapshots <- list_table_snapshots("orders")
first_version <- snapshots$snapshot_id[1]

# Roll the table back to its first snapshot
restore_table_version("orders", version = first_version)

# Record who performed the restore in the audit trail
restore_table_version(
  "orders",
  version = first_version,
  author = "Data Steward",
  commit_message = "Roll back erroneous bulk update"
)

detach_ducklake("restore_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Rewrites Parquet files whose rows have mostly been deleted. Deletes in DuckLake are recorded in separate delete files; heavily-deleted data files slow reads down until they are rewritten without the dead rows.

Usage

rewrite_data_files(
  ducklake_name = NULL,
  table_name = NULL,
  delete_threshold = NULL,
  schema_name = NULL
)

Arguments

Details

The rewritten originals are scheduled for deletion once no snapshot references them; run cleanup_old_files() to remove them.

Value

A data frame with one row per output file (columns schema_name, table_name, files_processed, files_created).

See Also

cleanup_old_files(), checkpoint_ducklake()

Other maintenance: backup_ducklake(), checkpoint_ducklake(), cleanup_old_files(), delete_orphaned_files(), expire_snapshots(), flush_inlined_data(), get_table_info(), list_ducklake_files(), merge_adjacent_files(), plot_table_files()

Examples

lake_dir <- tempfile("rewrite_lake_")
dir.create(lake_dir)
attach_ducklake("rewrite_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

rows_delete(
  get_ducklake_table("cars"),
  data.frame(gear = 3),
  by = "gear"
)

# Rewrite any file that is at least half deleted
rewrite_data_files("rewrite_lake", delete_threshold = 0.5)

# Just one table, with DuckLake's default threshold
rewrite_data_files(table_name = "cars")

detach_ducklake("rewrite_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

Rolls back the current transaction, discarding all changes made since the transaction began.

Usage

rollback_transaction(conn = NULL)

Arguments

Details

This function discards all changes made since begin_transaction() was called, reverting the database to its state before the transaction began.

Value

Invisibly returns TRUE on success

See Also

Other transactions: begin_transaction(), commit_transaction(), set_ducklake_retry(), set_snapshot_metadata(), with_transaction()

Examples

lake_dir <- tempfile("rollback_lake_")
dir.create(lake_dir)
attach_ducklake("rollback_lake", lake_path = lake_dir)
create_table(mtcars, "cars")

begin_transaction()
rows_delete(
  get_ducklake_table("cars"),
  data.frame(gear = 3),
  by = "gear"
)

# Something went wrong, rollback
rollback_transaction()

detach_ducklake("rollback_lake", shutdown = TRUE)
unlink(lake_dir, recursive = TRUE)

Description

A wrapper around dplyr::rows_delete() with in_place = TRUE as the default, since DuckLake is designed for in-place modifications.

Usage

rows_delete(
  x,
  y,
  by = NULL,
  copy = TRUE,
  in_place = TRUE,
  unmatched = "ignore",
  ...
)

Arguments

Details

Choosing how to change a table

• To look up or combine data for analysis, use dplyr joins (left_join() and friends). Joins read from the lake and build a new result; they never modify a lake table.

• To append, correct, or remove specific rows, use rows_insert(), rows_update(), or rows_delete(). Each call is a single SQL statement against the existing table – no data leaves the database, and with data inlining enabled (DuckLake's default) small changes land in the catalog without creating tiny Parquet files.

• To update rows that exist and insert the ones that don't in one atomic statement, use rows_upsert().

• For conditional merge logic or deletes driven by a staging table, use merge_into().

• To change a table's shape without touching its data – add, drop, or rename columns, widen a type – use the schema evolution family (add_table_column() and friends): metadata-only changes that rewrite nothing.

• For bulk transformations that touch most rows, use replace_table(). It rewrites the whole table inside DuckDB – heavier than the row operations, and it resets the row lineage that the in-place operations preserve in the change feed.