Static vs Dynamic Partitioning in Hive

Updated On February 12, 2021 | 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).

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.

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.

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

Where and Filter in Spark Dataframes

In this blog, we will learn how to filter rows from spark dataframe using Where and Filter functions.

Where and Filter in Spark Dataframes
Read More
Distinct Rows and Distinct Count from Spark Dataframe

Getting distinct values from columns or rows is one of most used operations. We will learn how to get distinct values as well as count of distinct values.

Distinct Rows and Distinct Count from Spark Dataframe
Read More
Sorting in Spark Dataframe

In this blog, we will learn how to sort rows in spark dataframe based on some column values.

Sorting in Spark Dataframe
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