原创 Hive-常见调优方式?\u0026\u0026 两个面试sql

发布时间:2021-06-24 03:50:40 浏览 105 来源:猿笔记 作者:Hoult丶吴邪

    Hive作为大数据领域常用的数据仓库组件,在设计和开发阶段需要注意效率。影响Hive效率的不仅仅是数据量过大;数据倾斜、数据冗余、job或I/O过多、MapReduce分配不合理等因素都对Hive的效率有影响。对Hive的调优既包含对HiveQL语句本身的优化,也包含Hive配置项和MR方面的调:执行引擎方面针对公司内平台的资源:为了减少数据传输,下面给几个参数(更多参数可以查看官网),有部分也是通过参数来控制的,这一部分的参数控制主要有下面几个方面,本地模式、严格模式、JVM重用、并行执行、推测执行、合并小文件、Fetch模式。


    Hive作为大数据领域常见的数据仓库组件,在设计开发阶段需要注重效率。影响Hive效率的不仅仅是数据量大;数据偏斜、数据冗余、作业或I/O过多、MapReduce分配不合理等因素都会对Hive的效率产生影响。Hive的优化不仅包括HiveQL语句本身的优化,还包括Hive配置项和MR方面的调整

    整。

    从以下三个方面:

    架构优化

    参数最优化

    SQL优化

    #1.结构

    在执行引擎方面,更合适更快的引擎,比如MR、TEZ、Spark等。,是为公司平台的资源选择的。

    如果选择TEZ引擎,矢量化优化器可以在优化时启动,成本优化器CBO可以选择,配置如下:

    SQLsethive.vectorized.execution.enabled=true;--默认falsesethive.vectorized.execution.reduce.enabled=true;--默认falseSEThive.cbo.enable=true;--从v0.14.0默认trueSEThive.compute.query.using.stats=true;--默认falseSEThive.stats.fetch.column.stats=true;--默认falseSEThive.stats.fetch.partition.stats=true;--默认true

    优化表设计,如选择分区表、桶表、表存储格式等。为了减少数据传输,可以使用压缩。这里有几个参数(更多参数见官网)

    SQL--中间结果压缩SEThive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;--输出结果压缩SEThive.exec.compress.output=true;SETmapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodc

    #2.参数最优化

    第二部分是参数优化。事实上,上面的一些架构部分也是由参数控制的。该部分的参数控制主要包括以下几个方面

    本地模式、严格模式、JVM重用、并行执行、推测执行、合并小文件、提取模式

    ##2.1本地模式

    当数据量较小时,启动分布式数据处理会比较慢,启动时间会比较长,没有本地模式快。使用以下参数进行调整

    SQLSEThive.exec.mode.local.auto=true;--默认false小SEThive.exec.mode.local.auto.inputbytes.max=50000000;--输入文件的大小小于hive.exec.mode.local.auto.inputbytes.max配置的大SEThive.exec.mode.local.auto.input.files.max=5;--默认4map任务的数量小于hive.exec.mode.local.auto.input.files.max配置的大小

    ##2.2严格模式

    这其实是一个开关。当满足以下三个语句时,就会失败。如果不打开,将正常执行。打开后,这些语句将自动失败

    SQLhive.mapred.mode=nostrict--查询分区表时不限定分区列的语句;--两表join产生了笛卡尔积的语句;--用orderby来排序,但没有指定limit的语句

    ##2.3Jvm重用

    在mr中,进程是单元,进程是Jvm。事实上,像短作业一样,这些流程可以快速重用,但它的缺点是在任务执行后会等待任务槽,这在数据倾斜时更明显。用以下参数打开它

    SQLSETmapreduce.job.jvm.numtasks=5;

    ##2.4并行执行

    Hive的查询将被转换为stage。这些阶段并不相互依赖。您可以并行执行这些阶段,并使用以下参数

    SQLSEThive.exec.parallel=true;--默认falseSEThive.exec.parallel.thread.number=16;--默认8

    ##2.5投机性执行

    这个参数的作用是用空间资源来换取获得最终结果的时间。例如,由于网络和资源不平等,一些任务运行非常慢,会启动备份过程来处理相同的数据,最后会选择第一个成功的计算结果作为最终结果。

    SQLsetmapreduce.map.speculative=truesetmapreduce.reduce.speculative=truesethive.mapred.reduce.tasks.speculative.execution=true

    ##2.6合并小文件

    在地图执行之前,合并小文件以减少地图的数量

    SQLsethive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

    任务完成后,合并小文件

    SQL#在map-only任务结束时合并小文件,默认trueSEThive.merge.mapfiles=true;#在map-reduce任务结束时合并小文件,默认falseSEThive.merge.mapredfiles=true;#合并文件的大小,默认256MSEThive.merge.size.per.task=268435456;#当输出文件的平均大小小于该值时,启动一个独立的map-reduce任务进行文件mergeSEThive.merge.smallfiles.avgsize=16777216;

    ##2.7Fetch模式

    最后一种获取模式是在某些情况下尽量不运行mr,比如查询几个字段、全局搜索、字段搜索、限制搜索等等

    SQLhive.fetch.task.conversion=more

    #3.sql优化

    这部分比较复杂,可能涉及到数据倾斜的问题。至于数据倾斜的问题,一直是大数据处理中无法比拟的,不可避免的问题,有很多方法可以处理

    ##3.1sql优化

    Sql优化是开发者最容易控制的部分,往往是凭经验做出来的。总结以下几种方式。

    列,分区拆解,sortby代替orderby,groupby代替count(distinct),groupby的预聚合(通过参数来控制),倾斜配置项,mapjoin,单独过滤空值,适当调整map和reduces数,这些在工作中几乎都会碰到,尽可能去优化他们呢是你要做的

    ##3.2倾斜平衡配置项目

    这个配置与groupby的倾斜均衡配置项异曲同工,通过hive.optimize.skewjoin来配置,默认false。如果开启了,在join过程中Hive会将计数超过阈值hive.skewjoin.key(默认100000)的倾斜key对应的行临时写进文件中,然后再启动另一个job做mapjoin生成结果。通过hive.skewjoin.mapjoin.map.tasks参数还可以控制第二个job的mapper数量,默认1000

    ##3.3单独处理倾斜key

    如果斜键有实际意义,一般来说斜键很少。此时可以分别提取,对应的行可以分别存储在临时表中,然后前缀一个小随机数(如0~9),最后进行聚合。在Select语句中不要写太多联接。一定要了解业务和数据。(A0-A9)分成若干语句,一步一步执行;(A0-A4;a5-A9);首先,执行大表和小表之间的关联;

    ##4.两个SQL

    ###4.1找出所有连续赢得3场比赛的球队

    team,year

    活塞,1990

    公牛,1991

    公牛,1992

    SQL----1排名selectteam,year,row_number()over(partitionbyteamorderbyyear)asrankfromt1;--2获取分组idselectteam,year,row_number()over(partitionbyteamorderbyyear)asrank,(year-row_number()over(partitionbyteamorderbyyear))asgroupidfromt1;--3分组求解selectteam,count(1)yearsfrom(selectteam,(year-row_number()over(partitionbyteamorderbyyear))asgroupidfromt1)tmpgroupbyteam,groupidhavingcount(1)>=3;

    ###4.2找出一天中每个id的所有峰值和谷值

    波峰:

    此时的值>前一时刻的值

    此时的值>下一时刻的值

    波谷:

    此时的值是前一时刻的值

    此刻的值<下一刻的值

    idtimeprice前一时刻的值(lag)后一时刻的值(lead)

    sh66688,9:35,29.48null28.72

    sh66688,9:40,28.7229.4827.74

    sh66688,9:45,27.74

    sh66688,9:50,26.75

    sh66688,9:55,27.13

    sh66688,10:00,26.30

    sh66688,10:05,27.09

    sh66688,10:10,26.46

    sh66688,10:15,26.11

    sh66688,10:20,26.88

    sh66688,10:25,27.49

    sh66688,10:30,26.70

    sh66688,10:35,27.57

    sh66688,10:40,28.26

    sh66688,10:45,28.03

    SQL--思路:关键是找到波峰波谷的特征--波峰的特征:大于前一个时间段、后一个时间段的值--波谷的特征:小于前一个时间段、后一个时间段的值--找到这个特征SQL就好写了selectid,time,price,casewhenprice>beforepriceandprice>afterpricethen"波峰"whenpricebeforepriceandprice>afterprice)or(price

作者信息

Hoult丶吴邪 [等级:3] 大数据开发工程师
发布了 55 篇专栏 · 获得点赞 35 · 获得阅读 9312

相关推荐 更多