@n c Make sure you are having same number of columns and datatypes of both sides of union are same. Union: Eliminates duplicates from the result. Ex: hive> select "1" id,"2" age from (select 1)t
union
select "1" id,"2" age from(select 1) t;
+---------+----------+--+
| _u2.id | _u2.age |
+---------+----------+--+
| 1 | 2 |
+---------+----------+--+ as i'm having 1,2 as result but in the result we are having only one row as hive eliminated duplicates. UnionAll: Shows all duplicates also. hive> select "1" id,"2" age from (select 1)t
union all
select "1" id,"2" age from(select 1) t;
+---------+----------+--+
| _u1.id | _u1.age |
+---------+----------+--+
| 1 | 2 |
| 1 | 2 |
+---------+----------+--+ In both cases we need to have same number of columns/datatypes while performing union or union all operations. If you don't have same number of columns/datatypes from different tables then use null value to match number of columns on both sides. Ex: hive> select "1" id,"2" age from (select 1)t
union
select "1",null from(select 1) t;
+---------+----------+--+
| _u2.id | _u2.age |
+---------+----------+--+
| 1 | NULL |
| 1 | 2 |
+---------+----------+--+ in the above example i have used null for age column and result we going to have 2 columns. Refer to this link for more details regards to union and union all operators.
... View more
