hql优化篇:
*******优化的核心一 优先考虑全表扫描问题 Where条件判断等在TableScan阶段就进行过滤
2 跨区间跑数
方案 :关联时间维表设计
left join (select part_dt from dim.dim_period_d) 可以根据需求设计不同时间字段
主表时间分区小于时间维 做跨区间运行。
例子:3. left join (select distinct substr(period_date,1,10) period_date from
dim.dim_period_d ta where ta.period_date >= substr(cast(date_sub({START_DATE}’,dayofweek(’{START_DATE}’)-2+if(dayofweek(’{START_DATE}’)=1,7,0))
as string),1,10) AND ta.period_date < ‘{END_DATE}’ )tc on 1=1
where part_dt<=period_date and
weekofyear(part_dt)=weekofyear(period_date) and
substr(part_dt,1,4)=substr(period_date,1,4) group by
yw,wr,period_date******* 时间维的使用灵活常见
优化策略:
1 去除不必要的字段column
2 按需求取分区限制
3 提前清除数据,限制map数据大小,map端的combine建议常规开启
4 如果多个union all ,因为有很多0插入,建议写成多段insert into ,实测提高效率。优化性能。
5 null值处理 ,coalesce或nvl 处理。
******* 优化的核心二 提前过滤,减小主表数据,根据需要做hash,distinct处理,结合对mr的理解做优化Join使用:
map端join以大表驱动 (小表载入到内存 ,参考shuffle过程的内存缓冲区)
left join 小表在右,on 条件左表在前,right join 或inner join 小表在左 on 条件右表在前,left semi join 等同于in,但是实际使用中 semi join的剔除条件需要加到主表去限制,判断是否有完全剔除(有坑)hive中不让使用not in:
常见方法
left join 或 not exists处理
例子 :
and not exists (SELECT 1 from table_name1)
and not exists (SELECT 1 from table_name2)
各位hive报错,不能同时启用2个not exists ,虽然not exists 效率会高于left join ,但还是忍痛割爱。
where限制数据为null:
常见方法
where a.id=case when b.id is null then concat(‘hive’,rand() ) else b.id end;数据倾斜 :
常见的数据倾斜有3种情况
group 分组,count(distinct ) ,join 所导致
常用解决方案
1负载均衡设置 hive.groupby.skewindata=true
2 如count(distinct)可以考虑用goup by sum()替代计算
3 可以考虑在对大数据量的字段如果为 null ,在字段后面拼接hash 或者 rand()去打散。
4 可以考虑将倾斜的数据单独业务处理union all.优化基本流程
一 性能评估和服务构成
二明确当前环境各个组件性能
三分析定位性能瓶颈
四优化性能瓶颈
五性能监控和告警
开发规范,设计规范,命名规范
原则上
理解需求原则
把握全数据链路原则
检查代码简洁原则
优化手段上
hql优化,配置参数,从需求和架构(代码,模块,系统)hql工具篇:
1. 针对hql,首先考虑避免全表扫描,hive没有索引扫描时是读取到hdfs文件中,所以主表分区表中分区字段放在前面,不要限制:
如: where substr(part_dt,1,7) = substr({START_DATE}) 不建议
where part_dt>=concat(substr(START_DATE,1,7),’-01’) 正确
另外还有部分案例 :
题目一 计算当月最后一天的汇总数据
题目二 当月最后一天计算当月汇总,其它情况算近30天2. 每个月最后一天 :last_day(part_dt) as period_date
substr(cast(add(part_dt,1) as string),9,2)=‘01’
当月最后一天计算当月汇总,其不是最后一天算近30天:
另外关联时间维计算最后一天 period_date 跨区间分别计算时间 period_wid
part_dt>=(case when part_dt=period_date then concat(substr(period_date,1,7),’-01’)
when part_dt<period_date then date_sub(part_dt,29) end
and part_dt<=(case when part_dt=period_date then period_date
when part_dt<period_date then period_wid end )******* 写在最后,防止数据发散,计算严谨。