HiveSQL优化
join关联表接纳on举行数据的过滤
通过limit分批写入数据
insert into table tb1
select * from tb2 limit 100
1 分桶采样
- 当表的数据量比较巨大的时候, 在编写SQL语句后, 需要起首测试 SQL是否可以正常的执行, 需要在表中执
行查询操作, 由于表数据量比较巨大, 在测试一条SQL的时候整个运行的时间比较久, 为了提拔测试效率, 可以整个表
抽样出一部分的数据, 举行测试
- 校验数据的可行性(质量校验) 100条 sum() join
- 举行统计分析的时候, 并不需要统计出详细的指标, 大概统计的都是一些相对性指标, 好比说一些比率(合
格率)问题, 此时可以通过采样处置惩罚 10 /100 1/10
- select * from 表 tablesample ( bucket x out of y [on column|rand()])
- select * from ods.ods_mem_member_union_i tablesample ( bucket 3 out of 3 on sex);
- select sex,
- count(member_id) over(partition by sex),
- count(member_id) over(),
- count(member_id) over(partition by sex) / count(member_id) over()
- from ods.ods_mem_member_union_i tablesample ( bucket 3 out of 3 on rand());
在表关联时，因为关联字段的原因造成某个reduce处置惩罚的数据量过大，产生数据倾斜，导致盘算时间过长
项目中主要发生DWD层中，
门店销售明细表
dwd_sale_store_sale_dtl_i
线下销售数据
商城核销明细表
dwd_sold_shop_order_dtl_i
线上的销售数据
将支付金额和退款金额合并在一起
方案一 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
大表join大表
通过分桶的方式将大表文件拆分小文件
1-开启Bucket Map Join支持： set hive.optimize.bucketmapjoin =true;
2-Join两个表必须是分桶表
3-一个表的分桶数量是另一个表的分桶数量的整倍数
4-分桶列必须是join的ON条件的列
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);
全称 ：sort merge bucket map join
大表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;
4-分桶列必须是join的on条件的列，同时必须保证按照分桶列举行排序操作 当向表中写入数据是，按照分桶字段举行排序写入
- -- 开启强制排序
- set hive.enforce.sorting=true;
- -- 在建分桶表使用 使用sorted by()
- create table tb_cls(
- cid int,
- name string
- )
- clustered by(cid) into 2 buckets
- sorted by(cid)-- 指定排序字段
- row format delimited fields terminated by ',';
- create table tb_student(
- uid string,
- name string,
- cid int
- )
- clustered by(cid) into 2 buckets
- sorted by(cid) -- 指定排序字段
- row format delimited fields terminated by ',';
- -- 开启bucket map join
- set hive.optimize.bucketmapjoin =true;
- set hive.optimize.bucketmapjoin.sortedmerge =true;
1-判断表是不是 分桶表，在判断表的大小 假如不是分桶，但是属于小表 触发mapjoin
2-判断表是分桶，但是分桶数量不同等 触发Bucket Map Join
3-断表是分桶，但是分桶数量同等，关联字段是次序的 触发SMB Join
假如条件不符和上面要求，就执行一样平常join流程，大概出现数据倾斜
3 索引优化
用于提拔where和on查询过滤的效率
原始索引
hive的原始索引可以针对某个列, 大概某几列构建索引信息, 构建后提拔查询执行列的查询效率
所以在HIVE3.x版本后, 已经直接将这种索引废弃掉了, 无法使用
Row Group Index索引
行组索引
条件:
- 要求表的存储类型为ORC存储格式
- 在创建表的时候, 必须开启 row group index 索引支持
‘orc.create.index’=‘true’
- 在插入数据的时候, 必须保证按照where过滤的字段举行数据的次序插入
实用于: 数值类型的 int float decimal, 并且对数值类型举行不等的过滤操作
- -- 建表时定义
- 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;
布隆索引
条件:
- 要求表的存储类型为 ORC存储方案
- 在建表的时候, 必须设置为那些列构建布隆索引
- 仅能适合于等值过滤查询操作
- -- 建表时定义
- 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 = '北京'
, 那最好, 假如保证不了, 也无所谓, 大不了这个索引的效率不是特别好
2- 对于布隆过滤索引: 建议将后续会大量的用于等值连接的操作字段, 创建成布隆索引, 好比说: JOIN的字段 常常
在where后面出现的等值连接字段
4 数据倾斜优化
1：数据倾斜现象 ？
【疑似】当执行过程中任务卡在 99%，大概率是出现了数据倾斜
详细看:::::::::::::::::::::::::::: 这里!!!难点是发现问题后如何办理,观察业务本领
______________________________________________________________________
join造成数据倾斜
方案一： 参考join优化
mapjoin
bucket mapjoin
SMBjoin
方案二：
将那些产生倾斜的key和对应v2的数据, 从当前这个MR中移出去, 单独找一个MR来处置惩罚即可, 处置惩罚后, 和之前的MR举行汇总结果即可
运行期处置惩罚方案:
思路: 在执行MR的时候, 会动态统计每一个 k2的值出现重复的次数, 当这个重复的次数达到一定的阈值后, 认为
当前这个k2的数据存在数据倾斜, 主动将其剔除, 交由给一个单独的MR来处置惩罚即可,两个MR处置惩罚完成后, 将结果基于
union all 合并在一起即可
实操:
- -- 开启运行期处理倾斜参数
- set hive.optimize.skewjoin=true;
- -- 阈值, 此参数在实际生产环境中, 需要调整在一个合理的值(否则极易导致大量的key都是倾斜的)
- set hive.skewjoin.key=100000;
- 如果倾斜的数据量超过10万，会产生一个新的mr单独处理者10万条数据
好比判断:
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;
- join id = id
group by造成数据倾斜
相同分组的数据会放入同一个reduce处置惩罚
2
3
4
5
方案一
基于MR的 combiner(规约, 提前聚合) 淘汰数据达到reduce数量, 从而减轻倾斜问题
只需要在HIVE中开启combiner提前聚合设置参数即可:
set hive.map.aggr=true;
方案二
负载均衡的办理方案(需要运行两个MR来处置惩罚) (大combiner方案）
只需要开启负载均衡的HIVE参数设置即可:
set hive.groupby.skewindata=true;
假如分组的字段对应的数据较多，建议使用负载的方案
group by city
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
