Hive场景问题+Hive排序+Hive中的join+Hive参数优化

目录

一、一个实际场景，组合多的问题

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.id

3、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来完成笛卡尔积。