Hive作为大数据领域常用的数据仓库组件,在平时设计和查询时要特别注意效率。影响Hive效率的几乎从不是数据量过大,而是数据倾斜、数据冗余、job或I/O过多、MapReduce分配不合理等等。对Hive的调优既包含对HiveSQL语句本身的优化,也包含Hive配置项和MR方面的调整。
目录
- 列裁剪和分区裁剪
- 谓词下推
- sort by代替order by
- group by代替distinct
- group by配置调整
- map端预聚合
- 倾斜均衡配置项
- join基础优化
- build table(小表)前置
- 多表join时key相同
- 利用map join特性
- 分桶表map join
- 倾斜均衡配置项
- 优化SQL处理join数据倾斜
- 空值或无意义值
- 单独处理倾斜key
- 不同数据类型
- build table过大
- MapReduce优化
- 调整mapper数
- 调整reducer数
- 合并小文件
- 启用压缩
- JVM重用
- 并行执行与本地模式
- 严格模式
- 采用合适的存储格式
列裁剪和分区裁剪
最基本的操作。所谓列裁剪就是在查询时只读取需要的列,分区裁剪就是只读取需要的分区。以我们的日历记录表为例:
select uid,event_type,record_datafrom calendar_record_logwhere pt_date >= 20190201 and pt_date <= 20190224and status = 0;
当列很多或者数据量很大时,如果select *或者不指定分区,全列扫描和全表扫描效率都很低。
Hive中与列裁剪优化相关的配置项是hive.optimize.cp,与分区裁剪优化相关的则是hive.optimize.pruner,默认都是true。在HiveSQL解析阶段对应的则是ColumnPruner逻辑优化器。
谓词下推
在关系型数据库如MySQL中,也有谓词下推(Predicate Pushdown,PPD)的概念。它就是将SQL语句中的where谓词逻辑都尽可能提前执行,减少下游处理的数据量。
例如以下HiveSQL语句:
select a.uid,a.event_type,b.topic_id,b.titlefrom calendar_record_log aleft outer join (select uid,topic_id,title from forum_topicwhere pt_date = 20190224 and length(content) >= 100) b on a.uid = b.uidwhere a.pt_date = 20190224 and status = 0;
对forum_topic做过滤的where语句写在子查询内部,而不是外部。Hive中有谓词下推优化的配置项hive.optimize.ppd,默认值true,与它对应的逻辑优化器是PredicatePushDown。该优化器就是将OperatorTree中的FilterOperator向上提,见下图。
图来自https://tech.meituan.com/2014/02/12/hive-sql-to-mapreduce.html
上面的链接中是一篇讲解HiveSQL解析与执行过程的好文章,前文提到的优化器、OperatorTree等概念在其中也有详细的解释,非常推荐。
sort by代替order by
HiveSQL中的order by与其他SQL方言中的功能一样,就是将结果按某字段全局排序,这会导致所有map端数据都进入一个reducer中,在数据量大时可能会长时间计算不完。
如果使用sort by,那么还是会视情况启动多个reducer进行排序,并且保证每个reducer内局部有序。为了控制map端数据分配到reducer的key,