一篇学习HiveSQL参数和性能调优的文章

如果我们只局限于使用Hive而不考虑性能问题，是很难构建一个完美的数据仓库的，所以Hive性能调优是我们大数据从业者必须掌握的技能.本文将讲解Hive参数和性能调优的一些方法和技巧。1.limit调整一般来说，limit语句还是需要执行整个查询语句，然后再返回一些结果。可以打开一个配置属性来避免这种情况：对数据源进行采样。hive.limit.optimize.enable=true--开启数据源采样功能hive.limit.row.max.size--设置最小采样容量hive.limit.optimize.limit.file--设置最大采样样本数的缺点：有可能有些数据永远不会处理到2.JOIN优化1.使用相同的connectionkey在对3个或更多表进行join连接时，如果每个on子句使用相同的connectionkey，只将生成一个MapReduce作业。2、尽早过滤数据，减少每个阶段的数据量，对分区表添加分区，只选择需要使用的字段。3、尽量原子化操作，避免在单个SQL中出现复杂的逻辑，可以使用中间表来完成复杂的逻辑。三、小文件优化1、小文件过多的影响首先，对于底层存储HDFS，HDFS本身并不适合存储大量的小文件。小文件太多会导致namenode元数据特别大，占用内存太多，严重影响HDFS的性能对于Hive，查询时，每个小文件都会被当成一个块，会启动一个Map任务来完成它，一个Map任务的启动和初始化时间远远大于逻辑处理时间，会造成巨大的资源浪费。而且同时执行的map数量是有限的2.小文件太多如何解决1）使用hive自带的concatenate命令自动合并小文件使用方法：#对于非分区表altertableAconcatenate;#对于分区表altertableBpartition(day=20201224)concatenate;注意：连接命令仅支持RCFILE和ORC文件类型。使用concatenate命令合并小文件时，不能指定合并文件的个数，但可以多次执行该命令。多次使用concatenate时，文件数不变。这个跟参数mapreduce.input.fileinputformat.split.minsize=256mb的设置有关，可以设置每个文件的最小大小。2）调整参数减少map个数，设置mapinput合并小文件的相关参数：#MergesmallfilesbeforeexecutionMap#CombineHiveInputFormat底层是Hadoop的CombineFileInputFormat方法#该方法是将多个文件合并成一个在映射器中拆分为输入集hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat；--Default#每个Map的最大输入大小（这个值决定了合并文件的个数）setmapred.max.split.size=256000000;--256M#节点上拆分的最小大小（该值决定多个DataNode上的文件是否需要合并）setmapred.min.split.size.per.node=100000000;--100M#Aswitchsplit的最小大小（这个值决定多个switch上的文件是否需要合并）setmapred.min.split.size.per.rack=100000000;--100M设置合并map输出和reduce输出的相关参数：#设置合并map端的输出，默认为truesethive.merge.mapfiles=true;#设置reduce端合并的输出，默认为falsesethive.merge.mapredfiles=true;#设置合并文件的大小sethive.merge。size.per.task=256*1000*1000;--256M#当输出文件的平均大小小于该值时，为文件mergeset启动一个独立的MapReduce任务hive.merge.smallfiles.avgsize=16000000;--16MenabledCompression:#hive查询结果输出是否压缩sethive.exec.compress.output=true;#MapReduceJob的结果输出是否压缩setmapreduce.output.fileoutputformat.compress=true;3）减少Reduce的数量#reduce的数量决定了输出文件的数量，所以可以调整reduce个数来控制hive表的文件个数。#hive中的分区函数distributeby只是控制MR中的partition分区。#然后设置reduce个数，结合partition函数，让数据均衡的进入每个reduce。#设置reduce个数有两种方式，第一种是直接设置reduce个数setmapreduce.job.reduces=10;#二是设置每次reduce的大小，Hive会根据数据的总大小猜测并确定一个reduceNumbersethive.exec.reducers.bytes.per.reducer=5120000000;--默认1G，设置为5G#执行以下语句将数据均匀分布reducesetmapreduce.job.reduces=10;insertoverwritetableApartition(dt)select*fromBdistributebyrand();说明：如果reduce个数设置为10，使用rand()随机生成一个数x%10，这样数据会随机进入reduce，防止任意4）使用Hadoop的archive来归档小文件HadoopArchive，参考toasHAR，是一种文件归档工具，可以高效地将小文件放入HDFS块中，它可以将多个小文件打包成一个HAR文件，仍然允许透明访问文件，同时减少namenode内存使用。#用来控制archive是否可用sethive.archive.enabled=true;#通知Hive创建archive时是否可以设置父目录sethive.archive.har.parentdir.settable=true;#控制大小thearchivefilesethar.partfile.size=1099511627776;#使用以下命令进行归档ALTERTABLEAARCHIVEPARTITION(dt='2022-02-24',hr='12');#将归档分区恢复为原来的文件ALTERTABLEAUNARCHIVEPARTITION(dt='2022-02-24',hr='12');注意：归档的分区可以查看但不能插入覆盖，必须先解压。4.在本地模式下，有时hive的输入数据量很小。在这种情况下，查询执行任务的时间消耗可能比实际作业的执行时间要多得多。对于大多数这些情况，hive可以通过本地模式在单台机器上处理所有任务。对于小数据集，执行时间可以显着减少。设置hive.exec.mode.local.auto=true；只有当作业满足以下条件时，才能使用本地模式：作业的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(default128MB)映射数job的数量必须小于参数：hive.exec.mode.local.auto.tasks.max(default4)job的reduce数量必须是0或1。参数hive.mapred.local.mem(default0)可以用来控制childjvm使用的最大内存量。5.严格模式启用严格模式查询分区表。如果where子句中没有partitionfilter，将禁止提交任务（默认：nonstrict）设置hive.mapred.mode=strict开启严格模式注意：使用严格模式可以禁止以下三种查询：1、对分区表的查询必须使用与分区相关的字段。分区表的数据量通常比较大，而且对分区表的查询必须要用到分区相关的字段，而且是不允许扫描所有Partition的，想一想，如果扫描所有分区，会怎样分区表的重点是什么？当然，在某些特殊情况下，可能还是需要扫描所有分区。这个时候需要记得确保严格模式是关闭的。2.orderby必须有一个限制，因为所有的数据都需要拉到一个Reducer来保证全局的顺序。当数据集比较大时，速度会很慢。我个人的猜测可能是设置了限制N之后，会有一个很简单的优化算法：每个Reducer按N排序，然后按N合并排序，这样可以大大减少数据传输量。3.禁止笛卡尔乘积查询（joinmusthaveonconnectioncondition）Hive不会优化wheretoon的连接条件，所以join必须有onconnectioncondition，不允许两表直接相乘。6.并行执行优化Hive会将一个查询转换为一个或多个阶段。这些阶段可以是MapReduce阶段、采样阶段、合并阶段和限制阶段。或者Hive执行期间可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。但是，一个具体的作业可能包含很多阶段，这些阶段之间可能并不完全相互依赖，也就是说，有些阶段是可以并行执行的，这样可能会缩短整个作业的执行时间。如果有更多的阶段可以并行执行，作业可能会更快完成。通过将参数hive.exec.parallel设置为true，可以启用并发执行。在共享集群中，需要注意的是，如果作业中的并行阶段数增加，集群利用率会增加。设置hive.exec.parallel=true；//开启任务并行执行sethive.exec.parallel.thread.number=16;//同一条SQL允许的最大并行度默认为8。当然，在系统资源比较空闲的情况下有优势，不然就没有资源了，并行也启动不了。7、JVM优化JVM复用是Hadoop调优参数的内容，对Hive的性能影响很大，尤其是小文件难以避免的场景或者任务很多的场景，大部分的执行时间这样的情景很短暂。Hadoop的默认配置通常是使用派生的JVM来执行map和reduce任务。此时，JVM的启动过程可能会产生相当大的开销，尤其是当执行的作业包含数百或数千个任务时。JVM重用使得JVM实例可以在同一个作业中被重用N次。N的值可以在Hadoop的mapred-site.xml文件中配置。通常在10-20个之间，具体数量需要根据具体的业务场景进行测试。mapreduce.job.jvm.numtasks10每个jvm运行多少任务。如果设置为-1，则没有限制。我们还可以在Hive中设置：setmapred.job.reuse.jvm.num.tasks=10设置jvm复用功能的缺点是开启JVM复用会一直占用已用的task槽，所以that它被重用，直到任务完成才能被释放。如果“不平衡”作业中的某些reduce任务比其他reduce任务花费更多时间执行，则保留槽将保持空闲但不能被其他作业使用。直到所有任务完成后才会释放。8.推测执行优化在分布式集群环境中，由于程序bug（包括Hadoopbug）、负载不均衡，或者资源分配不均，会导致同一个作业的多个任务运行速度不一致。可能会明显慢于其他任务（例如，某个作业的某个任务的进度只有50%，而其他所有任务都已运行完毕），这些任务会减慢作业的整体执行进度。为了避免这种情况，Hadoop采用了推测执行（SpeculativeExecution）机制，根据一定的规则推断出“延迟”的任务，并为这样的任务启动一个备份任务，使得任务和原任务可以同时处理。数据，最后选择第一次成功操作完成任务的计算结果作为最终结果。设置并启用推测执行参数：在Hadoop的mapred-site.xml文件中配置：mapreduce.map.speculativetrue如果为true，则多个实例一些map任务可能会并行执行。mapreduce.reduce.speculativetrue如果为true，则多个实例一些reduce任务可能会并行执行。Hive本身也提供配置项来控制reduce-side的推测执行：sethive.mapred.reduce.tasks.speculative.execution=true关于调优优化这些推测性的执行变量，很难给出具体的建议。如果用户因为输入数据量大，需要执行一个长期的map或者reduce任务，启动推测执行造成的浪费是非常巨大的。9、数据倾斜优化数据倾斜的原理众所周知，就是一个或几个key占据了整个数据的90%，这样整个任务的效率就会因为这个key的处理而变慢，它也可能是同一个键引起的。聚集在一起导致内存溢出。Hive的数据倾斜一般处理方案：常见做法，通过调参：sethive.map.aggr=true;设置hive.groupby.skewindata=true;当该选项设置为true时，生成的查询计划有两个MapReduce任务。在第一个MapReduce中，map的输出结果集随机分布在reducer中，每个reducer进行部分聚合操作并输出结果。这样处理的结果就是同一个GroupByKey可能会分布到不同的reducer上，从而达到负载均衡的目的；第二个MapReduce任务根据预处理后的数据结果按照GroupByKey分发到reducer（这个过程可以保证同一个GroupByKey分发到同一个reduce），最后完成最后的聚合操作.但是这个解决方案对我们来说是一个黑盒子，无法控制。那么在日常需求的情况下，如何处理这种数据倾斜；样本采样，得到哪些集中的key；按照一定的规则给集中的密钥加上随机数；join，因为是分散的，所以避免了数据倾斜；将处理结果中之前加入的随机数切分成为原始数据。10.动态分区调整动态分区属性：设置为true开启动态分区功能（默认为false）hive.exec.dynamic.partition=true;动态分区属性：设置为nonstrict，表示允许所有分区为dynamic（默认为strict）设置为strict，表示至少要保证一个分区为statichive.exec.dynamic.partition.mode=严格的;动态分区属性：每个mapper或reducer可以创建hive.exec的最大动态分区数。max.dynamic.partitions.pernode=100;动态分区属性：一条动态分区创建语句最多可以创建的动态分区数hive.exec.max.dynamic.partitions=1000；动态分区属性：全局可以创建的最大文件数Counthive.exec.max.created.files=100000；11.其他参数调优在打开CLI提示符前打印出当前数据库名sethive.cli.print.current.db=true;让CLI打印出字段Namehive.cli.print.header=true;设置任务名称，方便搜索和监控setmapred.job.name=P_DWA_D_IA_S_USER_PROD;决定是否可以在Map端进行聚合操作sethive.map.aggr=true;当存在数据倾斜时进行负载均衡sethive.groupby.skewindata=true;对于不需要聚合的简单SELECTcolfromtableLIMITn语句，不需要启动MapReduce作业，直接通过Fetch任务集获取数据hive.fetch.task.conversion=more;最后，代码优化原则：理解需求原则，这是优化的基础；掌握全数据链路的原则，这是优化的脉络；坚持代码简洁的原则，让优化更容易；没有瓶颈的时候谈优化，这是自找麻烦。