一、Hive优化

1-1 分桶采样

1. 当表的数据量比较庞大的时候, 在编写SQL语句后, 需要首先测试 SQL是否可以正常的执行, 需要在表中执

行查询操作, 由于表数据量比较庞大, 在测试一条SQL的时候整个运行的时间比较久, 为了提升测试效率, 可以整个表

抽样出一部分的数据, 进行测试

1. 校验数据的可行性(质量校验)

2. 进行统计分析的时候, 并不需要统计出具体的指标, 可能统计的都是一些相对性指标, 比如说一些比率(合

格率)问题, 此时可以通过采样处理

tablesample ( bucket x out of y [on column|rand()])

分桶采样

use itcast;
create table tb_test(
    id int,
    name string
)
row format delimited fields terminated by ',';
insert into tb_test values
                        (1,'张三'),
                        (2,'李四'),
                        (3,'沙和尚'),
                        (4,'唐僧'),
                        (5,'孙悟空'),
                        (6,'猪八戒');
​
​
​
-- 分桶表
create table tb_test_buckets(
    id int,
    name string
)clustered by(id) into 6 buckets
row format delimited fields terminated by ',';
insert into tb_test_buckets values
                        (1,'张三'),
                        (2,'李四'),
                        (3,'沙和尚'),
                        (4,'唐僧'),
                        (5,'孙悟空'),
                        (6,'猪八戒');
                   
​

分桶表

取值数量 = 分桶数/y值

x值决定从哪个分桶文件开始取值

---

未分桶表

1-按字段分桶 hash(字段)%y值=余数 相同余数放在一起

2-按照x值找余数对应的值 x=1 就找第一个余数 0 x=2 找第二个余数 1 x=3 找第三个余数 2

1-2 Join优化

在表关联时，因为关联字段的原因造成某个reduce处理的数据量过大，产生数据倾斜，导致计算时间过长

join在

方案一 MapJoin

Map Join: 每一个mapTask在读取数据的时候, 每读取一条数据, 就会和内存中班级表数据进行匹配, 如果能匹配的上

, 将匹配上数据合并在一起, 输出即可

好处:

将原有reduce join 问题全部都可以解决

弊端:

1- 比较消耗内存

2- 要求整个 Join 中, 必须的都有一个小表, 否则无法放入到内存中

仅适用于: 小表 join 大表 | 大表 join 小表

---

设置

set hive.auto.convert.join=true; -- 开启 map join的支持 默认值为True

set hive.auto.convert.join.noconditionaltask.size=20971520; -- 设置 小表数据量的最大阈值: 默认值

为 20971520(20M)

方案二 Bucket Map Join

1-开启Bucket Map Join支持： set hive.optimize.bucketmapjoin =true;

2-Join两个表必须是分桶表

3-一个表的分桶数量是另一个表的分桶数量的整倍数

4-分桶列必须是join的ON条件的列 join tb1.id = tb2.id

5-必须建立在Map Join场景中

create table tb_cls(
    cid int,
    name string
)clustered by(cid) into 2 buckets
row format delimited fields terminated by ',';
insert overwrite table tb_cls values
                       (1,'大数据1班'),
                       (2,'大数据2班'),
                       (3,'大数据3班'),
                       (4,'大数据4班');
                       
create table tb_student(
    uid string,
    name string,
    cid int
)clustered by(cid) into 4 buckets
row format delimited fields terminated by ',';
​
insert overwrite table tb_student values
                       ('01','张三',1),
                       ('02','李四',1),
                       ('03','王五',2),
                       ('04','赵六',1),
                       ('05','田七',3),
                       ('06','周八',1),
                       ('07','钱九',4);

方案三 SMB Join

sort merge bucket map join

1-两个表必须都是分桶表

2-开启SMB Join支持： set hive.auto.convert.sortmerge.join=true; set hive.optimize.bucketmapjoin.sortedmerge =true;

set hive.auto.convert.sortmerge.join.noconditionaltask=true;

3-两个表的分桶的数量是一致的

4-分桶列必须是join的on条件的列，同时必须保证按照分桶列进行排序操作 当向表中写入数据是，按照分桶字段进行排序写入

--开启强制排序 ​ set hive.enforce.sorting=true;

--在建分桶表使用：必须使用sorted by() 5- 应用在Bucket Map Join场景中

--开启bucket map join

set hive.optimize.bucketmapjoin =true; 6-必须开启HIVE自动尝试使用SMB方案：

set hive.optimize.bucketmapjoin.sortedmerge =true;

当相关服务都开启后，有hive决定使用那种方式进行关联

1-判断表是不是 分桶表，在判断表的大小 如果不是分桶，但是属于小表 触发mapjoin

2-判断表是分桶，但是分桶数量不一致 触发Bucket Map Join

3-断表是分桶，但是分桶数量一致，关联字段是顺序的 触发SMB Join

如果条件不符和上面要求，就执行一般join流程，可能出现数据倾斜

1-2 索引优化

用于提升查询的效率

原始索引

hive的原始索引可以针对某个列, 或者某几列构建索引信息, 构建后提升查询执行列的查询效率

所以在HIVE3.x版本后, 已经直接将这种索引废弃掉了, 无法使用

Row Group Index索引

行组索引

条件:

1) 要求表的存储类型为ORC存储格式 2) 在创建表的时候, 必须开启 row group index 索引支持

'orc.create.index'='true'

3) 在插入数据的时候, 必须保证按照where过滤的字段进行数据的顺序插入

适用于: 数值类型的, 并且对数值类型进行 不等的过滤操作

-- 建表时定义
create table tb (   
    字段 字段类型
) 
stored AS ORC
TBLPROPERTIES (
'orc.compress'='SNAPPY',
-- 开启行组索引
'orc.create.index'='true'
)
​
-- 插入数据 需要按照where过滤的字段顺序写入
insert table tb select * from tb_ods order by id
​
-- 查询是设置
set hive.optimize.index.filter=true;
SELECT COUNT(1) FROM tb WHERE id >= 1382 AND id <= 1399;

Bloom Fliter Index索引

布隆索引

条件:

1) 要求表的存储类型为 ORC存储方案 2) 在建表的时候, 必须设置为那些列构建布隆索引 3) 仅能适合于等值过滤查询操作

-- 建表时定义
create table tb (   
    字段 字段类型
) 
stored AS ORC
TBLPROPERTIES (
    'orc.compress'='SNAPPY',
    -- 开启行组索引
    'orc.create.index'='true',
    -- 开启BloomFilter索引
    'orc.bloom.filter.columns'='city,字段3...'
)
​
-- 查询是设置
set hive.optimize.index.filter=true;
SELECT COUNT(1) FROM tb WHERE city = '北京'

1- 对于行组索引, 我们建议只要数据存储格式为ORC, 就将这种索引全部打开, 至于导入数据的时候, 如果能保证有序

, 那最好, 如果保证不了, 也无所谓, 大不了这个索引的效率不是特别好

2- 对于布隆过滤索引: 建议将后续会大量的用于等值连接的操作字段, 建立成布隆索引, 比如说: JOIN的字段 经常

在where后面出现的等值连接字段

1-3 数据倾斜优化

join造成数据倾斜

方案一： 参考join优化

方案二：

将那些产生倾斜的key和对应v2的数据, 从当前这个MR中移出去, 单独找一个MR来处理即可, 处理后, 和之前的MR进行汇总结果即可

• 运行期间

运行期处理方案:

思路: 在执行MR的时候, 会动态统计每一个 k2的值出现重复的次数, 当这个重复的次数达到一定的阈值后, 认为

当前这个k2的数据存在数据倾斜, 自动将其剔除, 交由给一个单独的MR来处理即可,两个MR处理完成后, 将结果基于

union all 合并在一起即可

实操:

-- 开启运行期处理倾斜参数
set hive.optimize.skewjoin=true; 
-- 阈值, 此参数在实际生产环境中, 需要调整在一个合理的值(否则极易导致大量的key都是倾斜的)
set hive.skewjoin.key=100000; 10万

判断依据: 查看 join的 字段 对应重复的数量有多少个, 然后选择一个合理值

比如判断:

id为 1 大概有 100w id为 2 88w id 为 3 大概有 500w 设置阈值为 大于500w次数据

或者: 总数量大量1000w, 然后共有 1000个班级, 平均下来每个班级数量大概在 1w条, 设置阈值: 大于3w条 ~5w条范围 (超过3~5倍才认为倾斜)

110万 50个 2.2万 6.6万-11万

select * from tb1 join tb2 on tb1.store_no= tb2.store_no

适用于: 并不清楚那个key容易产生倾斜, 此时交由系统来动态检测

• 编译期间

思路: 在创建这个表的时候, 我们就可以预知到后续插入到这个表中数据, 那些key的值会产生倾斜, 在建表的时

候, 将其提前配置设置好即可, 在后续运行的时候, 程序会自动将设置的key的数据单独找一个MR来进行处理即可, 处

理完成后, 再和原有结果进行union all 合并操作

实操:

set hive.optimize.skewjoin.compiletime=true; -- 开启编译期处理倾斜参数
​
CREATE TABLE list_bucket_single(
 id int, 
 value STRING
)
-- 倾斜的字段和需要拆分的key值
SKEWED BY (id) ON (1,5,6)
-- 为倾斜值创建子目录单独存放
STORED AS DIRECTORIES;

适用于: 提前知道那些key存在倾斜

group by造成数据倾斜

相同分组的数据会放入同一个reduce处理

方案一

基于MR的 combiner(规约, 提前聚合) 减少数据达到reduce数量, 从而减轻倾斜问题

只需要在HIVE中开启combiner提前聚合配置参数即可:

set hive.map.aggr=true;

方案二

负载均衡的解决方案(需要运行两个MR来处理) (大combiner方案）

只需要开启负载均衡的HIVE参数配置即可:

set hive.groupby.skewindata=true;

如果分组的字段对应的数据较多，建议使用负载的方案

group by city