HiveSQl 常见的优化手段07

最新推荐文章于 2024-11-26 11:59:09 发布

疲惫的花卷

最新推荐文章于 2024-11-26 11:59:09 发布

阅读量1k

点赞数 18

文章标签： hive 大数据

本文链接：https://blog.csdn.net/2202_75300139/article/details/135915853

版权

4.2 Row Group Index索引

4.3 Bloom Fliter Index索引

5 如何解决数据倾斜问题

5.1 Join数据倾斜

5.2 group by 数据倾斜

1.分桶表基本介绍

分桶表:

分文件的, 在创建表的时候, 指定分桶字段, 并设置分多少个桶, 在添加数据的时候, hive会根据设置分桶字段, 将数据划分到N个桶(文件)中, 默认情况采用HASH分桶方案 , 分多少个桶, 取决于建表的时候, 设置分桶数量, 分了多少个桶最终翻译的MR也就会运行多少个reduce程序(HIVE的分桶本质上就是MR的分区操作)

如何构建一个分桶表呢?

create table 表名(
    字段 类型 ,
    ......
) [comment '表的描述信息']
clustered by(分桶字段) sorted by (字段 [asc|desc]) into N buckets]
row format...   --- 定义分桶表核心语句
;

如何向桶表添加数据

如果现有一个文本文件数据, 需要加载到分桶表,如何解决呢?
第一步: 基于桶表创建一张临时表, 此表和桶表保持相同字段, 唯一区别, 当前这个表不是一个桶表
第二步: 将数据先加载到这个临时表中
第三步: 基于临时表, 使用 insert into|overwrite + select 将数据添加到桶表

如果使用 apache 版本的HIVE, 默认情况下, 是可以通过 load data 方式来加载数据. 只不过没有分桶的效果

load data local inpath '' into table 表名; -- 不会触发MR hdfs dfs -put

但是对于 CDH版本中, 是不允许通过 load data 方式来加载的:
在CDH中默认开启了一个参数, 禁止采用load data方式向桶表添加数据:

set hive.strict.checks.bucketing = true;

CDH（Cloudera Distribution Hadoop）是一个基于 Hadoop 生态系统的分布式计算平台，由 Cloudera 公司开发和维护。它提供了一整套集成的 Hadoop 组件和工具，包括 Hadoop 核心、HDFS、YARN、MapReduce 以及其他相关的大数据处理和分析工具端口号7180。

桶表的作用

1) 进行数据采样工作
1.1) 当表的数据量比较庞大的时候, 在编写SQL语句后, 需要首先测试 SQL是否可以正常的执行, 需要在表中执行查询操作, 由于表数据量比较庞大, 在测试一条SQL的时候整个运行的时间比较久, 为了提升测试效率, 可以整个表抽样出一部分的数据, 进行测试
1.2) 校验数据的可行性(质量校验)
1.3) 进行统计分析的时候, 并不需要统计出具体的指标, 可能统计的都是一些相对性指标, 比如说一些比率(合格率)问题, 此时可以通过采样处理

2) 提升查询的效率(更主要是提升JOIN的效率)
可以减少JOIN次数, 从而提升效率

注意:
在生产环境中, 何时使用桶表, 主要看是否需要应用上述作用

2. 数据采样

采样函数:
tablesample(bucket x out of y [on column])

使用位置: 紧紧跟在表名的后面, 如果表名有别名, 必须放置别名的前面

说明:
x: 从第几个桶开始进行采样
y: 抽样比例
column: 分桶的字段, 可以省略

注意:
x 不能大于 y
y 必须是表的分桶数量的倍数或者因子


案例:
1) 假设 A表有10个桶, 请分析, 下面的采样函数, 会将那些桶抽取出来呢?
tablesample(bucket 2 out of 5 on xxx)

会抽取出几个桶数据呢? 总桶数 / 抽样比例 = 分桶数量 2个桶
抽取那几个桶呢? (x + y)
2, 7

2) 假设 A 表有20个桶, 请分析, 下面的抽样函数, 会将那些桶抽取出来呢?
tablesample(bucket 4 out of 4 on xxx)

会抽取出几个桶数据呢? 总桶数 / 抽样比例 = 分桶数量 5个桶
抽取那几个桶呢?
4 , 8,12,16,20

tablesample(bucket 8 out of 40 on xxx)

会抽取出几个桶数据呢? 总桶数 / 抽样比例 = 分桶数量二分之一个桶
抽取那几个桶呢?
8号桶二分之一

大多数情况下, 都是因子, 取某几个桶的操作

3.Join优化操作

在执行Join的SQL的时候, SQL会被翻译为MR, 思考, 翻译后MR默认是如何进行JOIN操作的呢?

弊端：

1- 可能会存在数据倾斜的问题 (某几个reduce接收数据量远远大于其他的reduce接收数据量)
2- 所有的数据处理的操作, 全部都压在reduce中进行处理, 而reduce数量相比Map来说少的多,导致整个reduce压力比较大

如何提升Join的效率呢?

思路: 能否不让reduce做这个聚合处理的事情, 将这项工作尝试交给mapTask

3.1 Map Join

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

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

弊端:
1- 比较消耗内存
2- 要求整个 Join 中, 必须的都有一个小表, 否则无法放入到内存中

仅适用于: 小表 join 大表 | 大表 join 小表
在老版本(1.x以下)中, 需要将小表放置在前面, 大表放置在后面, 在新版本中, 无所谓
建议, 如果明确知道那些表示小表, 可以优先将这些表, 放置在最前面

如何使用呢?
set hive.auto.convert.join=true; -- 开启 map join的支持默认值为True
set hive.auto.convert.join.noconditionaltask.size=20971520; -- 设置小表数据量的最大阈值: 默认值为 20971520(20M)

如果不满足条件, HIVE会自动使用 reduce join 操作

3.2 Bucket Map Join

适用场景: 中型表和大表 join:

方案一: 如果中型表能对数据进行提前过滤, 尽量提前过滤, 过滤后, 有可能满足了Map Join 条件 (并不一定可用)
方案二: Bucket Map Join

使用条件:
1- Join两个表必须是分桶表
2- 开启 Bucket Map Join 支持: set hive.optimize.bucketmapjoin = true;
3- 一个表的分桶数量是另一个表的分桶数量的整倍数
4- 分桶列必须是 join的ON条件的列
5- 必须建立在Map Join场景中(中型表是小表的3倍, 此时分至少3个桶)

3.3 SMB Join

适用场景: 大表和大表 join
解决方案: 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;

最终汇总出来整体配置:

    set hive.auto.convert.join=true;
    set hive.auto.convert.join.noconditionaltask.size=20971520; 
    set hive.optimize.bucketmapjoin = true;
    set hive.auto.convert.sortmerge.join=true;
    set hive.optimize.bucketmapjoin.sortedmerge = true;
    set hive.auto.convert.sortmerge.join.noconditionaltask=true;
    set hive.enforce.sorting=true;
    set hive.optimize.bucketmapjoin.sortedmerge = true;
  
建表:
  create table test_smb_2(mid string,age_id string) CLUSTERED BY(mid) SORTED BY(mid) INTO 500 BUCKETS;

 
至于分多少个桶: 取决于表的数据大小 和 小表阈值 之间相差了多少倍

使用情况：

事实表 + 低基数的维度表进行关联: Map Join 优化
事实表 + 高基数的维度表进行关联: Bucket Map Join 优化
事实表 + 事实表进行关联: SMB Join 优化(相对使用最少, 因为条件过于苛刻, 而且对资源消耗过大, 如果资源不够, 运行效率低于原始Join方案)

思考: 讲上面的目的是什么?
尝试在数仓中, 找到可以匹配的地方, 在面试的时候, 可以在这个位置中讲解对应的优化

建议: Join优化集中在DWD和DWM层进行优化讲解

4 HIVE的索引

索引有什么用呢? 用于提升查询的效率

为什么说, 索引可以提升查询的效率呢? 思想性

4.1 HIVE原始索引(废弃)

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

存在弊端:
hive原始索引不会自动更新,每次表中数据发生变化后, 都是需要手动重建索引操作, 比较耗费时间和资源, 整体提升性能一般

所以在HIVE3.x版本后, 已经直接将这种索引废弃掉了, 无法使用, 而且官方描述在hive1.x 和 hive2.x版本中, 也不建议优先使用原始索引

4.2 Row Group Index索引

row group index: 行组索引

条件:
1) 要求表的存储类型为ORC存储格式
2) 在创建表的时候, 必须开启 row group index 索引支持
'orc.create.index'='true'
3) 在插入数据的时候, 必须保证需求进行索引列, 按序插入数据

适用于: 数值类型的, 并且对数值类型进行 > < = 操作

思路:
插入数据到ORC表后, 会自动进行划分为多个script片段, 每个片段内部, 会保存着每个字段的最小, 最大值, 这样, 当执行查询 > < = 的条件筛选操作的时候, 根据最小最大值锁定相关的script片段, 从而减少数据扫描量, 提升效率

操作:
CREATE TABLE lxw1234_orc2 (字段列表 ....) stored AS ORC
TBLPROPERTIES (
'orc.compress'='SNAPPY',
-- 开启行组索引
'orc.create.index'='true'
)
插入数据的时候, 需要保证数据有序的
insert overwrite table lxw1234_orc2
SELECT id, pcid FROM lxw1234_text
-- 插入的数据保持排序(可以使用全局排序, 也可以使用局部排序, 只需要保证一定有序即可, 建议使用局部排序插入数据效率高一些, 因为全局排序只有一个reduce)
DISTRIBUTE BY id sort BY id;

使用:
set hive.optimize.index.filter=true;
SELECT COUNT(1) FROM lxw1234_orc1 WHERE id >= 1382 AND id <= 1399;

create table  表名() 
tblproperties('orc.compress'='SNAPPY',
--     开启行组索引
'orc.create.index'='true');

4.3 Bloom Fliter Index索引

bloom filter index (布隆过滤索引): 布隆过滤器

条件:
1) 要求表的存储类型为 ORC存储方案
2) 在建表的饿时候, 必须设置为那些列构建布隆索引
3) 仅能适合于等值过滤查询操作
思路:
在开启布隆过滤索引后, 可以针对某个列, 或者某几列来建立索引, 构建索引后, 会将这一列的数据的值存储在对应script片段的索引信息中, 这样当进行等值查询的时候, 首先会到每一个script片段的索引中, 判断是否有这个值, 如果没有, 直接跳过script, 从而减少数据扫描量, 提升效率

CREATE TABLE lxw1234_orc2 (字段列表....)
    stored AS ORC 
    TBLPROPERTIES ( 
      'orc.compress'='SNAPPY', 
      -- 开启 行组索引 (可选的, 支持全部都打开, 也可以仅开启一个)
      'orc.create.index'='true', 
      -- pcid字段开启BloomFilter索引 
      'orc.bloom.filter.columns'='pcid,字段2,字段3...'
    );

插入数据: 没有要求, 当然如果开启行组索引, 可以将需要使用行组索引的字段, 进行有序插入即可

使用:
SET hive.optimize.index.filter=true;
SELECT COUNT(1) FROM lxw1234_orc1 WHERE id >= 0 AND id <= 1000 AND pcid IN ('0005E26F0DCCDB56F9041C','A');

在什么时候可以使用

1- 对于行组索引, 我们建议只要数据存储格式为ORC, 建议将这种索引全部打开, 至于导入数据的时候, 如果能保证有序, 那最好, 如果保证不了, 也无所谓, 大不了这个索引的效率不是特别好

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

5 如何解决数据倾斜问题

5.1 Join数据倾斜

在前序讲解reduce 端 JOIN的时候, 描述过reduce 端Join的问题, 其中就包含reduce端Join存在数据倾斜的问题

解决方案一:

可以通过 Map Join Bucket Map Join 以及 SMB Join 解决

注意:
通过 Map Join,Bucket Map Join,SMB Join 来解决数据倾斜, 但是这种操作是存在使用条件的, 如果无法满足这些条件, 无法使用这种处理方案

解决方案二:

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

关键问题: 如何找到那些存在倾斜的key呢? 特点: 这个key数据有很多

运行期处理方案:
思路: 在执行MR的时候, 会动态统计每一个 k2的值出现重复的次数, 当这个重复的次数达到一定的阈值后, 认为当前这个k2的数据存在数据倾斜, 自动将其剔除, 交由给一个单独的MR来处理即可,两个MR处理完成后, 将结果基于union all 合并在一起即可

实操:
set hive.optimize.skewjoin=true; -- 开启运行期处理倾斜参数
set hive.skewjoin.key=100000; -- 阈值, 此参数在实际生产环境中, 需要调整在一个合理的值(否则极易导致大量的key都是倾斜的)
判断依据: 查看 join的字段对应重复的数量有多少个, 然后选择一个合理值
比如判断: id为 1 大概有 100w id为 2 88w id 为 3 大概有 500w 设置阈值为大于500w次数据
或者: 总数量大量1000w, 然后共有 1000个班级, 平均下来每个班级数量大概在 1w条, 设置阈值: 大于 3w条 ~5w条范围 (超过3~5倍才认为倾斜)


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

编译期处理方案:
思路: 在创建这个表的时候, 我们就可以预知到后续插入到这个表中数据, 那些key的值会产生倾斜, 在建表的时候, 将其提前配置设置好即可, 在后续运行的时候, 程序会自动将设置的key的数据单独找一个MR来进行处理即可, 处理完成后, 再和原有结果进行union all 合并操作

实操:
set hive.optimize.skewjoin.compiletime=true; -- 开启编译期处理倾斜参数

CREATE TABLE list_bucket_single (key STRING, value STRING)
-- 倾斜的字段和需要拆分的key值
SKEWED BY (key) ON (1,5,6)
-- 为倾斜值创建子目录单独存放
[STORED AS DIRECTORIES];

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

在实际生产环境中, 应该使用那种方式呢? 两种方式都会使用的
一般来说, 会将两个都开启, 编译期的明确在编译期将其设置好, 编译期不清楚, 通过运行期动态捕获即可

union all 优化方案

说明: 不管是运行期还是编译期的join倾斜解决, 最终都会运行多个MR, 将多个MR结果通过union all 进行汇总, union all也是需要单独一个MR来处理

解决方案:
让每一个MR在运行完成后, 直接将结果输出到目的地即可, 默认是各个MR将结果输出临时目录, 通过 union all 合并到最终目的地

开启此参数即可:
set hive.optimize.union.remove=true;

5.2 group by 数据倾斜

为什么在group by 的时候, 可能会出现倾斜的问题呢

假设目前有这么一个表:

sid sname cid
s01 张三 c01
s02 李四 c02
s03 王五 c01
s04 赵六 c03
s05 田七 c02
s06 周八 c01
s07 李九 c01
s08 老王 c04

需求: 请计算每个班级有多少个人
select cid,count(1) as total from stu group by cid;

翻译后MR是如何处理SQL呢?

MAP 阶段: 假设Map阶段跑了二个MapTask

mapTask1:
k2 v2
c01 {s01 张三 c01}
c02 {s02 李四 c02}
c01 {s03 王五 c01}
c03 {s04 赵六 c03}
mapTask2:
k2 v2
c02 {s05 田七 c02}
c01 {s06 周八 c01}
c01 {s07 李九 c01}
c04 {s08 老王 c04}

reduce阶段: 假设reduceTask有二个

reduceTask1: 接收 c01 和 c02的数据
接收数据
k2 v2
c01 {s01 张三 c01}
c02 {s02 李四 c02}
c01 {s03 王五 c01}
c02 {s05 田七 c02}
c01 {s06 周八 c01}
c01 {s07 李九 c01}

分组后:
c01 [{s01 张三 c01},{s03 王五 c01},{s06 周八 c01},{s07 李九 c01}]
c02 [{s02 李四 c02},{s05 田七 c02}]

结果数据:
c01 4
c02 2

reduceTask2: 接收 c03 和 c04的数据
接收数据
k2 v2
c03 {s04 赵六 c03}
c04 {s08 老王 c04}

分组后:
c03 [{s04 赵六 c03}]
c04 [{s08 老王 c04}]

结果数据:
c03 1
c04 1

在以上整个计算流程中, 发现其中一个reduce接收到的数据量比另一个reduce接收的数据量要多的多, 认为出现了数据倾斜的问题, 所以group by 也有可能产生数据倾斜

如何解决group by的数据倾斜呢?

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

假设目前有这么一个表:

sid sname cid
s01 张三 c01
s02 李四 c02
s03 王五 c01
s04 赵六 c03
s05 田七 c02
s06 周八 c01
s07 李九 c01
s08 老王 c04

需求: 请计算每个班级有多少个人
select cid,count(1) as total from stu group by cid;

翻译后MR是如何处理SQL呢?

MAP 阶段: 假设Map阶段跑了二个MapTask
mapTask1:
k2 v2
c01 {s01 张三 c01}
c02 {s02 李四 c02}
c01 {s03 王五 c01}
c03 {s04 赵六 c03}
规约(提前聚合)操作: 处理逻辑与reduce处理逻辑一直
分组:
c01 [{s01 张三 c01},{s03 王五 c01}]
c02 [{s02 李四 c02}]
c03 [{s04 赵六 c03}]
聚合得出结果:
c01 2
c02 1
c03 1
mapTask2:
k2 v2
c02 {s05 田七 c02}
c01 {s06 周八 c01}
c01 {s07 李九 c01}
c04 {s08 老王 c04}
规约(提前聚合)操作: 处理逻辑与reduce处理逻辑一直
分组:
c01 [{s06 周八 c01},{s07 李九 c01}]
c02 [{s05 田七 c02}]
c04 [{s08 老王 c04}]
聚合得出结果:
c01 2
c02 1
c04 1

reduce阶段: 假设reduceTask有二个
reduceTask1: 接收 c01 和 c02的数据
接收数据
k2 v2
c01 2
c02 1
c01 2
c02 1
分组后:
c01 [2,2]
c02 [1,1]
结果数据:
c01 4
c02 2
reduceTask2: 接收 c03 和 c04的数据
接收数据
k2 v2
c03 1
c04 1
分组后:
c03 [1]
c04 [1]
结果数据:
c03 1
c04 1

通过规约来解决数据倾斜, 处理完成后, 发现两个reduce中从原来相差 3倍, 变更为相差 2倍, 减轻了数据倾斜问题

如何配置呢?
只需要在HIVE中开启combiner提前聚合配置参数即可:
set hive.map.aggr=true;

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

假设目前有这么一个表:

sid sname cid
s01 张三 c01
s02 李四 c02
s03 王五 c01
s04 赵六 c03
s05 田七 c02
s06 周八 c01
s07 李九 c01
s08 老王 c04

需求: 请计算每个班级有多少个人
select cid,count(1) as total from stu group by cid;

翻译后MR是如何处理SQL呢?

第一个MR的操作: 对数据进行打散
Map 阶段: 假设运行了两个MapTask
mapTask1:
k2 v2
c01 {s01 张三 c01}
c02 {s02 李四 c02}
c01 {s03 王五 c01}
c03 {s04 赵六 c03}
mapTask2:
k2 v2
c02 {s05 田七 c02}
c01 {s06 周八 c01}
c01 {s07 李九 c01}
c04 {s08 老王 c04}

mapTask执行完成后, 在进行分发数据到达reduce, 默认情况下将相同k2的数据发往同一个reduce, 目前采用方案为随机分发, 保证每一个reduce拿到相等数量的数据信息(负载过程, 让每一个reduce接收到相同数量的数据)

reduce阶段: 假设有两个reduceTask

reduceTask1:
接收到数据:
c01 {s01 张三 c01}
c01 {s03 王五 c01}
c01 {s06 周八 c01}
c01 {s07 李九 c01}
分组操作:
c01 [{s01 张三 c01},{s03 王五 c01},{s06 周八 c01},{s07 李九 c01}]
输出结果:
c01 4
reduceTask2:
接收到数据:
c03 {s04 赵六 c03}
c02 {s05 田七 c02}
c02 {s02 李四 c02}
c04 {s08 老王 c04}

分组操作:
c03 [{s04 赵六 c03}]
c02 [{s02 李四 c02},{s05 田七 c02}]
c04 [{s08 老王 c04}]

输出结果:
c02 2
c03 1
c04 1

第一个MR执行完成了, 每个reduce都接收到四条数据, 自然也就不存在数据倾斜的问题了

第二个MR进行处理: 严格按照相同k2发往同一个reduce

Map 阶段: 假设有二个mapTask
mapTask1:
k2 v2
c01 4
c02 2
mapTask2:
k2 v2
c03 1
c04 1
reduce阶段: 假设有两个reduce
reduceTask1: 接收 c01 和 c02 数据
接收数据:
k2 v2
c01 4
c02 2
结果:
c01 4
c02 2
reduceTask2: 接收 c03 和c04
接收数据:
k2 v2
c03 1
c04 1
结果:
c03 1
c04 1

通过负载均衡方式来解决数据倾斜, 同样也可以减轻数据倾斜的压力

细细发现, 方案一和方案二, 是有类似之处的, 方案一, 让每一个mapTask内部进行提前聚合, 然后到达reduce进行汇总合并得出结构, 方案二: 让第一个MR进行打散并对数据进行聚合计算得出局部结果, 然后让第二个MR进行最终聚合计算操作, 得出最终结果

说明: 方案二, 比方案一, 更能彻底解决数据倾斜问题, 因为其处理数据范围更大, 整个整个数据集来处理, 而方案一, 只是每个MapTask处理, 仅仅局部处理

如何使用方案二:
只需要开启负载均衡的HIVE参数配置即可:
set hive.groupby.skewindata=true;

这两种方式: 建议在生产中, 优先使用第一种, 如果第一种无法解决, 尝试使用第二种解决

注意事项: 使用第二种负载均衡的解决group by 的数据倾斜, 一定要注意, SQL语句中不能出现多次 distinct操作, 否则 HIVE会直接报错的
错误信息:
Error in semantic analysis: DISTINCT on different columns not supported with skew in data.
比如说:
SELECT ip, count(DISTINCT uid), count(DISTINCT uname) FROMlog GROUP BY ip 此操作就直接报错了,只能使用方案一解决数据倾斜