Hive优化
如果你想查看hive的优化器，可以从github上面拉一份hive的源码，在org.apache.hadoop.hive.ql.optimizer目录下可以看到hive里面有哪些逻辑优化器。
下面介绍一些常见的优化器:
列裁剪优化器
官方解释
Implementation of one of the rule-based optimization steps. ColumnPruner getsthe current operator tree. The tree is traversed to find out the columnsused for all the base tables. If all the columns for a table are not used, aselect is pushed on top of that table (to select only those columns). Sincethis changes the row resolver, the tree is built again. This can be optimizedlater to patch the tree
我们sql中都会用到列裁剪。所谓列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区。以我们的日历记录表为例：
select uid,uname,sexfrom user_infowhere dt >= '20190201'and dt <= '20190224'and age = 18;当列很多或者数据量很大时，如果select * 或者不指定分区，全列扫描和全表扫描效率都很低。
Hive中与列裁剪优化相关的配置项是hive.optimize.cp，与分区裁剪优化相关的则是hive.optimize.pruner，默认都是true。在HiveSQL解析阶段对应的则是ColumnPruner逻辑优化器。
PDD(Predicate Pushdown)
谓词下推优化器，在许多数据库中都会使用到，简单说就是把后面的查询条件前置，以下面sql来讲：
select a.*, b.* from a join b on (a.col1 = b.col1) where a.col1 > 20and b.col2 > 40大部分人可能认为应该通过将 a.col1 > 20 and b.col2 > 40 放到a表和b表里做子查询，减少数据量输入，这样做没有任何问题，但是上面这种写法，通过谓词下推优化器可以实现在读取a表和b表的同时将不符合条件的数据过滤掉。所以有时候不需要通过写子查询减少数据量输入，上面这种语法更加干净整洁。
mapjoin
Map joins have restrictions on which joins can be converted as memory restrictions
简单来说map join就是把小表加入到内存中，直接把相同的key进行join处理，减少shuffle过程，可以极大提高工作效率，适用于码表或者一些大表和小表join的情况。下面是执行流程图：
1.先启动Task A；Task A启动一个MapReduce的local task；通过该local task把small table data的数据读取进来；之后会生成一个HashTable Files；之后将该文件加载到分布式缓存中；
2.启动MapJoin Task，读大表的数据，每读一个就会和Distributed Cache中的数据关联一次，关联上后进行输出，整个阶段中没有reduce 和 shuffle。
SkewJoinOptimizer
数据倾斜优化器：主要应用在发生倾斜的任务中。数据倾斜的情况相信大家也经常遇到，如mapreduce任务进度长时间等待在99%或者一些内存溢出的情况等。产生数据倾斜的原因有很多种，倾斜的原理是很多相同的key用同一个reduce处理，导致处理的任务过大，如共有200亿数据，有100亿为男生 100亿为女生，如图只有两个reduce处理数据：
上述情况发生了数据倾斜，两个reduce承受了所有的压力，不会有第三个reduce处理数据。
hive倾斜的优化器把一个shuffle拆分成两个shuffle过程：1、第一个shuffle过程：给key增加一个随机数，因此生成的hash值也不尽相同，相同的随机数+相同的原key生成的hash值依然一样，如此数据就会放到一起；
2、第二个shuffle过程：将前边的随机数去掉，重新聚合可以得到想要的结果。
通过分批处理解决数据倾斜问题的方案也是在spark等其他大数据计算引擎中通用且有效的方法。
总结
hive的优化器其实很多，通过学习hive优化器的原理，让我们可以写出效率更高的sql，如果有兴趣的话可以从github下载hive源码去学习更多优化器的详细内容。
hql语法进阶与常用小技巧
CTE查询
通过as将查询语句作为一个临时存储表给后边的查询使用，可以使你的sql更加灵活简洁。
WITH t1 as (select * from t1 where name = '1'),t2 as (select * from t2 where name = '2')select * from t1 join t2 on t1.id = t2.id列匹配正则表达式
一个表一千多个列痛苦不痛苦？通过设置 SET hive.support.quoted.identifiers = none；可以输出正则匹配到的列并排除一些不需要的列。
SELECT `^o.*` from table_name;多表插入
有时候我们会遇到不同的查询条件或者需要将一个大表中的数据拆分到不同的数据表中，如果每个数据表写一个sql会造成资源浪费效率也比较低，这时候from语法就来了，如下
from table_nameinsert overwrite table table1 where id > 100insert overwrite table table2 where name = 'lee'可以一次读取table_name表数据插入不同表中
cube rollup
很多时候我们除了需要在报表中罗列出每个具体项的数据，还需要进行汇总，并且是不同维度的汇总。如果在展示表格的时候汇总，可能会比较慢，我们一般是把结果计算出来之后，以'ALL'或者'总计'，'汇总'等字样作为项的名称，然后放入汇总值。如果仅仅是所有行的汇总，一次聚合就搞定。但是不同维度的汇总就会很麻烦,这时候就轮到cube rollup出场了。
cube函数
cube(a,b,c)则首先会对(a,b,c)进行group by，然后依次是(a,b), (a,c), (a), (b,c), (b), (c)，最后在对全表进行group by，他会统计所选列中值的所有组合的聚合，用cube函数就可以完成所有维度的聚合工作。
select a,b,c from table_name group by a,b,c with cube如果我们想要手动实现cube函数需要把所有维度的聚合都用union all来汇总.可以说cube函数方便了用户的使用.但是我并不用知道所有维度的聚合,我就想要col1,(col2,col3)的怎么办?
grouping sets
select a,b,c from table_name group by a,b,c grouping sets(a,(b,c));rollup
rullup函数是cube的子集，以最左侧维度为主,按照顺序依次进行聚合。例如聚合的维度为 col1,col2,col3 使用rollup聚合的字段分别为 col1, (col1,col2), (col1,col3), (col1,col2,col3)。
select a,b,c from table_name group by a,b,c with rollup;行转列
提到行转列相信大家首先想到的是explode，配合lateral view可以实现一行变多行。
select a, b, c from table_name t lateral view explode(t.e) as c;--可以配合split使用select a, b, c from table_name t lateral view explode(split(t.e,',')) as c;--可以配合json另外lateral view 还可以配置json_tuple使用，抽取json的多个字段。
SELECT *from(select * from table_name ) t1lateral view json_tuple(json_field,'a','b','c') state_json as a,b,c这样会把json里面的三个value加载到已有字段后
行转列对应hive里面的udtf，如果现有的函数满足不了你的需求，可以开发一个udtf来实现自定义的行转列操作。
列转行
多行变成一行，其实就是聚合操作，通过group by操作可以实现聚合操作，有时候我们需要把字段信息也保留下来，这时候就用到了collect_ws、collect_set。
--根据a b 聚合，对c去重select a,b,collect_set(c) from table_name group by a,b;--根据a b 聚合，然后把c放到一个数据select a,b,collect_list(c) from table_name group by a,b;--根据a b 聚合，然后使用，拼接c字段select a,b,concat_ws(",",collect_list(c)) from table_name group by a,b;窗口函数
窗口函数是用于分析的一类函数，要理解窗口函数要先从聚合函数说起。大家都知道聚合函数是将某列中多行的值合并为一行，比如sum、count等。而窗口函数则可以在本行内做运算，得到多行的结果，即每一行对应一行的值。通用的窗口函数可以用下面的语法来概括：
Function() Over (Partition By Column1,Column2,Order By Column3);窗口函数分三类：聚合型窗口函数、分析型窗口函数、取值型窗口函数
聚合型
聚合型即SUM(), MIN(), MAX(), AVG(), COUNT()这些常见的聚合函数。聚合函数配合窗口函数使用可以使计算更加灵活。
select *,sum/count/max/min/avg(file_name) over(partition by file_name order by field_name) from table_name分析型
分析型即RANk(), ROW_NUMBER(), DENSE_RANK()等常见的排序用的窗口函数，不过他们也是有区别的。
select *,--俩个元素相同会跳过下一个序列号rank() over (order by create_time) as user_rank,--生成连续的序列号row_number() over (order by create_time) as user_row_number,--俩个元素相同不会跳过下一个序列号dense_rank() over (order by create_time) as user_dense_rankfrom table_name取值型
这几个函数可以通过字面意思可知，LAG是迟滞的意思，用于统计窗口内往上第n行值；LEAD是LAG的反义词，用于统计窗口内往下第n行值；FIRST_VALUE是该列到目前为止的首个值，而LAST_VALUE是到目前行为止的最后一个值。
--以贷款为例SELECT *,--取上一笔贷款的日期,缺失默认填NULLLAG(orderdate, 1) OVER(PARTITION BY name ORDER BY orderdate) AS last_dt,--取下一笔贷款的日期,缺失指定填'1970-1-1'LEAD(orderdate, 1,'1970-1-1') OVER(PARTITION BY name ORDER BY orderdate) AS next_dt,--去第一个的日期FIRST_VALUE(orderdate) OVER(PARTITION BY name ORDER BY orderdate) AS first_dt,--取最后一个日期LAST_VALUE(orderdate) OVER(PARTITION BY name ORDER BY orderdate) AS latest_dtfrom table_name总结
本文主要分享了hive的优化器和SQL使用技巧。最后，不管你是SQL boy or SQL girl，只要掌握一些技巧，相信都能够Happy SQL querying 。
