Partitioning in Hive

The partitioning in Hive means dividing the table into some parts based on the values of a particular column like date, course, city or country. The advantage of partitioning is that since the data is stored in slices, the query response time becomes faster.

As we know that Hadoop is used to handle the huge amount of data, it is always required to use the best approach to deal with it. The partitioning in Hive is the best example of it.

Let's assume we have a data of 10 million students studying in an institute. Now, we have to fetch the students of a particular course. If we use a traditional approach, we have to go through the entire data. This leads to performance degradation. In such a case, we can adopt the better approach i.e., partitioning in Hive and divide the data among the different datasets based on particular columns.

The partitioning in Hive can be executed in two ways -

• Static partitioning
• Dynamic partitioning

---

Static Partitioning

In static or manual partitioning, it is required to pass the values of partitioned columns manually while loading the data into the table. Hence, the data file doesn't contain the partitioned columns.

Example of Static Partitioning

• First, select the database in which we want to create a table.

1. hive> use test;  

• Create the table and provide the partitioned columns by using the following command: -

1. hive> create table student (id int, name string, age int,  institute string)   
2. partitioned by (course string)  
3. row format delimited  
4. fields terminated by ',';  

• Let's retrieve the information associated with the table.

1. hive> describe student;  

• Load the data into the table and pass the values of partition columns with it by using the following command: -

1. hive> load data local inpath '/home/codegyani/hive/student_details1' into table student  
2. partition(course= "java");    

Here, we are partitioning the students of an institute based on courses.

• Load the data of another file into the same table and pass the values of partition columns with it by using the following command: -

1. hive> load data local inpath '/home/codegyani/hive/student_details2' into table student  
2. partition(course= "hadoop");  

In the following screenshot, we can see that the table student is divided into two categories.

• Let's retrieve the entire data of the able by using the following command: -

1. hive> select * from student;  

• Now, try to retrieve the data based on partitioned columns by using the following command: -

1. hive> select * from student where course="java";  

In this case, we are not examining the entire data. Hence, this approach improves query response time.

• Let's also retrieve the data of another partitioned dataset by using the following command: -

1. hive> select * from student where course= "hadoop";