Comprehensive Guide to Hive Static, Dynamic, and Multi-Level Partitions

Six data files containing hero information for the game "King of Glory" need to be loaded into a Hive table t_all_hero . The article starts with a

create table t_all_hero(
    id int,
    name string,
    hp_max int,
    mp_max int,
    attack_max int,
    defense_max int,
    attack_range string,
    role_main string,
    role_assist string)
row format delimited
fields terminated by "\t";

statement and describes loading the files to an HDFS directory.

A sample query that counts archers with hp_max greater than 6000 demonstrates that Hive must scan every file in a non‑partitioned table, which is inefficient when many files exist.

Hive can avoid full scans by partitioning the table on a column such as role. The article defines a partitioned table with the syntax

CREATE TABLE table_name (column1 data_type, column2 data_type)
PARTITIONED BY (partition1 data_type, partition2 data_type, …);

and creates t_all_hero_part partitioned by role. It notes that the partition column must not already exist in the table schema.

Static partition means the partition value is manually specified during data load. The load command format is

load data [local] inpath 'file_path' into table tablename partition(partition_column='partition_value');

and the article provides a series of load data local inpath statements that load each hero file into the appropriate role partition.

Dynamic partition lets Hive infer the partition value from the query result. To enable it, the session parameters

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

are required; strict mode demands at least one static partition. A new table t_all_hero_part_dynamic is created with the same schema and partitioned by (role). Data is inserted using

insert into table t_all_hero_part_dynamic partition(role)
select tmp.*, tmp.role_main from t_all_hero tmp;

, where Hive automatically derives the role value.

The article shows that, although a partitioned table adds only a partition column, its underlying storage changes: non‑partitioned data resides in a single directory, while each partition creates a separate subdirectory. Images illustrate the directory structures for t_all_hero (non‑partitioned) and t_all_hero_part (partitioned).

Key usage tips are summarized: choose partition columns that reflect business dimensions (date, region, category, etc.), always filter by partition in WHERE clauses to avoid full scans, and remember that partition columns are virtual fields not stored in the data files.

Hive also supports multiple (nested) partitions . The syntax

PARTITIONED BY (province string, city string, county string);

creates hierarchical folders, enabling fine‑grained data organization. Example DDL statements for single‑, double‑, and triple‑partition tables are provided, and the article explains that each additional partition adds another level of subfolders in HDFS.