Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 47 ยท Data Formats and Storage

Partitioning, Bucketing and File Layout

Organize big data files so queries read less: directory partitioning, partition pruning, bucketing, the small-files problem and compaction, with runnable simulations.

A good file format (Parquet and ORC for example) lets a reader skip rows inside a file. Better still is never to open the file at all. How you arrange files into directories and how big you make them often matters more for query speed than any engine setting. This lesson covers the three layout tools of a Hadoop data lake: partitioning, bucketing and compaction.

The commands, DDL and configuration are read-only, because there is no cluster on this site. The runnable examples are Python simulations of the logic: which files a query would open, how rows map to buckets and how small files merge.

Directory partitioning

Partitioning stores data in subdirectories named after column values. A table of web events partitioned by date and country looks like this in HDFS (or an object store):

TEXT
/warehouse/events/
  dt=2026-03-01/
    country=DE/part-00000.parquet
    country=FR/part-00000.parquet
  dt=2026-03-02/
    country=DE/part-00000.parquet
    country=FR/part-00000.parquet

The values dt=2026-03-01 are not stored inside the files. They are part of the path, and engines such as Hive and Spark add them back as ordinary columns. This is the "Hive-style" layout, and almost every tool understands it.

What partitioning buys

Folders as index The path holds the column value.
Pruning A filter on the column skips whole folders.
Easy retention Drop old data by deleting a folder.
1 / 2

Partition pruning

When a query filters on a partition column, the engine looks at the directory names and reads only those that match. This is partition pruning, and it happens at planning time, before any data is opened.

SQL
-- read-only HiveQL
CREATE EXTERNAL TABLE events (user_id BIGINT, url STRING)
PARTITIONED BY (dt STRING, country STRING)
STORED AS PARQUET
LOCATION '/warehouse/events';

SELECT COUNT(*) FROM events
WHERE dt = '2026-03-02' AND country = 'DE';   -- reads one directory

Compare with a filter on a normal column, such as url: the engine has to open every file (and rely on statistics inside the files). Pruning is far cheaper, because it avoids even the metadata and block lookups for the skipped files.

Python Which files does the query open?

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The date filter and the country filter each prune a lot, and both together read a single file. The last query shows the cost of a missing filter: all 28 files. That is why teams often require a partition filter in queries on big tables.

Choosing partition columns

  • Pick columns that queries filter on every time, almost always a date or hour.
  • Pick low cardinality: tens or hundreds of values, not millions. Partitioning by user_id creates one folder per user, which overwhelms the NameNode (see NameNode HA, Federation and Operations) and produces thousands of tiny files.
  • Do not partition so finely that a partition is smaller than one HDFS block (128 MB). A daily partition of 5 MB is a bad idea; use monthly partitions or none.

Bucketing

Partitioning suits columns with few values. For a high-cardinality column like user_id, use bucketing: a hash of the column assigns each row to one of a fixed number of buckets, and each bucket is a file.

SQL
-- read-only HiveQL
CREATE TABLE users_b (user_id BIGINT, name STRING)
CLUSTERED BY (user_id) INTO 8 BUCKETS
STORED AS ORC;

Bucketing helps in two ways:

  1. Joins without a full shuffle. If two tables are bucketed on the join key with the same (or a multiple of the) bucket count, bucket n of one table can only match bucket n of the other. Each pair joins locally, with no network shuffle (a bucket map join, or in Spark a shuffle-free sort-merge join).
  2. Sampling and even file sizes. TABLESAMPLE (BUCKET 1 OUT OF 8) reads one file for a quick sample.

Remember the partitioner from Inside the Shuffle and Sort: bucketing is the same trick, hashing a key to a number, but done once at write time so you never repeat it.

Python Bucketing rows by a hash

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Every order finds its user in the same-numbered bucket, so the join needs only local work. Notice the buckets are not perfectly even (hashing is random-like), which is fine.

The small-files problem

Every file in HDFS costs the NameNode memory, and every file a query opens costs a task or at least a metadata round trip. Streaming and frequent batch writes produce thousands of files of a few megabytes, and a table built this way queries slowly even if it is small in total.

Typical causes:

  • Over-partitioning: each partition gets a handful of rows.
  • Too many writers: 200 reducers or Spark tasks each write one file per partition they hold.
  • Frequent small appends: a streaming job commits every minute.

The cure is compaction: periodically read the small files of a partition and rewrite them as a few files near the target size (128 MB to 1 GB). Options:

  • Spark: df.repartition(n).write... or coalesce(n) before writing.
  • Hive: ALTER TABLE ... PARTITION (...) CONCATENATE for ORC, or ACID table compaction.
  • HDFS: hadoop archive (HAR) to pack files, or merge jobs with a map-only job.
  • Table formats such as Iceberg and Delta (see Data Lakes, Warehouses and Lakehouses) offer built-in compaction commands.
Python Compacting small files

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Thirty files become two (121 MB and 46 MB): the NameNode tracks far fewer objects and a scan starts two tasks instead of thirty. The 150-bytes-per-object figure is the common rule of thumb, not an exact number.

Putting it together

A solid layout for an event table: partition by date (maybe also hour for very large data), store as Parquet or ORC with ZSTD or Snappy, sort within files on the most-filtered column so statistics prune well, aim for 128 MB to 1 GB files, bucket on the join key only if a big repeated join justifies it, and run a compaction job on yesterday's partitions every night. Add a rule to require partition filters on the biggest tables.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Where is the value of a Hive-style partition column such as dt=2026-03-01 stored?

    The value is part of the path. Engines read it from the directory name and expose it as a column.
  2. 2. Which column is the best partition column for a table with billions of events?

    A date has low cardinality, is filtered on in almost every query and produces well-sized partitions. High-cardinality columns create too many tiny folders and files.
  3. 3. What is the main benefit of bucketing two tables on the same join key with the same bucket count?

    Equal keys hash to the same bucket number in both tables, so matching rows are always in the same-numbered buckets.
  4. 4. A table has 50,000 files of 2 MB each. What is the usual remedy?

    Many small files burden the NameNode and make scans slow. Compaction rewrites them into a few large files.
  5. 5. Why should you avoid partitioning a table by user_id?

    One folder per user means millions of small files. Use bucketing for high-cardinality columns.

Summary

  • Partitioning puts column values in directory names, so filters on them prune whole folders at planning time.
  • Partition on low-cardinality columns that queries always filter on, typically the date.
  • Bucketing hashes a high-cardinality key into a fixed number of files and enables shuffle-free joins and sampling.
  • The small-files problem hurts the NameNode and the scan; prevent it with sensible partitions and fix it with compaction.
  • Aim for files of 128 MB to 1 GB, in a columnar format, sorted on the main filter column.

Next: Hive: SQL over Files.