Hive sql 优化方案梳理总结
目录
说明
此篇文章我们将对Hivesql的优化方案进行梳理和总结,欢迎大家一起讨论,可以补充和完善的地方欢迎各位大牛积极在评论区提出,大家一起共同学习共同成长
简单最合理
这个是我个人的习惯,能实现的情况下最简单的就是最好的,逻辑sql不要写的过复杂,越简单越好
对应表的HDFS文件大小和数量问题
很多在写sql的时候不关心表对应的HDFS路径下小文件数量的问题,其实要写高效的sql必须要关注文件大小和数量情况
- 文件过小,数据量过多
- 一般会出现的问题
- 文件数量越多在执行sql的时候可能会启更多的mr任务,耗费更多的cpu core,尤其是在join的时候会导致启动大量的的mr任务和耗费非常多的core,在job计算完成后同样会使用大量的cpu资源去落地HDFS,比如某个任务在计算完成之后产生了20w个文件,那么在落地hdfs的时候意味着需要20w个core去写hdfs,会导致集群所有的资源去做这件事情,在此期间就会导致spark thrift server 无法提交任务,会频繁出现OOM或者timeout的情况,对hdfs的namenode读写也会带来很大的负面影响,增加namenode rpc 响应时间降低hadoop的读写效率
- 下游job同样会产生大量的小文件,恶性循环,导致文件数量越来越多
- 如果任务非正常执行完成,产生的中间文件.hive文件不会自行删除,这个文件可能会很大,小文件数量可能也会非常多浪费集群资源
- 解决方案
- 文件务必设置压缩
基于TEXTFILE进行gzip压缩(文件大小按需调整) set mapred.max.split.size=256000000 ; set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat ; set hive.merge.mapfiles = true ;set hive.merge.mapredfiles= true ; set hive.merge.size.per.task = 256000000 ; set hive.merge.smallfiles.avgsize=256000000 ; set hive.exec.compress.output=true; set mapreduce.output.fileoutputformat.compress=true ; set mapreduce.output.fileoutputformat.compress.type=BLOCK ; set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.GzipCodec ; set hive.exec.dynamic.partition.mode=nonstrict; set hive.hadoop.supports.splittable.combineinputformat=true; 基于ORC进行合并snnapy压缩(文件大小按需调整) set mapred.min.split.size.per.node=7000000000; set mapred.min.split.size.per.rack=7000000000; set mapred.max.split.size=7000000000; set hive.merge.smallfiles.avgsize=6000000000; set hive.merge.size.per.task=6000000000; set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat ; set hive.merge.mapfiles=trueset hive.merge.mapredfiles=true; set hive.exec.compress.output=true;set mapreduce.output.fileoutputformat.compress=true ; set hive.exec.dynamic.partition.mode=nonstrict;set hive.exec.dynamic.partition=true;
- 文件务必设置压缩
- 一般会出现的问题
- 文件过大
- 一般会出现的问题
- 个别分区文件过大,其他分区文件很小很容易出现数据倾斜情况
- 单文件过大,一般进行spark shuffle的时候一个文件对应一个core,如果文件过大经常出现一个线程无法按时完成分析任务的情况
- 解决方案
- 均匀打散文件,把一个大文件拆分成几个较小的文件,以便能分配更多的core去并行计算,提高并行度,并合理设置 spark.sql.shffule.partition参数值,如果文件数量过多,而这个值设置的特别小也是无济于事
set spark.sql.shuffle.partitions=n; ---n代表需要打散成几个文件 INSERT overwrite TABLE table_a Select * from table_a distribute by rand()
-
调整计算资源大小
--调整hive # map 任务内存总大小 set mapreduce.map.memory.mb=4096 # map 堆大小 set mapreduce.map.java.opts=-Xmx3072m; set mapreduce.reduce.memory.mb=8192; set mapreduce.reduce.java.opts=-Xmx2048m; --调整spark --调整executor的memory大小 SPARK_EXECUTOR_MEMORY="18G" #Memory per Worker (e.g. 1000M, 2G) (Default: 1G) --调整executor的core数量 SPARK_EXECUTOR_CORES="6" --调整executor的数量 spark.dynamicAllocation.initialExecutors 60 spark.dynamicAllocation.maxExecutors 100 spark.dynamicAllocation.minExecutors 60 --调整spark driver memory的大小 SPARK_DRIVER_MEMORY="48G" #Memory for Master (e.g. 1000M, 2G) (Default: 512 Mb)
- 均匀打散文件,把一个大文件拆分成几个较小的文件,以便能分配更多的core去并行计算,提高并行度,并合理设置 spark.sql.shffule.partition参数值,如果文件数量过多,而这个值设置的特别小也是无济于事
- 一般会出现的问题
-
数据倾斜问题
- 说明 在数据ETL中,数据倾斜一直以来是个讨论高频的问题,尤其是在集群规模比较大的集群,数据量越大,发生的几率越高,一般来说最常见的表现就是hive mr任务或者spark task中 大部分任务已经succeed,但是剩下很少的几个mr任务或者 spark task仍需要执行很长的时间,或者根本执行不完,这种情况原因也比较简单,就是某个分区的一个分区数据量过大,或者某一个分区内文件分布不均匀,有的文件很大,有的文件很小,导致分配到处理这个文件的task需要耗费很长的时间去处理,这种情况下,入股分析逻辑里再加个复杂的udf,整上个3个if的逻辑,那这个任务几乎很难跑完了
- 常用的解决方案 我个人觉得在解决数据倾斜情况没有一个特定的方案,不过我们方向是唯一的,我们需要把较大的数据均匀的分配到不同的task中去。
- 将大文件打散上面已有说明,并设置spark.sql.shuffle.partitions 设置spark的并行度
- 广播小表变量,当大表与小表进行join的时候,将小表作为变量广播,把之前的shuffle转化为本地的map阶段处理,在一定程度上也会缓解
-
where在on前面后面的问题
请参考此链接 -
数仓逻辑层面的优化
- 有些场景ODS层明细数据可能比较集中,比方说如果所有设备和业务的数据都上报到了同一个表里,再拆分不同业务的数据到各自的业务表的时候,如果数据规模很大(ods总表每天几千个文件,每个文件压缩完300M+),设备种类多(100+),每个业务每个设备的数据都要起一个各自的流程去扫描ods总表去拉取各自需要的数据,那么意味着这几千个大文件会被所有这些sql都扫描一遍,很浪费资源,为此我们可以对ods总表做初步的拆分,ods总表->ods_分表1,ods_分表2,ods_分表3->ods_each_product表,这样会稳定很多,避免了资源拥堵情况
- 从大局考虑,如果sql job多的情况,定时任务尽量安排到凌晨到早上之前,白天的时间段内的计算资源主要用于日常的开发和日常临时需求的交互式查询,资源得到合理有效利用
- 对于不同业务数据品类繁多而需要同时复杂计算(例如窗口函数比较复杂的计算),这种情况我们一般会设计二级分区作为不同业务的标识(不建议使用二级以上的hive 分区,因为分区等级越多,会导致分区数量越大,这样会给hive metastore 带了很大的压力,在hdfs落地的时候也会有很大的压力),这种情况下如果不同业务的数据量差距很多,建议针对不同的业务分区采取不同的业务优化方案,比如根据不同业务的数据规模把job 分拆成大中小三类,如果是使用hive进行分析,我们就分配不同的map reduce 堆内存,如果是spark 我们就设置不同的并行度,当然针对数据量规模大的业务数据我们可以提前进行拆分,比如将每个256M的文件拆成5个50M左右的文件,这样分配到每个task的时候就能执行的快一些
-
group by代替COUNT(DISTINCT)
在很多时候好多同学喜欢直接用count(distinct c1)用于数据统计,但是这个的计算效率远远不如group by,尤其是在数据量很大的时候,数据量小的时候就无所谓了,count(distinct c1)这个函数一般只启用一个reduce task 去计算,效率很低,但是group by不会,在公司好几次有些人使用count(distinct c1)算很久算不出想要的结果值,还进行了投诉,但是我们告诉他们换成 select c1, count(c1) as cnt from tab group by c1 之后很快得出了结果 -
尽量不要使用in这种函数
-
case when函数when越多算的越慢
表中的文件都需要走一遍when流程,when越多效率就越低,而且在reduce阶段最好做一遍合并压缩操作,否则可能会产生很多文件