HIVE优化：语句、参数、表结构优化

show conf 值名：查看配置项当前的值，如 show conf 'hive.execution.engine'。

map相关配置

• 在默认情况下Map的个数defaultNum=目标文件或数据的总大小totalSize/hdfs集群文件块的大小blockSize。
• 当用户指定mapred.map.tasks，即为用户期望的Map大小，用expNum表示，这个期望值计算引擎不会立即采纳，它会获取mapred.map.tasks与defaultNum的较大值，用expMaxNum表示，作为待定选项。
• 获取文件分片的大小和分片个数，分片大小为参数mapred.min.split.size 和blockSize间的较大值，用splitMaxSize表示，将目标文件或数据的总大小除以splitMaxSize即为真实的分片个数，用realSplitNum表示。
• 获取realSplitNum与expMaxNum较小值则为实际的Map个数。

减少Map个数，需要增大mapred.min.split.size的值，减少mapred.map.tasks的值；
增大Map个数，需要减少mapred.min.split.size的值，同时增大mapred.map.tasks的值。

如果开启了map前小文件合并set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;，map数则取决于合并相关参数

map资源配置

mapreduce.map.memory.mb

该参数的含义是，单个Map Task申请的container容器内存大小，其默认值为1024。该值不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。
该参数需要根据不同的计算任务单独进行配置，在hive中，可直接使用如下方式为每个SQL语句单独进行配置：
set mapreduce.map.memory.mb=2048;

mapreduce.map.cpu.vcores

该参数的含义是，单个Map Task申请的container容器cpu核数，其默认值为1。该值一般无需调整。

---

reduce相关配置

• mapred.reduce.tasks：设置Reducer的数量，默认值是-1，代表由系统根据需要自行决定Reducer的数量。
• hive.exec.reducers.bytes.per.reducer：设置每个Reducer所能处理的数据量，在Hive 0.14版本以前默认是1000000000（1GB）, Hive 0.14及之后的版本默认是256MB。输入到Reduce的数据量有1GB，那么将会拆分成4个Reducer任务。
• hive.exec.reducers.max：设置一个作业运行的最大Reduce个数，默认值是999。
• hive.multigroupby.singlereducer：表示如果一个SQL 语句中有多个分组聚合操作，且分组是使用相同的字段，那么这些分组聚合操作可以用一个作业的Reduce完成，而不是分解成多个作业、多个Reduce完成。这可以减少作业重复读取和Shuffle的操作。
• hive.mapred.reduce.tasks.speculative.execution：表示是否开启Reduce 任务的推测执行。即系统在一个Reduce 任务中执行进度远低于其他任务的执行进度，会尝试在另外的机器上启动一个相同的Reduce任务。
• hive.optimize.reducededuplication：表示当数据需要按相同的键再次聚合时，则开启这个配置，可以减少重复的聚合操作。
• hive.vectorized.execution.reduce.enabled：表示是否启用Reduce任务的向量化执行模式，默认是true。MapReduce计算引擎并不支持对Reduce阶段的向量化处理。
• hive.vectorized.execution.reduce.groupby.enabled：表示是否移动Reduce任务分组聚合查询的向量化模式，默认值为true。MapReduce计算引擎并不支持对Reduce阶段的向量化处理。

自动计算reduce个数=min(hive.exec.reducers.max,总数据量大小/hive.exec.reducers.bytes.per.reducer)
或者通过mapred.reduce.tasks参数手动指定reduce个数
每个reduce都会生成一个文件；reduce的初始启动也会消耗资源：并不是越多越好；
reduce如果按每hive.exec.reducers.bytes.per.reducer，这个大小取自map前的文件大小；如果map有过滤预聚合之类的，可以根据实际情况适当调大该参数或者指定mapred.reduce.tasks减少reduce个数；

reduce资源配置

mapreduce.reduce.memory.mb

该参数的含义是，单个Reduce Task申请的container容器内存大小，其默认值为1024。该值同样不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。
该参数需要根据不同的计算任务单独进行配置，在hive中，可直接使用如下方式为每个SQL语句单独进行配置：
set mapreduce.reduce.memory.mb=2048;

mapreduce.reduce.cpu.vcores

该参数的含义是，单个Reduce Task申请的container容器cpu核数，其默认值为1。该值一般无需调整。

同一个job下，hive通过mapreduce.job.running.map.limit参数设置maptask并行度，即同一时间该job最多同时可执行多少个task。参数为0表示不限制（但这里仍然可能会收到集群资源管理器的限制）
mapreduce.job.running.reduce.limit，设置最大reduce并行执行数量，0表示不限制，可能受资源管理器限制

这意味着，如果资源够能保证并行度，没有其他限制，如果task数量小于该参数限制，我们增加task的数量，使得接近限制参数值，能减少job运行时长（不过会适当增加cpu消耗vcores）

---

表结构

分区

where条件是在map端过滤，分区筛选在输入阶段过滤；在hdfs上一个分区对应一个目录；对常用字段使用分区字段，比如日期，筛选某天的数据速度显著提升；

分桶

分桶能够对原有表或者分区所存储的数据进行重新组织，使得通过分桶的方式能够快速过滤掉大量不需要遍历的文件。分桶是对文件过滤，一般使用hash模余，每个记录存储到桶的算法：记录所存储的桶=mod(hash(分桶列的值),4)
hash表示Hash函数，获取分桶列的值对应的哈希值；mod表示取余函数。
如果查询字段为分桶字段，能快速定位条件所在记录位置，而无须全表扫表，类似索引。对于大表，可以快速缩短读取数据时间，同时也能优化表的链接。比如在两表JOIN中，相同桶的数据进行join可以节约时间。（不是一个桶的数据join不上）
可以使用hdfs dfs -ls 表路径查看表在HDFS的存储。
使用桶的Map连接要保证连接的两张表的分桶数之间是倍数关系。
如果两个表的某字段分桶是10个，在两个表使用该字段关联join时，启用6个reduce

索引

Hive的索引在Hive 3.0版本中被废弃，可以使用两种方式进行替换：

• 物化视图（Materialized views）。这个概念对于使用Oracle的开发者并不陌生，通过使用物化视图，可以达到类似hive索引的效果，该特性在Hive 2.3.0版本中引入。
• 使用ORC/Parquet的文件存储格式，也能够实现类似索引选择性扫描，快速过滤不需要遍历的block，这是比分桶更为细粒度的过滤。

列筛选

ORC/Parquet中存储了文件定义的Schema, ORC/Parquet可以通过Schema直接读取表所在的列，以达到列过滤的目的。
在spark中，rdd需要对每一行按分隔符分割筛选，DataFrame有表结构可直接筛选对应数据列。

---

语句优化

大表join小表：Replication

HIVE中表连接的两种方式，Repartition连接和Replication连接。
Repartition连接
发生在Shuffle和Reduce阶段。一般如果不特别做其他声明，通常提到的连接就是Repartition连接。Map的任务读取A、B两个表的数据，将按连接条件发往相同的Reduce，在Reduce中计算合并的结果。

Replication连接
发生在Map阶段，Replication连接在Map阶段完成连接操作，相比发生在Shuffle阶段的Repartition连接，可以减少从HDFS读取表的次数，可以在Map 阶段实现连接时不匹配条件的记录行的过滤，减少下游网络传输的数据量和下游计算节点处理的数据量。
Replication 连接在操作时会将一个表的数据复制到各个Map 任务所在的节点并存储在缓存中，如果连接的两个表都是数据量庞大的表，会带来较大的性能问题，仅适用于两表连接中有一张小表的情况。
Replication连接根据实现的不同表连接可以分为：

• 普通的MapJoin：对使用的表类型无特殊限制，只需要配置相应的Hive配置。
• Bucket MapJoin：要求使用的表为桶表。hash模余相同的值会被分发到同一个桶。
• Skewed MapJoin：要求使用的表为倾斜表。
• Sorted Merge Bucket MapJoin：要求使用的表为桶排序表。

普通mapjoin

-- hive命令可能被禁用，这里开启
set hive.ignore.mapjoin.hint=false;
select /*+mapjoin(t2)*/ t2.product_type
	,sum(t1.salses) as sum_sale
from sales_table t1 
join dim_product_info t2 
on t1.product_id = t2.product_id
group by t2.product_type

在Hive中使用common map join有几种方式，方式一是使用MapJoin的hint语法。需要注意的是要关闭忽略hint的配置项，否则该方法不会生效，即set hive.ignore.mapjoin.hint=false;

可使用Hive配置MapJoin。使用Hive配置需要使用到以下配置：

• hive.auto.convert.join：在Hive 0.11版本以后，默认值为true，表示是否根据文件大小将普通的repartition连接将化为Map的连接。
• hive.smalltable.filesize/hive.mapjoin.smalltable.filesize：默认值为25000000（bytes）。两个配置表示的含义都是当小表的数据小于该配置指定的阀值时，将尝试使用普通repartition连接转化Map连接。该配置需要和hive.auto.convert.join配合使用。

Map Join相关的Hive配置如下：

• hive.mapjoin.localtask.max.memory.usage：默认值为0.9。表示小表保存到内存的哈希表的数据量最大可以占用到本地任务90%的内存，如果超过该值，则表示小表的数据量太大，无法保存到内存中。
• hive.mapjoin.followby.gby.localtask.max.memory.usage：默认值是0.55。表示如果在MapJoin之后还有group by的分组聚合操作，本地任务最大可以分配当前任务55%的内存给哈希表缓存数据，如果缓存的数据大于该值，表示停止当前本地任务。在优化时，如果分组聚合后的数据会大幅度地缩小，可以适当提高该阀值，以提升内存可以缓存的数据量，如果分组聚合后的数据不降反增，则需要适当调低该比值防止内存溢出，导致作业失败。

倾斜连接：

set hive.optimize.skewjoin=true;
-- 负载均衡参数
set hive.skewjoin.key=100000;
select  t2.product_type
	,t1.salses
from sales_table t1 
join dim_product_info t2 
on t1.product_id = t2.product_id

创建倾斜表：通过在创建表时指定数据倾斜键，将指定的数据键分割成单独的数据文件或者目录，这样可以加快数据过滤从而提供作业的运行速度。

--创建倾斜表student_list_bucket表
CREATE TABLE student_info_bucket (s_no STRING, s_score bigint)
--指定倾斜的键
SKEWED BY (s_score) ON (96,97)
--以目录形式存储倾斜的数据
STORED AS DIRECTORIES;

倾斜键的数据存储在Hive_DEFAULT_LIST_BUCKETING_DIR_NAME目录中（比如上面的96和97），而其他数据则存储在与该目录同一级的文件目录下。
Hive中与SkewedJoin相关的配置如下：

• hive.optimize.skewjoin：默认值是false，表示是否优化有倾斜键的表连接。如果为true, Hive将为连接中的表的倾斜键创建单独的计划。
• hive.skewjoin.key：默认值为100000。如果在进行表连接时，相同键的行数多于该配置所指定的值，则认为该键是倾斜连接键。
• hive.skewjoin.mapjoin.map.tasks：默认值为10000。倾斜连接键，在做MapJoin 的Map任务个数。需要与hive.skewjoin.mapjoin.min.split一起使用。
• hive.skewjoin.mapjoin.min.split：默认值为33554432，即32MB。指定每个split块最小值，该值用于控制倾斜连接的Map任务个数。

count(distinct)

语句1：
select count(distinct age) as dis_cnt from user_info
count(distinct)会将同一个key的数据交给一个reduce处理，数据数据记录行多，跑批相对耗时长。以上语句由一个MR处理完成。
语句2：

select count(1) as dis_cnt 
from (select age from user_info group by age) t

语句2执行计划由两个MR构成；
age年龄枚举值较少，map段预聚合，shuffle到reduce端数据量整体还是比较少，两者区别不是很明显。count(distinct)计算使用一个MR计算耗时可能会更短些。
如果key数据量比较大的情况下，两个MR处理相对会快些，比如大企业用户规模较大，查看近1年的用户活跃数（月表，用户id去重），如果使用count(distinct)，如果每个月活跃去重在1亿，一年意味着大概有12亿的数据交由1个reduce去重计数。这时候交由两个MR处理，多处一个MR的开销相比时效提升几乎可以忽略不计。
在Hive 3.0中即使遇到数据倾斜，语句1将hive.optimize.countdistinct设置为true，写法也能达到语句2的效果。

表过滤

表过滤是指过滤掉同一个SQL 语句需要多次访问相同表的数据，将重复的访问操作过滤掉并压缩成只读取一次。表过滤的常见操作就是使用multi-group-by语法替换多个查询语句求并集的句式。

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
explain
insert into table insert_table partition(tp)
select s_age, min(s_birth) as birth, 'max' as label
from user_info
group by s_age
union all 
select s_age, max(s_birth) as birth, 'min' as label
from user_info
group by s_age

上面的sql，user_info表会读两次

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
explain
from user_info
insert into table student_stat partition(tp)
select s_age, min(s_birth) as birth, 'max' as label
group by s_age
insert into table  student_stat partition(tp)
select s_age, max(s_birth) as birth, 'min' as label
group by s_age;

left semi join

类似与mysql的exists语句，只要找到了便打断不再往下找；而join会遍历试着匹配每一个能匹配上的key。

select id,t1.field1,t2.field2
from table_a t1 
left semi join table_b t2 
on t1.id = t2.id 

等同于

select id,t1.field1,t2.field2
from table_a t1 
where id in (
	select id from table_b
) 

join倾斜

此外我们也可以把倾斜键单独拉出来处理，比如下面一段sql

select t1.field1,t1.mobile_no,t2.field2 
from table_a t1
left join table_b t2 
on t1.mobile_no=t2.mobile_no

如果table_a表的mobile手机号存在大量缺失，那那么我们可以把这部分单独拉出来处理，比如修改为：

select t1.fields,t1.mobile_no,t2.field2 
from table_a t1
left join table_b t2 
on t1.mobile_no=t2.mobile_no
where t1.mobile_no is not null   -- 筛选非空
union all 
select t1.fields,null as mobile,null as field2
from table_a    -- 因为null关联不上，此处省略join步骤
where mobile_no is null 

或者我们也可以这样：

select t1.field1,t1.mobile_no,t2.field2 
from table_a t1
left join table_b t2 
on nvl(t1.mobile_no,rand())=t2.mobile_no   -- 引入随机数打散null-key
-- 如果table_b的mobile_no有缺失的话，过滤掉，一般不加缺失过滤，执行计划也会加上该filter操作；
	and t2.mobile_no is not null   

该方法虽然打散了null，不过mobile_no为null部分仍然经历了shuffle和reduce过程

groupby倾斜

原sql为：

select key_name,sum(val) as sum_val
from table_name 
group by key_name

我们可以在第一个stage添加随机数key，改写为：

select key_name    
	,sum(sum_val) as sum_val
from 
(	-- 第一个mr，添加随机数列打散
	select key_name
		,ceiling(rand() * 99) as rnk    -- 添加随机数打散
		,sum(val) as sum_val
	from table_name 
	group by key_name,ceiling(rand() * 99)
) t 
group by key_name   -- 再次聚合，第二个mr

类似于添加groupby倾斜参数

---

参数优化

with语句

hive的with语句默认生成with语句一个视图，并不会把数据物化。使用with语句代码看起来相对简洁；如果sql中with的代码块有多次调用，会重复生成with执行计划，不一定会提高执行效率。
在高版本中，with语句可物化，参数为：hive.optimize.cte.materialize.threshold，参数默认是-1关闭。如果开启（大于0），比如n，当with…as语句被引用n次以上，会物化with生成的表，对应的with语句只执行一次。

-- 该sql中，tmp_tb1有调用了两次，该部分语句只会执行一次
set hive.optimize.cte.materialize.threshol=2
with tmp_tb1 as (
	select user_id,register_date from tb 
),tmp_tb2 as (
	select user_id from tb2 where dt = '20220918' group by userid
)
select user_id,register_date from tmp_tb1 
union all
-- tmp_tb2剔除tmp_tb1的数据
select t2.user_id,'20220918' as register_date
from tmp_tb2 t2 left join tmp_tb1 t1 
on t2.user_id=t1.user_id 
where t1.user_id is null 

向量运算

hive开启向量计算，将一次处理一条数据变为一次处理1万条数据，以此提高程序的性能
hive.vectorized.execution.enabled：表示是否开启向量模式，默认值为false。
开启：set hive.vectorized.execution.enabled = true;

目前MapReduce计算引擎只支持Map端的向量化执行模式，Tez和Spark计算引擎可以支持Map和Reduce端的向量化执行模式

Hint

hive.ignore.mapjoin.Hint：是否忽略SQL中MapJoin的Hint关键，在Hive 0.11版本之后默认值为true，即开启忽略Hint的关键字。如果要使用MapJoin的Hint关键字，要在使用前开启支持Hint语法，否则达不到预期的效果。
Hint关键字，比如/*+ MAPJOIN(smalltable)*/

mapjoin

大表join小表
hive.auto.convert.join：是否开启MapJoin自动优化，hive 0.11版本以前默认关闭， 0.11及以后的版本默认开启。
hive.smalltable.filesize or hive.mapjoin.smalltable.filesize：默认值2500000（25MB）如果大小表在进行表连接时的小表数据量小于这个默认值，则自动开启MapJoin优化。在Hive 0.8.1以前使用hive.smalltable.filesize，之后的版本使用hive.mapjoin.smalltable.filesize参数。

• hive.mapjoin.optimized.hashtable：默认值是true, Hive 0.14新增，表示使用一种内存优化的哈希表去做MapJoin。由于该类型的哈希表无法被序列化到磁盘，因此该配置只能用于Tez或者Spark。
• hive.mapjoin.optimized.hashtable.wbsize：默认值是10485760（10MB），优化的哈希表使用的是一种链块的内存缓存，该值表示一个块的内存缓存大小。这种结构对于数据相对较大的表能够加快数据加载，但是对于数据量较小的表，将会分配多余的内存。
• hive.vectorized.execution.mapjoin.native.enabled：是否使用原生的向量化执行模式执行MapJoin，它会比普通MapJoin速度快。默认值为False。

hive.auto.convert.join.noconditionaltask这是一个布尔类型的参数，用于决定是否可以将多个MapJoin任务合并成一个任务。如果设置为true，那么当满足一定条件时，Hive会将多个MapJoin任务合并成一个任务。在开启该参数后，比如如下sql，假设table_b和table_c都是小表；集群是一个stage

select b.field_a,c.field_a
	,sum(a.val) as val
from table_a a 
join table_b b
on a.filed_b=b.field_b
join table_c c
on a.field_c = c.field_c
group by b.field_a,c.field_a

执行计划会先生成一个本地的stage，读取小表把小表推到map节点，然后map端join两个小表按b.field_a,c.field_a分区（如果有预聚合，map端会有显示），reduce再groupby聚合
hive.auto.convert.join.noconditionaltask.size这是另一个与合并MapJoin相关的参数，它表示在将多个MapJoin任务合并成一个任务时，所有小表的文件大小总和的最大值。
在执行计划里，如果map端的数据源来自于上一个reduce的输出，那执行计划里打印的都是reduce join，实际执行日志里，如果满足小表join则该stage没有reduce；因为上一个stage输出的数据量只有计算完才知道多少，在初始时是不确定的
此外，mapjoin小表大小取自于map时读取文件的大小（filter筛选前大小）

mapjoin失效情况
mapjoin在left或者right连接，小表为主表时会失效。
如下：小表 left join，大表开启了两个map，对于小表为2这一行，在上面这个map，由于没有2，大表为null，但在第二个map时，大表存在2这行有记录。这样就会出现一个问题，在大表某一个map不存在2这条记录时，大表的这个字段是为null还是2？
不可操作，所以该种情况mapjoin失效

hive的mapjoin参数

map端预聚合

Map端聚合通常指代实现Combiner类。Combiner也是处理数据聚合，不同于Reduce是聚合集群的全局数据。Combiner聚合是Map阶段处理后的数据，处理类似于spark里边的reduceByKey。
Map预聚合目标可以减少Shuffle数据量。如果数据经过聚合后不能明显减少，那就是浪费机器的I/O资源。

• hive.map.aggr：是否开启Map任务的聚合，默认值是true。
• hive.map.aggr.hash.min.reduction：是一个阈值，默认值是0.5。
• hive.groupby.mapaggr.checkinterval：默认值是100000。Hive在启用Combiner时会尝试取这个配置对应的数据量进行聚合，将聚合后的数据除以聚合前的数据，如果小于hive.map.aggr.hash.min.reduction会自动关闭。
• hive.map.aggr.hash.percentmemory：默认值是0.5。该值表示在进行Mapper端的聚合运行占用的最大内存。例如，分配给该节点的最大堆（xmx）为1024MB，那么聚合所能使用的最大Hash表内存是512MB，如果资源较为宽裕，可以适当调节这个参数。
• hive.map.aggr.hash.force.flush.memory.threshold：默认值是0.9。该值表示当在聚合时，所占用的Hash表内存超过0.9，将触发Hash表刷写磁盘的操作。例如Hash表内存是512MB，当Hash表的数据内存超过461MB时将触发Hash表写

文件压缩

减少Shuffle数据量，开启压缩同时意味着有解压缩消耗，一般适用于大型作业。
开启文件作业的压缩只要将hive.exec.compress.intermediate参数设置为true
压缩如果要是MapReduce中起作用，前提是需要配置mapred.output.compression. codec和mapred.output.compression两个属性。

合并小文件

• map执行前合并文件
  set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

  我们假设表a通过sparksql默认参数写入到hdfs（shuffle.partitions=200），有200个文件块，每个文件块大小只有1m；开启该参数后，map时只有一个task，会将小文件进行合并；如果没开启，每个文件块一个task；
  mapred.max.split.size=256000000 每个map最大输入大小
  mapred.min.split.size.per.node 每个节点split的至少大小
  mapred.min.split.size.per.rack 一个交换机下split至少大小

  合并逻辑是，先是每个节点各个数据块按mapred.max.split.size切割，剩下的碎片按mapred.min.split.size.per.node合并，最后不同节点碎片按mapred.min.split.size.per.rack合并

• 启用hive.merge.mapfile参数，默认启用，合并只有Map任务作业的输出文件；
  比如：select id,name,sale from tablename where sale>1000这条sql语句只有map操作

• 启用hive.merge.mapredfiles参数，默认启用，合并MapReduce作业最终的输出文件；
  比如：select age,count(1) as cnt from tablename group by age在reduce后，默认每个reducetask会生成一个文件快，如果文件块小则可以通过该参数将文件进行合并；

• 设置hive.merge.smallfiles.avgsize参数，默认16MB，当输出的文件小于该值时，启用一个MapReduce任务合并小文件；

• 设置hive.merge.size.per.task参数，默认256MB，是每个任务合并后文件的大小。一般设置为和HDFS集群的文件块大小一致。set hive.merge.size.per.task = 268435456;

文件太多会增加map-task开销；同事如果reduce个数太多，每个reduce会输出一个文件也会增加下游任务map读取开销；

可以通过show files in <table_name> 查看表文件数；通过alter table <table_name> concatenate;开启文件合并；

作业并行

hive.optimize.countdistinct：默认值为true, Hive 3.0新增的配置项。当开启该配置项时，去重并计数的作业会分成两个作业来处理这类SQL，以达到减缓SQL的数据倾斜作用。

hive.exec.parallel：默认值是False，是否开启作业的并行。默认情况下，如果一个SQL被拆分成两个阶段，如stage1、stage2，假设这两个stage没有直接的依赖关系，还是会采用窜行的方式依次执行两个阶段。如果开启该配置，则会同时执行两个阶段。在资源较为充足的情况下开启该配置可以有效节省作业的运行时间。比如使用union all语句，union前后sql子句是没有依赖可以并行执行；join关联表如果是子查询，子查询可以并行执行

select 'label1' as label
	,sum(val) as val
from tablename1
union all   -- 前后两段可以并行执行
select 'label2' as label 
	,sum(val) as val
from tablename2

hive.exec.parallel.thread.number ；set hive.exec.parallel.thread.number=16 设置并行度为16

相关性优化

hive.optimize.correlation：默认值为false，打开该配置可以减少重复的Shuffle操作。
比如sql，join已经按product_type分区，groupby没必要重新Shuffle。

select t2.product_type,sum(t1.sale) as sale 
from dw_cus_sale_ptd t1 
join dim_product_info t2 
on t1.product_type = t2.product_type 
group by t2.product_type`

本地执行

文件数量大，使用分布式计算，多台机器并行计算可以显著减少计算时间。当hive数据量非常小，查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。这种情况，hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显减少。
相关参数：
set hive.exec.mode.local=true开启本地mr
set hive.exec.mode.local.auto.inputbytes.max=5000000设置local mr的最大数据数据量，当输入数据量小于这个值时采用local mr的方式，默认为134217728，即128M。
set hive.exec.mode.local.auto.input.files.max=12`设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4。

Fetch抓取

Fetch抓取：hive中某些情况可以不必使用mr计算，例如：select * from table1。这种情况，hive可以简单读取文件输出到控制台。
在hive-default.xml.tempate文件中，hive.fetch.task.conversion默认是more，老版本是minimal，这个属性修改为more后，全局查找，字段查找，limit查找不走mr计算。
比如：select id,name from table name limit 1000开启参数后，这条sql语句并不会走mr计算
在关系型数据库中使用select * from table limit 10通常会全表扫描再limit。

SET hive.fetch.task.conversion=none; 走mr设置

limit优化相关参数：

# 开启对数据进行采样的功能
SET hive.limit.optimize.enable=true; 
 
# 设置最小采样容量，默认为100000 
SET hive.limit.row.max.size=100000; 
 
# 可抽样的最大文件数，默认为10  
SET hive.limit.optimize.file=10;

join倾斜

hive.optimize.skewjoin=true
hive.skewjoin.key=10000可定义倾斜键界定返回，超过该值则会把该key部分单独拉起一个mr任务进行map端join逻辑加速join
实际是两个MR过程，该参数不适用于out外连接，原因类同mapjoin小表作为主表失效
hive.skewjoin.mapjoin.map.task参数可以控制第二个job的mapper数量，默认是1000

groupby倾斜

set hive.map.aggr = true 开启map端聚合
set hive.groupby.mapaggr.checkinterval = 100000map端聚合数据条数，如果map数据量超过该该记录数，会按记录数拆分新增task处理
set hive.groupby.skewindata=true

开启该参数，HIVE会生成的查询计划会有两个 MapReduce Job。

第一个 MapReduce Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个Reduce 做部分聚合操作并输出结果。相同的 GroupBy Key 有可能被分布到不同的 Reduce 中，负载均衡；
第二个 MapReduce Job 再根据预处理的数据结果，按照 GroupBy Key 分布到 Reduce 中(这过程可以保证相同的GroupBy Key被分布到同一个Reduce中)，最后完成最终的聚合操作
类似于写一段sql，第一个job对key加入随机数

select key_field
	,sum(val) as val   -- 全局聚合
from 
(	-- 局部聚合
	select key_filed,cast(rand() * 100 as int) as rnd
		,sum(val) as val
	from tablename 
	group by key_filed,cast(rand() * 100 as int)  -- 加入随机数
) t 

其他

• hive.map.groupby.sorted：在Hive 2.0以前的默认值是False,2.0及2.0以后的版本默认值为true。对于分桶或者排序表，如果分组聚合的键（列）和分桶或者排序的列一致，将会使用BucketizedHiveInputFormat。

• hive.vectorized.execution.mapjoin.minmax.enabled：默认值为False，是否使用vector map join哈希表，用于整型连接的最大值和最小值过滤。

---

连接优化器

CBO(成本优化器:Cost Based Optimizer)可以基于收集到的统计信息，估算出每个表连接的组合，生成一个成本代价最低的表连接方案，预先两两结合生成中间结果集，再针对这些中间结果集进行操作。
简化表的连接，在多表连接的情况下，CBO在解析SQL子句时，会识别并抽取相同的连接谓词，并根据情况适当构造一个隐式的连接谓词作为替换，以避免高昂的表连接操作。

select tb1.id
	,tb2.field2
	,tb3.field3
	,tb4.field4
from table1 tb1 
join table2 tb2
on tb1.id = tb2.id
join table3 tb3
on tb2.id = tb3.id
join table4 tb4
on tb3.id = tb4.id

正常来说，tb3的join要等待tb1和tb2的join之后发生，这样无法充分利用集群计算。优化后的执行过程可能是：tb1 join tb2和tb3 join tb4同时进行。
step1:
tb1 join tb2 -> tb_tmp1
tb3 join tb4 -> tb_tmp2
step2:
tb_tmp1 join tb_tmp2

---

其他相关

查看SQL执行计划

• 查看执行计划的基本信息，即explain；
• 查看执行计划的扩展信息，即explain extended；
• 查看SQL数据输入依赖的信息，即explain dependency；
• 查看SQL操作相关权限的信息，即explain authorization；
• 查看SQL的向量化描述信息，即explain vectorization。

show：查看信息

• show databases：获取hive的库名列表。
• show tables：获取当前库下的表名列表。
• show partitions表名：获取表的分区列表。
• show functions：获取当前可用函数列表。
• show views：获取当前库下的视图列表，Hive 2.2版本中新增。
• show tblproperties：获取某个表的表配置属性。
• show create table表名：获取某个表的完整建表语句。
• show column from表名：获取表的所有字段名。
• show locks：获取某个库、表或者某个表的分区的锁信息。
• show transactions：获取库内的事务信息。
• show compactions：获取已经被压缩或正在被压缩的表/分区的压缩信息。
• show conf ’值名’：查看配置项当前的值，如show conf ‘hive.execution.engine’。
• show table extended like表名：获取表的大部分信息，包括数据存储位置、输入/输出格式、列基本信息和表的配置信息等

• desc：描述库表信息

• desc database库名，用于获取库的描述，包括库的存储位置及权限信息。
• desc [extended|formatted]表/分区，用于获取表或者某个表的分区的详细信息，比show table extend更加具体。

load：数据加载

将本地/集群上的某个文件直接插入表/分区

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 ...)]

export命令

将表或分区的数据连同元数据导出到指定的输出位置。

EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])]
TO 'export_target_path' [ FOR replication('eventid') ]

有关窗口函数的执行计划

select field1
	,field2
	,row_number() over(partition by field2 order by field3 desc) as rnk1
	,row_number() over(partition by field2 order by field4 desc) as rnk2
	,row_number() over(partition by field2 order by field5 desc) as rnk5
from dw_cus_safe.dw_cus_user_login_info

该窗口函数没有其他特殊情况，比如小文件合并带来的mr增加，每个row_number都是一个mr，三个stage顺序依赖；窗口函数执行过程中的key即是partition by的字段，这里是field2；

---

推荐：《Hive性能调优实战》