Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 36 ยท Tables, Partitions and Buckets

Dynamic Partitioning

Load many Hive partitions in one statement with dynamic partitioning: required settings, strict versus nonstrict mode, column order rules, limits, small files and overwrite behavior.

With static partitioning you write the partition value in every statement. That is fine for one day of data, but not for loading three years of history, or a table partitioned by country where you do not even know the list of countries in advance. Dynamic partitioning lets Hive read the partition values from the data and create the partitions for you, in one statement.

Dynamic partitions need Hive, so the statements and settings are read-only. The runnable editors are SQLite stand-ins that show the heart of the idea: grouping rows by a value to decide where each one belongs.

The settings

Dynamic partitioning is controlled by session properties:

SQL
SET hive.exec.dynamic.partition=true;               -- on by default in modern Hive
SET hive.exec.dynamic.partition.mode=nonstrict;     -- allow ALL partition columns to be dynamic
SET hive.exec.max.dynamic.partitions=5000;          -- total limit per statement
SET hive.exec.max.dynamic.partitions.pernode=2000;  -- limit per task
SET hive.exec.max.created.files=200000;             -- file-count safety valve

The two modes:

  • strict (the default): at least one partition column must be static (named in the statement). It protects you against a typo that creates thousands of partitions.
  • nonstrict: all partition columns may be dynamic.

A dynamic insert

SQL
INSERT OVERWRITE TABLE orders PARTITION (order_date)
SELECT order_id,
       customer_id,
       total,
       order_date          -- LAST column in the SELECT is the partition value
FROM staging_orders;

The rule that surprises everyone: the partition columns must be the last columns of the select list, in the order of the PARTITION clause. Hive matches them by position, not name. If you put them in the wrong place, the data lands in the wrong partitions or fails with a type error. Always write the select list explicitly, never SELECT * unless the source column order is guaranteed.

A mixed static and dynamic example (the static column must come first in the PARTITION clause):

SQL
INSERT OVERWRITE TABLE sales PARTITION (country = 'NO', dt)
SELECT id, total, dt
FROM staging_sales
WHERE country = 'NO';

Here is the stand-in. The query we use to decide partitions in a dynamic load is a GROUP BY on the partition value; this one shows which "partitions" the bookstore orders would create and how big each would be:

SQL Stand-in: which partitions would a dynamic load create? (SQLite, not Hive) ยท database: bookstore

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

Every distinct value in the first column would become a folder year_month=..., which is exactly what Hive does at write time.

What happens during the load

flowchart LR
    S["staging_orders"] --> M["Map tasks:<br/>read rows"]
    M -->|"shuffle by partition value"| R["Reducers / writers:<br/>one open file per partition"]
    R --> P1["order_date=2025-03-01/"]
    R --> P2["order_date=2025-03-02/"]
    R --> P3["order_date=2025-03-03/"]

Each writer may open one file per partition it sees; many partitions times many writers means many small files.

Small-file and memory problems

Every task that sees k distinct partition values writes k files. With 200 tasks and 365 partitions that can mean up to 73,000 files, and each open ORC writer uses memory, so tasks run out of heap. Cures:

  1. Distribute by the partition column, so each partition value goes to one or a few reducers:
SQL
INSERT OVERWRITE TABLE orders PARTITION (order_date)
SELECT order_id, customer_id, total, order_date
FROM staging_orders
DISTRIBUTE BY order_date;
  1. Enable the optimized sorted dynamic partition insert (on by default): SET hive.optimize.sort.dynamic.partition=true;, which sorts rows by the partition key so each writer keeps only one file open at a time.
  2. Load history in batches (a month at a time) rather than years at once.
  3. Merge small output files: SET hive.merge.tezfiles=true; SET hive.merge.smallfiles.avgsize=134217728;.

Overwrite semantics

With INSERT OVERWRITE and dynamic partitioning, only the partitions that appear in the query result are replaced. Other partitions are untouched. That is useful: reload "yesterday and today" without touching the rest. But it also means that if a day has no rows in the new data, the old partition for that day stays; Hive does not delete it.

With INSERT INTO (append), new files are added to each partition.

The stand-in below shows that an overwrite of "partitions present in the data" means deleting only those keys first:

SQL Stand-in: replace only the partitions that appear in new data (SQLite, not Hive)

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

The result keeps d1 and d3 and replaces d2, the same behaviour as a dynamic INSERT OVERWRITE.

NULL and bad partition values

Rows with a NULL or empty partition value go to a special partition, __HIVE_DEFAULT_PARTITION__. If you see it in SHOW PARTITIONS, some rows have a missing key; find them with WHERE order_date IS NULL in the source. Also watch the format: 2025-3-1 and 2025-03-01 are two different partitions. Normalize before loading.

Other ways to create partitions

  • Static: name the partition (previous lesson).
  • Dynamic: the statement above.
  • External tables: new folders plus MSCK REPAIR TABLE, or ALTER TABLE ADD PARTITION.
  • Spark or another engine writing to the same layout, then registering partitions through the metastore.

Common mistakes

  • Leaving the mode as strict and getting "Dynamic partition strict mode requires at least one static partition column".
  • Putting the partition column first in the select list.
  • Hitting hive.exec.max.dynamic.partitions because the partition column has too many distinct values (a sign of a poor design).
  • Expecting OVERWRITE to clear partitions that are absent from the new data.
  • Loading many partitions without DISTRIBUTE BY and ending up with thousands of tiny files.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Where must the partition column be in the SELECT list of a dynamic partition insert?

    Hive maps the trailing select columns to the partition columns by position.
  2. 2. What does hive.exec.dynamic.partition.mode=strict require?

    Strict mode guards against creating too many partitions by accident.
  3. 3. With INSERT OVERWRITE and dynamic partitions, which partitions are replaced?

    Partitions without new rows are left untouched.
  4. 4. How can DISTRIBUTE BY the partition column help a dynamic load?

    Grouping each partition's rows in one task avoids every task writing a file for every partition.

Summary

  • Dynamic partitioning creates partitions from data values; enable it and set nonstrict if all partition columns are dynamic.
  • Partition columns must be the last columns of the SELECT, in order.
  • Use DISTRIBUTE BY or sorted dynamic partition insert to avoid small files; mind the partition-count limits.
  • INSERT OVERWRITE replaces only the partitions present in the new data; NULL keys go to __HIVE_DEFAULT_PARTITION__.

Next: Bucketed Tables and Sampling