Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 36 ยท Tables, Partitions and Buckets

Bucketed Tables and Sampling

Learn Hive bucketing: CLUSTERED BY and INTO n BUCKETS, how rows are assigned by hash, bucket map joins, sorted buckets, TABLESAMPLE for data sampling, and partitioning versus bucketing.

Partitioning splits a table by a column with few values. But what about a column with millions of values, such as user_id? A folder per user would be a disaster. Bucketing solves it differently: it hashes the column into a fixed number of buckets (files), so rows with the same key always land in the same file. Bucketing makes joins and sampling cheaper, and can be combined with partitions.

Bucketing is a Hive storage feature, so the DDL is read-only. The runnable editors are SQLite stand-ins that compute the same kind of bucket number with modulo arithmetic (id % n). Hive uses its own hash function, so real bucket numbers differ from ours.

Creating a bucketed table

SQL
CREATE TABLE customers_b (
  id    BIGINT,
  name  STRING,
  city  STRING
)
CLUSTERED BY (id) INTO 8 BUCKETS
STORED AS ORC;

Hive assigns each row to a bucket with hash(id) mod 8 and writes one file per bucket (per partition). Rows with the same id are always in the same file. In Hive 2 and later, bucketing is enforced automatically on INSERT; in Hive 1 you had to SET hive.enforce.bucketing=true;.

Each row's bucket can be computed by hand; the stand-in below uses id % 4 as a simple hash to show how rows spread over buckets:

SQL Stand-in: assign rows to 4 buckets with modulo (SQLite, not Hive's hash) ยท database: bookstore

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

A good hash gives buckets of roughly equal size. If the bucketing column is skewed (a few ids with most of the rows), some buckets get heavy while others stay nearly empty.

Bucketing together with partitions

SQL
CREATE TABLE events (
  user_id BIGINT,
  action  STRING,
  ts      TIMESTAMP
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) SORTED BY (ts) INTO 16 BUCKETS
STORED AS ORC;

The folder dt=2025-03-01/ now holds 16 files. SORTED BY (ts) stores the rows of each bucket sorted, which allows even faster merge joins and better compression.

flowchart TD
    T["events"] --> P1["dt=2025-03-01/"]
    T --> P2["dt=2025-03-02/"]
    P1 --> B0["bucket 0"]
    P1 --> B1["bucket 1"]
    P1 --> B2["... bucket 15"]
    P2 --> C0["bucket 0"]
    P2 --> C1["bucket 1"]

Partition by day (few values), bucket by user (many values).

Why bucket? Faster joins

If two tables are bucketed on the join key with the same (or a multiple) number of buckets, matching rows are in matching files. Hive can join bucket 3 of one table with bucket 3 of the other, with no shuffle of the whole data:

SQL
SET hive.optimize.bucketmapjoin=true;
SET hive.optimize.bucketmapjoin.sortedmerge=true;   -- if both are also sorted on the key

SELECT c.name, COUNT(*) AS purchases
FROM customers_b c
JOIN purchases_b p ON c.id = p.customer_id
GROUP BY c.name;

Requirements: same key, bucket counts that are equal or multiples, and the data must actually have been written bucketed (data copied with LOAD DATA is not re-bucketed; use INSERT ... SELECT). Join strategies are explored in Joins and Optimization.

Sampling with TABLESAMPLE

Bucketing enables cheap, repeatable samples. TABLESAMPLE (BUCKET x OUT OF y ON col) reads only part of the buckets:

SQL
-- bucket 1 out of 8: roughly 1/8 of the data (reads only one file if the table is bucketed on id)
SELECT * FROM customers_b TABLESAMPLE (BUCKET 1 OUT OF 8 ON id);

-- block sampling on any table: by percent, size or rows
SELECT * FROM big_table TABLESAMPLE (10 PERCENT);
SELECT * FROM big_table TABLESAMPLE (100M);
SELECT * FROM big_table TABLESAMPLE (1000 ROWS);

BUCKET x OUT OF y on an unbucketed table still works, but then Hive has to scan the full table and filter by hash. Sampling by rows is per input split, so 1000 ROWS means 1000 rows per split, not 1000 in total. Use sampling for exploration and for developing queries before running on all data.

A portable way to take a repeatable sample by key hash, imitated with modulo:

SQL Stand-in: repeatable 1-in-3 sample by key (SQLite, not Hive) ยท database: bookstore

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

Unlike ORDER BY rand() LIMIT n, hash-based samples are the same each time, so results can be compared between runs. And rand() forces a full sort and a single reducer.

Partitioning versus bucketing

Partitioning Bucketing
Splits data into Folders Files within a folder
Column cardinality Low (days, countries) High (user ids)
Number of splits Varies with data Fixed in the DDL
Main benefit Skip folders (pruning) Faster joins, sampling, even file sizes
Value stored In the folder name In the file
Changing it Add partitions any time Needs a rewrite of data

They combine well: partition by date, bucket by an id.

Choosing the number of buckets

Pick a number so that each bucket file is a decent size (hundreds of MB, about 256 MB is typical), and use a power of two so another table can use a multiple of it. Too many buckets produce small files again; too few limit parallelism. Because changing it means rewriting data, plan for growth.

Hive 4: bucketing improvements

Newer versions of Hive support bucketing for Iceberg tables with partition transforms (bucket(16, id)), which replaces the classic design for new tables. Classic CLUSTERED BY is still very common in existing warehouses.

Common mistakes

  • Expecting LOAD DATA to bucket files (it just moves them).
  • Joining tables whose bucket counts are not multiples of each other.
  • Bucketing on a skewed column or one with many NULLs.
  • Using ORDER BY rand() for sampling on large tables.
  • Choosing too many buckets for small tables.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does Hive decide which bucket a row goes to?

    Equal keys always hash to the same bucket.
  2. 2. When is bucketing preferred over partitioning?

    High-cardinality columns would create far too many partition folders; buckets keep a fixed number of files.
  3. 3. What is required for a bucket map join between two tables?

    Matching bucket files can be joined pairwise without shuffling all the data.
  4. 4. Why is TABLESAMPLE (BUCKET 1 OUT OF 8 ON id) better than ORDER BY rand() LIMIT n for sampling?

    Hash-based sampling is deterministic and cheap; rand() requires a sort on one reducer.

Summary

  • CLUSTERED BY (col) INTO n BUCKETS hashes rows into a fixed number of files; SORTED BY orders each file.
  • Bucketing suits high-cardinality keys and is often combined with date partitions.
  • Matching bucketed tables enable bucket map joins; TABLESAMPLE gives cheap repeatable samples.
  • Data must be written through INSERT, not LOAD DATA, to be truly bucketed; choose bucket counts as powers of two.

Next: Join Types in HiveQL