Dynamic Partitioning

In dynamic partitioning, the values of partitioned columns exist within the table. So, it is not required to pass the values of partitioned columns manually.

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

1. hive> use show;  

• Enable the dynamic partition by using the following commands: -

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

• Create a dummy table to store the data.

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

• Now, load the data into the table.

1. hive> load data local inpath '/home/codegyani/hive/student_details' into table stud_demo;  

• Create a partition table by using the following command: -

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

• Now, insert the data of dummy table into the partition table.

1. hive> insert into student_part  
2. partition(course)  
3. select id, name, age, institute, course  
4. from stud_demo;  

 

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

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

1. hive> select * from student_part;  

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

1. hive> select * from student_part 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_part where course= "hadoop";