Files and Lakehouse Tables¶
A DuckDB data source can read files and Iceberg tables where
they are: Parquet, CSV and JSON files on disk or in object storage, and Apache
Iceberg tables behind a REST catalog. No warehouse is needed. The DuckDB inside
the OrionBelt process runs the queries, so the model's dialect is duckdb.
When one source's load needs more than one process, its queries can run on
DuckDB workers instead.
| What you read | How a data object names it | Setup statements | DUCKDB_ALLOWED_PATHS |
|---|---|---|---|
| Parquet, CSV or JSON files on disk | code is the path or a glob, schema: "" |
none | the files' directory |
| Files in S3-compatible object storage | code is the s3:// path or glob, schema: "" |
a CREATE SECRET for the bucket |
the bucket, s3://bucket/ |
| Iceberg tables in a REST catalog | code is the table, schema its namespace |
ATTACH the catalog; SET search_path on each connection |
the catalog's URL and the bucket |
| An Iceberg table without a catalog | a view over iceberg_scan(...) |
CREATE OR REPLACE VIEW |
the table's folder |
A source without a database file¶
Declare a DuckDB source with :memory: as its database:
Such a source keeps one DuckDB instance for as long as the server runs, and
each query runs on a connection of its own to it. The queries running at once
share the instance's memory limit, and what its setup statements attach stays
attached. The instance is the source's own, a named :memory:name included:
sources with the same settings share one, and nothing else reaches it by its
name.
Set :memory: explicitly where DUCKDB_DATABASE is set for the whole server,
as the Docker image does: a named source falls back to the unprefixed variable.
What a source may read¶
A data object's code can name a file, so a model could name any file the
server can read: its configuration, a key file. A DuckDB source therefore reads
only its own database, unless DUCKDB_ALLOWED_PATHS lists more:
| Entry | Allows |
|---|---|
| A directory | Every file in it and below it, and nothing beside it: /data/orders/ does not open /data/orders_private/, and .. cannot climb out |
| A file | That file alone |
| A URL prefix | Everything under it: an S3 bucket, an Iceberg catalog's REST endpoint |
* |
Everything the server can read, as before this setting existed |
| Unset | The source's database alone. A MotherDuck (md:) database is not limited unless the setting is given: its data is remote |
- What it covers. Every query of the source, on REST, the Postgres wire and
Flight SQL. A path the list leaves out is refused with DuckDB's
Permission Error, and the error namesDUCKDB_ALLOWED_PATHS. - The setup statements run first. The limit is set after
DUCKDB_INIT_SQL, so it can still load extensions and attach a catalog;DUCKDB_CONNECTION_SQLruns under the limit. What the queries then read, the files behind a view or the catalog's tables, has to be listed. - Extensions. Once the limit is set, DuckDB loads no further extension. One
that the setup statements do not load (with
LOAD, or by using it, asCREATE SECRET (TYPE s3, ...)loadshttpfs) cannot be loaded later. - It cannot be lifted while the server runs. DuckDB refuses to turn it off again. A changed list takes effect after a restart.
- One list per database file. The first source to open a file decides its paths until the server restarts; another source of the file with other paths is refused.
Files by path¶
Put the path in the data object's code, leave schema empty, and list the
files' directory in the source's DUCKDB_ALLOWED_PATHS (here
DB_LAKE_DUCKDB_ALLOWED_PATHS=/data/orders/):
dataObjects:
Orders:
code: "/data/orders/*.parquet"
schema: ""
columns:
Order Id:
code: id
abstractType: int
Amount:
code: amount
abstractType: float
- The empty
schemakeeps the path whole. The DuckDB dialect names a table"schema"."code", and with no schema the path alone is the name. - A glob reads every file it matches as one table.
- DuckDB picks the reader by the file's extension:
.parquet,.csv,.json.
For files in S3-compatible storage, give DuckDB the bucket's keys in the
source's setup statements, and use the s3:// path in code:
DB_LAKE_DUCKDB_INIT_SQL=CREATE OR REPLACE SECRET lake_s3 (TYPE s3, KEY_ID 'AKIA...', SECRET '...', REGION 'eu-west-1')
DB_LAKE_DUCKDB_ALLOWED_PATHS=s3://my-bucket/
DuckDB's secrets cover the other ways to sign in, and Google Cloud Storage and Azure.
Setup statements¶
Two settings hold statements DuckDB runs before a source's queries, on REST, the Postgres wire and Flight SQL alike:
| Setting | Runs | For |
|---|---|---|
DUCKDB_INIT_SQL |
Once for each DuckDB instance of the source: for a source without a database file, once while the server runs; for a database file, which each query opens anew, on each query | What the queries share: CREATE SECRET for storage or a catalog, ATTACH, LOAD, views |
DUCKDB_CONNECTION_SQL |
On every connection: every query, and every Flight SQL cursor | What lasts only for the connection that ran it: SET search_path, USE, TEMP views |
| Rule | Why |
|---|---|
Write them to run again: ATTACH IF NOT EXISTS, CREATE OR REPLACE SECRET, CREATE OR REPLACE VIEW |
A database file runs its setup statements on each query, and the connection statements run on every connection |
| They run for one connection of an instance at a time | Run at once, two connections' CREATE OR REPLACE VIEWs conflict in DuckDB's catalog (Catalog write-write conflict) |
| They are never shown | They can hold keys. They are not listed with the source, and an error names the variable rather than repeating the statements |
| Changing them is a different source | They decide what the source's tables are and how its names resolve, so cached results of the old statements are not served for the new ones |
A statement that fails reports which variable holds it and what DuckDB said,
for example: The setup statements of data source 'lake'
(DB_LAKE_DUCKDB_INIT_SQL) failed: Catalog Error: ....
Iceberg tables in a REST catalog¶
Attach the catalog in the setup statements, and point each connection's search path at one of its namespaces:
OBSL_DATABASES=lake
DB_LAKE_TYPE=duckdb
DB_LAKE_DUCKDB_DATABASE=:memory:
DB_LAKE_DUCKDB_INIT_SQL="CREATE OR REPLACE SECRET lake_s3 (TYPE s3, KEY_ID 'admin', SECRET 'password', REGION 'us-east-1', ENDPOINT 'minio:9000', URL_STYLE 'path', USE_SSL false); ATTACH IF NOT EXISTS 'warehouse' AS lake (TYPE iceberg, ENDPOINT 'http://iceberg-rest:8181', AUTHORIZATION_TYPE 'none', ACCESS_DELEGATION_MODE 'none')"
DB_LAKE_DUCKDB_CONNECTION_SQL=SET search_path = 'lake.sales'
DB_LAKE_DUCKDB_ALLOWED_PATHS=http://iceberg-rest:8181/,s3://warehouse/
Each query asks the catalog for the table, so the catalog's URL is listed as well as the bucket that holds the data files.
A data object's schema is the namespace and its code the table:
- Any namespace of the catalog. The search path names one namespace, and
DuckDB finds
finance.budgetin the same catalog too. - The newest snapshot. Each query reads a table's newest snapshot: a commit made after the source attached the catalog is read by the next query.
- Storage keys. With
ACCESS_DELEGATION_MODE 'none'DuckDB reads the data files with your own S3 secret. Without it DuckDB asks the catalog for credentials to the files (vended_credentials), where the catalog hands them out; the buckets they are for still have to be listed. - Signing in to the catalog. The example's catalog needs none; most do.
Each way to sign in is a secret in the setup statements, named in the
ATTACH(SECRET lake_cat); DuckDB's Iceberg catalogs page covers each catalog:
| Catalog sign-in | Secret | List in DUCKDB_ALLOWED_PATHS |
|---|---|---|
| OAuth2 client credentials (Apache Polaris, Lakekeeper, most REST catalogs) | CREATE OR REPLACE SECRET lake_cat (TYPE iceberg, CLIENT_ID '...', CLIENT_SECRET '...', OAUTH2_SERVER_URI 'https://auth.example.com/oauth/tokens') |
The catalog's URL and the token server's |
| A bearer token | CREATE OR REPLACE SECRET lake_cat (TYPE iceberg, TOKEN '...') |
The catalog's URL |
| AWS (Glue, S3 Tables) | AWS credentials as an S3 secret (CREATE OR REPLACE SECRET (TYPE s3, ...)); the ATTACH takes ENDPOINT_TYPE 'glue' or 's3_tables' (or the catalog's URI with AUTHORIZATION_TYPE 'sigv4') |
The catalog's AWS endpoint and the bucket |
Measured on DuckDB 2.0, with a token server and a catalog that refuses expired tokens:
| Case | What happens |
|---|---|
| Many queries | An OAuth2 secret asks the token server once, when the source's instance is set up; every query after uses that token |
| The token expires | DuckDB asks for a new one before it expires (from the token server's expires_in), or on the catalog's 401 where the server sends none, and repeats the request. The query does not fail |
| The token is revoked early | The same: a 401, a new token, the request repeated |
A bearer token (TOKEN '...') expires |
The query fails: HTTP Error: ... Unauthorized_401. Neither DuckDB nor OrionBelt has what it takes to ask for another: replace it in the setting (Google Cloud's expire after an hour), and restart the server |
DuckDB renews tokens while queries run, after the limit on paths is set, so
the token server has to be in DUCKDB_ALLOWED_PATHS. Left out, only the
first token is fetched, and the first renewal fails with Could not get
token from ....
- A catalog or object store that stops answering. DuckDB waits 30 s for
each HTTP request and tries it 3 more times: a query then waits about 2
minutes before it fails. Measured with the object store stopped, the catalog
itself hung, waiting for it. Shorter limits in the setup statements, before
the ATTACH, fail it sooner:
The query then failed in about 30 s (10 s, twice more), and the next one
read again once the store was back. The limit is per request, so it has
to leave room for the largest file read a query makes. SET GLOBAL, not
SET: a plain SET lasts for the setup's own connection only.
This setup was tested with Apache Iceberg's REST catalog and MinIO, without sign-in, and with OAuth2 and bearer-token secrets against a local token server and a catalog that refuses expired tokens. Vended credentials and the AWS and Google catalogs were not tested.
An Iceberg table without a catalog¶
Define a view over iceberg_scan in the setup statements:
CREATE SCHEMA IF NOT EXISTS sales;
CREATE OR REPLACE VIEW sales.orders AS
SELECT * FROM iceberg_scan('/warehouse/sales/orders', version = '?');
- List the table's folder in
DUCKDB_ALLOWED_PATHS: the view reads it at query time. version = '?'reads the newest metadata file DuckDB finds in the table's folder.- A path to one
metadata.jsonfile reads that snapshot, and keeps reading it after later commits.
Memory and spilling¶
| Variable | Default | What it sets |
|---|---|---|
DUCKDB_MEMORY_LIMIT |
50% of the memory DuckDB sees |
DuckDB's memory_limit, such as 500MB or 50%. DuckDB's own default, 80%, is left out on purpose: see below |
DUCKDB_THREADS |
every CPU DuckDB sees | DuckDB's threads |
DUCKDB_SPILL_DIR |
see below | Where DuckDB writes joins, aggregates and sorts that outgrow the limit; off turns spilling off |
DUCKDB_SPILL_MAX_MB |
DuckDB's, 90% of the free disk | The most DuckDB may spill, wherever it spills |
Each is read as DB_<NAME>_<VARIABLE> for a named source, then as the bare
variable. In a container, DuckDB sees the container's memory and CPU limits.
Unset, the spill directory is:
- for a source without a database file, a new directory under the system's temp directory, removed when the server stops; no spilling where that directory is in memory (a tmpfs or ramfs, or Cloud Run's filesystem), as a file there takes the memory it was meant to free;
- for a database file, DuckDB's own default:
<file>.tmpbeside it.
How much memory to give DuckDB¶
DuckDB's limit covers its own work, not the server around it, and DuckDB spills to disk only once it reaches the limit. Measured in a 1 GB container with 2 CPUs, four queries at once, each grouping 24 million Parquet rows by 7.6 million keys:
| Memory limit | Result |
|---|---|
| DuckDB's default, 80% (819 MiB) | The container ran out of memory after 11 s: the server was killed, and all four queries were lost. With the server's own memory on top, 80% leaves no room |
| Unset: 50% (512 MiB) | All four answered in about 68 s, spilling to disk; the server stayed up |
| 500 MB | All four answered in about 83 s, spilling to disk; the server stayed up |
| 150 MB per query | Each query failed with DuckDB's Out of Memory Error: too little memory even to spill. The server stayed up |
That is why a source's limit is half the memory unless you set it. Spilling lets a query past the limit finish, more slowly, rather than fail: one of these queries alone took 7.6 s with the memory it needed.
Each limit is one instance's. Two DuckDB sources busy at once can, at half the memory each, still take all of it; where several are, set their limits so that together they stay near half the container's memory. The limits that add up:
- each source without a database file: one limit for all its queries;
- each database file: queries that overlap share one DuckDB instance and its limit; a query that runs alone opens the file anew;
- each federated query's combine
step:
FEDERATION_COMBINE_MEMORY_LIMIT, up toFEDERATION_MAX_CONCURRENTat once.
Instances belong to sources, not to models: every model on one source shares
its instance. DB_<NAME>_MAX_CONNECTIONS caps how many of the source's queries
run at once.
Where spilling is off (DUCKDB_SPILL_DIR=off, or a temp directory in memory),
a query past the limit fails with DuckDB's Out of Memory Error.
In Docker¶
DuckDB loads an extension the first time a query needs it: httpfs for object
storage, iceberg and avro for Iceberg. The image lets it download them into
/app/.duckdb. Measured: the first query of an Iceberg source took 26 s while
DuckDB downloaded them, the next one 32 ms.
For a host that cannot reach extensions.duckdb.org, build them into the image
(about 105 MB):
To spill to a volume rather than the container's writable layer:
Limitations¶
| Limitation | Why | Workaround |
|---|---|---|
Sources of one database file must agree on DUCKDB_ALLOWED_PATHS |
DuckDB shares one instance of a file between the queries reading it at once, and its limit, once set, cannot change; setting it would also cut off a source reading without one | The first source to open a file decides its paths until the server restarts, however the path is spelled. A source of the file with other paths is refused, naming its setting: give them the same list |
| One Iceberg catalog per source | The DuckDB dialect writes "schema"."table", without the catalog's name |
Declare a second source for the second catalog. A query reading both is a federated query |
Sources that read one database file at the same time must agree on DUCKDB_MEMORY_LIMIT, DUCKDB_THREADS and DUCKDB_SPILL_DIR |
DuckDB opens one instance per file in a process, with one set of settings | Give them the same settings. A mismatch is refused, naming the settings |
An Iceberg folder cannot be named in code |
DuckDB reads a bare path only for files such as Parquet | A view over iceberg_scan, or a catalog |