全方位揭秘!大数据从0到1的完美落地之Hive查询操作

v2-4b22527985384cedba02ae95e6d244cc_250x0

Hive基本查询语法

基本使用规则

基本查询语句组成
select ..
from ..
	join [tableName] on ..
	where ..
	group by ..
	having ..
	order by ..
	sort by ..
	limit ..
union | union all ...
执行顺序
第一步: FROM <left_table>
第二步: ON <join_condition>
第三步: <join_type> JOIN <right_table>
第四步: WHERE <where_condition>
第五步: GROUP BY <group_by_list>
第六步: HAVING <having_condition>
第七步: SELECT
第八步: DISTINCT <select_list>
第九步: ORDER BY <order_by_condition>
第十步: LIMIT <limit_number>

标准sql语句的一些规则:
-1. 列别名的使用,必须完全符合执行顺序,不能提前使用。(mysql除外)
-2. 在分组查询时,select子句中只能含有分组字段和聚合函数,不能有其他普通字段。(mysql除外)
查询原则
1. 尽量不使用子查询、尽量不使用in 或者not in (可以使用 [not] exists替代)
2. 尽量避免join连接查询,但是通常避免不了
3. 查询永远是小表驱动大表(小表作为驱动表)
  --注意:内连接时,默认是左表是驱动表,因此左表一定要是小表。
  --	 外连接看需求而定。  

常用子句回顾

where语句特点
where后不能使用聚合函数,可以使用子查询,也可以是普通函数。
条件可以是:
1. 关系表达式: =, >,>=,<,<=,!=,<>
2. 连接符号:  or,and, between .. and ..
3. 模糊查询: like   
			 %:通配符
			 _:占位符
4. [not] in
    >all(set)  >any();

注意事项:在hive的where中如果使用了子查询作为条件,等号“=”不好使,需要使用[not] in.
		换句话说,即使子查询返回的是唯一的一个值,也是集合形式。
		

group by语句特点

group by: 分组,通常和聚合函数搭配使用

查询的字段要么出现在group by 后面,要么出现在聚合函数里面

聚合函数:count(),sum(),max(),min(),avg()

count的执行
1. 执行效果上:
	- count(*)包括了所有的列,相当于行数,在统计结果的时候不会忽略null值
	- count(1)包括了所有列,用1代表行,在统计结果的时候也不会忽略null值
	- count(列名)只包括列名那一列,在统计结果时,会忽略null值

2.执行效率上:
	- 列名为主键,count(列名)会比count(1)快
	- 列名不为主键,count(1)会比count(列名)快
	- 如果表中有多个列并且没有主键,count(1)的效率高于count(*)
	- 如果有主键count(主键)效率是最高的
	- 如果表中只有一个字段count(*)效率最高
having子句特点
对分组以后的结果集进行过滤。可以使用聚合函数。
order by子句
对查询的数据进行排序。
desc 降序
asc	 升序

语法:
order by colName [desc|asc][,colName [desc|asc]]

limit语句特点

limit : 从结果集中取数据的条数
将set hive.limit.optimize.enable=true 时,limit限制数据时就不会全盘扫描,而是根据限制的数量进行抽样。

同时还有两个配置项需要注意:
hive.limit.row.max.size=100000        这个是控制最大的抽样数量【默认值是100000】
hive.limit.optimize.limit.file=10 这个是抽样的最大文件数量【默认值是10个】
缺点:可能输入中有用的数据永远都不会被抽样到。

注意:limit 在mysql中 可以有两个参数 limit [m,] n
	       在hive中,只能有一个参数 limit n;  查询前n条。
	  一般情况下,在使用limit时,都会先order by排序。     

join连接

join知识点回顾

有的业务所需要的数据,不是在一张表中,通常会存在多张表中,而这些表中通常应该会存在"有关系"的字段。多表查询时,使用关联字段"连接"(join)在一起,组合成一个新的数据集,就是连接查询。

连接查询操作分为两大类:内连接和外连接,而外连接有细分为三种类型。参考下图

1. 内连接:  [inner] join
2. 外连接 (outer join):(引出一个驱动表的概念:驱动表里的数据全部显示)
  - 左外连接:left [outer] join, 左表是驱动表
  - 右外连接:right [outer] join, 右表是驱动表
  - 全外连接:full [outer] join, hive支持,mysql不支持.两张表里的数据全部显示出来
3. 注意: join连接只支持等值连接  

需要大家注意的是,两张表的关联字段的值往往是不一致的。比如,表 A 包含张三和李四,表 B 包含李四和王五,匹配的只有李四这一条记录。从上图很容易看出,一共有四种处理方式和结果。下图就是四种连接的图示,这张图比上面的维恩图更易懂,也更准确。

join

上图中,表 A 的记录是 123,表 B 的记录是 ABC,颜色表示匹配关系。返回结果中,如果另一张表没有匹配的记录,则用 null 填充,这四种连接,又可以分成两大类:内连接(inner join)表示只包含匹配的记录,外连接(outer join)表示还包含不匹配的记录。所以,左连接、右连接、全连接都属于外连接。

笛卡尔积

指的是表 A 和表 B 不存在关联字段,这时表 A(共有 n 条记录)与表 B (共有 m 条记录)连接后,会产生一张包含 n x m 条记录(笛卡尔积)的新表。

cross

案例演示:

准备数据
u1文件中的数据如下:
1,a
2,b
3,c
4,d
7,y
8,u

u2文件中的数据如下:
2,bb
3,cc
7,yy
9,pp

create table if not exists u1(
id int,
name string
)
row format delimited 
fields terminated by ','
;

create table if not exists u2(
id int,
name string
)
row format delimited fields terminated by ','
;

load data local inpath './data/u1.txt' into table u1;
load data local inpath './data/u2.txt' into table u2;
left semi join

在hive中,有一种专有的join操作,left semi join,我们称之为半开连接。它是left join的一种优化形式,只能查询左表的信息,主要用于解决hive中左表的数据是否存在的问题。相当于exists关键字的用法。

先回顾exists关键字的用法:exists关键字:满足条件返回true,不满足条件返回false

练习:  
查询有领导的员工信息
	  select * from emp where mgr is not null
	  select * from emp A where exists (select 1 from emp B where B.empno = A.mgr )	
	  select * from emp A left semi join emp B on A.mgr = B.empno;
	  
查询有下属的员工信息
      select * from emp A where exists (select 1 from emp B where B.mgr = A.empno )
      
查看有部门的所有员工的信息
      select * from emp A where exists (select 1 from dept B where B.deptno = A.deptno )

left semi join的写法。

-- 左外连接,左表中的数据全部返回
select * from u1 left join u2 on u1.id =u2.id;
select * from u1 left outer join u2 on u1.id =u2.id;
-- 左半开连接,只显示左表中满足条件的数据。和exists的逻辑是相同的
select * from u1 left semi join u2 on u1.id =u2.id;
-- exists的写法
select * from u1 where exists (select 1 from u2 where u2.id =u1.id);

--验证left semi join 是否可以显示第二张表的信息:错误写法。
select A.*, B.* from u1 A left semi join u2 B on A.id =B.id;

注意 hive中不支持right semi join

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值