Skip to contents

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 with create_storage_secret(). For "duckdb" the catalog file lives in this directory too by default; give catalog_connection_string to 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 while lake_path points 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 with quack:, 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 to 0 to disable inlining for this connection. This setting is not persisted; use set_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 experimental write_deletion_vectors option are not. Default FALSE.

meta_encryption_key

Optional key that encrypts the catalog database file itself, with AES-256-GCM ("duckdb" backend only; the META_ 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 with encrypted, whose Parquet keys are stored in the catalog. Pass askpass::askpass() to be prompted rather than putting the key in code. The key is interpolated into the ATTACH statement 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's DUCKLAKE_VERSION option). When NULL, 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 unless automatic_migration = TRUE migrates 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 (the AUTOMATIC_MIGRATION option). 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 with backup_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. Default FALSE, in which case a lake opens in the format it has and a ducklake_version newer than that format is an error.

create

Create the lake when none exists at the catalog location (default TRUE, DuckLake's CREATE_IF_NOT_EXISTS). Pass FALSE when 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: main for a DuckDB or SQLite catalog, usually public for 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's META_TOKEN option. Like meta_encryption_key, it is interpolated into the ATTACH statement text. NULL lets a Quack secret in the session supply it.

author

Author to record on snapshot 0, the creation snapshot, when this call creates the lake. Defaults to the ducklake.author option 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"
)
} # }