- 语法
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;