Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 38 ยท Reading Files and Formats

Many Files, Globs and Hive Partitions

Query hundreds of CSV, Parquet or JSON files as one table in DuckDB: glob patterns, file lists, union_by_name, the filename column, Hive-partitioned folders and partition pruning.

Real data rarely sits in one tidy file. It arrives as one file per day, per customer or per export, often hundreds of them in a folder tree. DuckDB lets you treat all of them as one table, using a pattern in place of a file name. It is the feature that makes a laptop behave like a small data lake engine.

File access cannot run in the page, so DuckDB examples here are read-only. The runnable stand-ins imitate "many files" with one table that has a source_file column, which is exactly the shape DuckDB gives you when you ask for filename = true.

A folder of files

Suppose a folder holds monthly exports:

TEXT
sales/
  2026-01.csv
  2026-02.csv
  2026-03.csv

One query reads them all:

SQL
-- DuckDB (read-only example)
SELECT * FROM 'sales/*.csv';
SELECT * FROM read_csv('sales/2026-0*.csv');
SELECT * FROM read_csv(['sales/2026-01.csv', 'sales/2026-03.csv']);

The result is the rows of every matching file stacked together (a UNION ALL), and the files are read in parallel.

Glob patterns

A glob is a pattern with wildcards:

Pattern Matches
* Any run of characters within one folder level
? Exactly one character
[0-9] One character from a set or range
** Any number of nested folders
SQL
-- DuckDB (read-only example)
SELECT count(*) FROM 'logs/*.parquet';              -- files in logs/
SELECT count(*) FROM 'logs/**/*.parquet';           -- logs/ and all subfolders
SELECT count(*) FROM 'logs/2026-0[1-3]-*.json';     -- a range of dates

To see which files a pattern matched, use the glob() table function:

SQL
-- DuckDB (read-only example)
SELECT * FROM glob('sales/*.csv');

Knowing which file a row came from

Add filename = true and every row gets a column with its source path. This is very useful when the file name carries information (a date, a customer id, a region):

SQL
-- DuckDB (read-only example)
SELECT filename,
       regexp_extract(filename, '(\d{4}-\d{2})', 1) AS month,
       count(*) AS rows,
       sum(amount) AS revenue
FROM read_csv('sales/*.csv', filename = true)
GROUP BY ALL
ORDER BY month;

Here is the same idea on a table that already carries a source column:

SQL Stand-in: rows tagged with their file ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

When the files differ: union_by_name

Files from different months might have slightly different columns: one added discount, another has the columns in a different order. By default DuckDB takes the schema from the first file and expects the rest to match by position. union_by_name = true matches columns by name and fills missing ones with NULL:

SQL
-- DuckDB (read-only example)
SELECT * FROM read_csv('sales/*.csv', union_by_name = true, filename = true);
SELECT * FROM read_parquet('exports/*.parquet', union_by_name = true);

Think of it as UNION ALL BY NAME, which you saw in Friendly SQL. The stand-in below shows the effect with two "files" of different columns:

SQL Stand-in: union by name with different columns

Press Run (or Ctrl + Enter) to see the result here.

Hive partitioning

A partitioned dataset stores each value of a column in its own folder, named column=value. This layout is the standard in data lakes and is called Hive-style partitioning:

TEXT
events/
  year=2025/
    month=11/data.parquet
    month=12/data.parquet
  year=2026/
    month=01/data.parquet

Partition columns are not stored inside the files. They live in the folder names. When you read with hive_partitioning = true (DuckDB usually detects it automatically for key=value folders), they appear as ordinary columns:

SQL
-- DuckDB (read-only example)
SELECT year, month, count(*) AS events
FROM read_parquet('events/**/*.parquet', hive_partitioning = true)
GROUP BY ALL
ORDER BY year, month;

Partition pruning

The real benefit is speed. A filter on a partition column removes folders from the plan before any file opens:

SQL
-- DuckDB (read-only example)
SELECT count(*)
FROM read_parquet('events/**/*.parquet', hive_partitioning = true)
WHERE year = 2026 AND month = 1;

Only events/year=2026/month=01/ is read. The other files are never opened, so the query scales with the data you ask for, not the size of the dataset.

flowchart LR
  Q["WHERE year = 2026 AND month = 1"] --> P{"Prune by folder name"}
  P -->|skip| A["year=2025/month=11"]
  P -->|skip| B["year=2025/month=12"]
  P -->|read| C["year=2026/month=01"]

Writing partitioned data

COPY ... PARTITION_BY builds that layout for you:

SQL
-- DuckDB (read-only example)
COPY (SELECT *, year(ordered_at) AS year, month(ordered_at) AS month FROM orders)
TO 'events' (FORMAT parquet, PARTITION_BY (year, month), OVERWRITE_OR_IGNORE);

Choose partition columns that you filter on often and that have a modest number of values (year, month, region). A column with millions of distinct values (like a user id) creates millions of tiny files, which is slower than not partitioning.

The stand-in imitates pruning: counting orders for one month requires only the "folder" for that month.

SQL Stand-in: filtering on a partition column ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Practical tips

  • Keep files similar in size, tens to hundreds of MB each for Parquet.
  • Prefer Parquet over CSV for partitioned lakes: faster and typed.
  • Make a view over a glob so analysts query a stable name: CREATE VIEW sales AS SELECT * FROM read_parquet('sales/**/*.parquet', hive_partitioning = true);
  • If a glob matches no files DuckDB raises an IO error, so check the path first with glob().
  • Mixed schemas? Use union_by_name; mixed formats need separate queries.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does FROM 'logs/**/*.parquet' read?

    ** matches any number of nested folders, and *.parquet matches the files inside.
  2. 2. What does filename = true add?

    It tells you which file every row came from, which is handy when names hold dates or ids.
  3. 3. What does union_by_name = true do when files have different columns?

    Columns are aligned by name, so files with extra or reordered columns can be combined.
  4. 4. Where are Hive partition values stored?

    Partition columns live in the path, and DuckDB turns them into columns when reading.
  5. 5. Why is filtering on a partition column fast?

    Partition pruning avoids opening files in folders that fail the filter.

Summary

  • Globs (*, ?, [..], **) and lists make many files behave like one table.
  • filename = true records the source; union_by_name aligns differing schemas.
  • Hive partitioning stores values in key=value folders; they become columns when read.
  • Filters on partition columns prune whole folders; COPY ... PARTITION_BY writes the layout.
  • Pick partition columns with few values and keep file sizes reasonable.

Next: Writing files and remote data