目录
一、一个实际场景，组合多的问题
1、场景描述
2、如何优化？
二、Hive中的排序
1、order by (全局排序)
2、sort by (分区内排序)
3、distribute by (分区排序)
4、cluster by (简写)
5、一个关于排序的实际场景例子
（1）问题描述
（2）解决方法
三、hive中的join
1、普通的join（left，inner等）（common/shuffle/reduce join）
2、map join 大小表关联
3、SMB join 大表关联大表
四、hive的参数优化
1、combine的参数
(1)combine 在map端聚合
(2)在map端进行聚合的条数
2、负载均衡
(1)数据倾斜的时候
五、思考题
1、分桶表
（1）概念
（2）创建一个分桶表
（3）查看分桶表内
（4）查看HDFS
（5）分桶表常用在抽样查询
（6）实现 SMB join
2、Count(Distinct) 去重统计
3、行列过滤
4、避免笛卡尔积
一、一个实际场景，组合多的问题
1、场景描述
-- a ，b，c等字段是用户的标签
select a , b ,c ......
count(distinct user_id)
from table_a ----数据大约10亿条
group by ds
,cube(a,b,c,d,e,f,g) --- 128种组合
,grouping sets(
x ,y ,z , ()
) --- 4 种组合
可能最后结果只有几百条
2、如何优化？
找分析师，聊清楚具体想要哪些组合的统计
不要把精力都钻到 SQL上 ！！要了解相应的业务
二、Hive中的排序
1、order by (全局排序)
select * from emp order by sal ;只有一个 reduce ，这里无论设置多少个reduce，都是一个
优点：全局排序
缺点：当数据量大的时候，耗时长，效率低，适用于数据量小的场景
2、sort by (分区内排序)
对每一个reduce内部数据进行排序，全局结果来说不是排序的
适用于数据量大，但是对排序要求不严格的场景，可以大幅提高执行效率
需要预先设定reduce个数，reduce结果文件内部有序
set mapreduce.job.reduces = 10 ;
select * from emp sort by sal desc;3、distribute by (分区排序)
控制特定的key 到指定的reducer，方便后续的聚合操作，类似于MR中自定义分区，一般结合sort by 使用
需要预先设定reduce个数，否则不会启动相应的reducer进行任务的执行，导致最后不能完全分区。
set mapreduce.job.reduces = 10 ;
select * from emp distribute by deptno sort by sal desc;注意
（1）distribute by 要在sort by之前
（2） distribute by 的分区规则是根据分区字段 hash 码与 reduce的个数进行取模后，余数相同的分到一个分区
4、cluster by (简写)
当distribute by 和 sort by 的字段相同时，可以简写成 cluster by ，但是这个排序只能升序
select * from emp distribute by deptno sort by deptno ;
select * from emp cluster by deptno;5、一个关于排序的实际场景例子
（1）问题描述
订单表，取GMV（商品交易总额）top前100的用户
假如有 1 亿用户 有GMV
（2）解决方法
多个reduce排序，每个分区排序取前100，最后汇总再全局排序，取前100
select
*
from
(
select
user_id
,gmv
from dwd_order_df
distribute by city sort by gmv desc limit 100
) x
order by gmv desc limit 100 ;三、hive中的join
1、普通的join（left，inner等）（common/shuffle/reduce join）
最普通的join，会产生shuffer，且这个会在reduce端做join
2、map join 大小表关联
小表的阈值控制
set hive.mapjoin.smalltable.filesize = 25123456 ; -- 大约25M（1）写法
select
/*+MAPJOIN(b)*/count(1)
from a -- 大表
join b -- 小表
on a.id = b.id流程：可以将b表放在内存，在map端做join ，这个join就发生在map操作的时候，每当扫描一个大表的数据，就要去内存查看小表，有没有与之相等的，继而进行关联。
map端的join优势：没有shuffle
（2）另一种写法 加上参数
set hive.auto.convert.join = true ;
select
count(1)
from a -- 大表
join b -- 小表
on a.id = b.id3、SMB join 大表关联大表
SMB --》sort merge bucket
有 a表 5 亿 ， b表 10亿
使用限制
（1）a b 必须都是分桶的
（2）a b分桶的key必须是关联条件即是key join
（3）设置一些参数
set hive.auto.convert.sortmerge.join = true
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;（4）语句执行
select
count(1)
from a -- 大表
join b -- 小表
on a.id = b.id四、hive的参数优化
1、combine的参数
(1)combine 在map端聚合
set hive .map.aggr = true(2)在map端进行聚合的条数
set hive.groupby.mapaggr.checkinterval = 100000; --默认10万2、负载均衡
(1)数据倾斜的时候
set hive.groupby.skewindata = true当选定为TRUE时，生成的查询计划会产生两个 MR job
先加随机前缀，聚合一次，再去掉随机前缀，再聚合一次
五、思考题
1、分桶表
（1）概念
分区针对的是数据的存储路径；分桶针对的是数据文件。
（2）创建一个分桶表
准备数据
student.txt
1001 ss1
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1006 ss6
1007 ss7
1008 ss8
1009 ss9
1010 ss10
1011 ss11
1012 ss12
1013 ss13
1014 ss14
1015 ss15
1016 ss16创建分桶表
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';导入数据
load data local inpath '/home/peizk/student.txt' into table stu_buck;（3）查看分桶表内
（4）查看HDFS
（5）分桶表常用在抽样查询
查询表stu_buck中的数据。
select * from stu_buck tablesample(bucket 1 out of 4 on id);注：tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=2时，抽取(4/2=)2个bucket的数据，当y=8时，抽取(4/8=)1/2个bucket的数据。
x表示从哪个bucket开始抽取，如果需要取多个桶，以后的桶号为当前桶号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。
注意：x的值必须小于等于y的值，否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck
（6）实现 SMB join
在创建一个stu_buck2表数据同stu_buck
create table stu_buck2(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
load data local inpath '/home/peizk/student.txt' into table stu_buck2;实现 SMB join
set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
select a.id,b.name
from stu_buck a
inner join stu_buck2 b
on a.id=b.id
2、Count(Distinct) 去重统计
数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换
3、行列过滤
列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。
行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤
4、避免笛卡尔积
尽量避免笛卡尔积，join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积。
