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", "quack"),
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,
ducklake_version = NULL,
automatic_migration = FALSE,
create = TRUE,
metadata_schema = NULL,
quack_token = NULL,
author = NULL,
commit_message = "Create lake"
)Arguments
- ducklake_name
Name for the ducklake, used as the database alias in DuckDB
- lake_path
Directory where the Parquet data files are stored (DuckLake's
DATA_PATH). May be a local directory, created if it does not exist yet, or an object-storage URI such as"s3://bucket/path"– register credentials first withcreate_storage_secret(). For"duckdb"the catalog file lives in this directory too by default; givecatalog_connection_stringto place it elsewhere, which is how a local catalog pairs with remote data.- backend
Catalog backend:
"duckdb"(default),"postgres","sqlite","mysql", or"quack"(experimental: a DuckDB catalog that a Quack server holds open for several clients, see Details).- catalog_connection_string
Backend-specific connection string:
"duckdb"Optional path for the catalog database file. Defaults to
{lake_path}/{ducklake_name}.ducklake. Set it to keep the catalog on local disk whilelake_pathpoints at object storage."postgres"libpq string, e.g.
"dbname=mydb host=localhost"."sqlite"Path to the SQLite file, e.g.
"metadata.sqlite"."mysql"MySQL connection string, e.g.
"db=mydb host=localhost"."quack"The Quack server's address, e.g.
"quack:catalog.example.org:9494"; a bare host is prefixed withquack:, and the default port is 9494.
- read_only
Attach in read-only mode (default
FALSE).- override_data_path
Override the stored DATA_PATH in the catalog (default
FALSE). Needed when restoring a backup to a different location.- data_inlining_row_limit
Optional integer. Sets the per-connection data inlining row limit. Inserts or deletes affecting fewer rows than this threshold are stored directly in the catalog instead of writing Parquet files. The default (when
NULL) uses the DuckLake default of 10 rows. Set to0to disable inlining for this connection. This setting is not persisted; useset_inlining_row_limit()for persistent overrides.- encrypted
If
TRUE, DuckLake encrypts the Parquet data files it writes. Encryption keys are stored in the catalog database, so anyone with access to the catalog can read the data – protect the catalog accordingly. Only applies when the lake is first created; an existing lake keeps the setting it was created with. The httpfs extension is loaded automatically: on some platforms (notably Windows) DuckDB's built-in crypto module is read-only and httpfs provides the writer. Parquet data and delete files are encrypted; the Puffin deletion vectors of the experimentalwrite_deletion_vectorsoption are not. DefaultFALSE.- meta_encryption_key
Optional key that encrypts the catalog database file itself, with AES-256-GCM (
"duckdb"backend only; theMETA_prefix is how DuckLake forwards the option to the metadata catalog). The key takes effect when the catalog is first created – an existing unencrypted catalog cannot be encrypted after the fact – and the same key is required on every later attach, with no recovery if it is lost. Pairs naturally withencrypted, whose Parquet keys are stored in the catalog. Passaskpass::askpass()to be prompted rather than putting the key in code. The key is interpolated into theATTACHstatement text, so it can surface where statements are logged or profiled.- snapshot_version
Optional snapshot id. Attaches the lake pinned to that snapshot: queries see the lake exactly as it was then, and writes are rejected. Mutually exclusive with
snapshot_time.- snapshot_time
Optional POSIXct or UTC timestamp string. Attaches the lake pinned to its state at that moment. Mutually exclusive with
snapshot_version.- ducklake_version
Optional DuckLake catalog format version,
"1.0"or"1.1"(DuckLake'sDUCKLAKE_VERSIONoption). WhenNULL, the default, a new lake is created in the newest format, 1.1 with duckdb 2.0, and an existing lake opens in the format it has. Pass"1.0"to create a lake that duckdb 1.5 clients can still open, for a team on a SQLite or PostgreSQL catalog that is not upgrading together; a DuckDB-file catalog gives no such compatibility, see Details. A 1.0 lake does without the 1.1 features (nested schemas, comments on view columns, and the"NULL"column type among them). Requesting"1.1"for an existing 1.0 lake is an error unlessautomatic_migration = TRUEmigrates it, and a 1.1 lake cannot be taken back to 1.0.- automatic_migration
If
TRUE, let DuckLake migrate a catalog in the 1.0 format to 1.1 when the lake is attached (theAUTOMATIC_MIGRATIONoption). A lake created with duckdb 1.5 is a 1.0 catalog, which duckdb 2.0 reads and writes as it is; the 1.1 features need the migration. The migration is permanent (DuckLake refuses to downgrade a catalog), and after it no duckdb 1.5 client can open the lake, whatever the catalog backend. So move every client of the lake to duckdb 2.0 first, take a backup withbackup_ducklake(), and attach once with this option from a writable session that may alter the catalog's tables (on PostgreSQL, the owner of the metadata tables); later attaches need nothing. A catalog from before 1.0 (DuckDB 1.5.1 wrote 0.4) cannot be opened at all, not even read-only, until it is migrated. DefaultFALSE, in which case a lake opens in the format it has and aducklake_versionnewer than that format is an error.- create
Create the lake when none exists at the catalog location (default
TRUE, DuckLake'sCREATE_IF_NOT_EXISTS). PassFALSEwhen you mean to open an existing lake, so a mistyped path or name is an error rather than a new, empty lake.- metadata_schema
Optional schema inside the catalog database that holds this lake's metadata tables (DuckLake's
METADATA_SCHEMA). Without it DuckLake uses the backend's default schema:mainfor a DuckDB or SQLite catalog, usuallypublicfor PostgreSQL. Lets several lakes share one PostgreSQL database, each in its own schema. A SQLite catalog has no schemas and refuses the option.- quack_token
Token of the Quack server that holds the catalog (
"quack"backend only), sent as DuckLake'sMETA_TOKENoption. Likemeta_encryption_key, it is interpolated into theATTACHstatement text.NULLlets a Quack secret in the session supply it.Author to record on snapshot 0, the creation snapshot, when this call creates the lake. Defaults to the
ducklake.authoroption when it is set (see?ducklake), otherwise none. Not written for a lake that already exists.- commit_message
Commit message to record on snapshot 0 when this call creates the lake (default
"Create lake").NULL, with no author, leaves snapshot 0 as DuckLake writes it, without metadata.
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.
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.
Upgrading duckdb: The first write from duckdb 2.0 to a lake whose
catalog is a DuckDB file rewrites that file in a storage format duckdb
1.5 cannot open, whatever the lake's catalog format version, and no
attach option prevents it: a lake created on duckdb 2.0 with
ducklake_version = "1.0" is just as unreadable on 1.5 when its catalog
is a DuckDB file. SQLite and PostgreSQL catalogs are not affected. So
before the first duckdb 2.0 session writes to a DuckDB-file lake that
other machines open, take a backup with backup_ducklake() and move
every client of the lake to 2.0 together; a team that cannot upgrade
together belongs on a SQLite or PostgreSQL catalog. The catalog format
migration (automatic_migration) is a separate, opt-in step.
vignette("deployment") has the full sequence.
A catalog served over Quack (experimental). A DuckDB-file catalog
admits one process at a time. With backend = "quack", that file is
opened by one DuckDB process running a Quack server (see
quack_serve() and vignette("quack-remote-access")), and every
client attaches the lake through it: the metadata lives on the server,
commits go through it, and the clients read and write the Parquet files
themselves, so lake_path must be storage the server and every client
can reach by the same path, such as a mounted share or an object store.
DuckLake marks this catalog experimental. Backups of the catalog are the
server's DuckDB file; backup_ducklake() copies the data files only.
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.
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.
if (FALSE) { # \dontrun{
# 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/"
)
# A DuckDB catalog held open by a Quack server, shared by several clients
# (experimental); the data path is a share every client mounts
attach_ducklake(
"my_lake",
backend = "quack",
catalog_connection_string = "quack:catalog.example.org",
quack_token = askpass::askpass("Quack token"),
lake_path = "/shared/lake/data/"
)
# 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 made with duckdb 1.5 is a 1.0 catalog and opens as it is. Once
# every client is on duckdb 2.0: back it up, then attach once with
# automatic_migration to move it to 1.1; later attaches need nothing
attach_ducklake("old_lake", lake_path = "path/to/lake")
backup_ducklake("old_lake", lake_path = "path/to/lake", backup_path = "path/to/backups")
detach_ducklake("old_lake")
attach_ducklake("old_lake", lake_path = "path/to/lake", automatic_migration = TRUE)
get_ducklake_info("old_lake")$format_version
# A new lake that duckdb 1.5 clients can still open: a 1.0 catalog in
# SQLite (a DuckDB-file catalog written by 2.0 would lock them out)
attach_ducklake(
"shared_lake",
backend = "sqlite",
catalog_connection_string = "/shared/lakes/shared_lake/catalog.sqlite",
lake_path = "/shared/lakes/shared_lake/data",
ducklake_version = "1.0"
)
# 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"
)
} # }
