Hive Union (translated from hive wiki)

Union syntax

 
Select_statement Union all select_statement...
 

Union is used to combine the result sets of multiple select statements into an independent result set. Currently, only union all (BAG Union) is supported ). Duplicate rows are not eliminated. The number and name of columns returned by each select statement must be the same. Otherwise, a syntax error is thrown.

 

If some extra processing is required for the Union result, the entire statement can be embedded in the from clause, as shown below:

 
Select *From (select_statement Union all select_statement) unionresult
 

 

For example, if we assume there are two different tables to track which user publishes a video and which user has posted a comment, and the following query results are used together with all user tables, creates a single comment stream for all video publishing and comment publishing events.

Select U.ID, Actions.DateFrom (select Av. UID as uid from action_video AV where AV.Date='2008-06-03'Union all select AC. UID as uid from action_comment AC where AC.Date='2008-06-03') Actions
Join users U on (U.ID= Actions. UID)
 

Translation from https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union