Overview of Dynamic Partition in Hive

Hive was developed by Facebook. It is used for analytics and MapReduce jobs. It can read, write, and manage large datasets. Hive can replace traditional database operations. Hive uses indexing to make queries more efficient, and it can work with compressed data stored in the Hadoop ecosystem.

In this article, we will discuss Dynamic Partitioning and operations on Dynamic Partition in Hive.

Apache Hive

Apache Hive is a warehousing system. It is used to perform operations on structured data. It is widely used for analytics and MapReduce jobs. Apache Hive provides functionality for reading, writing, and managing large datasets. One of the key features of Hive is its ability to partition data. In this article, we will discuss an overview of dynamic partitioning in Hive.

Partitioning in Hive

Partitioning is the process of dividing a large dataset into smaller and more manageable parts. There are types of partitioning in Hive: Static and dynamic partitioning.

Static partitioning

It specifies the partition key and value for each record when inserting data into a partitioned table. This approach is used where there are small number of partitions.

Dynamic partitioning

It has more flexible approach. It determines the partition key and value based on the data being inserted. It is better choice for large datasets with a high number of partitions.

Features of Dynamic Partitioning

It has many features. These features make it an attractive option for handling large amounts of data stored in distributed storage. These are some feature in Dynamic Partitioning −

Ability to handle large datasets

Dynamic partitioning is a strategic approach for loading data from non-partitioned tables. It handles large datasets stored in distributed storage.

Support for external and managed tables

Dynamic partitioning can be performed on both external and managed tables in Hive.

No need for a WHERE clause

Unlike Static partitioning, dynamic partitioning does not require a WHERE clause to specify the partition key and value.

Ability to work with tables of unknown structure

Dynamic partitioning can be used to partition tables without knowing the number of columns in advance.

Operations on Dynamic Partitioning

Here are the steps to perform operations on Dynamic Partitioning in Hive −

Step 1 − Create a database where you want to perform the operation and select it.

Step 2 − Enable dynamic partition using the following commands

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

Step 3 − Create a table to store the data.

Step 4 − Load the data into the table.

Step 5 − Create a partitioned table using the partitioned by clause.

Step 6 − Load the data into the partitioned table.

Step 7 − Perform query operations.

Step 8 − To delete the dynamic partition column, use the following command:

hive> alter table partitioned_table drop partition (partition_col = 'value');

Make sure to replace partition_col and value with the appropriate column name and value.


Dynamic Partitioning is a feature in Hive. It is efficient and flexible for handling large datasets stored in distributed storage. It can be a great option for analytics and MapReduce jobs. Steps to perform operations on Dynamic Partitioning are relatively simple and easy to follow. Users can make the most out of Dynamic Partitioning in Hive. Users can improve their data management and analytics capabilities. Hive is a tool of big data and with features like Dynamic Partitioning, it is more versatile and valuable.

Updated on: 18-May-2023


Kickstart Your Career

Get certified by completing the course

Get Started