Thursday, January 14, 2016

How to do UNION ALL in hive

Suppose you have two table 
Boys
- Name
- Age

Girls
- Name
- Age

Now lets say we want to a query that returns all Boys and Girls. Most of us who come from Oracle/SqlServer/MySql etc world will write following query 

select name, age from Boys 
union all 
select name, age from Girls

This query will throw an error in Hive and it will look like UNION ALL does not works in Hive. UNION ALL does works in Hive, the only trick is to enclose the UNION ALL tables in a subquery, i.e. write the above as 

select name, age from (
     select name, age from Boys 
     union all 
     select name, age from Girls
) BoysAndGirls

No comments:

Post a Comment