To operate the result set vertically, Hive only supports UNION ALL right now. And, the result set of UNION ALL keeps duplicates if any. Before Hive 0.13.0, UNION ALL can only be used in the subquery. Since Hive 0.13.0, UNION ALL can also be used in top-level queries. The following are examples of the UNION ALL statements:
Check the
namecolumn in theemployee_hrandemployeetable:jdbc:hive2://> SELECT name FROM employee_hr; +----------+ | name | +----------+ | Michael | | Will | | Steven | | Lucy | +----------+ 4 rows selected (0.116 seconds) jdbc:hive2://> SELECT name FROM employee; +----------+ | name | +----------+ | Michael | | Will | | Shelley | | Lucy | +----------+ 4 rows selected (0.049 seconds)
Use
UNIONon thenamecolumn from both tables, including duplications:jdbc:hive2://> SELECT a.name . . . . . . .> FROM employee a . . . . . . .> UNION ALL . . . . . . .> SELECT b.name . . . . . . .> FROM employee_hr...