HIVE高级查询之CTE,MapJoin，数据排序（不得不提一下数据倾斜）,窗口函数

CTE(common table expression)

with a as (select * from table1)
select * from a;

MapJoin

小表关联大表，解决数据倾斜的问题

set hive.auto.convert.join = true              //默认为true
set hive.mapjoin.smalltable.filesize=25000000 //设置小表不超过多大时开启 mapjoin 优化  ，默认25MB左右

系统会以mapjoin为优先，这样运行速度更快。

这边就必须要提一下数据倾斜

由于数据大量的集中到一点，造成数据热点,
hadoop框架特点：
不怕数据多，就怕数据倾斜；
job数较多作业运行的速度就慢（因为每个job启动之后在rousourcemanager那都要走一系列的流程），如having的运行就会产生很多的job，所以尽量在where进行数据清洗；
聚合函数通常不会有数据倾斜的问题；
如何发现数据倾斜：
在8088端口，查看任务进度维持在99%或者100%附近，查看任务监控界面，发现只有少量的reduce任务未完成，因为其处理的数据量和其他reduce的差异过大，最长时间远大于平均时间
容易导致数据倾斜的情况：
groupby 不和聚合函数搭配使用
count(distinct)
小表关联超大表
特殊值过多，如null或者0，会让reduce非常耗时
针对问题的解决办法

问题1：空值产生的数据倾斜：
把为空的数据挑出来，不参与排序，最后union all 进去

select * from table1 a join table2 b on a.id is not null and a.id =b.id
union all 
select * from table1 c where c.id is null;

给空值赋一个新的值，这样效率更高，（只查一次表，减少IO和作业数）

select * from table1 a left jojin table2 b 
on case when a.id is null then concat('hive',rand()) else a.id end = b.id;

2.不同的数据类型关联导致产生数据倾斜
例如有些id的类型为int,有些id的类型为string,reduce的时候，会导致string类型的被分到一个reducer中
解决办法：转型