Joins in Hive :

Hive converts joins over multiple tables into a single map/reduce job if for every table the same column is used in the join clauses

Only equality joins, outer joins, and left semi joins are supported in Hive. Hive does not support join conditions that are not equality conditions as it is very difficult to express such conditions as a map/reduce job. Also, more than two tables can be joined in Hive.

 

Inner join/EquiJoin :

create table student_details(id int,name string,age int,location string) row format delimited fields terminated by ‘,’ lines terminated by ‘\n’;

load data local inpath ‘/home/hduser/student_details’ into table student_details;

/>

select * from student_details;

/>

create table student_marks(c_id int,subject1 int,subject2 int.subject3 int,subject4 int,subject5 int,subject6 int) row format delimited fields terminated by ‘,’ lines terminated by ‘\n’;

/>

load data local inpath ‘/home/hduser/student_marks’ into table student_marks;

/>

select * from student_marks;

/>

select d.id, m.c_id, d.name, d.age, d.department, m.subject1, m.subject2, m.subject3, m.subject4, m.subject5, m.subject6, d.location from student_details d join student_marks m on(d.id=m.c_id);

/>

Left outer join:

select d.id, m.c_id, d.name, d.age, d.department, m.subject1, m.subject2, m.subject3, m.subject4, m.subject5, m.subject6, d.location from student_details d left outer join student_marks m on(d.id=m.c_id);

/>

Right outer join:

select d.id, m.c_id, d.name, d.age, d.department, m.subject1, m.subject2, m.subject3,
m.subject4, m.subject5, m.subject6, d.location from student_details d right outer join student_marks m on(d.id=m.c_id);

Full outer join:

select d.id, m.c_id, d.name, d.age, d.department, m.subject1, m.subject2, m.subject3, m.subject4, m.subject5, m.subject6, d.location from student_details d full outer join student_marks m on(d.id=m.c_id);

Self join:

select * from student_details d join student_details d1;

/>

/>

Leave a Reply

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