hive之full outer join（全连接）使用方法

目录

介绍

语法

例子

创建顾客表：customers

创建订单表：orders

?full outer join语句

? left join + union + right join语句

---

介绍

full outer join结合了 LEFT JOIN 和 RIGHT JOIN 的结果，并使用NULL值作为两侧缺失匹配结果。

语法

SELECT 
    table1.column_name(s),table2.column_name(s) 
FROM table1 
    FULL OUTER JOIN table2 
ON table1.column_name = table2.column_name;
1
2
3
4
5

相当于:left join + union + right join

SELECT 
    table1.column_name(s),table2.column_name(s)
FROM table1 
    LEFT OUTER JOIN table2 
ON table1.column_name = table2.column_name;
UNION
SELECT 
    table1.column_name(s),table2.column_name(s)
FROM table1 
    RIGHT OUTER JOIN table2 
ON table1.column_name = table2.column_name;
1
2
3
4
5
6
7
8
9
10
11

例子

创建顾客表：customers

CREATE TABLE `default.customers`(
  `customer_id` varchar(25) COMMENT '顾客id',
  `customer_name` varchar(25) COMMENT '顾客姓名',
  `customer_age` varchar(25) COMMENT '顾客年龄')
PARTITIONED BY ( 
  `date_time` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/default.db/customers'
1
2
3
4
5
6
7
8
9
10
11
12
13
14

插入数据

insert into table default.customers partition(date_time='2022-04-01')
select '1','zhangsan','22'
union all
select '2','lisi','34' 
union all
select '3','wangwu','21' 
union all
select '7','zhaoliu','33' 
union all
select '9','liuqi','44' 
1
2
3
4
5
6
7
8
9
10

创建订单表：orders

CREATE TABLE `default.orders`(
  `order_id` varchar(25) COMMENT '订单id', 
  `customer_id` varchar(25) COMMENT '顾客id', 
  `order_date` varchar(25) COMMENT '订单日期')
PARTITIONED BY ( 
  `date_time` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://master:9000/user/hive/warehouse/default.db/orders'
1
2
3
4
5
6
7
8
9
10
11
12
13
14

插入数据

insert into table default.orders partition(date_time='2022-04-01')
select '1','1','2022-04-01'
union all
select '2','2','2022-03-01' 
union all
select '3','3','2022-03-02' 
union all
select '4','4','2022-03-03' 
union all
select '5','5','2022-03-02' 
union all
select '6','6','2022-03-03'
1
2
3
4
5
6
7
8
9
10
11
12

full outer join语句

select a.customer_id,a.customer_name,a.customer_age,b.order_id,b.order_date from default.customers a
full outer join default.orders b
on a.customer_id=b.customer_id
1
2
3

结果

left join + union + right join语句

select a.customer_id,a.customer_name,a.customer_age,b.order_id,b.order_date from default.customers a
left join default.orders b
on a.customer_id=b.customer_id
union 
select a.customer_id,a.customer_name,a.customer_age,b.order_id,b.order_date from default.customers a
right join default.orders b
on a.customer_id=b.customer_id
1
2
3
4
5
6
7

结果

可以看出结果是一摸一样的。

---

结语

本次的分享就到这里了，下一篇博客博主将带来hive之left semi join（左半连接）使用方法，敬请期待！受益的朋友或对大数据技术感兴趣的伙伴记得点赞关注支持一波！

最后

深知大多数初中级Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则近万的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《Java开发全套学习资料》送给大家，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

小编已加密：aHR0cHM6Ly9kb2NzLnFxLmNvbS9kb2MvRFVrVm9aSGxQZUVsTlkwUnc==出于安全原因，我们把网站通过base64编码了，大家可以通过base64解码把网址获取下来。