Hivesql优化&sparksql优化梳理

最新推荐文章于 2024-05-11 09:37:40 发布
最新推荐文章于 2024-05-11 09:37:40 发布
阅读量1.1k 收藏 6
点赞数
分类专栏： spark 文章标签： sql优化 sparksql优化 hivesql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liu82327114/article/details/106037080
版权

Hive sql 优化方案梳理总结

目录

Hive sql 优化方案梳理总结

说明

简单最合理

对应表的HDFS文件大小和数量问题

数据倾斜问题

where在on前面后面的问题

数仓逻辑层面的优化

group by代替COUNT(DISTINCT)

尽量不要使用in这种函数

case when函数when越多算的越慢

 

说明

此篇文章我们将对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)

         

  • 数据倾斜问题

    • 说明 在数据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阶段最好做一遍合并压缩操作，否则可能会产生很多文件

 

踩大侠
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Hive与spark优化之小文件问题及其解决方案
mojolang
02-09 2257
小文件是如何产生的 1.动态分区插入数据，产生大量的小文件，从而导致map数量剧增。 2.reduce数量越多，小文件也越多(reduce的个数和输出文件是对应的)。 3.数据源本身就包含大量的小文件。 小文件问题的影响 1.从Hive的角度看，小文件会开很多map，一个map开一个JVM去执行，所以这些任务的初始化，启动，执行会浪费大量的资源，严重影响性能。 2.在HDFS中，每个小文件对象约占150byte，如果小文件过多会占用大量内存。这样NameNode内存容量严重制约了集群的扩展。
hive,spark sql优化
ronaldo-liu的博客
11-06 2015
一.hive sql优化 1.发生数据倾斜，如何解决？ 什么是数据倾斜？ 由于数据分布不均匀，造成数据大量的集中到一点，造成数据热点 1.1 原因 1)、key分布不均匀 2)、业务数据本身的特性 3)、建表时考虑不周 4)、某些SQL语句本身就有数据倾斜 1.2 表现 任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其...
HIVE大数据平台SQL优化分享
最新发布
wowulita123的博客
05-11 615
此文章是大数据平台运维组从多维度参数（CPU，内存，运行时长等）筛选出TOP任务，联合数据开发人员进行优化，最终出具优化方案优化跑批作业的业务逻辑，SQL逻辑等，并跟进方案落地和报告整理。
Spark性能优化
qq_25073261的博客
08-11 898
1.程序开发开发调优 Spark性能优化的第一步，就是要在开发Spark作业的过程中注意和应用一些性能优化的基本原则。开发调优，就是要让大家了解以下一些Spark基本开发原则，包括：RDD lineage设计、算子的合理使用、特殊操作的优化等。在开发过程中，时时刻刻都应该注意以上原则，并将这些原则根据具体的业务以及实际的应用场景，灵活地运用到自己的Spark作业中。 1.1 原则一：避免创建重复的RDD 通常来说，我们在开发一个Spark作业时，首先是基于某个数据源（比如Hive表或HDFS文件）创建一个初
30. Spark SQL case when用法：
元元的李树专栏
11-11 9994
30. Spark SQL case when用法： https://sparkbyexamples.com/spark-case-when-otherwise-example/ https://stackoverflow.com/questions/37064315/how-to-write-case-with-when-condition-in-spark-sql-using-scala...
Hive SQL性能优化
02-26
Hive SQL性能优化
Hive与SQL优化.md
12-29
Hive与SQL优化.md
HiveSQL优化手册
03-15
hiveSQL优化整理版，基于hive2整理，从数据倾斜、表优化、并行、压缩、语法树方面整理
HIVE查询优化
01-07
Hive QL的执行本质上是MR任务的运行，因此优化主要考虑到两个方面：Mapreduce任务优化、SQL语句优化 一、Mapreduce任务优化 1、设置合理的task数量（map task、reduce task） 这里有几个考虑的点，一方面Hadoop MR ...
大数据hive数据倾斜，hive-sql优化
08-17
大数据Hive数据倾斜、Hive-SQL优化 在大数据处理中，Hive是一个非常重要的工具，然而在实际应用中，数据倾斜和性能优化问题经常会出现。为了解决这些问题，我们需要了解Hive的性能调优、数据模型设计、数据倾斜判断...
MR/hive/shark/sparkSQL
weixin_30505043的博客
04-17 147
shark完全兼容hive，完全兼容MR，它把它们替代。类SQL查询，性能比hive高很多 sparkSQL比shark更快。shark严重依赖hive，hive慢，无法优化。 SparkSQL和shark最大不同，shark封装hive，受制于hive底层局限，性能无法优化，SparkSQL完全基于Spark，性能超过，Spark2.0最大改善，把SparkSQL作为优化中的，比1.5.2更...
SparkSQL整合Hive
m0_51691291的博客
01-05 1833
SparkSQL整合Hive1、Hive远程模式2、SparkSQL与Hive共用元数据2.1、开启Hive元数据服务2.1.1、修改配置2.1.2、启动服务2.2、拷贝hive-site.xml与mysql驱动2.3、启动SparkSQL2.4、测试使用3、SparkSQL JDBC连接方式3.1、启动Spark JDBC服务3.2、启动beeline3.3、测试使用 1、Hive远程模式 SparkSQL整合Hive需要明白如下结构图 2、SparkSQL与Hive共用元数据 2.1、开启Hive元数
spark sql任务性能优化(基础)
一群专业码农的笔记本
11-22 6813
优化任务的意义 对于项目而言，可以节省机器计算资源 执行时间可能大幅度缩短，对于长链条任务依赖减少等待时间，尤其于上游任务而言，从而数据稳定性增加 spark UI 在上面的导航栏： 1 代表job页面，在里面可以看到当前应用分析出来的所有任务，以及所有的excutors中action的执行时间。 2 代表stage页面，在里面可以看到应用的所有stage，stage是按照宽依赖来区分的，因此粒度上要比job更细一些 3 代表storage页面，我们所做的cache persist等操作，都会在这里看
Hive和Spark SQL优化
strongyoung的专栏
05-05 1412
优化步骤 问题定位，通过日志和代码分析，定位任务耗时长的原因 hive任务，对于hive任务，可以通过日志查看哪个application、以及是哪个阶段耗时较长，另外，可以通过stage编号，结合explain查看执行计划，可以得知耗时较长的application对应的是sql中的哪个操作，便于具体分析原因。如果是map阶段较长，很可能是并发不够，如果是reduce较长，且长时间留停在99...
Spark SQL 与 Hive 的小文件调优
JIE的博客 --- moon_coder
12-02 2038
小文件会造成 nn 处理压力变大，大大降低了读取性能，整个 HDFS 文件系统访问缓慢，大量的小文件还会导致 nn 内存溢出，无法正常使用。以上参数调优都是临时调优，仅限于本次会话，如果想要永久设置的话只需要将参数配置到。可以先查看我们操作的数据量大小，然后用它去除以我们的集群块。一般情况数据量小的话，只需要设置前面两个参数就可以了。我的建议是，不如直接调用方法。新建了一个和其结构一样。新建了一个和其结构一样。
Spark执行HiveSQL以及Hive自定义函数
swg321321的博客
06-14 1473
Spark执行HiveSQL，其中包括执行Hive自定义函数。Spark读取文件。包含Spark SQL等生成可执行的RDD，执行优化，Catalog
exchange 无权执行操作_技本功|Hive优化之Spark执行引擎参数调优（二）
weixin_42524013的博客
12-27 204
作者：九月袋鼠云大数据工程师多年大数据运维/数仓开发经验，擅长数仓模型开发、平台运维；参与/主导金融/教育等客户的大数据数仓建设；负责多家客户的大数据业务场景落地。Hive是大数据领域常用的组件之一，主要是大数据离线数仓的运算，关于Hive的性能调优在日常工作和面试中是经常涉及的的一个点，因此掌握一些Hive调优是必不可少的一项技能。影响Hive效率的主要有数据倾斜、数据冗余、job的...
SparkSQL性能调优
YQlakers的博客
03-31 9118
最近在学习spark时，觉得Spark SQL性能调优比较重要，所以自己写下来便于更过的博友查看，同时也希望大家给我指出我的问题和不足 在spark中，Spark SQL性能调优只要是通过下面的一些选项进行优化的： 1 spark.sql.codegen 默认值为false，当它设置为true时，Spark SQL会把每条查询的语句在运行时编译为java的二进制代码。这有什么作用呢？它可以提高
hivesql&sparksql —— 使用left semi join做in、exists类型子查询优化
03-16
HiveSQL和SparkSQL都支持使用left semi join来优化in、exists类型的子查询。这种优化方式可以将子查询转化为join操作，从而提高查询效率。 具体来说，left semi join会将左表和右表进行join操作，但只返回左表中存在于右表中的记录。这种方式可以避免in、exists类型的子查询中的重复计算，从而提高查询效率。 需要注意的是，left semi join只能用于判断是否存在，不能返回右表的任何信息。因此，在使用left semi join进行优化时，需要根据具体情况选择合适的字段进行join操作。 总之，使用left semi join来优化in、exists类型的子查询可以提高查询效率，但需要根据具体情况选择合适的字段进行join操作。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值