Friday, August 16, 2019

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 ',';  

Partitioning in Hive
  • Let's retrieve the information associated with the table.
  1. hive> describe student;  

Partitioning in Hive
  • 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");    

Partitioning in Hive
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");  

Partitioning in Hive
In the following screenshot, we can see that the table student is divided into two categories.
Partitioning in Hive
  • Let's retrieve the entire data of the able by using the following command: -
  1. hive> select * from student;  

Partitioning in Hive
  • Now, try to retrieve the data based on partitioned columns by using the following command: -
  1. hive> select * from student where course="java";  

Partitioning in Hive
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";  

Partitioning in Hive


No comments:

Post a Comment

Lab 09: Publish and subscribe to Event Grid events

  Microsoft Azure user interface Given the dynamic nature of Microsoft cloud tools, you might experience Azure UI changes that occur after t...