室友不知道Hive中的过滤多样性，我靠一本书给他讲的明明白白

玩了几个小时游戏，愧疚心理让我开始更文

小谈：

        玩了一下午Lol手游，昨天说好好好学习的，结果玩了半天的游戏，挺不想学习，不知道为啥，过了一会，竟然很认真的看起了书。不能这样摆烂，昨天定的flag。坚持下去，不玩游戏。

过滤模式，即对数据的过滤，从过滤的粒度来看，分为数据行过滤，数据列过滤，文件过滤和目录过滤。

Where子句过滤模式：

explain 
select * from student_tb_seq 
where s_age =19 and s_name like '%红%' 
and s_score in (100,50,22)

        先提出一个问题，请问Where条件是在什么时候起作用的？

        上次讲过Hive的语句执行顺序。现在问的是where是在Map还是Reduce阶段继续工作的。

        先说答案，Where是在Map阶段进行工作的。

看下面的讲解。

 

        我们可以看到 Predicate(谓语)后面就是我们的过滤条件。这个操作是在Map Operator Tree中进行中。所以where过滤是在Map中进行，而不是在Reduce中进行的。

        通过where子句的过滤模式，启示我们对于一个作业应尽量将其放在前面的环节。

select count(s_age) 
from 
( select s_age, count(1) num 
from student_tb_seq 
group by s_age ) a
where s_age <30 and num>20

        上面的SQL语句，Where过滤是在所有操作都完成之后才开始的。

        并不是很好，我们应该将where过滤提前，这样就可以提前过滤掉不用的数据。

--优化后
select count(s_age) from
( select s_age, count(1) num 
from student_tb_seq
where s_age<30 group by s_age having count(1)>20 ) 
a

小总结：

        Where的filter操作发生在Map阶段，说明Where在Map阶段就已经结束了。对于where过滤，我们应该提前过滤。

Having子句过滤

        having子句过滤发生在数据聚合之后，Having子句是在Reduce阶段进行过滤的

  

 select s_age, count(1) num 
from 
student_tb_orc 
group by s_age
 having count(1)>20

 

        怎么看出来是在having中进行过滤的呢，上面的Filter Operator中就是过滤条件。count(1)>20。

        在Reduce阶段进行分组聚合做数据过滤。

        Having子句也有着非常丰富的作用，因为having面对的是实体的一个集合。Having子句非常的强大，可以看<SQL进阶>中的讲解。

Distinct子句过滤

        distinct子句用来过滤一个列中有重复的数据。在Hive中也是发生在Reduce阶段里面。

        

explain select distinct s_Age from student_tb_seq

 

        从上图可以看到，在Reduce阶段里面进行分组聚合，根据s_Age进行分组聚合。

        本来 distinct s_age 是对s_age这个字段进行去重。从执行计划可以看出来，distinct去重是将s_age字段中每一个数据进行分组，分组之后，s_age这个字段里面的数据都已经不会再重复了。

        那么，我们根据上面的解释就可以写出下面的SQL语句。

select s_age from student_tb_seq group by s_age

同样，上面这个语句的执行计划就是我们distinct的执行计划一摸一样

 

表过滤

        表过滤是指过滤掉同一个SQL语句需要多次访问相同的表的数据，重复的访问操作过滤掉并压缩成只读取一次。表过滤的常见操作就是使用multi-group by 语法替换多个查询语句并求并集。

        光这样说，大家可能对表过滤还是不太懂，下面通过两个例子进行演示。

        在动态分区里面，我们将相应的数据插入到相应的分区表里面。

        第一个例子通过使用Union 将两个查询的结果放到一个数据集里面，然后将这个数据集根据分区字段进行分区。原本我们要分两次的动态分区插入，现在只需要一次动态分区插入就可以。不过需要先将两个查询进行union。这两个查询的对象都是同一个表。

set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict;
explain 
insert into table student_stat partition(tp) 
select s_age,max(s_birth) stat,'max' tp
from student_tb_seq 
group by s_age 
union all
select s_age,min(s_birth) stat, 'min' tp 
from student_tb_seq
group by s_age;

上面表的操作，就分为三部。

1. 分别用两个查询求出最大值和最小值
2. 将两个查询的结果进行合并
3. 将合并后的结果按照分区字段进行分区

        在计算最大值和最小值的时候对表student_tb_seq进行两次Table Scan（表扫描）的重复动作。如果将两次的重复扫描整合成只需要一次就可以进行操作。

优化

set hive.exec.dynamic.partition = true
set hive.exec.dynamic.partition.mode = nonstrict 
from student_tb_seq
insert into table student_stat partition(tp)
select s_age,max(s_birth) stat,'max' tp
group by s_age
insert into table student_stat partition(tp) 
select s_age,min(s_birth) stat,'min' tp 
group by s_age

        上面的SQL语句同样也是实现动态分区插入。不过不同的是，上面这个SQL语句对student_tb_seq进行插入，不过不同的是，这次只需要一次表扫描，会同时计算最大值和最小值，并且将最大值和最小值进行动态插入。

分区过滤

        Hive中所谓的分区过滤，其实就是对分区表中的分区列字段进行筛选。

        在分区表里面，会多一个字段，就是分区字段，而我们的分区过滤就是根据分区字段进行过滤的。分区过滤的发生其实不在Map阶段发生，他是在Map之前发生的。，即在输入路径进行路径的过滤的。

        为什么是在map之前进行过滤的，其实得益于分区表的存储格式。每个分区表都是以目录的格式存在的。

 

        过滤的时候就直接在这里进行过滤

        比如需要part = 2的数据

        直接

        select * from student_ord_partition where part = 2

        就可以得到想要的数据。

分桶过滤

        分桶能够对原有的表或者分区所存储的数据进行重新组织。

        可以说分区是对目录的过滤，分桶是对文件的过滤。

create table weblog(a string,b string)
clustered by(a) into 4 BUCKETS;

上图就是创建一个分桶表，根据字段a分为4个桶。

为了四个桶，就会对a字段的数据进行hash分桶。怎么个hash呢？

        1 % 4 = 1

        2 % 4 = 2

        3 % 4 = 3

        4 % 4 = 0

就是上面的那样，hash分桶。

 

        从上面的表中，可以看到，原本的表中有1002个文件。

        现在创建一个分桶表

create table student_orc_bucket()

clustrerd by () into 16 buckets

        正如图片中的所示，这个分桶表有16个字段，意味我们分了16个桶。原本需要1002个文件的数据，通过创建分桶表将文件从1002减少到了16个文件。

        可是减少了有什么意义呢？

        解释一下：在我们的查询语句中，如果字段中有我们的分桶字段，那么Hive会根据我们的分桶字段快速定位到那个桶里面，然后在桶里面进行查询。

如果不采用分桶表，我们就会进行整个表的扫描，这样费时间。而且极大的缩短了读取数据的时间

列过滤：

        在SQL当中，我们可以使用select对字段进行过滤，但是在存储的时候，我们是将数据存储在hdfs中的，如果不使用特殊的数据存储格式，一般查找一个列的字段，通常先要取行的数据，然后根据偏移量取得相应的列值。

        但是对于个别的存储格式，并不需要这么麻烦。

        如果存储格式是ORC或者Parquet格式的表，在表的Schema中就会记录各个表的信息，包括字段，最大值，最小值，多少行数据之类的，都会作为附加信息存储在Schema中。

总结：

        这篇文章是我花费了将近两个小时写的，在写的过程中，我对学过的知识进行了再一次的总结，让我记忆更加深刻，虽然可能过了一段时间后会忘记这些知识，就行那些配置参数，现在记住了过了几天就忘记了，记得一句话，叫做“无他，为手熟尔”

        所以我们需要多写，多练才可以。

        断更了好多天的SQL每日明天也要开始继续运作了。

        加油，不再摆烂·小学弟

最后说一句，本篇博客的指导书籍《Hive性能调优实践》