Hive:分布式SQL计算工具
作用:将SQL语句解析成MapReduce程序运行
架构:Metastore元数据存储 +Driver驱动解析SQL+用户接口
目录
1.Hive的SQL解析过程
SQL-->抽象语法树-->查询块-->逻辑计划-->物理计划(MRjob)
-
解析器: 将 SQL 字符串转换成抽象语法树( AST )
-
语义分析器:将抽象语法树进一步抽象为基本的查询单元 查询块(QueryBlock)
-
逻辑计划生成器:通过遍历查询块 ,生成逻辑计划
-
逻辑优化器 :对逻辑计划进行优化
-
物理计划生成器 :根据优化后的逻辑计划生成物理计划(MR任务)
-
物理优化器 :对物理计划进行优化
-
执行器:执行该计划(MR任务),得到查询结果并返回给客户端
2.Hive与数据库的区别
- 数据存储:Hive数据存储在HDFS中;数据库将数据保存在块设备或者本地文件系统中。
- 数据格式:Hive可以指定数据格式(指定列分隔符,行分隔符);数据库由数据引擎定义数据格式。
- 数据更新:Hive的内容是读多写少的,不建议对数据改写;数据库中的数据通常是需要经常进行修改。
- 执行延迟:Hive 执行延迟较高,数据库的执行延迟较低。Hive只有在大数据规模下才有优势。
- 索引:Hive没有,数据库有。
- 数据规模:Hive 支持很大规模的数据计算;数据库可以支持的数据规模较小。
3.内部表和外部表
- 内部表:保存元数据和原始数据,删除时会全部删除。
- 外部表:只保存元数据,删除后表的原始数据还在。
使用场景:绝大多数是外部表,防止数据误删,相对来说安全一些,同时也方便共享源数据。
4.分桶表和分区表
- 分区表:按照指定列对数据进行分区,分区相当于将表拆分到不同文件夹中进行存储,一个分区代表一个文件夹,查询时使用分区字段筛选数据可以直接找的对应的目录,避免全表扫描,提高查询效率。
- 分桶表:按照指定列对数据进行分桶,分桶相当于将表拆分到不同文件中进行存储,一个桶代表一个文件,查询时使用分桶字段可以直接定位到对应的分桶,避免全表扫描。 分桶列相同的数据一定在同一个桶内,所以基于分桶列的特定操作(过滤、分组、join)均可以带来性能提升。
使用场景:分区表适合用于多条数据有相同字段值的情况,比如所属分类、日期;分桶表适合用于数据字段没有重复值的情况,或者分区太多更不利于查询的情况,比如表id。
如何进行分桶:假设分桶数量为3,首先计算分桶列的hash值,然后基于分桶数3进行取模,结果为0.1.2分别进入3个桶。
5.Hive的四个by排序
- order by:全局排序,默认只走一个reduce,当数据量非常大时,耗时太长,效率低下,适用于数据量较小的场景。
select * from emp order by sal desc;
- sort by:在数据进入reducer前完成排序,每个reducer内部排序 ,不保证全局有序;适用于数据量较大,但对排序要求不严格的场合,可以大幅度提升执行效率。
set mapreduce.job.reduces=3 #预先设置reducer数量 set mapreduce.job.reduces; select * from emp sort by deptno desc;
- distribute by:相当于分区,按照指定的字段对数据进行划分输出到不同的reducer中,默认使用hash值取余数方法,常和sort by一起使用。
hive (default)> set mapreduce.job.reduces=3; hive (default)> select * from emp distribute by deptno sort by empno desc;
- cluster by:当distribute by和sorts by字段相同时,就等于cluster by,但是排序只能是升序排序。
hive (default)> select * from emp cluster by deptno; hive (default)> select * from emp distribute by deptno sort by deptno;
6.开窗函数
开窗函数(窗口函数)Window Funtion主要有两类:
- 聚合开窗函数
- 排序开窗函数
常见开窗函数
使用格式:函数名(列名) OVER(partition by 列名 order by列名)
- 函数部分:支持聚合函数(count 、sum、avg)、排序函数(row_number、rank)、分析窗口函数(cume_dist)
- 窗口定义部分:over里面的内容,包括了三部分(partition,order,ROWS)
实现原理:窗口函数的实现,主要借助 Partitioned Table Function (即PTF)
- 对于包含窗口函数的sql语句,首先计算除窗口函数以外的其他操作;
- 将第一步的输出作为PTF的输入,计算对应窗口函数的值;
- 最后输出合并数据。
7.表连接方式
8.union和union all区别
- union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序操作;
- union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复,不会进行排序操作;
总结:union all不会进行去重和排序操作,执行效率要比union高。
9.Delet/Drop/Truncate区别
- delet:一般用来删除表中的一行或部分行数据,在不设置WHERE条件下可以删除表中的所有信息,可以回滚;对表执行delete之后,该表不会减少表或者索引所占用的空间。
- truncate是清空的意思,从字面就可以理解为一次删除就可以将表里的数据全部清除,不改变表结构,没有回滚操作;对表执行truncate之后,表和索引就会恢复到初始的大小。
- drop可以将表的结构和数据内容都删除,没有回滚操作;对表执行drop之后,该表所占用的空间全部释放,执行速度快。
速度比较:drop>truncate>delete
10.count详解
- count(1):忽略所有列,用1代表行,统计行数,不会忽略null值。
- count(*):含所有列,统计行数,不会忽略null值。
- count(列名):对指定列进行统计,统计行数,不记录null值。
执行过程:server 层会循环向 InnoDB 读取一条记录,如果 count 函数指定的参数不为 NULL,那么就会将变量 count 加 1,直到符合查询的全部记录被读完,就退出循环,最后将 count 变量的值发送给客户端。
效率排序:count(*)=count(1)>count(主键)>count(字段)
原因:count(*) 执行过程跟 count(1) 执行过程基本一样的,使用 count(*
) 时,MySQL 会将 *
参数转化为参数 0 来处理,所以同样不需要读取记录中的字段值。count(主键)就需要,当前其他执行过程相同,所以稍慢一点。
11.join的实现
select * from testa ta left join testb tb on (ta.col1=tb.col2);
-
首先把驱动表testa的所有数据都加在到join buffer里面(内存操作),这里的所有数据是select后面的testa的字段,因为这里是select *,所以就是加载所有的testa字段。
-
然后遍历的取testb表中的每一行数据,并且与join buffer里面的数据进行对比,符合条件的,就作为结果集返回。
补充:如果join buffer内存的大小不足够容纳驱动表的数量级,则将驱动表(testa)分段加载到join buffer,查询完成后清空join buffer重新加载。
12.炸裂函数
一行变多行(列转行)
- explode炸裂数组,friends 是一个array数组,将数组内的元素炸裂成多行
select explode(friends) from teacher
- explode炸裂map键值对,students 是一个map,将map中元素炸裂成多行,key和value各为一列
select explode(students) from teacher
- posexplode炸裂数组,将数组内的元素炸裂成多行,并增加一列元素下标
select posexplode(friends) from teacher
-
lateral view 侧写视图
lateral view 通常与UDTF配合使用,侧视图的原理是将UDTF的结果构建成一个类似于视图的表,再将原表中的每一行和UDTF函数输出的每一行进行连接,生成一张新的虚拟表
需求:将表1中order_set进行炸裂成多行,目的是每一行的order_id与表2的order_id关联,打上是否异常标签。
select user_id
,user_layer
,explode(ordr_set) as ordr_id --炸裂后是多行,不能对应一行user_id,user_layer
from A -- 报错 不可这样写
select user_id
,user_layer
,ordr_id -- 拆分字段
from A
lateral view explode(ordr_set)tmp1 as ordr_id