使用cloudera impala sql 进行交集、并集、差集查询

impala-shell -i hadoop3 -f join_test.sql


Starting Impala Shell without Kerberos authentication
Connected to hadoop3:21000
Server version: impalad version 1.2.4 RELEASE (build ac29ae09d66c1244fe2ceb293083723226e66c1a)


use db02
show tables
+------+
| name |
+------+
| d1   |
| d2   |
+------+
Returned 2 row(s) in 0.01s


select * from d1
+-----------+-----+---------+
| username  | age | is_male |
+-----------+-----+---------+
| zhangshan | 23  | 1       |
| lisiiiii  | 24  | 1       |
| wangmazi  | 30  | 1       |
| meinvvvv  | 18  | 0       |
| damaaaaa  | 55  | 0       |
+-----------+-----+---------+
Returned 5 row(s) in 0.19s


select * from d2
+-----+---------+
| age | options |
+-----+---------+
| 1   | a       |
| 23  | bb      |
| 50  | ccc     |
| 30  | dddd    |
| 66  | eeeee   |
+-----+---------+
Returned 5 row(s) in 0.16s


#交集
select * from d1 a inner join d2 b on a.age=b.age
+-----------+-----+---------+-----+---------+
| username  | age | is_male | age | options |
+-----------+-----+---------+-----+---------+
| zhangshan | 23  | 1       | 23  | bb      |
| wangmazi  | 30  | 1       | 30  | dddd    |
+-----------+-----+---------+-----+---------+
Returned 2 row(s) in 0.29s


#多个条件下的交集
select * from d1 a inner join d2 b on a.age=b.age and a.username in ('zhangshan','lisiiiii','meinvvvv') and b.options in ('bb','ccc')
+-----------+-----+---------+-----+---------+
| username  | age | is_male | age | options |
+-----------+-----+---------+-----+---------+
| zhangshan | 23  | 1       | 23  | bb      |
+---------