Skip to contents

Create or attach a ducklake

Source: R/attach_ducklake.R
attach_ducklake.Rd

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

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", or "mysql".

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".

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. 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.

automatic_migration

If TRUE, let DuckLake upgrade a catalog written in an earlier format version to the one the installed extension uses (the AUTOMATIC_MIGRATION option). Needed once for a lake created with DuckDB 1.5.1, whose extension wrote the 0.4 format, after upgrading to 1.5.2 or later. The upgrade is permanent, so take a backup first. Default FALSE, in which case a mismatch 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, default main). Lets several lakes share one PostgreSQL database, each in its own schema.

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.

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.

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.
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/"
)

# 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"
)
} # }