优化时。把hive sql当做map reduce程序来读，会有意想不到的惊喜。
理解hadoop的核心能力，是hive优化的根本。这是这一年来，项目组全部成员宝贵的经验总结。
长期观察hadoop处理数据的过程，有几个显著的特征:
1.不怕数据多。就怕数据倾斜。
2．对jobs数比較多的作业执行效率相对照较低，比方即使有几百行的表。假设多次关联多次汇总，产生十几个jobs。没半小时是跑不完的。map reduce作业初始化的时间是比較长的。
3.对sum，count来说，不存在数据倾斜问题。
4.对count(distinct ),效率较低，数据量一多。准出问题，假设是多count(distinct )效率更低。
优化能够从几个方面着手：
1. 好的模型设计事半功倍。
2. 解决数据倾斜问题。
3. 降低job数。
4. 设置合理的map reduce的task数。能有效提升性能。
(比方，10w+级别的计算，用160个reduce，那是相当的浪费，1个足够)。
5. 自己动手写sql解决数据倾斜问题是个不错的选择。set hive.groupby.skewindata=true;这是通用的算法优化，但算法优化总是漠视业务，习惯性提供通用的解决方法。 Etl开发者更了解业务，更了解数据。所以通过业务逻辑解决倾斜的方法往往更精确，更有效。
6. 对count(distinct)採取漠视的方法，尤其数据大的时候非常easy产生倾斜问题。不抱侥幸心理。自己动手，丰衣足食。
7. 对小文件进行合并，是行至有效的提高调度效率的方法，假如我们的作业设置合理的文件数，对云梯的总体调度效率也会产生积极的影响。
8. 优化时把握总体，单个作业最优不如总体最优。
迁移和优化过程中的案例：
问题1：如日志中，常会有信息丢失的问题，比方全网日志中的user_id，假设取当中的user_id和bmw_users关联。就会碰到数据倾斜的问题。
方法：解决数据倾斜问题
解决方法1. User_id为空的不參与关联，比如：
Select *
From log a
Join bmw_users b
On a.user_id is not null
And a.user_id = b.user_id
Union all
Select *
from log a
where a.user_id is null.
解决方法2 ：
Select *
from log a
left outer join bmw_users b
on case when a.user_id is null then concat(‘dp_hive’,rand() ) else a.user_id end = b.user_id;
总结：2比1效率更好，不但io少了。并且作业数也少了。1方法log读取两次，jobs是2。2方法job数是1 。这个优化适合无效id(比方-99,’’,null等)产生的倾斜问题。
把空值的key变成一个字符串加上随机数，就能把倾斜的数据分到不同的reduce上 ,解决数据倾斜问题。由于空值不參与关联，即使分到不同的reduce上，也不影响终于的结果。附上hadoop通用关联的实现方法（关联通过二次排序实现的。关联的列为parition key,关联的列c1和表的tag组成排序的group key,依据parition key分配reduce。同一reduce内依据group key排序）。
问题2：不同数据类型id的关联会产生数据倾斜问题。
一张表s8的日志，每一个商品一条记录。要和商品表关联。
但关联却碰到倾斜的问题。
s8的日志中有字符串商品id,也有数字的商品id,类型是string的，但商品中的数字id是bigint的。推測问题的解决办法是把s8的商品id转成数字id做hash来分配reduce，所以字符串id的s8日志。都到一个reduce上了。解决办法验证了这个推測。
方法：把数字类型转换成字符串类型
Select * from s8_log a
Left outer join r_auction_auctions b
On a.auction_id = cast(b.auction_id as string);
问题3：利用hive 对UNION ALL的优化的特性
hive对union all优化仅仅局限于非嵌套查询。
比方下面的样例：
select * from
(select * from t1
Group by c1,c2,c3
Union all
Select * from t2
Group by c1,c2,c3) t3
Group by c1,c2,c3;
从业务逻辑上说，子查询内的group by 怎么都看显得多余（功能上的多余,除非有count(distinct)），假设不是由于hive bug或者性能上的考量(以前出现假设不子查询group by ，数据得不到正确的结果的hive bug)。所以这个hive按经验转换成
select * from
(select * from t1
Union all
Select * from t2
) t3
Group by c1,c2,c3;
经过測试，并未出现union all的hive bug,数据是一致的。mr的作业数有3降低到1。
t1相当于一个文件夹，t2相当于一个文件夹，那么对map reduce程序来说。t1,t2能够做为map reduce 作业的mutli inputs。那么，这能够通过一个map reduce 来解决问题。
Hadoop的计算框架，不怕数据多。就怕作业数多。
但假设换成是其它计算平台如oracle。那就不一定了。由于把大的输入拆成两个输入。分别排序汇总后merge(假如两个子排序是并行的话)，是有可能性能更优的（比方希尔排序比冒泡排序的性能更优）。
问题4：比方推广效果表要和商品表关联，效果表中的auction id列既有商品id,也有数字id,和商品表关联得到商品的信息。那么下面的hive sql性能会比較好
Select * from effect a
Join (select auction_id as auction_id from auctions
Union all
Select auction_string_id as auction_id from auctions
) b
On a.auction_id = b.auction_id。
比分别过滤数字id,字符串id然后分别和商品表关联性能要好。
这样写的优点,1个MR作业,商品表仅仅读取一次，推广效果表仅仅读取一次。
把这个sql换成MR代码的话。map的时候，把a表的记录打上标签a,商品表记录每读取一条。打上标签b。变成两个<key ,value>对，<b,数字id>，<b,字符串id>。所以商品表的hdfs读仅仅会是一次。
问题5：先join生成暂时表。在union all还是写嵌套查询，这是个问题。
比方下面样例：
Select *
From (select *
From t1
Uion all
select *
From t4
Union all
Select *
From t2
Join t3
On t2.id = t3.id
) x
Group by c1,c2;
这个会有4个jobs。
假如先join生成暂时表的话t5,然后union all。会变成2个jobs。
Insert overwrite table t5
Select *
From t2
Join t3
On t2.id = t3.id
;
Select * from (t1 union all t4 union all t5) ;
hive在union all优化上能够做得更智能（把子查询当做暂时表）。这样能够降低开发者的负担。出现这个问题的解决办法应该是union all眼下的优化仅仅局限于非嵌套查询。假设写MR程序这一点也不是问题，就是multi inputs。
问题6：使用map join解决数据倾斜的常景下小表关联大表的问题。但假设小表非常大。怎么解决。这个使用的频率非常高，但假设小表非常大。大到map join会出现bug或异常。这时就须要特别的处理。云瑞和玉玑提供了非常给力的解决方式。
下面样例：
Select * from log a
Left outer join members b
On a.memberid = b.memberid.
Members有600w+的记录，把members分发到全部的map上也是个不小的开销。并且map join不支持这么大的小表。假设用普通的join，又会碰到数据倾斜的问题。
解决方法：
Select /*+mapjoin(x)*/* from log a
Left outer join (select /*+mapjoin(c)*/d.*
From (select distinct memberid from log ) c
Join members d
On c.memberid = d.memberid
)x
On a.memberid = b.memberid。
先依据log取全部的memberid，然后mapjoin 关联members取今天有日志的members的信息，然后在和log做mapjoin。
假如，log里memberid有上百万个，这就又回到原来map join问题。
所幸。每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多。有佣金的会员不会太多等等。所以这种方法能解决非常多场景下的数据倾斜问题。
问题7：HIVE下通用的数据倾斜解决方法,double被关联的相对较小的表，这种方法在mr的程序里经常使用。还是刚才的那个问题：
Select * from log a
Left outer join (select /*+mapjoin(e)*/
memberid, number
From members d
Join num e
) b
On a.memberid= b.memberid
And mod(a.pvtime,30)+1=b.number。
Num表仅仅有一列number，有30行，是1,30的自然数序列。就是把member表膨胀成30份。然后把log数据依据memberid和pvtime分到不同的reduce里去。这样能够保证每一个reduce分配到的数据能够相对均匀。就眼下測试来看。使用mapjoin的方案性能稍好。后面的方案适合在map join无法解决这个问题的情况下。
长远设想，把例如以下的优化方案做成通用的hive优化方法
1. 採样log表。哪些memberid比較倾斜，得到一个结果表tmp1。因为对计算框架来说，全部的数据过来。他都是不知道数据分布情况的，所以採样是并不可少的。
Stage1
2. 数据的分布符合社会学统计规则，贫富不均。倾斜的key不会太多。就像一个社会的富人不多，奇特的人不多一样。所以tmp1记录数会非常少。
把tmp1和members做map join生成tmp2,把tmp2读到distribute file cache。这是一个map过程。Stage2
3. map读入members和log，假如记录来自log,则检查memberid是否在tmp2里，假设是，输出到本地文件a,否则生成<memberid,value>的key,value对。假如记录来自member,生成<memberid,value>的key,value对。进入reduce阶段。
Stage3.
4. 终于把a文件。把Stage3 reduce阶段输出的文件合并起写到hdfs。
这种方法在hadoop里应该是能实现的。Stage2是一个map过程。能够和stage3的map过程能够合并成一个map过程。
这个方法目标就是：倾斜的数据用mapjoin,不倾斜的数据用普通的join。终于合并得到完整的结果。
用hive sql写的话，sql会变得非常多段。并且log表会有多次读。倾斜的key始终是非常少的，这个在绝大部分的业务背景下适用。
那能否够作为hive针对数据倾斜join时候的通用算法呢？
问题8：多粒度(平级的)uv的计算优化，比方要计算店铺的uv。还有要计算页面的uv,pvip.
方案1:
Select shopid,count(distinct uid)
From log group by shopid;
Select pageid, count(distinct uid),
From log group by pageid;
因为存在数据倾斜问题，这个结果的执行时间是很长的。
方案二：
From log
Insert overwrite table t1 (type=’1’)
Select shopid
Group by shopid ,acookie
Insert overwrite table t1 (type=’2’)
Group by pageid,acookie;
店铺uv:
Select shopid,sum(1)
From t1
Where type =’1’
Group by shopid ;
页面uv:
Select pageid,sum(1)
From t1
Where type =’1’
Group by pageid ;
这里使用了multi insert的方法。有效降低了hdfs读，但multi insert会添加hdfs写。多一次额外的map阶段的hdfs写。使用这种方法，能够顺利的产出结果。
方案三：
Insert into t1
Select type,type_name,’’ as uid
From (
Select ‘page’ as type,
Pageid as type_name,
Uid
From log
Union all
Select ‘shop’ as type,
Shopid as type_name,
Uid
From log ) y
Group by type,type_name,uid;
Insert into t2
Select type,type_name,sum(1)
From t1
Group by type,type_name;
From t2
Insert into t3
Select type,type_name,uv
Where type=’page’
Select type,type_name,uv
Where type=’shop’ ;
终于得到两个结果表t3,页面uv表，t4,店铺结果表。从io上来说。log一次读。
但例如案2少次hdfs写（multi insert有时会添加额外的map阶段hdfs写）。作业数降低1个到3，有reduce的作业数由4降低到2，第三步是一个小表的map过程，分下表。计算资源消耗少。但方案2每一个都是大规模的去重汇总计算。
这个优化的主要思路是。map reduce作业初始化话的时间是比較长，既然起来了，让他多干点活，顺便把页面按uid去重的活也干了，省下log的一次读和作业的初始化时间。省下网络shuffle的io。但添加了本地磁盘读写。效率提升较多。
这个方法适合平级的不须要逐级向上汇总的多粒度uv计算，粒度越多。节省资源越多。比較通用。
问题9：多粒度。逐层向上汇总的uv结算。
比方4个维度。a,b,c,d，分别计算a,b,c,d,uv。
a,b,c,uv;a,b,uv;a;uv,total uv4个结果表。
这能够用问题8的方案二，这里因为uv场景的特殊性。多粒度，逐层向上汇总，就能够使用一次排序，全部uv计算受益的计算方法。
案例：眼下mm_log日志一天有25亿+的点击数。要从mm日志中计算uv，与ipuv,一共计算
三个粒度的结果表
（memberid,siteid,adzoneid,province,uv,ipuv） R_TABLE_4
（memberid,siteid,adzoneid,uv,ipuv） R_TABLE_3
(memberid,siteid,uv,ipuv) R_TABLE_2
第一步：按memberid,siteid,adzoneid,province,使用group去重,产生暂时表，对cookie,ip
打上标签放一起，一起去重，暂时表叫T_4;
Select memberid,siteid,adzoneid,province,type,user
From(
Select memberid,siteid,adzoneid,province,‘a’ type ,cookie as user from mm_log where ds=20101205
Union all
Select memberid,siteid,adzoneid,province,‘i’ type ,ip as user from mm_log where ds=20101205
) x group by memberid,siteid,adzoneid,province,type,user ;
第二步：排名,产生表T_4_NUM.Hadoop最强大和核心能力就是parition 和 sort.按type，acookie分组，
Type，acookie，memberid,siteid,adzoneid,province排名。
Select * ,
row_number(type,user,memberid,siteid,adzoneid ) as adzone_num ,
row_number(type,user,memberid,siteid ) as site_num,
row_number(type,user,memberid ) as member_num,
row_number(type,user ) as total_num
from (select * from T_4 distribute by type,user sort by type,user, memberid,siteid,adzoneid ) x;
这样就能够得到不同层次粒度上user的排名，同样的user id在不同的粒度层次上，排名等于1的记录仅仅有1条。
取排名等于1的做sum，效果相当于Group by user去重后做sum操作。
第三步：不同粒度uv统计，先从最细粒度的開始统计，产生结果表R_TABLE_4,这时。结果集仅仅有10w的级别。
如统计memberid,siteid,adzoneid,provinceid粒度的uv使用的方法就是
Select memberid,siteid,adzoneid, provinceid,
sum(case when type =’a’ then cast(1) as bigint end ) as province_uv ,
sum(case when type =’i’ then cast(1) as bigint end ) as province_ip ,
sum(case when adzone_num =1 and type =’a’ then cast(1) as bigint end ) as adzone_uv ,
sum(case when adzone_num =1 and type =’i’ then cast(1) as bigint end ) as adzone_ip ,
sum(case when site_num =1 and type =’a’ then cast(1) as bigint end ) as site_uv ,
sum(case when site_num =1 and type =’i’ then cast(1) as bigint end ) as site_ip ,
sum(case when member_num =1 and type =’a’ then cast(1) as bigint end ) as member_uv ,
sum(case when member_num =1 and type =’i’ then cast(1) as bigint end ) as member_ip ,
sum(case when total_num =1 and type =’a’ then cast(1) as bigint end ) as total_uv ,
sum(case when total_num =1 and type =’i’ then cast(1) as bigint end ) as total_ip ,
from T_4_NUM
group by memberid,siteid,adzoneid, provinceid ;
广告位粒度的uv的话，从R_TABLE_4统计，这是源表做10w级别的统计
Select memberid,siteid,adzoneid,sum(adzone_uv),sum(adzone_ip)
From R_TABLE_4
Group by memberid,siteid,adzoneid；
memberid,siteid的uv计算 ，
memberid的uv计算,
total uv 的计算也都从R_TABLE_4汇总。