Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 36 ยท Tables, Partitions and Buckets

Partitioned Tables in Hive

Learn Hive partitioning: how partition folders work, creating and loading static partitions, partition pruning, SHOW and DROP PARTITION, MSCK REPAIR TABLE and how to choose partition keys.

Partitioning is the single most important performance feature in Hive. Instead of one huge folder of files, a partitioned table is split into subfolders, one per value of a partition column (for example one folder per day). A query that filters on that column reads only the matching folders and skips the rest, which can turn a ten-minute scan into a ten-second one.

Partitions are a Hive and file-system concept. The DDL is read-only. The runnable editors are SQLite stand-ins: a column plus an index, and a WHERE filter, show the pruning idea; they do not create folders.

What a partition looks like on disk

TEXT
/warehouse/retail.db/orders/
  order_date=2025-03-01/   000000_0   000001_0
  order_date=2025-03-02/   000000_0
  order_date=2025-03-03/   000000_0   000001_0   000002_0

Each folder name is column=value. The partition column's value is stored in the folder name, not inside the files. That is why a partition column is not part of the table's regular column list, yet it still behaves like a column in queries.

Creating a partitioned table

SQL
CREATE TABLE orders (
  order_id    BIGINT,
  customer_id BIGINT,
  total       DECIMAL(10,2)
)
PARTITIONED BY (order_date STRING)
STORED AS ORC;

Note that order_date is declared in PARTITIONED BY and not in the column list. You can have several partition columns, which creates nested folders (PARTITIONED BY (country STRING, dt STRING) gives country=NO/dt=2025-03-01/).

Static partition loading

In a static load you name the partition value yourself:

SQL
INSERT OVERWRITE TABLE orders PARTITION (order_date = '2025-03-01')
SELECT order_id, customer_id, total
FROM staging_orders
WHERE day = '2025-03-01';

LOAD DATA INPATH '/landing/2025-03-02/'
INTO TABLE orders PARTITION (order_date = '2025-03-02');

The select list contains only the regular columns; the partition value comes from the clause. Loading many partitions one statement at a time is tedious, which is why Dynamic Partitioning exists.

Partition pruning

When a query filters on partition columns with constants, the compiler reads only the metadata for matching partitions and never lists the others:

SQL
SELECT SUM(total)
FROM orders
WHERE order_date = '2025-03-02';             -- reads one folder

SELECT SUM(total)
FROM orders
WHERE order_date BETWEEN '2025-03-01' AND '2025-03-07';   -- reads seven folders

Pruning requires the filter to be applied to the partition column itself. These forms defeat it or make it harder:

SQL
WHERE substr(order_date, 1, 7) = '2025-03'   -- function on the column: no pruning in many versions
WHERE order_date = ${some_subquery}          -- non-constant: needs dynamic pruning
WHERE CAST(order_date AS DATE) = DATE '2025-03-02'

Check with EXPLAIN DEPENDENCY or EXPLAIN EXTENDED, which list the partitions read. In strict mode (hive.mapred.mode=strict) Hive even rejects a query on a partitioned table that has no partition filter, protecting you from scanning everything by accident.

The stand-in below filters on a date column. In SQLite it still scans (or uses an index), but it shows the query shape you would use:

SQL Stand-in: filtering on a 'partition-like' column (SQLite, not Hive) ยท database: bookstore

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

SQL Stand-in: totals per day, the grouping a partition would hold (SQLite, not Hive) ยท database: bookstore

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

Managing partitions

SQL
SHOW PARTITIONS orders;
SHOW PARTITIONS orders PARTITION (order_date = '2025-03-01');
DESCRIBE FORMATTED orders PARTITION (order_date = '2025-03-01');

ALTER TABLE orders ADD IF NOT EXISTS PARTITION (order_date = '2025-03-04')
  LOCATION '/landing/orders/2025-03-04';

ALTER TABLE orders DROP IF EXISTS PARTITION (order_date < '2025-01-01');

ALTER TABLE orders PARTITION (order_date = '2025-03-01')
  RENAME TO PARTITION (order_date = '2025-03-01-old');

For an external table, DROP PARTITION removes only metadata; for a managed table it deletes the folder's files. ADD PARTITION ... LOCATION is also how you attach an existing folder anywhere on the file system as a partition.

MSCK REPAIR TABLE

When another tool (Spark, a copy job, hdfs dfs -put) creates a folder order_date=2025-03-05/ directly in the table's directory, the files exist but the metastore does not know about the partition, so queries ignore them. Register all missing folders with:

SQL
MSCK REPAIR TABLE orders;
-- some versions: MSCK REPAIR TABLE orders SYNC PARTITIONS;  (also removes entries whose folders vanished)

On tables with hundreds of thousands of partitions this check is slow. Automate it with ALTER TABLE ... ADD PARTITION statements from your pipeline instead.

Choosing partition columns

A good partition column has these properties:

  • Used in most WHERE filters, so pruning applies (date is the classic).
  • Low cardinality: tens to a few thousand distinct values, not millions.
  • Evenly sized partitions: avoid a column where one value holds 95% of the data.

The danger is over-partitioning. Partitioning by user_id creates a million folders with one tiny file each; the metastore gets overloaded, listing becomes slow and the small files ruin performance. Aim for partitions of at least hundreds of MB. If a column is too fine-grained (hours, user ids, product ids), use bucketing instead (see Bucketed Tables) or partition at a coarser level (day, not hour).

flowchart TD
    Q["WHERE order_date = '2025-03-02'"] --> P["Compiler asks the metastore:<br/>which partitions match?"]
    P --> F1["order_date=2025-03-02/ (read)"]
    P -. skipped .-> F2["order_date=2025-03-01/"]
    P -. skipped .-> F3["order_date=2025-03-03/"]

Skipped partitions are never opened, which is where the speed-up comes from.

Common mistakes

  • Including the partition column in the regular column list (the statement fails with a duplicate column error).
  • Partitioning on a high-cardinality column, producing millions of tiny files.
  • Writing folders by hand and forgetting MSCK REPAIR TABLE.
  • Putting a function around the partition column and losing pruning.
  • Using different value formats (2025-3-1 versus 2025-03-01) which become different partitions.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where is the value of a partition column stored?

    Partition values live in the folder names, so they are not part of the file contents.
  2. 2. What does partition pruning achieve?

    Only the folders that match the partition predicate are read.
  3. 3. Files were copied to order_date=2025-03-05/ but the query returns nothing. Which fix?

    The metastore needs to register the new partition folder.
  4. 4. Which is a poor partition column?

    High-cardinality columns cause an explosion of tiny partitions.

Summary

  • A partitioned table stores one subfolder per partition value; the value is in the folder name and filters on it let Hive skip folders.
  • Load static partitions by naming them; manage them with SHOW, ADD, DROP and MSCK REPAIR TABLE.
  • Choose low-cardinality columns that appear in filters (usually a date) and keep partitions reasonably large.
  • Over-partitioning creates tiny files and overloads the metastore.

Next: Dynamic Partitioning