大数据开发笔记(四):Hive数仓调优

本文涉及的产品
云原生大数据计算服务MaxCompute,500CU*H 100GB 3个月
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
云原生大数据计算服务 MaxCompute,5000CU*H 100GB 3个月
简介: Hive调优策略

Hive调优策略


1. map阶段输出数据压缩 ,在这个阶段,优先选择一个低CPU开销的算法。

set hive.exec.compress.intermediate=true
set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec
set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;

2. 对最终输出结果压缩

set hive.exec.compress.output=true
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
## 当然,也可以在hive建表时指定表的文件格式和压缩编码

结论,一般选择orcfile/parquet + snappy 方式


3.合理利用分区分桶

分区是将表的数据在物理上分成不同的文件夹,以便于在查询时可以精准指定所要读取的分区目录,从来降低读取的数据量


分桶是将表数据按指定列的hash散列后分在了不同的文件中,将来查询时,hive可以根据分桶结构,快速定位到一行数据所在的分桶文件,从来提高读取效率


4.hive参数优化

// 让可以不走mapreduce任务的,就不走mapreduce任务
hive> set hive.fetch.task.conversion=more;
// 开启任务并行执行
set hive.exec.parallel=true;
// 解释:当一个sql中有多个job时候,且这多个job之间没有依赖,则可以让顺序执行变为并行执行(一般为用到union all的时候)
// 同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;
// 设置jvm重用
// JVM重用对hive的性能具有非常大的 影响,特别是对于很难避免小文件的场景或者task特别多的场景,这类场景大多数执行时间都很短。jvm的启动过程可能会造成相当大的开销,尤其是执行的job包含有成千上万个task任务的情况。
set mapred.job.reuse.jvm.num.tasks=10;
// 合理设置reduce的数目
// 方法1:调整每个reduce所接受的数据量大小
set hive.exec.reducers.bytes.per.reducer=500000000; (500M)
// 方法2:直接设置reduce数量
set mapred.reduce.tasks = 20
// map端聚合,降低传给reduce的数据量
set hive.map.aggr=true
// 开启hive内置的数倾优化机制
set hive.groupby.skewindata=true


5.sql优化

1where条件优化


优化前(关系数据库不用考虑会自动优化)


select m.cid,u.id from order m join customer u on( m.cid =u.id )where m.dt='20180808';


优化后(where条件在map端执行而不是在reduce端执行)


select m.cid,u.id from (select * from order where dt='20180818') m join customer u on( m.cid =u.id);


2union优化


尽量不要使用union (union 去掉重复的记录)而是使用 union all 然后在用group by 去重


3.count distinct优化


不要使用count (distinct cloumn) ,使用子查询


select count(1) from (select id from tablename group by id) tmp;


4.用in 来代替join


如果需要根据一个表的字段来约束另为一个表,尽量用in来代替join . in 要比join 快


select id,name from tb1 a join tb2 b on(a.id = b.id);
select id,name from tb1 where id in(select id from tb2);


5优化子查询


消灭子查询内的 group by 、 COUNT(DISTINCT),MAX,MIN。可以减少job的数量。


6 join 优化


Common/shuffle/Reduce JOIN 连接发生的阶段,发生在reduce 阶段, 适用于大表 连接 大表(默认的方式)


Map join :连接发生在map阶段 , 适用于小表 连接 大表

大表的数据从文件中读取

小表的数据存放在内存中(hive中已经自动进行了优化,自动判断小表,然后进行缓存)


set hive.auto.convert.join=true;


SMB join

Sort -Merge -Bucket Join 对大表连接大表的优化,用桶表的概念来进行优化。在一个桶内发生笛卡尔积连接(需要是两个桶表进行join)


set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

6.数据倾斜

表现:任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。


原因:某个reduce的数据输入量远远大于其他reduce数据的输入量


1.sql本身导致的倾斜


1)group by


如果是在group by中产生了数据倾斜,是否可以讲group by的维度变得更细,如果没法变得更细,就可以在原分组key上添加随机数后分组聚合一次,然后对结果去掉随机数后再分组聚合


在join时,有大量为null的join key,则可以将null转成随机值,避免聚集


2)count(distinct)


情形:某特殊值过多


后果:处理此特殊值的 reduce 耗时;只有一个 reduce 任务


解决方式:count distinct 时,将值为空的情况单独处理,比如可以直接过滤空值的行,


在最后结果中加 1。如果还有其他计算,需要进行 group by,可以先将值为空的记录单独处理,再和其他计算结果进行 union。


3)不同数据类型关联产生数据倾斜


情形:比如用户表中 user_id 字段为 int,log 表中 user_id 字段既有 string 类型也有 int 类型。当按照 user_id 进行两个表的 Join 操作时。


后果:处理此特殊值的 reduce 耗时;只有一个 reduce 任务


默认的 Hash 操作会按 int 型的 id 来进行分配,这样会导致所有 string 类型 id 的记录都分配


到一个 Reducer 中。


解决方式:把数字类型转换成字符串类型


select * from users a
left outer join logs b
on a.usr_id = cast(b.user_id as string)

4)mapjoin


2业务数据本身的特性(存在热点key)


join的每路输入都比较大,且长尾是热点值导致的,可以对热点值和非热点值分别进行处理,再合并数据


3key本身分布不均,可以在key上加随机数,或者增加reduceTask数量


开启数据倾斜时负载均衡 :set hive.groupby.skewindata=true;


思想:就是先随机分发并处理,再按照 key group by 来分发处理。


操作:当选项设定为 true,生成的查询计划会有两个 MRJob。


第一个 MRJob 中,Map 的输出结果集合会随机分布到 Reduce 中,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 GroupBy Key 有可能被分发到不同的Reduce 中,从而达到负载均衡的目的;


第二个 MRJob 再根据预处理的数据结果按照 GroupBy Key 分布到 Reduce 中(这个过程可以保证相同的原始 GroupBy Key 被分布到同一个 Reduce 中),最后完成最终的聚合操作。


4控制空值分布


将为空的 key 转变为字符串加随机数或纯随机数,将因空值而造成倾斜的数据分不到多个 Reducer。


注:对于异常值如果不需要的话,最好是提前在 where 条件里过滤掉,这样可以使计算量大大减少


7.合并小文件

小文件的产生有三个地方,map输入,map输出,reduce输出,小文件过多也会影响hive的分析效率:


设置map输入的小文件合并


set mapred.max.split.size=256000000;
//一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000;
//一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)
set mapred.min.split.size.per.rack=100000000;
//执行Map前进行小文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
设置map输出和reduce输出进行合并的相关参数:
//设置map端输出进行合并,默认为true
set hive.merge.mapfiles = true
//设置reduce端输出进行合并,默认为false
set hive.merge.mapredfiles = true
//设置合并文件的大小
set hive.merge.size.per.task = 256*1000*1000
//当输出文件的平均大小小于该值时,启动一个独立的MapReduce任务进行文件merge。
set hive.merge.smallfiles.avgsize=16000000


8.查看sql的执行计划

explain sql

学会查看sql的执行计划,优化业务逻辑 ,减少job的数据量。




相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
目录
相关文章
|
7月前
|
SQL 大数据 HIVE
Hive 任务调优实践总结
Hive 任务调优实践总结
62 0
|
1月前
|
监控 数据挖掘 OLAP
深入解析:AnalyticDB中的高级查询优化与性能调优
【10月更文挑战第22天】 AnalyticDB(ADB)是阿里云推出的一款实时OLAP数据库服务,它能够处理大规模的数据分析任务,提供亚秒级的查询响应时间。对于已经熟悉AnalyticDB基本操作的用户来说,如何通过查询优化和性能调优来提高数据处理效率,是进一步提升系统性能的关键。本文将从个人的角度出发,结合实际经验,深入探讨AnalyticDB中的高级查询优化与性能调优技巧。
105 4
|
2月前
|
大数据 网络安全 数据安全/隐私保护
大数据-03-Hadoop集群 免密登录 超详细 3节点云 分发脚本 踩坑笔记 SSH免密 集群搭建(二)
大数据-03-Hadoop集群 免密登录 超详细 3节点云 分发脚本 踩坑笔记 SSH免密 集群搭建(二)
146 5
|
2月前
|
XML 大数据 网络安全
大数据-03-Hadoop集群 免密登录 超详细 3节点云 分发脚本 踩坑笔记 SSH免密 集群搭建(一)
大数据-03-Hadoop集群 免密登录 超详细 3节点云 分发脚本 踩坑笔记 SSH免密 集群搭建(一)
74 4
|
3月前
|
SQL 数据库 HIVE
hive数仓 ods层增量数据导入
根据业务需求,当表数据量超过10万条时采用增量数据导入,否则全量导入。增量导入基于`create_date`和`modify_date`字段进行,并确保时间字段已建立索引以提升查询效率。避免在索引字段上执行函数操作。创建增量表和全量表,并按日期进行分区。首次导入全量数据,后续每日新增或变更数据保存在增量表中,通过全量表与增量表的合并保持数据一致性。
130 13
|
2月前
|
SQL 分布式计算 Java
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
大数据-96 Spark 集群 SparkSQL Scala编写SQL操作SparkSQL的数据源:JSON、CSV、JDBC、Hive
59 0
|
3月前
|
SQL JavaScript 前端开发
用Java、Python来开发Hive应用
用Java、Python来开发Hive应用
39 7
|
3月前
|
SQL JavaScript 前端开发
用Java来开发Hive应用
用Java来开发Hive应用
41 7
|
3月前
|
SQL 数据处理 HIVE
HIVE的数据倾斜调优
hive数据倾斜主要是由shuffle引起的,而引起shuffle的又主要有四种情况,分别为: 1.group by 2.join 3.count(distinct) 4.开窗函数
75 8
|
5月前
|
SQL 分布式计算 大数据
大数据处理平台Hive详解
【7月更文挑战第15天】Hive作为基于Hadoop的数据仓库工具,在大数据处理和分析领域发挥着重要作用。通过提供类SQL的查询语言,Hive降低了数据处理的门槛,使得具有SQL背景的开发者可以轻松地处理大规模数据。然而,Hive也存在查询延迟高、表达能力有限等缺点,需要在实际应用中根据具体场景和需求进行选择和优化。