DuckDB
| Status | verified |
|---|---|
| Reads | yes |
| Writes | yes |
| Iceberg v3 | reads and writes (1.5.5+) |
| Geometry, geography | geometry, read and written, pruned with &&; no geography (cannot open such a table) |
| Last verified | 2026-10-03 (DuckDB 2.0.0 and 1.5.6 (and DuckDB-WASM 1.5.6 in the browser)) |
DuckDB attaches a LakehouseBox catalog as a database. Every namespace is a schema, and every Iceberg table is a table you SELECT from, INSERT into, update and delete from. DuckDB talks to the catalog over the Iceberg REST protocol. It reads and writes the Parquet files itself, with storage credentials the catalog vends per table, so LakehouseBox is never in the query path.
DuckDB is also the engine lhbox itself uses. lhbox duckdb opens a shell with your catalog attached, and lhbox table import runs DuckDB on your machine to turn files into a table. The account page's SQL explorer and Import dialog run the same engine compiled to WebAssembly (DuckDB-WASM 1.5.6) in your browser tab.
The short path
lhbox duckdb --catalog acme_corp # a duckdb shell with acme_corp attached, credential hidden
lhbox table import demo.cities 'data/*.parquet' # or: many files into one new table, one commit
In the shell:
SHOW ALL TABLES;
CREATE SCHEMA IF NOT EXISTS acme_corp.demo;
CREATE TABLE acme_corp.demo.orders AS SELECT * FROM read_csv(['a.csv', 'b.csv'], union_by_name=true); -- all the files, one commit
SELECT count(*) FROM acme_corp.demo.orders;
The examples use the catalog acme_corp. Yours has its own name: run lhbox catalog list and use what it shows.
Before you start
- DuckDB 2.0.0 recommended; 1.5.5 or newer works. Filtering by geometry needs 1.5.6 or 2.0.0 (below). 1.5.0 writes manifest lists the catalog's maintenance cannot read (gotchas).
lhbox connectandlhbox duckdbwarn when the DuckDB they find is older.lhbox doctorshows which one that is: theduckdbPython module or the binary.- Most runs on this page used 1.5.6; those on 2.0.0 say so. In our tests DuckDB 2.0.0 keeps what this page describes, spatial pruning included. Tables it writes stay readable from 1.5.6 (Known limitations).
- Extensions. The recipe installs and loads
icebergandhttpfs. Geometry functions needspatial:INSTALL spatial; LOAD spatial;. - In Python,
TIMESTAMP WITH TIME ZONEvalues need thepytzmodule (gotchas).
Connect
The shortest way is to let lhbox attach the catalog without ever showing its credential:
lhbox duckdb --catalog acme_corp # a duckdb shell with acme_corp attached
lhbox duckdb --catalog acme_corp --persist # persistent DuckDB secrets instead, then one ATTACH line in any session
lhbox duckdb --catalog <handle>/<catalog> # another organisation's public catalog, read-only
lhbox duckdb runs the duckdb binary with the recipe in a private file, removed a second later. Arguments after -- go to duckdb.
--persist writes the catalog credential as DuckDB persistent secrets instead. They sit in DuckDB's secret directory, readable by any DuckDB you run. The shell and Python then need only the printed ATTACH. Revoke them with lhbox catalog rotate or DROP PERSISTENT SECRET.
To paste the statements yourself, or hand them to an agent:
lhbox connect --catalog acme_corp --engine duckdb --show-secrets # the recipe for your access
lhbox connect --catalog acme_corp --engine duckdb --readonly --show-secrets # the read-only recipe, for a colleague
Without --show-secrets, the secret is masked and the recipe starts with an error() naming the flag. The recipe:
INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
SET http_retries = 6; SET http_retry_backoff = 4;
CREATE SECRET lhbox (TYPE ICEBERG, CLIENT_ID '<client_id>', CLIENT_SECRET '<client_secret>',
OAUTH2_SERVER_URI 'https://catalog.lakehousebox.com/v1/oauth/tokens');
CREATE SECRET lhbox_s3 (TYPE S3, KEY_ID '<client_id>', SECRET '<client_secret>',
ENDPOINT 's3.lakehousebox.com', URL_STYLE 'path', USE_SSL true, REGION 'us-east-1');
ATTACH '<handle>--<catalog>' AS <catalog_name> (TYPE ICEBERG, ENDPOINT 'https://catalog.lakehousebox.com', SECRET lhbox);
-- '<handle>--<catalog>' is the recipe's warehouse_name (older catalogs keep w-<uuid>)
-- e.g. SELECT * FROM <catalog_name>.<namespace>.<table> LIMIT 10;
The lhbox_s3 secret is not needed to read or write tables through the catalog: vended credentials cover that. It lets read_blob('s3://acme-corp--acme-corp/**') and friends inspect the bucket. The recipe attaches the catalog under its own name (catalog_alias in the API answer). That is acme_corp for the default catalog, which the examples on this page assume.
Attach the bare bucket name, as the recipe does. ATTACH 's3://<bucket>/' attaches read-only (gotchas).
Reading
Everything DuckDB does with a local table works on an attached one: filters, projections, joins with your own files and with other catalogs. A query reads only the columns it names. It skips data files and row groups whose statistics rule out the filter. How tables are laid out shows what that saves, measured with DuckDB 1.5.5.
Snapshots and time travel (run on 2026-09-29, DuckDB 1.5.5):
SELECT * FROM iceberg_snapshots('acme_corp.demo.cities'); SELECT * FROM acme_corp.demo.cities AT (VERSION => <snapshot id>); SELECT * FROM acme_corp.demo.cities AT (TIMESTAMP => TIMESTAMP '2026-09-28 12:00:00');DuckDB has no rollback; time travel shows PyIceberg's.
Public catalogs read with no account at all, through
iceberg_scanover plain HTTPS. With any account, attach one by name (lhbox duckdb --catalog <handle>/<catalog>): public catalogs. Run on 2026-09-29 with DuckDB 1.5.5 and no account, a 249-row table answeredcount(*)in 0.8 s.In the browser, DuckDB-WASM is single-threaded with a 4 GB address space. Use it to look at a table and for selective queries, not for full scans of large tables (performance).
Writing
CREATE TABLE … AS, INSERT, UPDATE, DELETE, MERGE and DROP TABLE work against the catalog (a recorded run against the live service, 2026-09-20, DuckDB 1.5.5). On format-version 3 tables, DuckDB writes row-level deletes as deletion vectors. UPDATE and DELETE there work since 2026-09-28 (a DuckDB 1.5.5 probe, 16 of 16 checks). Updates and deletes one DuckDB version wrote read back the same in another (2026-09-29).
Importing files: one commit for many Parquet or CSV files
lhbox table import demo.cities 'data/*.parquet' # a new table (<catalog>.demo.cities with several)
lhbox table import demo.cities more/*.parquet --mode append # into it
lhbox table import demo.places 'data/*.parquet' --format-version 3 # geometry stays typed
lhbox table import demo.orders 'exports/*.csv' # CSV, detected by the extension (or --format csv)
lhbox table import demo.cities 'data/*.parquet' --python ~/venv/bin/python --full # that venv's DuckDB; print the SQL
table import runs DuckDB 1.5.5 or newer on your machine. It uses the duckdb Python module of --python PATH or LHBOX_PYTHON when given. Otherwise it uses the module of the interpreter running lhbox, else the duckdb binary.
It reads every file in one statement, so the whole set is one Iceberg commit. That statement is read_parquet([every file], union_by_name=true), or read_csv when every file ends in .csv or --format csv says so. Importing files one statement at a time is one commit per file: 44 files were 44 commits and 44 waits. Globs expand on your side. DuckDB's httpfs reads s3:// and https:// sources.
The report says rows, files, bytes, seconds, commits (1) and the path taken. --full adds the generated SQL statements. The path depends on the format version:
- Format version 2 (the catalog's default unless changed): DuckDB's own
CREATE TABLEwith the inferred column types, then oneINSERT. - Format version 3 (
--format-version 3, or the catalog's default): the table is created throughPOST /v1/tableswith the schema DuckDB inferred, geometry columns typed. OneINSERTthen fills it.
Either way, the table gets write.target-file-size-bytes = 134217728 and write.parquet.compression-codec = zstd before its first row. The rows are written ORDER BY the first DATE/TIMESTAMP column, so a query on that column skips whole files. --order-by names another column, and --no-order keeps the files' order. Why it matters: how tables are laid out.
Measured 2026-09-21 from a laptop: three Parquet files, 3000 rows, one commit, 3.96 s. An append took 1.21 s. Everything else about imports: importing files.
The same by hand
In any DuckDB session with the recipe pasted:
CREATE SCHEMA IF NOT EXISTS acme_corp.demo; -- CTAS needs the schema; the API creates it on demand
CREATE TABLE acme_corp.demo.cities AS SELECT * FROM read_parquet(['a.parquet', 'b.parquet'], union_by_name=true);
INSERT INTO acme_corp.demo.cities SELECT * FROM read_parquet('more-cities.parquet'); -- into a table that exists
COPY acme_corp.demo.cities FROM 'more-cities.parquet' (FORMAT PARQUET);
CREATE TABLE acme_corp.demo.orders AS SELECT * FROM read_csv(['a.csv', 'b.csv'], union_by_name=true); -- CSV: the list in ONE statement
-- CREATE OR REPLACE TABLE is not supported on an Iceberg catalog. To rebuild the rows in one commit:
BEGIN; DELETE FROM acme_corp.demo.orders; INSERT INTO acme_corp.demo.orders SELECT * FROM read_csv('c.csv'); COMMIT;
Put the whole file list in one statement: 40 files in one statement took 2.4 s, one by one 66 s (2026-09-20). Things to know when DuckDB writes on its own:
- DuckDB's own
CREATE TABLEmakes a format-version 2 table unless the statement asks for 3, whatever the catalog'sdefault_format_version: DuckDB names the version in every create it sends and does not read a default from the catalog. Ask for 3 in the statement:CREATE TABLE acme_corp.demo.places (id INT, geom GEOMETRY) WITH ('format-version' = '3'), orCREATE TABLE acme_corp.demo.places WITH ('format-version' = '3') AS SELECT …. Both made version 3 tables thatINSERTthen filled, a geometry column included (DuckDB 1.5.6, 2026-11-15). Every column of aCREATE TABLE … ASis optional, with no identifier fields and no partitioning. For any of those, create the table first (lhbox table create, below) andINSERTinto it. - Settings a table needs before its first row. A table DuckDB created has neither the 128 MiB file size nor ZSTD. DuckDB writes SNAPPY by default: a 5,210-polygon table took 722 MB as SNAPPY and 461 MB as ZSTD (DuckDB 1.5.6, 2026-10-02). Set them with
CALL set_iceberg_table_properties('acme_corp.demo.cities', {'write.parquet.compression-codec': 'zstd'}). For the file size,ALTER TABLE … SET (…)stored a property under another name, which the writer ignored. ThenDETACHandATTACHthe catalog again before theINSERT. In the same attached session, DuckDB kept the table metadata it had loaded and still wrote SNAPPY. Performance has the full loading recipe. CREATE OR REPLACE TABLEis refused. To rebuild a table's rows,BEGIN; DELETE FROM t; INSERT INTO t SELECT …; COMMIT;is one commit: readers see the old rows until it lands, and a run that fails or is killed beforeCOMMITleaves them (measured on DuckDB 2.0.0 and 1.5.6, 2026-11-15). A change of schema needsDROP TABLE, thenCREATE TABLE: two commits, with a few seconds in which readers get "table does not exist". Swapping in a table built aside by name is not atomic either: DuckDB 2.0 refusesDROPandALTER TABLE … RENAMEin one transaction.- Retry on 409. Another writer, or the platform's maintenance, can commit between your read and your commit. DuckDB then raises
CommitFailedException … branch "main" has changedand does not retry. An unattended job should catch it, wait a few seconds and re-run the statement (gotchas).
Geometry and geography
Geometry columns: create the table first, then INSERT
lhbox table create acme_corp.demo.places --format-version 3 --column id:long:required:identifier --column name:string --column geom:geometry
-- then, in DuckDB with the recipe pasted (INSTALL spatial; LOAD spatial;):
INSERT INTO acme_corp.demo.places SELECT id, name, ST_Point(lon, lat) FROM read_csv('places.csv');
SELECT id, ST_AsText(geom) FROM acme_corp.demo.places LIMIT 3; -- POINT (…)
A geometry column needs an Iceberg format-version 3 table. DuckDB's own CREATE TABLE … AS SELECT ST_Point(…) cannot make one: DuckDB creates a version-2 table whatever the catalog's default_format_version. The catalog then answers the CTAS with a 400 that names no cause. This is a known limitation, and the two-step path above is the way:
lhbox table create --format-version 3 --column geom:geometry(geometry(EPSG:xxxx)for a CRS);- a plain
INSERT … SELECTfrom DuckDB.
That was measured at 0.8 s for a small table, and read back as POINT. lhbox table import … --format-version 3 does exactly this for a GeoParquet. DuckDB 1.5.5 and newer keep the column's CRS (verified with geometry(EPSG:28992) and geometry(EPSG:2913)).
Measured against the service: a million rows (18 MB of Parquet) in about 2.3 s from a laptop, three data files, one commit; format-version 2.
Filter with &&, and use DuckDB 1.5.6 or 2.0.0
DuckDB skips data files (by the geometry bounds in the table's manifests) and row groups (by the native geometry statistics) for the bounding-box operator &&. It never skips them for ST_Intersects alone. Put both in the query: && narrows the data, and ST_Intersects gives the exact answer.
SELECT * FROM acme_corp.demo.places
WHERE geom && ST_MakeEnvelope(-3.72, 40.40, -3.68, 40.43)
AND ST_Intersects(geom, ST_MakeEnvelope(-3.72, 40.40, -3.68, 40.43));
- On a table
lhbox table importwrote (1.77 million parcels, 5 files), one point lookup read 444 MB withST_Intersectsalone. With&&added it read 5.3–6.7 MB (DuckDB 1.5.6, 2026-09-29). - On Overture tables of 1.2–5.7 GB, each box question read 2.5–9.3 MB in 1.6–4.9 s from a laptop in Madrid. The tables were Spain's 7.1 million road segments and 19.2 million buildings, and the world's 81 million places. In DuckDB-WASM 1.5.6 in a browser tab, anonymously over the public URLs, each took 1.3–3.6 s (2026-10-03). Every answer matched a count made without Iceberg.
- DuckDB 1.5.5 returns rows with a NULL geometry as
&&matches. WithST_Intersectsalongside, the answer is right on 1.5.5 too.
GeoParquet: the geo metadata becomes table properties
Until Iceberg v3 geometry is readable by every engine, an imported GeoParquet's geo metadata lives in the table's properties. DuckDB spatial can rebuild the GeoParquet from them on export. table import reads the file's geo key (parquet_kv_metadata) and sets these through PATCH /v1/table:
geo.encoding(WKB);geo.primary_column;geo.crs:EPSG:xxxxwhen the PROJJSON carries an authority id, else the PROJJSON text, andOGC:CRS84when the file names none;geo.columns: the columns object as JSON (encoding, geometry_types, bbox), merged across files;geo.version;geo.crs.projjson, when there was a PROJJSON.
Into a format-version 2 table, the geometry column is written as WKB binary, as geo.encoding says. Into a format-version 3 table it stays a typed geometry(<crs>) column, with the same properties. lhbox table get shows them. lhbox table set-properties --property geo.crs=EPSG:28992 sets or corrects any of them.
A table registered over existing GeoParquet files (PyIceberg add_files, geometry as WKB) fails in DuckDB with failed to cast column "geometry" from type GEOMETRY(…) to BLOB. Run SET enable_geoparquet_conversion = false; before the query, and it reads (2026-10-02).
Geography: not in DuckDB yet
DuckDB has no GEOGRAPHY type ("Type with name GEOGRAPHY does not exist"), so it cannot write one. It also cannot open a table that has a geography column at all, not even to read that table's geometry column ("Not implemented Error: Geography support"). Measured on 2026-10-03 with DuckDB 1.5.6, a 2.0 nightly and DuckDB-WASM 1.5.6.
Keep a geometry-only copy of such a table for DuckDB. For spherical questions, use Snowflake or Spark with Apache Sedona (Spark).
Performance
What a DuckDB query reads is decided by how the table was written. How tables are laid out has the measurements (TPC-H-derived, DuckDB 1.5.5). In short, for DuckDB as the writer:
- Large, sorted files. DuckDB's Iceberg writer honours
write.target-file-size-bytes(128 MiB on every table LakehouseBox creates). It keeps anORDER BYonly withpreserve_insertion_orderon. Across files, it keeps it only in a single-threaded write, because its parallel writers each take a slice of the sorted stream. - Row groups. DuckDB ignores
write.parquet.row-group-limitand writes 122,880-row row groups by default. It will not cut a row group below 2,048 rows, whateverROW_GROUP_SIZEsays (DuckDB 1.5.6, 2026-10-02). - Geometry tables that
lhbox table importwrites are in Hilbert order of the primary geometry. They are written by one thread with 8 MB row groups, so nearby features share files and row groups. On Overture's places (7.26 million points), a box around central Madrid read 1.1–1.8 MB, against 25.8–27.4 MB from the same data as a version-2 WKB table. The price: a whole-table aggregate made 399 requests in 3.5 s instead of 16 in 1.0 s (DuckDB 1.5.6, 2026-10-02). - Large polygons do not cluster. With that 2,048-row floor, 5,210 country and region polygons (461 MB) make 2–3 row groups whatever the order. A point lookup then reads the whole table. Order such a table by an attribute you filter on instead (
--order-by country_code). A one-country query then read 247 MB, against 237 MB from the same data as a version-2 table.
Known limitations
As measured with DuckDB 1.5.6 (2026-10-03) unless dated otherwise:
- No geography: no type to write, and a table with a geography column cannot be opened (above).
CREATE TABLE … ASmakes version-2 tables only, so a geometry CTAS fails with a 400. Create the table first.CREATE OR REPLACE TABLEis refused on an attached catalog.- No retry on 409: the caller retries.
- No rollback: use PyIceberg.
- Spatial filters prune only with
&&, and only on 1.5.6 without the NULL caveat. 2.0.0 prunes; the caveat is not measured on it yet. - DuckDB now and then commits a 0-row data file (1 of 44 files for the 81-million-row places, single-threaded). It costs one manifest entry and nothing else.
- Other engines and DuckDB's geometry tables. Spark 4.1 with Iceberg 1.12 reads every value. Trino 483 reads them without spatial pruning. Snowflake fails on large DuckDB-written point and polygon tables; it reads line tables (Snowflake). iceberg-go 0.7.0 cannot read them ("only parquet format is implemented, got parquet").
- DuckDB 2.0.0 (released 2026-10-21) prunes with
&&through Iceberg. The 2.0 nightlies before 19 October did not.- On
2.0.0.dev2609250715(2026-09-29), a point lookup that 1.5.6 answers from 5.0–6.7 MB read 793 MB, every file. - On
2.0.0.dev2610011535, the Overture box questions read 1.1–4.0 GB instead of 2.5–9.3 MB. - Two fixes, both from our reports, are in 2.0.0: the Iceberg scan (duckdb/duckdb-iceberg#1464) and DuckDB itself (duckdb/duckdb#27249).
- On 2.0.0, a box query on a public rail table reads 11.5 MiB in 12 GETs (1.5.6: 9.4 MiB), with the same answer (2026-10-21).
- The rest of our contract held on 2.0.0 (2026-10-21). Its version-2 and version-3 writes, updates and deletes read the same in 1.5.6, and the store maintains its tables.
- On
- The browser build cannot read a GeoParquet file DuckDB itself wrote (
Invalid Error: stoi: no conversion, DuckDB-WASM 1.5.5 and 1.5.6, 2026-09-29). Such a file may not preview in the account page's Import dialog; a GDAL-written GeoParquet reads. Import it withlhbox table importinstead.