HIVE UNION

• 语法

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

• 说明
  HIVE 1.2.0版本之前仅支持： UNION ALL (bag union)
  1.2.0版本之后：UNION的默认结果是去重后的结果

• UNION within a FROM Clause：

/* 例子1 */SELECT *FROM (  select_statement  UNION ALL  select_statement) unionResult;/* 例子2 */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);

• 使用 ORDER BY, SORT BY, CLUSTER BY, DISTRIBUTE BY or LIMIT：

/* 例子1、修饰单个SELECT */SELECT key FROM (SELECT key FROM src ORDER BY key LIMIT 10)subq1UNIONSELECT key FROM (SELECT key FROM src1 ORDER BY key LIMIT 10)subq2;/* 例子2、修饰整个UNION结果 */SELECT key FROM srcUNIONSELECT key FROM src1 ORDER BY key LIMIT 10;/* 例子3、错误用法（×）：以下用法是错的 */INSERT OVERWRITE TABLE target_table    SELECT name, id, category FROM source_table_1    UNION ALL    SELECT name, id, "Category159" FROM source_table_2;/* 例子4、正确用法 */INSERT OVERWRITE TABLE target_table  SELECT name, id, category FROM source_table_1  UNION ALL  SELECT name, id, "Category159" as category FROM source_table_2;

• 列的值类型转换：
  （在HIVE-2.2.0版本之前，HIVE尽量试图执行类型转换，但是之后只在类型组内转换，即string group\ number group \ date group三个组内默认转换，跨组转换需要显示进行）

SELECT name, id, cast('2001-01-01' as date) d FROM source_table_1UNION ALLSELECT name, id, hiredate as d FROM source_table_2;

• 参考文献
  https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union