Partitioning, bucketing and joins

Partitioning and Bucketing in HiveSparklingFuture · Partitioning and bucketing part 1Partitioning and Bucketing in HiveSparklingFuture · Partitioning and bucketing part 2Partition vs Bucketinglearn by doing it · Partition vs bucketingHive Tutorial #7: Learn Apache Hive Bucketing in less than 10 minutes Unboxing Big Data · Bucketing with an example

The problem: full scans do not scaleSearch on YouTubeBookmarkSaved

A 500 GB orders table grows every day. A query for the orders of 2026-08-11 still reads all 500 GB. It opens every file and every row, then throws away everything except one day.

The bigger the table gets, the worse this scales. Every query pays the full cost of the whole table, no matter how little of it the query needs.

The slides give two different fixes. Partitioning splits data into separate directories, so a query skips whole directories it does not need. It suits columns you filter on often. Bucketing splits data into a fixed number of files by a hash of a column value. It suits high-cardinality columns used in joins or sampling. Neither replaces the other. The two combine on the same table.

Managed and external tablesSearch on YouTubeBookmarkSaved

Before partitioning or bucketing, every Hive table is one of two types. The choice is made at CREATE TABLE time. A managed (also called internal) table is fully owned by Hive. Its files live inside Hive's own warehouse directory. An external table keeps only metadata in Hive: a schema and a LOCATION pointer. The files live wherever that location points, and something else may own them.

Creating each typeSearch on YouTubeBookmarkSaved

CREATE TABLE sales.orders (
  order_id INT, customer_id INT, amount DOUBLE, order_date STRING
)
STORED AS ORC;
-- data lands in the default warehouse path:
-- /user/hive/warehouse/sales.db/orders/

No LOCATION is given, so Hive picks the path inside its own warehouse directory.

CREATE EXTERNAL TABLE sales.orders_ext (
  order_id INT, customer_id INT, amount DOUBLE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION '/user/shared/orders_raw';

The EXTERNAL keyword plus an explicit LOCATION tells Hive to read data it does not own. A raw landing zone can be queried in place with no copy step. Engines such as Spark or Presto can read the same files.

What a DROP deletesSearch on YouTubeBookmarkSaved

DROP TABLE sales.orders;

DROP TABLE sales.orders_ext;
-- OK
-- metadata gone, but the file at
-- /user/shared/orders_raw is untouched

When to use eachSearch on YouTubeBookmarkSaved

Use a managed table when Hive should fully own the data lifecycle. It suits staging or intermediate tables that only Hive touches. It also suits the case where DROP TABLE should mean "delete everything" on purpose.

Use an external table when the data is shared with another team or tool. It suits a raw landing zone that you query in place. It suits files that Spark, Presto or another engine also reads. It also protects against an accidental DROP TABLE, since the files survive.

Partitioning on diskSearch on YouTubeBookmarkSaved

Partitioning splits a table into subdirectories based on the value of one or more partition columns. On HDFS it is only a directory structure:

/warehouse/sales.db/orders_partitioned/
  order_date=2026-08-10/
    000000_0
  order_date=2026-08-11/
    000000_0
  order_date=2026-08-12/
    000000_0

Each directory holds only the rows for that one partition value. The partition column itself is NOT stored inside the data files. Hive infers its value from the directory name. That is why it is sometimes called a virtual column.

The older deck shows the same idea with 50 thousand student or employee rows. Fetching the students of one course the traditional way scans everything, so performance degrades as data grows. Partitioning divides the data by a required column such as date, course, city or country. The query then reads only one partition, so the response time drops.

Static partitioningSearch on YouTubeBookmarkSaved

In static (manual) partitioning you pass the partition values by hand while loading. The data files do not contain the partition columns. First declare the partition column separately:

CREATE TABLE sales.orders_partitioned (
  order_id INT, customer_id INT, amount DOUBLE
)
PARTITIONED BY (order_date STRING)
STORED AS ORC;

order_date stays out of the main column list. It appears only in PARTITIONED BY. Then load one partition at a time:

INSERT OVERWRITE TABLE sales.orders_partitioned
  PARTITION (order_date='2026-08-10')
SELECT order_id, customer_id, amount
FROM sales.orders
WHERE order_date='2026-08-10';

You name the exact partition value. Repeat once per day, customer, region or whatever the column is. The older deck uses LOAD DATA for the same step:

LOAD DATA LOCAL INPATH '/home/codegyani/hive/student_details1'
INTO TABLE student PARTITION (course='java');
LOAD DATA LOCAL INPATH '/home/codegyani/hive/student_details2'
INTO TABLE student PARTITION (course='hadoop');

Each file lands in its own course=... directory. Later queries read only the directory they need instead of examining the entire data.

Dynamic partitioningSearch on YouTubeBookmarkSaved

Dynamic partitioning lets Hive do the splitting. The partition values already exist inside the data, so you never name them by hand. The deck works through six steps on a student table.

Steps 1 and 2. Pick the database and enable the feature.

USE show;
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

Steps 3 and 4. Build and fill a dummy staging table.

CREATE TABLE stud_demo (id INT, name STRING, age INT,
  institute STRING, course STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
LOAD DATA LOCAL INPATH '/home/codegyani/hive/student_details'
INTO TABLE stud_demo;

Steps 5 and 6. Create the partition table and insert.

CREATE TABLE student_part (id INT, name STRING, age INT, institute STRING)
PARTITIONED BY (course STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
INSERT INTO student_part
  PARTITION (course)
SELECT id, name, age, institute, course FROM stud_demo;

The partition column course is now the LAST column in the SELECT. Hive reads its value per row and creates whichever partition directories are needed automatically.

The slide screenshot shows the result: one directory per value found in the data.

/user/hive/warehouse/show.db/student_part/
  course=__HIVE_DEFAULT_PARTITION__/
  course=hadoop/
  course=java/

Hive creates one directory per value found in the data. Rows whose partition value is missing land in course=__HIVE_DEFAULT_PARTITION__. A plain SELECT * FROM student_part returns all rows. A filtered query such as SELECT * FROM student_part WHERE course='java' reads only the matching directory.

Partition pruning: the actual payoffSearch on YouTubeBookmarkSaved

SELECT * FROM sales.orders_partitioned
WHERE order_date = '2026-08-11';

On the partitioned table only the order_date=2026-08-11 directory is opened. The 08-10 and 08-12 directories are never read. They are not filtered after reading. They are never touched at all.

On an unpartitioned table every file must be opened and every row inspected, to discard everything that is not 08-11. Same result, far more I/O. This skipping of whole directories is called partition pruning.

Choosing partition columnsSearch on YouTubeBookmarkSaved

Good candidates have low-to-moderate cardinality and appear often in WHERE clauses. Examples are order_date, region, country and status. Poor candidates have high cardinality or are rarely filtered directly. Examples are customer_id, order_id and exact timestamps.

Why it matters: every partition is a directory. Every directory is an entry the Metastore must track and the query planner must consider. Partition on customer_id and a table with a million customers gets a million directories. That is the "small files problem": strained NameNode memory and slow planning, for a column that pruning barely helps anyway.

Bucketing and the hash ruleSearch on YouTubeBookmarkSaved

Bucketing splits a table into a FIXED number of files using a hash of a column value. It suits the case where partitioning turns hard: too many distinct values for directories. Buckets can also subdivide partitions further.

Three properties follow. The mapping is deterministic: the same key always picks the same file. The count is fixed: it is set once at CREATE TABLE time and never grows with the data, unlike partitions. It is made for high cardinality: one partition per value would create far too many directories. A fixed number of bucket files stays efficient instead.

Creating a bucketed tableSearch on YouTubeBookmarkSaved

CREATE TABLE sales.orders_bucketed (
  order_id INT, customer_id INT,
  amount DOUBLE, order_date STRING
)
CLUSTERED BY (customer_id) INTO 4 BUCKETS
STORED AS ORC;

CLUSTERED BY (customer_id) INTO 4 BUCKETS names the column and the fixed count. Add SORTED BY (col) to also sort rows inside each bucket file, which helps later queries and joins that like pre-sorted data. Then fill it:

INSERT OVERWRITE TABLE sales.orders_bucketed
SELECT * FROM sales.orders;

The older deck builds the same thing in seven steps on an employee table. It selects a database, creates a dummy table emp_demo, loads the file emp_details, and checks the rows. Then it enables bucketing, creates the bucketed table and copies the data over:

SET hive.exec.dynamic.partition = true;

is replaced here by the bucketing switch:

SET hive.enforce.bucketing = true;
CREATE TABLE emp_bucket (Id INT, Name STRING, Salary FLOAT)
CLUSTERED BY (Id) INTO 3 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
INSERT OVERWRITE TABLE emp_bucket SELECT * FROM emp_demo;

The screenshot shows the physical result: exactly three files, one per bucket.

/user/hive/warehouse/showbucket.db/emp_bucket/
  000000_0   (bucket 0)
  000001_0   (bucket 1)
  000002_0   (bucket 2)

Faster joins with bucketsSearch on YouTubeBookmarkSaved

Bucketing pays off when two tables share a join key. If both tables share the bucketed join column, Hive can join matching buckets directly. One bucket count must be a multiple of the other. Hive then knows which files can hold matching keys. No full shuffle crosses the network.

SET hive.optimize.bucketmapjoin = true;
SELECT o.order_id, c.name
FROM sales.orders_bucketed o
JOIN sales.customers_bucketed c
  ON o.customer_id = c.customer_id;

Fast sampling with TABLESAMPLESearch on YouTubeBookmarkSaved

TABLESAMPLE reads one (or a few) bucket files instead of scanning the whole table. That gives a fast, honest sample for exploratory queries or tests, without paying for a full scan:

SELECT * FROM sales.orders_bucketed
  TABLESAMPLE(BUCKET 1 OUT OF 4 ON customer_id);

Only rows whose hash(customer_id) mod 4 selects bucket 1 are returned. One file out of four means about one quarter of the rows. The clause needs the same column the table is bucketed on.

Worked example: eight employees into three bucketsSearch on YouTubeBookmarkSaved

The staging table emp_demo holds eight rows:

The table is bucketed with CLUSTERED BY (Id) INTO 3 BUCKETS. Since Id is an INT, the hash is the value itself:

Partitioning vs bucketingSearch on YouTubeBookmarkSaved

Combining both on one tableSearch on YouTubeBookmarkSaved

One table can be partitioned AND bucketed. Within every partition directory, data splits further into a fixed number of bucket files:

CREATE TABLE sales.orders_full (
  order_id INT, customer_id INT,
  amount DOUBLE
)
PARTITIONED BY (order_date STRING)
CLUSTERED BY (customer_id)
  INTO 4 BUCKETS
STORED AS ORC;

The layout has directories from partitioning, each holding four files from bucketing:

order_date=2026-08-10/  { 000000_0, 000001_0, 000002_0, 000003_0 }
order_date=2026-08-11/  { 000000_0, 000001_0, 000002_0, 000003_0 }

A query filtering by order_date prunes whole partitions. A join or sample on customer_id inside each partition gets the bucketing benefit too. Both wins apply on the same table, since each technique handles the column type it suits.

Joins in HiveQLSearch on YouTubeBookmarkSaved

Inner joinsSearch on YouTubeBookmarkSaved

An inner join returns the intersection of the two tables: only rows with a match on both sides. The slide joins a students table to a grades table on the grade point:

SELECT students.*, grades.*
FROM students JOIN grades
ON (students.grade = grades.grade);

Each student matches exactly one grade row, so 3 input rows give 3 output rows.

The slide's larger example joins a 7-row CUSTOMER table to a 4-row ORDER table. CUSTOMER_ID values 2, 3 and 4 match. Customer 3 has two orders, so the join output has 4 rows. They are order 102 with kaushik (3000), order 100 with kaushik (1500), order 101 with Khilan (1560) and order 103 with Chaitali (2060). Customers 1, 5, 6 and 7 have no orders and drop out of an inner join.

Outer joinsSearch on YouTubeBookmarkSaved

An outer join keeps non-matching rows too, filling the missing side with NULL. Three types exist. LEFT OUTER JOIN returns a row for every row in the first (left) table. RIGHT OUTER JOIN returns a row for every row in the second (right) table. FULL OUTER JOIN returns a row for every row from both tables.

Cross joinsSearch on YouTubeBookmarkSaved

A cross join, also called a Cartesian product, pairs every row of one table with every row of the other table. No ON condition is used:

SELECT * FROM orders CROSS JOIN customers;
-- also written with plain JOIN and no ON clause

Pitfalls and best practicesSearch on YouTubeBookmarkSaved

1. Partitioning on high cardinality. Columns such as customer_id or exact timestamps create one directory per value. Use bucketing for these instead.
2. Over-partitioning. Too many small partitions strain the Metastore and slow query planning. Hive enforces hive.exec.max.dynamic.partitions as a safety limit. Do not rely on hitting it as your only check.
3. Mismatched bucket counts. A bucket map join needs matching (or multiple) bucket counts on both sides. Get it wrong and the join still runs. It silently skips the optimization, with no error.
4. Skipping table statistics. Run ANALYZE TABLE ... COMPUTE STATISTICS after large loads. The optimizer bases its partition-pruning and join decisions on real data, not guesses.

Quick self-testSearch on YouTubeBookmarkSaved