Hive优化

Hive优化

一、大表Join大表

1、空KEY过滤

有时 join 超时是因为某些 key 对应的数据太多，而相同 key 对应的数据都会发送到相同 的 reducer 上，从而导致内存不够。此时我们应该仔细分析这些异常的 key，很多情况下， 这些 key 对应的数据是异常数据，我们需要在 SQL 语句中进行过滤。例如 key 对应的字段为 空，操作如下：

创建原始数据空 id 表

// 创建空 id 表
create table nullidtable(id bigint, t bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

分别加载原始数据和空 id 数据到对应表中

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

测试不过滤空 id

hive (default)> insert overwrite table jointable select n.* from nullidtable n left join bigtable o on n.id = o.id;

测试过滤空 id

hive (default)> insert overwrite table jointable select n.* from (select
* from nullidtable where id is not null) n left join bigtable o on n.id = o.id;

2、空KEY转换

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

案例实操：

不随机分布空 null 值：

设置 5 个 reduce 个数

set mapreduce.job.reduces = 5;

JOIN 两张表：

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

结果：可以看出来，出现了数据倾斜，某些 reducer 的资源消耗远大于其 他 reducer。

随机分布空 null 值：

设置 5 个 reduce 个数

set mapreduce.job.reduces = 5;

JOIN 两张表：

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

结果：可以看出来，消除了数据倾斜，负载均衡 reducer 的资源消耗

3、SMB(Sort Merge Bucket join)

分桶后JOIN

创建第二张大表

create table bigtable2( id bigint,
t bigint, uid string,
keyword string, url_rank int, click_num int, click_url string)
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/bigtable' into table bigtable2;

测试大表直接 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 s
join bigtable2 b on b.id = s.id;

创建分通表 1,桶的个数不要超过可用 CPU 的核数

create table bigtable_buck1( id bigint,
t bigint, uid string,
keyword string, url_rank int, click_num int, click_url string)
clustered by(id) sorted by(id) into 6 buckets
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/bigtable' into table bigtable_buck1;

创建分通表 2,桶的个数不要超过可用 CPU 的核数

create table bigtable_buck2( id bigint,
t bigint, uid string,
keyword string, url_rank int, click_num int, click_url string)
clustered by(id) sorted by(id) into 6 buckets
row format delimited fields terminated by '\t';
load data local inpath '/opt/module/data/bigtable' into table bigtable_buck2;

设置参数

set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true; 
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

测试

insert overwrite table jointable
select b.id, b.t, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url from bigtable_buck1 s
join bigtable_buck2 b on b.id = s.id;

二、Group By

默认情况下，Map 阶段同一 Key 数据分发给一个 reduce，当一个 key 数据过大时就倾斜 了。

并不是所有的聚合操作都需要在 Reduce 端完成，很多聚合操作都可以先在 Map 端进行 部分聚合，最后在 Reduce 端得出最终结果。

1、开启 Map 端聚合参数设置

是否在 Map 端进行聚合，默认为 True

set hive.map.aggr = true 

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

set hive.groupby.mapaggr.checkinterval = 100000 

有数据倾斜的时候进行负载均衡（默认是 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;

优化以后

hive (default)> set hive.groupby.skewindata = true;
hive (default)> select deptno from emp group by deptno;

2、Count(Distinct) 去重统计

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

案例实操

创建一张大表

hive (default)> create table bigtable(id bigint, time bigint, uid string, keyword
string, url_rank int, click_num int, click_url string) row format delimited
fields terminated by '\t';

加载数据

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

设置 5 个 reduce 个数

set mapreduce.job.reduces = 5; 

执行去重 id 查询

hive (default)> select count(distinct id) from bigtable;

采用 GROUP by 去重 id

hive (default)> select count(id) from (select id from bigtable group by id) a;

虽然会多用一个 Job 来完成，但在数据量大的情况下，这个绝对是值得的。

3、笛卡尔积

尽量避免笛卡尔积，join 的时候不加 on 条件，或者无效的 on 条件，Hive 只能使用 1 个

reducer 来完成笛卡尔积。

4、行列过滤

列处理：在 SELECT 中，只拿需要的列，如果有分区，尽量使用分区过滤，少用 SELECT *。

行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在 Where 后面，那么就会先全表关联，之后再过滤，比如： 案例实操：

测试先关联两张表，再用 where 条件过滤

hive (default)> select o.id from bigtable b join bigtable o on o.id = b.id
where o.id <= 10;
# Time taken: 34.406 seconds, Fetched: 100 row(s)

通过子查询后，再关联表

hive (default)> select b.id from bigtable b
join (select id from bigtable where id <= 10) o on b.id = o.id;
# Time taken: 30.058 seconds, Fetched: 100 row(s)

5、复杂文件增加 Map 数

当 input 的文件都很大，任务逻辑复杂，map 执行非常慢的时候，可以考虑增加 Map 数， 来使得每个 map 处理的数据量减少，从而提高任务的执行效率。

增加 map 的方法为：根据

computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M 公式， 调整 maxSize 最大值。让 maxSize 最大值低于 blocksize 就可以增加 map 的个数。

案例实操：

执行查询：

hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1

设置最大切片值为100个字节

hive (default)> set mapreduce.input.fileinputformat.split.maxsize=100; hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 6; number of reducers: 1

6、小文件进行合并

在 map 执行前合并小文件，减少 map 数：CombineHiveInputFormat 具有对小文件进行合 并的功能（系统默认的格式）。HiveInputFormat 没有对小文件合并功能。

set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

在 Map-Reduce 的任务结束时合并小文件的设置：

在 map-only 任务结束时合并小文件，默认 true

SET hive.merge.mapfiles = true; 

在 map-reduce 任务结束时合并小文件，默认 false

SET hive.merge.mapredfiles = true; 

合并文件的大小，默认 256M

SET hive.merge.size.per.task = 268435456; 

当输出文件的平均大小小于该值时，启动一个独立的 map-reduce 任务进行文件 merge

SET hive.merge.smallfiles.avgsize = 16777216;