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

最新推荐文章于 2024-05-14 03:38:33 发布

牛永刚

最新推荐文章于 2024-05-14 03:38:33 发布

阅读量5.9k

点赞数 1

分类专栏： Hadoop 文章标签： cloudera impala 交集并集差集

本文链接：https://blog.csdn.net/lightcloudDotcc/article/details/42153631

版权

本文详细介绍了如何利用Cloudera Impala的SQL语法进行数据集合操作，包括如何执行交集、并集和差集查询，帮助用户更高效地处理大数据分析任务。

摘要由CSDN通过智能技术生成

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 |
+