Globs and Hive Partitioning

Querying Many Files with Globs and Hive Partitioning

Data lakes keep tables as many files; DuckDB 61,228 's readers accept globs (*, ** for any depth) and lists of paths. The Hive 129 layout puts column values in directory names, year=2026/month=6/, so a reader can skip whole directories. DuckDB detects such paths, turns year and month into columns and pushes filters on them down to the file list:

474-hive.sql: a Hive-partitioned copy of the orders and a pruned querySQL

SET TimeZone = 'UTC';
COPY (SELECT *, year(order_ts) AS year, month(order_ts) AS month FROM 'files/orders.parquet')
  TO 'files/lake_orders' (FORMAT parquet, PARTITION_BY (year, month), OVERWRITE_OR_IGNORE);
SELECT count(*) AS files, min(file) AS first FROM glob('files/lake_orders/*/*/*.parquet');
EXPLAIN ANALYZE
SELECT month, count(*) AS orders, sum(total) AS revenue
FROM read_parquet('files/lake_orders/*/*/*.parquet', hive_partitioning = true)
WHERE year = 2026 AND month >= 5 GROUP BY ALL;

Output

┌───────┬────────────────────────────────────────────────────┐
│ files │                       first                        │
│ int64 │                      varchar                       │
├───────┼────────────────────────────────────────────────────┤
│    18 │ files/lake_orders/year=2025/month=1/data_0.parquet │
└───────┴────────────────────────────────────────────────────┘
...
│       File Filters:       │
│ (year = 2026)(month >= 5) │
│                           │
│    Scanning Files: 2/18   │
...

PARTITION_BY wrote one directory per month, 18 files; the query opened 2 of them (11,090 orders, 189,360.74 and 187,963.78 of revenue for May and June). Partition columns live only in the paths and come back as guessed types unless you pass hive_types. Partition on the columns queries filter on, at a grain that leaves files of tens of megabytes: thousands of tiny files make listing and opening them the bottleneck, which the table formats of Lakehouses, Data Quality and Governance solve with manifests.

MENU