这是一个面试经常面的问题，很不幸，在没有准备的时候，我面到了这个题目，反思了下，将这部分的内容进行总结，给大家一点分享。
hive其实是基于hadoop的数据库管理工具，底层是基于MapReduce实现的，用户写的hivesql最终转换成MapReduce的任务运行在hadoop上，不过MapReduce会因为磁盘IO的问题会运行较慢，因此在Hive sql进行优化，就需要考虑到MapReduce的生命周期，在各个时间节点上进行调优，从而实现Hive sql的整体优化。
如果要从MapReduce角度来分析，就需要从Map，Reduce，join，参数设置的角度来分析。
Map阶段
- 尽早使用where条件：提前把不需要计算的数据过滤掉，而不是在进行复杂操作后再集中过滤。
- 使用分区裁剪：Hive不同分区是按照不同目录存放的，指定分区可以访问特定的目录，减少数据量。
- 使用列裁剪：尽量不要使用select * from ...，指定特定列会只扫描特定列而不扫描全表，提高执行速度，同时select * 会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的 I/O，内存和 CPU 消耗。
- 相似任务尽量使用多路输出：相同的计算只需要计算一次，减少重复计算，同时也能减少reduce task
- 减少case when中的when：表中的文件都需要走一遍when流程，when越多效率就越低，而且在reduce阶段最好做一遍合并压缩操作，否则可能会产生很多文件。
reduce 阶段
- 使用 group by 代替 distinct：因为distinct会把所有任务都分配到一个reduce task中。
- 使用 sort by + distribute by代替 order by：order by 和 distinct 类似，在reduce阶段都会把所有的任务集中到一个reduce task中计算，使用 sort by 和 distribute by 后MR会根据情况启动多个reduce来排序，不过记得一定要加distribute by，否则map后的数据会随机分配到reducer中，不能保证全局有序。
- 尽量使用union all代替union：union去重，有shuffle，union all不去重，无shuffle，shuffle会造成数据在集群中传输，并且伴随着读和写，很影响任务的执行性能。如果要去重，可以最后用group by。
join task过程优化
- 避免使用笛卡尔积：尽量有关联键，hive本身不支持笛卡尔积，需要先用set hive.mapred.mode=nonstrict设为非strict模式。
- 多表join查询时，小表在前，大表在后，Hive在解析带join的SQL语句时，会默认将最后一个表作为probe table（大表），将前面的表作为build table（小表）并试图将它们读进内存（是否读入内存可以配置）。如果表顺序写反，probe table在前面，有引发OOM的风险。
- 小表超出内存限制，采用多次join：build table没有小到可以直接读如内存，但是相比probe table又很小，可以将build table拆成几个表，分别join。
- 小表join大表，尽量使用map join：将build table和probe table在map端直接完成join过程，没有了reduce，效率高很多。
- 多表join时如果允许尽量使用相同的key：这样会将多个join合并为一个MR job来处理。
- join时保证关联键类型相同：如果不同时也适用cast进行转换，否者会导致另外一个类型的key分配到一个reducer上。
- join的时候如果关联健某一类值较多先过滤：比如空值、0等，因为这会导致某一个reducer的计算量变得很大，可以单独处理倾斜key。
- left semi join 代替join判断in和exists：hive0.13前不支持在where 中使用in嵌套查询是否exists，使用left semi join代替join。
参数配置上的优化
小表join时尽量开启map join
set hive.auto.convert.join=true; -- 版本0.11.0之后，默认是开启状态的，但时不时会把这个配置关闭，所以最好还是手动配置一下
set hive.mapjoin.smalltable.filesize=25000000; -- 默认是25Mb开启mapjoin，对于稍微超过这大小的，可以适当调大，但不能太大调整map数
如果输入文件是少量大文件，就减少mapper数；如果输入文件是大量大文件，就增大mapper数；如果是大量的小文件就先合并小文件。
set mapred.min.split.size=10000; -- 最小分片大小
set mapred.max.split.size=10000000; -- 最大分片大小
set mapred.map.tasks=100; -- 设置map task任务数
map任务数计算规则：map_num = MIN(split_num, MAX(default_num, mapred.map.tasks))，合并小文件
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- 输入阶段合并小文件
set hive.merge.mapredfiles=true; -- 输出阶段小文件合并
set hive.merge.mapfiles=true; -- 开启map端合并小文件，默认开启
set hive.merge.mapredfiles=true; -- 开启reduce端合并小文件
set hive.merge.smallfiles.avgsize=16000000; -- 平均文件大小，默认16M，满足条件则自动合并，只有在开启merge.mapfiles和merge.mapredfiles两个开关才有效启用压缩
set hive.exec.compress.intermediate=true; -- 开启输入压缩
set hive.exec.compress.output=true; -- 开启输出压缩
set sethive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec; -- 使用Snappy压缩
set mapred.output.compreession.codec=org.apache.hadoop.io.compress.GzipCodec; -- 使用Gzip压缩
set hive.intermediate.compression.type=BLOCK; -- 配置压缩对象 快或者记录分桶设置
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;设置合适的数据存储格式
hive默认的存储格式是TextFile,但是这种文件格式不使用压缩，会占用比较大空间，目前支持的存储格式有SequenceFile、RCFile、Avro、ORC、Parquet，这些存储格式基本都会采用压缩方式，而且是列式存储，如果指定存储orc模式；
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
并行化执行并行化执行
每个查询被hive转化成多个阶段，有些阶段关联性不大，则可以并行化执行，减少执行时间，主要针对uoion 操作
set hive.exec.parallel=true; -- 开启并行模式
set hive.exec.parallel.thread.numbe=8; -- 设置并行执行的线程数本地化执行
本地模式主要针对数据量小，操作不复杂的SQL。文章来源：https://www.toymoban.com/news/detail-681287.html
set hive.exec.mode.local.auto; -- 开启本地执行模模式
需要满足的条件：
job的输入数据大小必须小于参数:hive.exec.mode.local.auto.inputbytes.max(默认128MB)
job的map数必须小于参数:hive.exec.mode.local.auto.tasks.max(默认4)
job的reduce数必须为0或者1使用严格模式
严格模式主要是防范用户的不规范操作造成集群压力过大，甚至是不可用的情况，只对三种情况起左右，分别是查询分区表是不指定分区；两表join时产生笛卡尔积；使用了order by 排序但是没有limit关键字。文章来源地址https://www.toymoban.com/news/detail-681287.html
set hive.mapred.mode=strict; -- 开启严格模式map端预聚合
预聚合的配置项是
set hive.map.aggr=true; -- group by时，如果先起一个combiner在map端做部分预聚合，使用这个配置项可以有效减少shuffle数据量，默认值true
set hive.groupby.mapaggr.checkinterval=100000; -- 也可以设置map端预聚合的行数阈值，超过该值就会分拆job，默认值100000倾斜均衡配置项
set hive.groupby.skewindata=false; -- group by时如果某些key对应的数据量过大，就会发生数据倾斜。Hive自带了一个均衡数据倾斜的配置项，默认值false动态分区配置
set hive.exec.dynamic.partition=false; -- 是否开启动态分区功能，默认false关闭
set hive.exec.dynamic.partition.mode=strict; -- 动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区
set hive.exec.max.dynamic.partitions.pernode=100; -- 在每个执行MR的节点上，最大可以创建多少个动态分区,根据实际的数据来设定，比如hour必须大于等于24，day必须大于365
set hive.exec.max.dynamic.partitions=1000; -- 在所有执行MR的节点上，最大一共可以创建多少个动态分区
set hive.exec.max.created.files=100000; -- 整个MR Job中，最大可以创建多少个HDFS文件
set hive.error.on.empty.partition=false; -- 当有空分区生成时，是否抛出异常JVM重用
set mapred.job.reuse.jvm.num.tasks=10; -- 在MR job中，默认是每执行一个task就启动一个JVM。如果task非常小而碎，那么JVM启动和关闭的耗时就会很长。可以通过调节参数这个参数来重用。例如将这个参数设成5，就代表同一个MR job中顺序执行的10个task可以重复使用一个JVM，减少启动和关闭的开销。但它对不同MR job中的task无效。到了这里，关于关于hive sql进行调优的理解的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
