Hive调优
一、Explain关键字
用于显示SQL查询的执行计划,即
EXPLAIN query
二、Fetch关键字
在hive-default.xml.template文件中hive.fetch.task.conversion默认是more,老版本hive默认是minimal,该属性修改为more以后,在全局查找、字段查找、limit查找等都不走mapreduce。
三、表与表之间joining的优化
- 空key过滤:join前进行空值过滤,使用场景:①非inner join;②不需要字段为null的
- 空key转换:改善数据倾斜,nvl(column,rand()),通过nvl函数替换null值
- SMB(Sort Merge Bucket Join):分桶,create子句,clustered by (column) sorted by (column) into n buckets(*n不要超过CPU总核数)
四、Group by
- 是否在map端进行聚合,默认为ture
set hive.map.aggr = true; - 在map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000; - 有数据倾斜的时候进行负载均衡(默认是false)
set hive.groupby.skewindata = true;
五、去重
- 先group by key进行并行去重,合并后求count,即
select count(*) from (select * from table group by key); - Union -> Union all + group by
select id from a group by id union all select id from b group by id
六、笛卡尔积
表与表之间做joining的时候,要避免不加on条件或无效的on条件,Hive只能用一个reducer来完成笛卡尔积,会导致效率变低
七、行列过滤
手动谓词下推,将过滤条件推入到副表中,防止SQL太长,谓词下推失效
select a.id from a join b on a.id = b.id where a.id<=10;
转成
select a.id from a join (select id from b where id<=10) b on a.id = b.id;
八、分区与分桶
-
分区(文件夹)
①建表时分区(分区字段不可使用表中字段);
create table dev_par(id int, name string) partitioned by (day string) row format delimited fields terminated by ‘\t’;
②添加分区
alter table dev_par add partition(day=‘2020-01-01’) partition(day=‘2020-01-02’);
③移除分区
alter table dev_par drop partition(day=‘2020-01-01’), partition(day=‘2020-01-02’);
④查看分区
show partitions dev_par;
⑤查看分区表的结构
desc formatted dev_par; -
动态分区
①修改动态分区模式(3.0后可以不修改模式)
set hive.exec.dynamic.partition.mode=nonstrict;
②创建动态分区表
create table dev_par2(name string) partitioned by (id int) row format delimited fields terminated by ‘\t’;
③动态插入数据
insert into dev_par select name,id from dev_par;
- 动态分区字段需要放在Select语句最后
- 分桶(文件)
①建表时分桶(分桶字段需要使用表中字段)
create table dev_buck (id int, name string) clustered by (id) into 4 buckets row format delimited fields terminated by ‘\t’;
②导入数据
load data inpath ‘/test.txt’ into table dev_buck;
③分桶规则
对分桶字段的值进行哈希,然后除以桶数取模;
④注意事项
reducer的个数建议设置为-1
⑤抽样查询(x < y)
select id,name from dev_buck tablesample(bucket x out of y on id);
- x指定从哪个桶抽,y指定均分的区域数
九、配置调优
- 合理控制并行
开启任务并行执行:
set hive.exec.parallel=true;
允许并行任务的最大线程数:
set hive.exec.parallel.thread.number=8; - 任务数的控制
①map控制
每个map最大输入大小:
set mapred.max.split.size=256000000;
一个节点上split的至少的大小:
set mapred.min.split.size.per.node=100000000;
一个交换机下split的至少的大小:
set mapred.min.split.per.rack=100000000;
执行map前进行小文件合并:
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
②reduce控制
每个reduce处理的数据量
set hive.exec.reducers.bytes.per.reducer=500000000;
指定reduce数量
set mapred.reduce.tasks=20;
十、排序
order by、sort by
避免直接使用order by,需加上limit
十一、数据倾斜
- 空值问题
①如果不需要保留空值,过滤掉空值数据
②给予空值一个随机值,确保不能被关联上 - 数据类型不一致
cast转成同类型 - 业务数据本身导致
通过生成一个随机数字段,group by打散到不同map中
- 调优要根据数据量去选择