性能调优的多样性
1 SQL优化union all可以改成两次连续的insert插入可以提高mapreduce性能，但是在之后的版本hive自身做了优化，该方法则并不能再提高性能2 数据块大小减少map数量，提高网络传输压力有时候可以提高性能3 不同的数据格式SequenceFileParquetORC
4 表设计如分区，分桶，同时分区分桶
优化案例
干预SQl运行方式1 引擎执行过程的干预 --union优化：用grouping sets代替union的SQL优化 SELECT a, b, SUM( c ) FROM table GROUP BY a, b GROUPING SETS ( (a,b), a) 等同于 SELECT a, b, SUM( c ) FROM table GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM table GROUP BY a 优点： GROUPING SETS在遇到多个条件时，聚合是一次性从数据库中取出所有需要操作的数据, 在内存中对数据库进行聚合操作并生成结果，而不会像GROUP BY多次扫描表后进行UNION操作。 这也就是为什么GROUPING SETS和UNION操作所返回的数据顺序是不同的 缺点： 使用union操作会增加IO开销,会减少cpu和内存的开销,使用grouping sets会减少IO开销,会增加cpu和内存的消耗 --count distinct 数据倾斜： 原因：所有key会分发到同一个reduce进行去重统计个数，数据量较大时导致运行时间过长。 解决办法：将 count distinct 改写为 group by 的写法。目的是增加一个MR的操作 案例：select s_age,count(distinct s_score) num from student_tb_orc group by s_age --改写后的代码段 select s_age,count(1) num from( select s_age,s_score,count(1) num group by s_age,s_score ) a 注意：distinct字段如果数据量不大不推荐使用该方法，因为该方式是通过1个MR任务变成2个MR任务来避免数据倾斜 另外 hive.optimize.countdistinct：默认值为true，Hive 3.0新增的配置项，hive自动分2个MR任务处理2 通过SQL-Hint语法实现对计算引擎执行过程的干预 --MAPJOIN()括号中指定的是数据量较小的表，表示在Map阶段完成a,b表的连接 将原来在Reduce中进行连接的操作，前推到Map阶段 --STREAMTABLE()，括号中指定的数据量大的表 默认情况下在reduce阶段进行连接，hive把左表中的数据放在缓存中，右表中的数据作为流数据表 如果想改变上面的那种方式，就用/*+streamtable(表名)*/来指定你想要作为流数据的表3 通过数据库开放的一些配置开关，来实现对计算引擎的干预 如短暂提高写入效率时，可以提高并行度，暂时关闭wal日志等
避免过度优化
代码优化原则
·理透需求原则，这是优化的根本； distinct字段如果数据量不大不推荐使用额外group by，因为该方式是通过1个MR任务变成2个MR任务来避免数据倾斜 如果去重数值本身不大，可自定义实现MR map阶段局部去重后全部写入null为key进一个reduce，唯一一个reduce做全局去重，可实现O(1)去重复杂度·把握数据全链路原则，这是优化的脉络； 1 desc formatted。通过desc formatted tablename来查看hive表信息 可以获取到注释、字段的含义（comment）、创建者用户、数据存储地址、数据占用空间和数据量等信息 2 查询元数据 Hive的元数据主要分为5个大部分：数据库相关的元数据、表相关的元数据、分区相关的元数据、文件存储相关的元数据及其他 3 通过组件反馈信息。常见的资源管理组件有YARN和Mesos，常见的任务管理调度工具有oozie、azkaban和airflow等·坚持代码的简洁原则，这让优化更加简单；·没有瓶颈时谈论优化，是自寻烦恼。
Hive程序相关规范
见书2.2.3
Mapreduce计算引擎
这里基本略过
Mapreduce原理和参数配置
mapper参数配置见5.3.2reducer参数配置见5.4.2
Map端的聚合与Hive配置 Hive配置与作业输出 MapReduce作业与Hive配置
HiveSQL执行计划
执行计划还是需要在工作中对照着书仔细查看 可以结合书本和改文章一起结合理解执行计划 MR join和group by代码实现
查看执行计划的基本信息，即explain；查看执行计划的扩展信息，即explain extended；查看SQL数据输入依赖的信息，即explain dependency； explain dependency识别SQL读取数据范围的差别 如果要使用外连接并需要对左、右两个表进行条件过滤，最好的方式就是将过滤条件放到表的就近处查看SQL操作相关权限的信息，即explain authorization；查看SQL的向量化描述信息，即explain vectorization 向量化模式是Hive的一个特性，在没有引入向量化的执行模式之前，一般的查询操作一次只处理一行， 在向量化查询执行时通过一次处理1024行的块来简化系统底层操作，提高了数据的处理性能 set hive.vectorized.execution.enabled = true; 前提是磁盘列式存储格式为：ORC、ParquetSQL执行计划解读 见6.2/6.3普通函数和操作符 见6.3.2带聚合函数的SQL执行计划解读 6.4 ·在Reduce阶段聚合的SQL执行计划； ·在Map和Reduce都有聚合的SQL执行计划； ·高级分组聚合的执行计划高级分组聚合 使用高级分组聚合需要确保map聚合是否开启 set hive.map.aggr=true GROUPING SETS,cube,rollup 用法如下图 通常使用简单的GROUP BY语句，一份数据只有一种聚合情况，一个分组聚合通常只有一个记录； 使用高级分组聚合，例如cube，在一个作业中一份数据会有多种聚合情况，最终输出时，每种聚合情况各自对应一条数据。 如果使用该高级分组聚合的语句处理的基表，在数据量很大的情况下容易导致Map或者Reduce任务因硬件资源不足而崩溃。 Hive中使用hive.new.job.grouping.set.cardinality配置项来应对上面可能出现的问题， 如果SQL语句中处理的分组聚合情况超过该配置项指定的值，默认值（30），则会创建一个新的作业来处理该配置项的情况。
带窗口/分析函数的SQL执行计划 6.5表连接的SQL执行计划 6.6内接连和外连接 6.6.2
Hive数据处理模式
过滤模式
·where子句过滤；·having子句过滤；·distinct命令过滤（去重原理）； 关闭Map端聚合下，的distinct去重会在Reduce阶段使用Group By Operator操作将其转化成分组聚合的方式 explain select distinct s_age from student_tb_seq == explain select s_age from student_tb_seq group by s_age 扩展：使用分组聚合的方式不是Hive去重的唯一方式，有时Hive还会用Hash表进行去重·表过滤； 表过滤是指过滤掉同一个SQL语句需要多次访问相同表的数据，将重复的访问操作过滤掉并压缩成只读取一次 如union改multi-group-by-insert方法 同时两个group by的insert，hive会合并一个mr处理·分区过滤； 普通where子句的过滤是在Map阶段，而分区列筛选其实是在Map的上一个阶段，即在输入阶段进行路径的过滤·分桶过滤； 说分区是对目录的过滤，分桶是对文件的过滤。如下图
·索引过滤； Hive的索引在Hive 3.0版本中被废弃，可以使用两种方式进行替换： 方式一，物化视图（Materialized views）。这个概念对于使用Oracle的开发者并不陌生， 通过使用物化视图，可以达到类似hive索引的效果，该特性在Hive 2.3.0版本中引入。 方式二，使用ORC/Parquet的文件存储格式，也能够实现类似索引选择性扫描， 快速过滤不需要遍历的block，这是比分桶更为细粒度的过滤·列过滤 在进行列筛选时，通常需要先取整行的数据，再通过列的偏移量取得对应的列值 但如果是列式存储，则可以直接读取
聚合模式
常见的聚合
4 表设计如分区，分桶，同时分区分桶0
可计算中间结果的聚合模式 不可计算中间结果的聚合模式
4 表设计如分区，分桶，同时分区分桶1
连接模式
4 表设计如分区，分桶，同时分区分桶2
4 表设计如分区，分桶，同时分区分桶3
4 表设计如分区，分桶，同时分区分桶4
YARN日志
ResourceManager Web UI 工作中经常会用到
数据存储
4 表设计如分区，分桶，同时分区分桶5
ORC
4 表设计如分区，分桶，同时分区分桶6
4 表设计如分区，分桶，同时分区分桶7
Parquet
4 表设计如分区，分桶，同时分区分桶8
数据归档
4 表设计如分区，分桶，同时分区分桶9
发现并优化Hive中的性能问题
10.1 监控Hive数据库的状态
干预SQl运行方式1 引擎执行过程的干预 --union优化：用grouping sets代替union的SQL优化 SELECT a, b, SUM( c ) FROM table GROUP BY a, b GROUPING SETS ( (a,b), a) 等同于 SELECT a, b, SUM( c ) FROM table GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM table GROUP BY a 优点： GROUPING SETS在遇到多个条件时，聚合是一次性从数据库中取出所有需要操作的数据, 在内存中对数据库进行聚合操作并生成结果，而不会像GROUP BY多次扫描表后进行UNION操作。 这也就是为什么GROUPING SETS和UNION操作所返回的数据顺序是不同的 缺点： 使用union操作会增加IO开销,会减少cpu和内存的开销,使用grouping sets会减少IO开销,会增加cpu和内存的消耗 --count distinct 数据倾斜： 原因：所有key会分发到同一个reduce进行去重统计个数，数据量较大时导致运行时间过长。 解决办法：将 count distinct 改写为 group by 的写法。目的是增加一个MR的操作 案例：select s_age,count(distinct s_score) num from student_tb_orc group by s_age --改写后的代码段 select s_age,count(1) num from( select s_age,s_score,count(1) num group by s_age,s_score ) a 注意：distinct字段如果数据量不大不推荐使用该方法，因为该方式是通过1个MR任务变成2个MR任务来避免数据倾斜 另外 hive.optimize.countdistinct：默认值为true，Hive 3.0新增的配置项，hive自动分2个MR任务处理2 通过SQL-Hint语法实现对计算引擎执行过程的干预 --MAPJOIN()括号中指定的是数据量较小的表，表示在Map阶段完成a,b表的连接 将原来在Reduce中进行连接的操作，前推到Map阶段 --STREAMTABLE()，括号中指定的数据量大的表 默认情况下在reduce阶段进行连接，hive把左表中的数据放在缓存中，右表中的数据作为流数据表 如果想改变上面的那种方式，就用/*+streamtable(表名)*/来指定你想要作为流数据的表3 通过数据库开放的一些配置开关，来实现对计算引擎的干预 如短暂提高写入效率时，可以提高并行度，暂时关闭wal日志等0
10.2 监控当前集群状态
10.3 定位性能瓶颈
干预SQl运行方式1 引擎执行过程的干预 --union优化：用grouping sets代替union的SQL优化 SELECT a, b, SUM( c ) FROM table GROUP BY a, b GROUPING SETS ( (a,b), a) 等同于 SELECT a, b, SUM( c ) FROM table GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM table GROUP BY a 优点： GROUPING SETS在遇到多个条件时，聚合是一次性从数据库中取出所有需要操作的数据, 在内存中对数据库进行聚合操作并生成结果，而不会像GROUP BY多次扫描表后进行UNION操作。 这也就是为什么GROUPING SETS和UNION操作所返回的数据顺序是不同的 缺点： 使用union操作会增加IO开销,会减少cpu和内存的开销,使用grouping sets会减少IO开销,会增加cpu和内存的消耗 --count distinct 数据倾斜： 原因：所有key会分发到同一个reduce进行去重统计个数，数据量较大时导致运行时间过长。 解决办法：将 count distinct 改写为 group by 的写法。目的是增加一个MR的操作 案例：select s_age,count(distinct s_score) num from student_tb_orc group by s_age --改写后的代码段 select s_age,count(1) num from( select s_age,s_score,count(1) num group by s_age,s_score ) a 注意：distinct字段如果数据量不大不推荐使用该方法，因为该方式是通过1个MR任务变成2个MR任务来避免数据倾斜 另外 hive.optimize.countdistinct：默认值为true，Hive 3.0新增的配置项，hive自动分2个MR任务处理2 通过SQL-Hint语法实现对计算引擎执行过程的干预 --MAPJOIN()括号中指定的是数据量较小的表，表示在Map阶段完成a,b表的连接 将原来在Reduce中进行连接的操作，前推到Map阶段 --STREAMTABLE()，括号中指定的数据量大的表 默认情况下在reduce阶段进行连接，hive把左表中的数据放在缓存中，右表中的数据作为流数据表 如果想改变上面的那种方式，就用/*+streamtable(表名)*/来指定你想要作为流数据的表3 通过数据库开放的一些配置开关，来实现对计算引擎的干预 如短暂提高写入效率时，可以提高并行度，暂时关闭wal日志等1
Reduce的数据倾斜
缓慢的Shuffle
干预SQl运行方式1 引擎执行过程的干预 --union优化：用grouping sets代替union的SQL优化 SELECT a, b, SUM( c ) FROM table GROUP BY a, b GROUPING SETS ( (a,b), a) 等同于 SELECT a, b, SUM( c ) FROM table GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM table GROUP BY a 优点： GROUPING SETS在遇到多个条件时，聚合是一次性从数据库中取出所有需要操作的数据, 在内存中对数据库进行聚合操作并生成结果，而不会像GROUP BY多次扫描表后进行UNION操作。 这也就是为什么GROUPING SETS和UNION操作所返回的数据顺序是不同的 缺点： 使用union操作会增加IO开销,会减少cpu和内存的开销,使用grouping sets会减少IO开销,会增加cpu和内存的消耗 --count distinct 数据倾斜： 原因：所有key会分发到同一个reduce进行去重统计个数，数据量较大时导致运行时间过长。 解决办法：将 count distinct 改写为 group by 的写法。目的是增加一个MR的操作 案例：select s_age,count(distinct s_score) num from student_tb_orc group by s_age --改写后的代码段 select s_age,count(1) num from( select s_age,s_score,count(1) num group by s_age,s_score ) a 注意：distinct字段如果数据量不大不推荐使用该方法，因为该方式是通过1个MR任务变成2个MR任务来避免数据倾斜 另外 hive.optimize.countdistinct：默认值为true，Hive 3.0新增的配置项，hive自动分2个MR任务处理2 通过SQL-Hint语法实现对计算引擎执行过程的干预 --MAPJOIN()括号中指定的是数据量较小的表，表示在Map阶段完成a,b表的连接 将原来在Reduce中进行连接的操作，前推到Map阶段 --STREAMTABLE()，括号中指定的数据量大的表 默认情况下在reduce阶段进行连接，hive把左表中的数据放在缓存中，右表中的数据作为流数据表 如果想改变上面的那种方式，就用/*+streamtable(表名)*/来指定你想要作为流数据的表3 通过数据库开放的一些配置开关，来实现对计算引擎的干预 如短暂提高写入效率时，可以提高并行度，暂时关闭wal日志等2
10.3.6 集群资源的限制 10.4 数据倾斜
干预SQl运行方式1 引擎执行过程的干预 --union优化：用grouping sets代替union的SQL优化 SELECT a, b, SUM( c ) FROM table GROUP BY a, b GROUPING SETS ( (a,b), a) 等同于 SELECT a, b, SUM( c ) FROM table GROUP BY a, b UNION SELECT a, null, SUM( c ) FROM table GROUP BY a 优点： GROUPING SETS在遇到多个条件时，聚合是一次性从数据库中取出所有需要操作的数据, 在内存中对数据库进行聚合操作并生成结果，而不会像GROUP BY多次扫描表后进行UNION操作。 这也就是为什么GROUPING SETS和UNION操作所返回的数据顺序是不同的 缺点： 使用union操作会增加IO开销,会减少cpu和内存的开销,使用grouping sets会减少IO开销,会增加cpu和内存的消耗 --count distinct 数据倾斜： 原因：所有key会分发到同一个reduce进行去重统计个数，数据量较大时导致运行时间过长。 解决办法：将 count distinct 改写为 group by 的写法。目的是增加一个MR的操作 案例：select s_age,count(distinct s_score) num from student_tb_orc group by s_age --改写后的代码段 select s_age,count(1) num from( select s_age,s_score,count(1) num group by s_age,s_score ) a 注意：distinct字段如果数据量不大不推荐使用该方法，因为该方式是通过1个MR任务变成2个MR任务来避免数据倾斜 另外 hive.optimize.countdistinct：默认值为true，Hive 3.0新增的配置项，hive自动分2个MR任务处理2 通过SQL-Hint语法实现对计算引擎执行过程的干预 --MAPJOIN()括号中指定的是数据量较小的表，表示在Map阶段完成a,b表的连接 将原来在Reduce中进行连接的操作，前推到Map阶段 --STREAMTABLE()，括号中指定的数据量大的表 默认情况下在reduce阶段进行连接，hive把左表中的数据放在缓存中，右表中的数据作为流数据表 如果想改变上面的那种方式，就用/*+streamtable(表名)*/来指定你想要作为流数据的表3 通过数据库开放的一些配置开关，来实现对计算引擎的干预 如短暂提高写入效率时，可以提高并行度，暂时关闭wal日志等3
Hive知识体系总结
在Hive中，数据粒度可以分为表、分区、桶、列和字段
原文：https://juejin.cn/post/7096687050941792286