Hive中分区（Partitioning）与分桶（Bucketing）的区别及适用场景

Hey there! If you've been working with Hive for any length of time, you've probably wondered when to use partitioning vs bucketing. Let's break down their key differences, plus the scenarios where each shines.

First, let's get clear on how they're fundamentally different:

• Data splitting logic:
  Partitioning splits data into directories based on a business-specific field (like date, region, or department). Each partition maps to a separate subdirectory in your Hive warehouse. Bucketing uses a hash function on a field (like user ID or order ID) to split data into a fixed number of files (buckets) within the table's main directory.
• Field cardinality fit:
  Partitioning works best for fields with low to medium cardinality—think dates (365 values a year) or regions (dozens of values). Using a high-cardinality field (like user ID) for partitioning would create millions of tiny directories, which cripples performance. Bucketing is built for high-cardinality fields: the fixed number of buckets (say 100 or 1000) keeps your storage structure clean, even with millions of unique values.
• Query optimization approach:
  Partitioning speeds up queries by pruning irrelevant directories—if you query only data from "2024-05-20", Hive skips every other date's directory entirely. Bucketing speeds things up by hashing directly to the exact bucket containing your target data, plus it enables advanced optimizations like map-side joins (no expensive cross-cluster data shuffling) and fast data sampling.
• Data distribution consistency:
  Partitioning can lead to data skew (e.g., a holiday's log volume being 10x higher than a regular day). Bucketing uses hashing to ensure data is evenly distributed across buckets, avoiding oversized files or uneven workloads.

Partitioning is your go-to when:

• Queries frequently filter on a specific business dimension: For example, log analysis tools almost always query by date, or e-commerce reports filter by region/month. Partitioning lets Hive skip scanning irrelevant data entirely, cutting query time drastically.
• Data is naturally segmented by a business rule: If your data is split by business line, channel, or geographic region, partitioning makes it easy to manage access (e.g., give a sales team access only to their region's partition) and maintain data.
• Your dataset has grown to GB/TB scale: When small tables start getting large enough that full-table scans become slow, partitioning is often the first optimization to implement.

Example of creating a partitioned table:

CREATE TABLE user_orders (
    order_id INT,
    amount DOUBLE,
    product_id INT
) PARTITIONED BY (order_month STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

Bucketing is ideal when:

• You need to work with high-cardinality fields that can't be partitioned: Fields like user ID or order ID have millions of unique values—partitioning here is impossible, but bucketing into 500-1000 buckets lets you quickly locate data for a specific user/order.
• Join operations are a performance bottleneck: If you're joining two large tables on the same field (e.g., user_id), bucket both tables by that field. Hive can perform a map-side join, where only corresponding buckets are joined instead of shuffling entire datasets—this cuts join time by 70-90% in many cases.
• You need fast data sampling: Bucketed tables let you sample a fraction of data quickly (e.g., SELECT * FROM user_actions TABLESAMPLE(BUCKET 3 OUT OF 100)), which is perfect for exploratory analysis or testing queries on a subset of data.
• You want to avoid data skew: Partitioning can lead to uneven data sizes, but bucketing's hashing ensures data is evenly distributed across files, preventing slowdowns from oversized partitions.

Example of creating a bucketed table:

CREATE TABLE user_profiles (
    user_id INT,
    name STRING,
    email STRING
) CLUSTERED BY (user_id) INTO 500 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

内容的提问来源于stack exchange，提问作者sharath