Static vs Dynamic Partitioning in Hive

Updated On August 11, 2020 | By Mahesh Mogal

There are two types of Partitions in Hive. Static and Dynamic. They both operate on the same basic principles of Partitioning. But they are suitable for particular use cases. In this tutorial, we are going to learn, static & dynamic partitioning, the difference between static and dynamic partition in Hive and when should we use one of them.

Static Partitioning in Hive

In Static Partitioning, we have to manually decide how many partitions tables will have and also value for those partitions. Consider we have employ table and we want to partition it based on department name. There are a limited number of departments, hence a limited number of partitions.

One of the major drawbacks of static partitioning is, when we are loading data to some partition, we have to make sure we are putting the right data in the right partition. In the below example, while loading HR data, it is our responsibility to make sure that the file only has HR data. (Learn more about Load hive command here).

CREATE EXTERNAL TABLE employee_dept (
    emp_id INT,
    emp_name STRING
) PARTITIONED BY (
    dept_name STRING
    )
location '/user/maheshmogal/employee_dept';


LOAD DATA LOCAL INPATH 'hr.txt'
INTO TABLE employee_dept
PARTITION (dept_name='HR');

Above queries will create table on partition on department name and then load data from our local system to this table in partitioned name "HR".

Now you should see another drawback with this method. What if you have 1000 different departments or you have some other partitioning column like year and month? You have to create those manually and that is no FUN!! This is where dynamic partitions help us.

With all these drawbacks you might think, static partitions are not that useful. But when you have limited and well know a set of values (like departments or state names etc), you can use static partitioning. Static partitioning is faster for loading data as all of the information is already present in the query. Also, it will stop you from creating unnecessary partitions creating performance issues the very thing which partitions are supposed to help us with. (read more on performance issues with a huge number of partitions)

Dynamic Partitioning In Hive

In most cases, you will find yourself using Dynamic partitions. Dynamic partitions provide us with flexibility and create partitions automatically depending on the data that we are inserting into the table.

In last tutorial, we have created orders table. We can insert data in to that table with following query.

hive (maheshmogal)> insert overwrite table order_partition partition (year,month)
                  > select order_id, order_date, order_status, substr(order_date,1,4) year, substr(order_date,5,2) month from orders;
FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

Oh we got error? what is this about?

By default, Hive does not enable dynamic partition. This is to protect us, from creating from a huge number of partitions accidentally. In dynamic partition, we are telling hive which column to use for dynamic partition. If we select the wrong column (say order id) we can end up with millions of partitions.

So how do we create dynamic partitions? We have to enable hive dynamic partition first (which is disabled by default). But when it is enabled, it is in strict mode. This means there has to be at least one static partition present in this table. Then hive will let us create other partitions dynamically.

An easier way is, we can set Hive's dynamic property mode to nonstrict using the following command. This will allow us to create dynamic partitions in the table without any static partition.

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

Now if you run the insert query, it will create all required dynamic partitions and insert correct data into each partition. This all happens in one single query and we do not have to manually check data and correct partition.

Static VS Dynamic Partitioning

In most cases, we can use dynamic partitioning. It provides us a lot of flexibility. The FOllowing table will help you understand the difference between Static and dynamic partitioning.

#Static PartitioningDynamic Partitioning
Partition CreationWe need to manually create each partition before inserting data into a partitionPartitions will be created dynamically based on input data to the table.
Suitable forWe need to know all partitions in advance. So it is suitable for use cases where partitions are defined well ahead and are small in numberDynamic partitions are suitable when we have lot of partitions and we can not predict in advance new partitions ahead of time.
ExamplesDepartments, State Names, etcDate, city names etc

Conclusion

Partitioning is one of the important topics in the Hive and it is used to improve the performance of queries. We have learned the basics of static and dynamic partition in this tutorial. In the next blog, we will learn different ways to insert data in the partition table and also learn about altering and dropping partitions.

static vs dynamic
.

Mahesh Mogal

I am passionate about Cloud, Data Analytics, Machine Learning, and Artificial Intelligence. I like to learn and try out new things. I have started blogging about my experience while learning these exciting technologies.

Stay Updated with Latest Blogs

Get latest blogs delivered to your mail directly.

Recent Posts

Select Expr in Spark Dataframe

In this blog, we will learn how to use select and expr in the Spark data frame. We will learn multiple use cases along with selectExpr.

Read More
Add, Rename, Drop Columns in Spark Dataframe

We will go through common column operations like add, rename, list, select, and dropping a column from spark dataframe.

Read More
MSCK Repair - Fixing Partitions in Hive Table

We will learn how to add multiple partitions to hive table using msck repair table command in hive.

msck repair hive
Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram
Share via
Copy link
Powered by Social Snap