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.date
FROM (
    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)subq1
UNION
SELECT key FROM (SELECT key FROM src1 ORDER BY key LIMIT 10)subq2;

/* 例子2、修饰整个UNION结果 */
SELECT key FROM src
UNION
SELECT 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_1
UNION ALL
SELECT name, id, hiredate as d FROM source_table_2;

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