Hive-调优，表的优化，谓词下推，负载均衡，去重统计

深碍√TFBOYSˉ_ 2023-03-06 05:45 75阅读 0赞

### 文章目录 ###

*   *   *  谓词下推(Predicate pushdown)
         *  MapJoin
         *  Group By
         *  Count(Distinct) 去重统计

### 谓词下推(Predicate pushdown) ###

空key转换  
有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。例如：

案例测试：

不随机分布空null值：

（1）设置5个reduce个数

set mapreduce.job.reduces = 5;

（2）JOIN两张表

insert overwrite table jointable
    select n.* from nullidtable n left join bigtable b on n.id = b.id;

结果：如下图所示，可以看出来，出现了数据倾斜，某些reducer的资源消耗远大于其他reducer。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70]

随机分布空null值：

（1）设置5个reduce个数

set mapreduce.job.reduces = 5;

（2）JOIN两张表

insert overwrite table jointable
    select n.* from nullidtable n full join bigtable o on nvl(n.id,rand()) = o.id;

结果：如下图所示，可以看出来，消除了数据倾斜，负载均衡reducer的资源消耗  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70 1]

### MapJoin ###

如果不指定 MapJoin 或者不符合 MapJoin 的条件，那么Hive解析器会将 Join 操作转换成 Common Join，即：在 Reduce 阶段完成 join。容易发生数据倾斜。可以用 MapJoin 把小表全部加载到内存在 map 端进行 join ，避免 reducer 处理。

**1）开启MapJoin参数设置**

（1）设置自动选择Mapjoin（默认为true）

set hive.auto.convert.join = true;

（2）大表小表的阈值设置（默认25M以下认为是小表）：

set hive.mapjoin.smalltable.filesize=25000000;

**2）MapJoin工作机制**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70 2]  
案例测试：

（1）开启Mapjoin功能（默认为true）

set hive.auto.convert.join = true;

（2）执行小表JOIN大表语句

注意：此时小表(左连接)作为主表，所有数据都要写出去，因此此时会走reduce，mapjoin失效

insert overwrite table jointable
    select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
    from smalltable s
    left join bigtable  b
    on s.id = b.id;
    
    Time taken: 24.594 seconds

（3）执行大表JOIN小表语句

insert overwrite table jointable
    select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
    from bigtable  b
    left join smalltable  s
    on s.id = b.id;
    
    Time taken: 24.315 seconds

### Group By ###

默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70 3]  
并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

**1）开启Map端聚合参数设置**

（1）是否在Map端进行聚合（默认为true）

set hive.map.aggr = true

（2）在Map端进行聚合操作的条目数目

set hive.groupby.mapaggr.checkinterval = 100000

（3）有数据倾斜的时候进行负载均衡（默认是false）

set hive.groupby.skewindata = true

案例测试：

当选项设定为 true，生成的查询计划会有两个MR Job。  
第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；  
第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作  
虽然能解决数据倾斜，但是不能让运行速度变快，这是为了保证集群的安全和健壮性，可以有效的避免高峰期时，由于数据倾斜导致的服务器宕机

hive (default)> select deptno from emp group by deptno;
    
    Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 23.68 sec   HDFS Read: 19987 HDFS Write: 9 SUCCESS
    Total MapReduce CPU Time Spent: 23 seconds 680 msec

优化以后

hive (default)> set hive.groupby.skewindata = true;
    hive (default)> select deptno from emp group by deptno;
    
    Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 28.53 sec   HDFS Read: 18209 HDFS Write: 534 SUCCESS
    Stage-Stage-2: Map: 1  Reduce: 5   Cumulative CPU: 38.32 sec   HDFS Read: 15014 HDFS Write: 9 SUCCESS
    Total MapReduce CPU Time Spent: 1 minutes 6 seconds 850 msec

### Count(Distinct) 去重统计 ###

数据量小的时候无所谓，数据量大的情况下，由于`COUNT DISTINCT`操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般`COUNT DISTINCT`使用先GROUP BY再COUNT的方式替换，但是需要注意group by造成的数据倾斜问题（比如count，可以先count group之后的数据，再sum）

案例测试：

（1）创建一张大表

hive (default)> create table bigtable(
    。。。
    ) 
    row format delimited fields terminated by '\t';

（2）加载数据

hive (default)> load data local inpath '/opt/module/datas/bigtable' into table bigtable;

（3）设置5个reduce个数

set mapreduce.job.reduces = 5;

（4）执行去重id查询

hive (default)> select count(distinct (id)) from bigtable;
    
    Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 7.12 sec   HDFS Read: 120741990 HDFS Write: 7 SUCCESS
    Total MapReduce CPU Time Spent: 7 seconds 120 msec
    OK
    c0
    99947
    Time taken: 23.607 seconds, Fetched: 1 row(s)

（5）采用GROUP by去重id

hive (default)> select count(id) from (select id from bigtable group by id) a;
    
    Stage-Stage-1: Map: 1  Reduce: 5   Cumulative CPU: 17.53 sec   HDFS Read: 120752703 HDFS Write: 580 SUCCESS
    Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 4.29 sec2   HDFS Read: 9409 HDFS Write: 7 SUCCESS
    Total MapReduce CPU Time Spent: 21 seconds 820 msec
    OK
    _c0
    99947
    Time taken: 50.795 seconds, Fetched: 1 row(s)

虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的，可以保证集群平稳

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70]: /images/20230208/a53dbe70db8b47e4999632e749010621.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70 1]: /images/20230208/8d3bd43f50ac4d6490a2002184ec1330.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70 2]: /images/20230208/c8c2faaa33d9468397aa0f3b10deac49.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70 3]: /images/20230208/0f40dc6ad7624517b6cd1883228e84b5.png