Hive partitioning
Hive partitioning is a directory-naming convention, originally from Apache Hive, that encodes column values in folder paths (like year=2026/month=01/) so query engines can skip irrelevant files without reading them.
Overview
Hive-style partitioning organizes files on disk (or in object storage) into nested directories named after column values, using a key=value convention. For example, a table partitioned by year and month might be laid out as:
Copy code
orders/
year=2025/
month=11/
part-0001.parquet
year=2026/
month=01/
part-0001.parquet
A query engine that understands this convention can look at the WHERE clause of a query, determine which partition directories could possibly contain matching rows, and skip reading every other directory entirely — a technique called partition pruning.
Why it originated in Hive
This layout comes from Apache Hive, which used directory structure as its primary mechanism for organizing and pruning large tables on HDFS. The convention outlived Hive itself and is now used broadly across the data lake ecosystem, independent of any particular query engine.
Tradeoffs
Hive partitioning is simple and works with any file format, but it has limits: too many small partitions ("over-partitioning") creates a large number of tiny files, which hurts performance, and changing partition columns requires physically rewriting the directory layout. Modern table formats like Apache Iceberg address this with "hidden partitioning," tracking partition transforms in metadata rather than requiring a specific folder structure.
DuckDB and Hive partitioning
DuckDB recognizes Hive-partitioned paths automatically when reading Parquet or CSV files, exposing the partition keys as regular columns:
Copy code
SELECT year, month, SUM(amount) AS total
FROM read_parquet('s3://bucket/orders/*/*/*.parquet', hive_partitioning = true)
WHERE year = 2026
GROUP BY ALL;
DuckDB will skip files outside year=2026 entirely, without needing to open them.
Related terms
Apache Hive is a data warehouse system built on Hadoop that lets users query large datasets stored in HDFS or object storage using HiveQL, a SQL-like language, which Hive translates into distributed jobs.
Partition pruning →Partition pruning is a query optimization that skips reading entire partitions of a dataset when a query's filters make it impossible for those partitions to contain matching rows.
Table format →A table format is a metadata layer on top of data files in a lake or object storage that defines what constitutes a table — its schema, partitioning, and file list — enabling ACID transactions, schema evolution, and time travel across multiple query engines.
Apache Iceberg →Apache Iceberg is an open table format that adds ACID transactions, schema evolution, and time travel to large analytic tables stored as files in a data lake.
PARTITION BY →PARTITION BY divides rows into independent groups for a window function or an output operation, so calculations reset for each group without collapsing rows the way GROUP BY does.
partitions →Partitions in data systems refer to the logical or physical division of large datasets into smaller, more manageable segments.
FAQS
Files are organized into nested folders named key=value, such as year=2026/month=01/, where each folder holds only the rows matching that value, letting query engines skip irrelevant folders.
DuckDB's read_parquet() and read_csv() functions accept a hive_partitioning option that detects key=value folder names in the file path, exposes them as query-able columns, and prunes folders that can't match a query's filters.
Over-partitioning creates many small files, which hurts read performance, and changing which columns you partition by requires physically rewriting the directory structure. Table formats like Apache Iceberg avoid this with hidden partitioning tracked in metadata instead of folder names.
