Hive语法：union

2022-01-05 15:27:34 浏览数 (1)

Hive语法：union

一、介绍

本文参考官方介绍，原文地址如下： https://cwiki.apache.org/confluence/display/Hive/LanguageManual Union

Hive官方提供了一种联合查询的语法，原名为Union Syntax，用于联合两个表的记录进行查询，此处的联合和join是不同的，join是将两个表的字段拼接到一起，而union是将两个表的记录拼接在一起。

通俗来讲，join是用于左右拼接，而union是用于上下拼接。

比如有如下两个表： 表1：

id

username

1

user001

2

user002

表2：

id

username

1

user003

2

user004

join的左右拼接如这样：

id

username

id

username

1

user001

1

user003

2

user002

2

user004

unoin的上下拼接如这样：

id

username

1

user001

2

user002

1

user003

2

user004

二、语法

官方语法：

代码语言：javascript复制
select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...

union用于将多个select语句的结果组合到单个结果集中。

需要注意：

  • 1.2.0之前的Hive版本仅支持union all，其中不会删除重复行。
  • 在Hive1.2.0##及更高版本中，union的默认行为是从结果中删除重复的行。可选的distinct关键字指定了删除重复行。使用可选的all关键字，不会发生重复行删除，结果包含select语句中的所有匹配行。
  • 可以在同一查询中混合使用union all和union distinct。
  • 每个select语句返回的列的数量和名称必须相同，否则，将引发架构错误。错误内容如下：SemanticException Schema of both sides of union should match.

三、应用

1、from子句

union语句可以作为form的子句进行使用，简单示例如下：

代码语言：javascript复制
select * form ( select_statement union all select_statement ) unionResult

如下是官方示例：

代码语言：javascript复制
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)

2、DDL和插入语句

union可以在视图，插入和CTAS（create table as select）语句中使用。查询可以包含多个union子句。

3、应用于从句

要将order by，sort by，cluster by，distribute by 或limit 应用于union两边的select语句中也是可以的，如下：

代码语言：javascript复制
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

要将以上应用于union之后的最终结果也是可以的，示例如下：

代码语言：javascript复制
SELECT key FROM src
UNION
SELECT key FROM src1 
ORDER BY key LIMIT 10

4、字段别名应用

union要求表达式两侧的字段名称以及字段数量都必须相同，这种情况下，有些表字段的含义相等，但是字段名称不同的情况，使用union就会出现报错。union支持字段别名相等。

如下示例：

代码语言：javascript复制
INSERT OVERWRITE TABLE target_table
  SELECT name, id, category FROM source_table_1
  UNION ALL
  SELECT name, id, "Category159" FROM source_table_2

上述语句会报错。

代码语言：javascript复制
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

上述语句可正常执行。

5、字段类型转换

Hive2.2.0版本的HIVE-14251中，Hive支持在每个类型组中执行隐式转换，包括字符串、数字、日期等。为了组合来自不同组的类型，在查询中需要显式强制转换。

示例如下：

代码语言：javascript复制
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

四、案例

本人自己寻找了一个使用此语法的案例，这里和大家进行分享。如有错误敬请指正。

1、要求

存在两张用户表，一张为历史表，一张为当日表，每天要将当日表中的数据和历史表中的数据进行去重合并到新的历史表中。

2、思路

这里本人首先想到的步骤是，将两张表的数据进行合并，然后查询所有去重存入另外一张表。 这个思路的实现局限于hive的版本，这里提供两个版本的，两种方式进行实现，更高的2.2.0版本，本人没有使用，这里使用的两个版本分别是CDH中集成的hive1.1.0版本和开源的hive1.2.0版本。

3、准备

创建三张表：

  • user01：代表历史表
  • user02：代表当日表
  • user03：代表最后合并的历史表。

建表语句：

代码语言：javascript复制
create external table user01(id int, username string) row format delimited fields terminated by '|' location 'hdfs://192.168.75.150:9000/test/user1';

create external table user02(id int,username string) row format delimited fields terminated by '|' location 'hdfs://192.168.75.150:9000/test/user2';

为了方便构造数据，本人将两个有数据的表创建成外部表，将最后的结果表创建成内部表。

代码语言：javascript复制
create table user03(id int,username string) row format delimited fields terminated by '|';

user01表的数据内容：

代码语言：javascript复制
1|user001
2|user002
3|user003
4|user004

user02表的数据内容：

代码语言：javascript复制
1|user002
2|user006
3|user007
4|user009
5|user003

4、合并数据

在提取数据之前先来做个测试，如下的测试环境是Hive1.2.0版本：

代码语言：javascript复制
select * from user01 union select * from user02;

结果如下图：

代码语言：javascript复制
select username from user01 union select username user02;

结果如下图：

第三个语句和结果如下图：

从上述的实验，我们可以看出：

  • 1.unoin去重的根据是把要展示的所有字段作为一个整体来去重的。
  • 2.unoin左右两边的字段结构要相同，此相同包括名称和个数，这个上面介绍已经说过了。
  • 3.unoin左右两边的字段结构不同就会报错。
1．方法一

此方式采用的是CDH5.7中集成的Hive1.1.0版本实现。 这里可以一条HQL实现，HQL如下：

代码语言：javascript复制
insert overwrite table user03 
select row_number() over() as id ,username from (
select distinct(username) as username from (
select username from user01 union all select username from user02) as A) as B;

这里需要说一下，去重的那一步，必须单独写，没办法加id，加上就报错，所以又在外面加了一层加ID的查询，然后再插入。

查询结果如下：

2．方法二

采用开源的Hive1.2.0版本进行，这里就比上面要简单多了，因为可以使用去重了！

代码语言：javascript复制
insert overwrite table user03 
select row_number() over() as id,username from (
select username from user01 union select username from user02) as A;

查询结果如下：

以上就是案例的整个过程！

hive

0 人点赞