hive sink hive sink table

小表Join大表

将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率。

说明：新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。

示例：

insert overwrite table jointable
select b.*
from  smalltable s left join bigtable b 
on b.id =s.id

大表Join小表

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。

示例：舍弃key为空的字段

insert overwrite table jointable
select n.*
from (select * from nullidtable where id is not null) n left join bigtable b
on b.id =n.id;

有时虽然空key对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。
示例：

insert overwrite table jointable
select n.*
from nullidtable n left join bigtable b
on case when n.id is null then  concat('id',rand()) else n.id end =b.id;

附：case语句语法：

CASE
WHEN a THEN b
[WHEN c THEN d]
[ELSE e]
END

a为真返回b；c为真返回d；否则返回e

MapJoin

如果不指定MapJoin，那么Hive解析器会将Join操作转换成Common Join，从而在Reduce阶段完成join。

这样很容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。

开启MapJoin的参数设置：

• 设置自动选择Mapjoin
  set hive.auto.convert.join = true; 默认为true
• 大表小表的阈值设置（默认25M以下是小表）：
  set hive.mapjoin.smalltable.filesize=25000000;

Group By

默认情况下，Map阶段同一Key的数据分发给一个reduce，当一个key数据过大时会发生数据倾斜。
其实，并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

开启Map端聚合的参数设置：

• 是否在Map端进行聚合，默认为True
  hive.map.aggr = true
• 在Map端进行聚合操作的条目数目
  hive.groupby.mapaggr.checkinterval = 100000
• 有数据倾斜的时候进行负载均衡（默认是false）
  hive.groupby.skewindata = true

当选项设定为 true，生成的查询计划会有两个MR Job：

• 在第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；
• 第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作。

Count(Distinct)去重统计

当数据量很大的时候，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这个Reduce需要处理的数据量太大，就会导致整个Job很难完成。
一般COUNT DISTINCT使用先GROUP BY再COUNT的方式。

示例：

行列过滤

列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。
行处理：在分区查找时，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤，建议优先使用子查询的方式。

示例：

select b.id
from bigtable b join (select id from smalltable where id <10) t
on b.id = t.id

动态分配调整

动态分区(Dynamic Partition)：对分区表Insert数据时候，数据库会自动根据分区字段的值，将数据插入到相应的分区中。

要在Hive中使用自动分区，需要做以下配置：

• 开启动态分区功能（默认true，开启）
  hive.exec.dynamic.partition=true
• 设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
  hive.exec.dynamic.partition.mode=nonstrict
  strict：表示必须指定至少一个分区为静态分区；nonstrict：表示允许所有的分区字段都可以使用动态分区。
• 指定在所有执行MR的节点上，最大一共可以创建多少个动态分区
  hive.exec.max.dynamic.partitions=1000
• 在每个执行MR的节点上，最大可以创建多少个动态分区。
  hive.exec.max.dynamic.partitions.pernode=100
• 整个MR Job中，最大可以创建多少个HDFS文件。
  hive.exec.max.created.files=100000
• 当有空分区生成时，是否抛出异常。一般不需要设置。
  hive.error.on.empty.partition=false

示例：

第一步：创建分区表

第二步：加载数据到分区表中

第三步：设置动态分区，开启非严格模式

set hive.exec.dynamic.partition.mode = nonstrict;

第四步：查看目标分区表的分区情况

show partitions tb_emp_partition;