DWB、Hive优化
资料
DWB层实现操作
DWB作用:维度退化操作(降维)
指的将各个维度表或者事实表的核心字段全部汇聚成一个表操作, 形成一个宽表, 这样在后续进行统计分析的时候, 只需要操作合并后大的宽表数据即可
对于当前项目, 此处汇聚操作, 跟我们的主题没有直接关系的, 更加是基于业务模块, 形成业务模块的一些宽表
但是对于其他的项目 可能从一开始就是直接针对主题进行处理, 所以在一些其他的项目中可能会直接基于主题形成主题相关的宽表
创建DWB层的库:
-- 1- 创建DWB层库
drop database if exists cascade;
create database yp_dwb;
订单明细宽表
涉及需要合并的表:
核心:
订单事实表: fact_shop_order
订单组表: fact_shop_order_group
订单组支付信息表: fact_order_pay
商品快照表: fact_shop_order_goods_details
订单副表: fact_shop_order_address_detail
退款订单表: fact_refund_order
结算信息表: fact_order_settle
订单评价表: fact_goods_evaluation
订单配送表: fact_order_delievery_item
-- 解释：
一个母订单（订单组）
子订单1（店铺）
子订单2（店铺）
子订单id 订单组id
order_id group_id
1001 g001
1002 g001
1003 g001
订单表：核心字段
订单副标：非核心字段
关系：订单表和订单副表合成完成的订单表
构建表:
-- 这个订单明细宽表是由很多表的字段抽取合并而成的
-- 我们该从这些表中抽取哪些字段到这个宽表
原则1：根据后边分析的指标自行判断
原则2：当你不知道具体要哪些字段时，先排除一些一定不会要的字段，剩余的用宁缺毋滥原则
DROP TABLE if EXISTS yp_dwb.dwb_order_detail;
CREATE TABLE yp_dwb.dwb_order_detail(
-- 订单事实表
order_id string COMMENT '根据一定规则生成的订单编号',
order_num string COMMENT '订单序号',
buyer_id string COMMENT '买家的userId',
store_id string COMMENT '店铺的id',
order_from string COMMENT '渠道类型：android、ios、miniapp、pcweb、other',
order_state int COMMENT '订单状态:1.已下单\; 2.已付款, 3. 已确认 \;4.配送\; 5.已完成\; 6.退款\;7.已取消',
create_date string COMMENT '下单时间',
finnshed_time timestamp COMMENT '订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需要根据订单完成时间,进行自动收货以及自动评价',
is_settlement tinyint COMMENT '是否结算\;0.待结算订单\; 1.已结算订单\;',
is_delete tinyint COMMENT '订单评价的状态:0.未删除\; 1.已删除\;(默认0)',
evaluation_state tinyint COMMENT '订单评价的状态:0.未评价\; 1.已评价\;(默认0)',
way string COMMENT '取货方式:SELF自提\;SHOP店铺负责配送',
is_stock_up int COMMENT '是否需要备货 0：不需要 1：需要 2:平台确认备货 3:已完成备货 4平台已经将货物送至店铺 ',
-- 订单副表
order_amount decimal(36,2) COMMENT '订单总金额:购买总金额-优惠金额',
discount_amount decimal(36,2) COMMENT '优惠金额',
goods_amount decimal(36,2) COMMENT '用户购买的商品的总金额+运费',
is_delivery string COMMENT '0.自提；1.配送',
buyer_notes string COMMENT '买家备注留言',
pay_time string,
receive_time string,
delivery_begin_time string,
arrive_store_time string,
arrive_time string COMMENT '订单完成时间,当配送员点击确认送达时,进行更新订单完成时间,后期需要根据订单完成时间,进行自动收货以及自动评价',
create_user string,
create_time string,
update_user string,
update_time string,
is_valid tinyint COMMENT '是否有效 0: false\; 1: true\; 订单是否有效的标志',
-- 订单组
group_id string COMMENT '订单分组id',
is_pay tinyint COMMENT '订单组是否已支付,0未支付,1已支付',
-- 订单组支付
group_pay_amount decimal(36,2) COMMENT '订单总金额\;',
-- 退款单
refund_id string COMMENT '退款单号',
apply_date string COMMENT '用户申请退款的时间',
refund_reason string COMMENT '买家退款原因',
refund_amount decimal(36,2) COMMENT '订单退款的金额',
refund_state tinyint COMMENT '1.申请退款\;2.拒绝退款\; 3.同意退款,配送员配送\; 4:商家同意退款,用户亲自送货 \;5.退款完成',
-- 结算单
settle_id string COMMENT '结算单号',
settlement_amount decimal(36,2) COMMENT '如果发生退款,则结算的金额 = 订单的总金额 - 退款的金额',
dispatcher_user_id string COMMENT '配送员id',
dispatcher_money decimal(36,2) COMMENT '配送员的配送费(配送员的运费(如果退货方式为1:则买家支付配送费))',
circle_master_user_id string COMMENT '圈主id',
circle_master_money decimal(36,2) COMMENT '圈主分润的金额',
plat_fee decimal(36,2) COMMENT '平台应得的分润',
store_money decimal(36,2) COMMENT '商家应得的订单金额',
status tinyint COMMENT '0.待结算；1.待审核 \; 2.完成结算；3.拒绝结算',
settle_time string COMMENT ' 结算时间',
-- 订单评价
evaluation_id string,
evaluation_user_id string COMMENT '评论人id',
geval_scores int COMMENT '综合评分',
geval_scores_speed int COMMENT '送货速度评分0-5分(配送评分)',
geval_scores_service int COMMENT '服务评分0-5分',
geval_isanony tinyint COMMENT '0-匿名评价，1-非匿名',
evaluation_time string,
-- 订单配送
delievery_id string COMMENT '主键id',
dispatcher_order_state tinyint COMMENT '配送订单状态:0.待接单.1.已接单,2.已到店.3.配送中 4.商家普通提货码完成订单.5.商家万能提货码完成订单。6，买家完成订单',
delivery_fee decimal(36,2) COMMENT '配送员的运费',
distance int COMMENT '配送距离',
dispatcher_code string COMMENT '收货码',
receiver_name string COMMENT '收货人姓名',
receiver_phone string COMMENT '收货人电话',
sender_name string COMMENT '发货人姓名',
sender_phone string COMMENT '发货人电话',
delievery_create_time string,
-- 商品快照
order_goods_id string COMMENT '--商品快照id',
goods_id string COMMENT '购买商品的id',
buy_num int COMMENT '购买商品的数量',
goods_price decimal(36,2) COMMENT '购买商品的价格',
total_price decimal(36,2) COMMENT '购买商品的价格 = 商品的数量 * 商品的单价 ',
goods_name string COMMENT '商品的名称',
goods_specification string COMMENT '商品规格',
goods_type string COMMENT '商品分类 ytgj:进口商品 ytsc:普通商品 hots爆品',
goods_brokerage decimal(36,2) COMMENT '商家设置的商品分润的金额',
is_goods_refund tinyint COMMENT '0.不退款\; 1.退款'
)
COMMENT '订单明细表'
PARTITIONED BY(dt STRING)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');
插入数据的操作:
--- 老师课堂代码-------------------------------
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
insert overwrite table yp_dwb.dwb_order_detail partition (dt)
select
-- 订单事实表: fact_shop_order
o.id ,
o.order_num,
o.buyer_id,
o.store_id,
o.order_from,
o.order_state,
o.create_date,
o.finnshed_time,
o.is_settlement,
o.is_delete,
o.evaluation_state,
o.way,
o.is_stock_up,
-- 订单副表: fact_shop_order_address_detail
a.order_amount,
a.discount_amount,
a.goods_amount,
a.is_delivery,
a.buyer_notes,
a.pay_time,
a.receive_time,
a.delivery_begin_time,
a.arrive_store_time,
a.arrive_time,
a.create_user,
a.create_time,
a.update_user,
a.update_time,
a.is_valid,
-- 订单组表: fact_shop_order_group
g.group_id,
g.is_pay,
-- 订单组支付信息表: fact_order_pay
p.order_pay_amount as group_pay_amount,
-- 退款订单表: fact_refund_order
r.id as refund_id,
r.apply_date,
r.refund_reason,
r.refund_amount,
r.refund_state,
-- 结算信息表: fact_order_settle
s. id as settle_id,
s.settlement_amount,
s.dispatcher_user_id,
s.dispatcher_money,
s.circle_master_user_id,
s.circle_master_money,
s.plat_fee,
s.store_money,
s.status,
s.settle_time,
-- 订单评价表: fact_goods_evaluation
e.id as evaluation_id,
e.user_id as evaluation_user_id,
e.geval_scores as geval_scores,
e.geval_scores_speed as geval_scores_speed,
e.geval_scores_service as geval_scores_service,
e.geval_isanony as geval_isanony,
e.create_time as evaluation_time,
-- 订单配送表: fact_order_delievery_item
i.id as delievery_id,
i.dispatcher_order_state,
i.delivery_fee,
i.distance,
i.dispatcher_code,
i.receiver_name,
i.receiver_phone,
i.sender_name,
i.sender_phone,
i.create_time as delievery_create_time,
-- 商品快照表: fact_shop_order_goods_details
d.id as order_goods_id,
d.goods_id,
d.buy_num,
d.goods_price,
d.total_price,
d.goods_name,
d.goods_specification,
d.goods_type,
d.goods_brokerage,
d.is_refund as is_goods_refund,
SUBSTRING(o.create_date,1,10) as dt
from (select * from yp_dwd.fact_shop_order where end_date='9999-99-99') o
left join (select * from yp_dwd.fact_shop_order_group where end_date='9999-99-99') g on o.id = g.order_id
left join (select * from yp_dwd.fact_order_pay where end_date='9999-99-99') p on g.group_id = p.group_id
left join (select * from yp_dwd.fact_shop_order_goods_details where end_date='9999-99-99') d on o.id = d.order_id
left join (select * from yp_dwd.fact_shop_order_address_detail where end_date='9999-99-99') a on o.id = a.id
left join (select * from yp_dwd.fact_refund_order where end_date='9999-99-99') r on o.id = r.order_id
left join (select * from yp_dwd.fact_order_settle where end_date='9999-99-99') s on o.id = s.order_id
left join yp_dwd.fact_goods_evaluation e on o.id = e.order_id and e.is_valid = 1
left join yp_dwd.fact_order_delievery_item i on o.id = i.shop_order_id and i.dispatcher_order_type = 1 and i.is_valid = 1;
------- 参考代码----------------------------------------
-- 关联表, 进行数据合并操作
insert overwrite table yp_dwb.dwb_order_detail partition(dt)
select
-- 订单事实表
o.id as order_id,
o.order_num,
o.buyer_id,
o.store_id,
o.order_from,
o.order_state,
o.create_date,
o.finnshed_time,
o.is_settlement,
o.is_delete,
o.evaluation_state,
o.way,
o.is_stock_up,
-- 订单详情表
a.order_amount,
a.discount_amount,
a.goods_amount,
a.is_delivery,
a.buyer_notes,
a.pay_time,
a.receive_time,
a.delivery_begin_time,
a.arrive_store_time,
a.arrive_time,
a.create_user,
a.create_time,
a.update_user,
a.update_time,
a.is_valid,
--订单组表
g.group_id,
g.is_pay,
-- 订单组支付
p.order_pay_amount,
--退款单
refund.id as refund_id,
refund.apply_date,
refund.refund_reason,
refund.refund_amount,
refund.refund_state,
--结算单表
os.id as settle_id,
os.settlement_amount,
os.dispatcher_user_id,
os.dispatcher_money,
os.circle_master_user_id,
os.circle_master_money,
os.plat_fee,
os.store_money,
os.status,
os.settle_time,
--订单评价表
e.id,
e.user_id,
e.geval_scores,
e.geval_scores_speed,
e.geval_scores_service,
e.geval_isanony,
e.create_time,
-- 订单配送表
d.id,
d.dispatcher_order_state,
d.delivery_fee,
d.distance,
d.dispatcher_code,
d.receiver_name,
d.receiver_phone,
d.sender_name,
d.sender_phone,
d.create_time as delievery_create_time,
-- 商品快照表
ogoods.id as order_goods_id,
ogoods.goods_id,
ogoods.buy_num,
ogoods.goods_price,
ogoods.total_price,
ogoods.goods_name,
ogoods.goods_specification,
ogoods.goods_type,
ogoods.goods_brokerage,
ogoods.is_refund as is_goods_refund,
SUBSTRING(o.create_date,1,10) as dt
from
-- 订单事实表
(select * from cy_class3_jiale_yp_dwd.fact_shop_order where end_date = '9999-99-99') o
-- 订单组表
left join cy_class3_jiale_yp_dwd.fact_shop_order_group g
on o.id = g.order_id and g.end_date = '9999-99-99'
-- 订单支付信息表
left join cy_class3_jiale_yp_dwd.fact_order_pay p
on p.group_id = g.group_id
-- 商品快照表
left join cy_class3_jiale_yp_dwd.fact_shop_order_goods_details ogoods
on ogoods.order_id = o.id and ogoods.end_date = '9999-99-99'
-- 订单详情表(附属表)
left join cy_class3_jiale_yp_dwd.fact_shop_order_address_detail a
on a.id = o.id and a.end_date = '9999-99-99'
-- 退款单表
left join cy_class3_jiale_yp_dwd.fact_refund_order refund
on refund.order_id=o.id and refund.end_date='9999-99-99'
-- 结算单表
left join cy_class3_jiale_yp_dwd.fact_order_settle os
on os.order_id=o.id and os.end_date='9999-99-99'
-- 订单评价表
left join cy_class3_jiale_yp_dwd.fact_goods_evaluation e
on e.order_id=o.id and e.is_valid=1
-- 订单配送表
left join cy_class3_jiale_yp_dwd.fact_order_delievery_item d
on d.shop_order_id=o.id and d.dispatcher_order_type=1 and d.is_valid=1 and d.end_date='9999-99-99';
店铺明细宽表
- 涉及的表
店铺表: dim_store
商圈表: dim_trade_area
地址信息表：dim_location
行政区域表: dim_district
- 建表操作:
-- 宁缺毋滥
-- 从原表中对所有字段进行一个个排除，先排除一定不会要的字段，如果剩余一些字段不能确定是否保留，则必须留下
DROP TABLE if EXISTS yp_dwb.dwb_shop_detail;
CREATE TABLE yp_dwb.dwb_shop_detail(
-- 店铺
id string,
address_info string COMMENT '店铺详细地址',
store_name string COMMENT '店铺名称',
is_pay_bond tinyint COMMENT '是否有交过保证金 1：是0：否',
trade_area_id string COMMENT '归属商圈ID',
delivery_method tinyint COMMENT '配送方式 1 ：自提 ；3 ：自提加配送均可\; 2 : 商家配送',
store_type int COMMENT '店铺类型 22天街网店 23实体店 24直营店铺 33会员专区店',
is_primary tinyint COMMENT '是否是总店 1: 是 2: 不是',
parent_store_id string COMMENT '父级店铺的id，只有当is_primary类型为2时有效',
-- 商圈
trade_area_name string COMMENT '商圈名称',
-- 区域-店铺
province_id string COMMENT '店铺所在省份ID',
city_id string COMMENT '店铺所在城市ID',
area_id string COMMENT '店铺所在县ID',
province_name string COMMENT '省份名称',
city_name string COMMENT '城市名称',
area_name string COMMENT '县名称'
)
COMMENT '店铺明细表'
PARTITIONED BY(dt STRING)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress' = 'SNAPPY');
- SQL实现
--动态分区配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
-------------------------课堂老师写的SQL----------------------------
insert overwrite table yp_dwb.dwb_shop_detail partition (dt)
select
-- 店铺表: dim_store
s.id,
s.address_info,
s.name as store_name, -- 这里可以不用别名，直接将name的值插入给宽表中store_name
s.is_pay_bond,
s.trade_area_id,
s.delivery_method,
s.store_type,
s.is_primary,
s.parent_store_id,
-- 商圈表: dim_trade_area
a.name, -- 注意这里可以不用起别名
-- 其他表
d3.id as province_id,
d2.id as city_id,
d1.id as area_id,
d3.name as province_name,
d2.name as city_name,
d1.name as area_name,
substr(s.create_time,1,10) as dt -- 动态分区字段，要放在最后
from (select * from yp_dwd.dim_store where end_date = '9999-99-99') s
left join (select * from yp_dwd.dim_trade_area where end_date = '9999-99-99') a on s.trade_area_id = a.id
left join (select * from yp_dwd.dim_location where end_date = '9999-99-99') l on s.id = l.correlation_id and l.type = 2
left join yp_dwd.dim_district d1 on l.adcode = d1.id
left join yp_dwd.dim_district d2 on d1.pid = d2.id
left join yp_dwd.dim_district d3 on d2.pid = d3.id;
-------------------------参考SQL-----------------------------------
insert overwrite table dwb.dwb_shop_detail partition(dt)
select
-- 店铺相关字段:
s.id,
s.address_info,
s.name as store_name,
s.is_pay_bond,
s.trade_area_id,
s.delivery_method,
s.store_type,
s.is_primary,
s.parent_store_id,
-- 商圈相关的:
a.name as trade_area_name,
-- 区域信息:
d3.id as province_id,
d2.id as city_id,
d1.id as area_id,
d3.name as province_name,
d2.name as city_name,
d1.name as area_name,
substr(s.create_time,1,10) as dt
from (select * from yp_dwd.dim_store where end_date='9999-99-99') s
left join yp_dwd.dim_trade_area a
on s.trade_area_id = a.id and a.end_date = '9999-99-99'
left join yp_dwd.dim_location l
on l.correlation_id = s.id and l.type = 2 and l.end_date = '9999-99-99'
left join yp_dwd.dim_district d1 on d1.id = l.adcode
left join yp_dwd.dim_district d2 on d1.pid = d2.id
left join yp_dwd.dim_district d3 on d2.pid = d3.id;
商品明细宽表
- 关联表
商品SKU表：dim_goods SPU ----》（ipone X） SKU -----》（128G的白色的ipone X）
商品分类表：dim_goods_class
品牌表： dim_brand
- 建表操作:
DROP TABLE if EXISTS yp_dwb.dwb_goods_detail;
CREATE TABLE yp_dwb.dwb_goods_detail(
-- 商品SKU表：dim_goods
id string,
store_id string COMMENT '所属商店ID',
class_id string COMMENT '分类id:只保存最后一层分类id',
store_class_id string COMMENT '店铺分类id',
brand_id string COMMENT '品牌id',
goods_name string COMMENT '商品名称',
goods_specification string COMMENT '商品规格',
search_name string COMMENT '模糊搜索名称字段:名称_+真实名称',
goods_sort int COMMENT '商品排序',
goods_market_price decimal(36,2) COMMENT '商品市场价',
goods_price decimal(36,2) COMMENT '商品销售价格(原价)',
goods_promotion_price decimal(36,2) COMMENT '商品促销价格(售价)',
goods_storage int COMMENT '商品库存',
goods_limit_num int COMMENT '购买限制数量',
goods_unit string COMMENT '计量单位',
goods_state tinyint COMMENT '商品状态 1正常，2下架,3违规（禁售）',
goods_verify tinyint COMMENT '商品审核状态: 1通过，2未通过，3审核中',
activity_type tinyint COMMENT '活动类型:0无活动1促销2秒杀3折扣',
discount int COMMENT '商品折扣(%)',
seckill_begin_time string COMMENT '秒杀开始时间',
seckill_end_time string COMMENT '秒杀结束时间',
seckill_total_pay_num int COMMENT '已秒杀数量',
seckill_total_num int COMMENT '秒杀总数限制',
seckill_price decimal(36,2) COMMENT '秒杀价格',
top_it tinyint COMMENT '商品置顶：1-是，0-否',
create_user string,
create_time string,
update_user string,
update_time string,
is_valid tinyint COMMENT '0 ：失效，1 ：开启',
-- 商品小类
min_class_id string COMMENT '分类id:只保存最后一层分类id',
min_class_name string COMMENT '店铺内分类名字',
-- 商品中类
mid_class_id string COMMENT '分类id:只保存最后一层分类id',
mid_class_name string COMMENT '店铺内分类名字',
-- 商品大类
max_class_id string COMMENT '分类id:只保存最后一层分类id',
max_class_name string COMMENT '店铺内分类名字',
-- 品牌
brand_name string COMMENT '品牌名称'
)
COMMENT '商品明细表'
PARTITIONED BY(dt STRING)
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
- SQL实现
--设置动态分区参数（作用周期：在当前会话（DataGrip和服务器当前连接中）期间自作用，是一次性的设置）
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
-------------------------老师课堂SQL-----------------------------
-- 将关联表的数据查询出来插入到商品明细宽表
insert overwrite table yp_dwb.dwb_goods_detail partition (dt)
select
-- 商品SKU表:dim_goods表
g.id,
g.store_id,
g.class_id,
g.store_class_id,
g.brand_id,
g.goods_name,
g.goods_specification,
g.search_name,
g.goods_sort,
g.goods_market_price,
g.goods_price,
g.goods_promotion_price,
g.goods_storage,
g.goods_limit_num,
g.goods_unit,
g.goods_state,
g.goods_verify,
g.activity_type,
g.discount,
g.seckill_begin_time,
g.seckill_end_time,
g.seckill_total_pay_num,
g.seckill_total_num,
g.seckill_price,
g.top_it,
g.create_user,
g.create_time,
g.update_user,
g.update_time,
g.is_valid,
-- 分类表
-- 小类ID ：level = 3
case when c1.level = 3
then c1.id
else null
end as min_class_id,
-- 小类名字 ：level = 3
case when c1.level = 3
then c1.name
else null
end as min_class_name,
-- mid_class_id,
case when c1.level = 2 then c1.id
when c2.level = 2 then c2.id
else null
end as mid_class_id,
-- mid_class_name,
case when c1.level = 2 then c1.name
when c2.level = 2 then c2.name
else null
end as mid_class_name,
-- max_class_id,
case when c1.level = 1 then c1.id
when c2.level = 1 then c2.id
when c3.level = 1 then c3.id
else null
end as max_class_id,
-- max_class_name,
case when c1.level = 1 then c1.name
when c2.level = 1 then c2.name
when c3.level = 1 then c3.name
else null
end as max_class_name,
--品牌表:dim_brand
b.brand_name,
substring(g.create_time,1,10) as dt -- 2022-06-16
from (select * from yp_dwd.dim_goods where end_date = '9999-99-99') g
left join (select * from yp_dwd.dim_goods_class where end_date = '9999-99-99') c1 on g.store_class_id = c1.id
left join (select * from yp_dwd.dim_goods_class where end_date = '9999-99-99') c2 on c1.parent_id = c2.id
left join (select * from yp_dwd.dim_goods_class where end_date = '9999-99-99') c3 on c2.parent_id = c3.id
left join (select * from yp_dwd.dim_brand where end_date = '9999-99-99') b on g.brand_id = b.id ;
-------------------------参考SQL-----------------------------
insert overwrite table dwb.dwb_goods_detail partition(dt)
select
goods.id,
goods.store_id,
goods.class_id,
goods.store_class_id,
goods.brand_id,
goods.goods_name,
goods.goods_specification,
goods.search_name,
goods.goods_sort,
goods.goods_market_price,
goods.goods_price,
goods.goods_promotion_price,
goods.goods_storage,
goods.goods_limit_num,
goods.goods_unit,
goods.goods_state,
goods.goods_verify,
goods.activity_type,
goods.discount,
goods.seckill_begin_time,
goods.seckill_end_time,
goods.seckill_total_pay_num,
goods.seckill_total_num,
goods.seckill_price,
goods.top_it,
goods.create_user,
goods.create_time,
goods.update_user,
goods.update_time,
goods.is_valid,
case when c1.level = 3 then c1.id
else null
end as min_class_id,
case when c1.level = 3 then c1.name
else null
end as min_class_name,
case when c1.level = 2 then c1.id
when c2.level = 2 then c2.id
else null end as mid_class_id,
case when c1.level = 2 then c1.name
when c2.level = 2 then c2.name
else null end as mid_class_name,
case when c1.level = 1 then c1.id
when c2.level = 1 then c2.id
when c3.level = 1 then c3.id
else null end as max_class_id,
case when c1.level = 1 then c1.name
when c2.level = 1 then c2.name
when c3.level = 1 then c3.name
else null end as max_class_name,
b.brand_name,
substr(goods.create_time,1,10) as dt
from (select * from yp_dwd.dim_goods where end_date = '9999-99-99') goods
left join yp_dwd.dim_goods_class c1
on goods.store_class_id = c1.id and c1.end_date = '9999-99-99'
left join yp_dwd.dim_goods_class c2
on c1.parent_id = c2.id and c2.end_date = '9999-99-99'
left join yp_dwd.dim_goods_class c3
on c2.parent_id = c3.id and c3.end_date = '9999-99-99'
left join yp_dwd.dim_brand b
on goods.brand_id = b.id and b.end_date = '9999-99-99';
Join 优化操作
小表 JOIN 大表(Map端Join)
思考: 在执行JOIN的SQL的时候, SQL会被翻译为MR. 请思考, 翻译后MR默认是如何进行Join操作的呢?
思考: 这种reduce join操作, 存在那些弊端呢?
1- 可能会存在数据倾斜的问题
2- 所有的数据的合并都是在reduce端完成的, 而reduce数量相对比较少的, 导致reduce的压力比较大
思考: 如何提升Join的效率呢? Map Join
Map Join: 将核心Join操作, 都集中在Map端进行处理, 而Map的数量是由读取文件的切片来决定的, 会保证每一个mapTask的读取数量基本是差不多, 可以大幅降低数据倾斜发生, Map的数量会随着读取文件数量增大而变多,以此来提升Join的执行效率
弊端:
1) 比较耗费内存
2) 要求整个Join中, 必须得有小表
使用条件的:
set hive.auto.convert.join=true; -- 开启Map Join的支持, 默认就是True
-- 小表数据量的最大阈值: 默认 20971520(20M)
set hive.auto.convert.join.noconditionaltask.size=512000000 默认值为 20971520
如果不满足条件, HIVE会自动使用 reduce Join 操作
-- 结论是：
1、Map端的Join当前的Hive已经默认支持
2、你什么都不需要做也可以使用Map端Join，但是这个多大的表算小表可以自己定义，默认是20M
set hive.auto.convert.join.noconditionaltask.size=512000000
中型表 JOIN 大表
- 方案一: 如果中型表能对数据提前过滤, 尽量提前过滤掉, 过滤后, 又可能就已经满足了Map Join条件(并不一定可用)
- 方案二: Bucket Map Join
使用条件:
1- Join两个表必须是分桶表
2- 开启Bucket Map Join 支持: set hive.optimize.bucketmapjoin = true;
3- 一个表的分桶数量是另一个表的分桶数量的整倍数
4- 分桶列 必须 是 Join条件的列
5- 必须建立在Map Join场景中
结论是：
1、Bucket Map Join :的表必须都是分桶表，分桶数量是整数倍
2、开启Bucket Map Join ：set hive.optimize.bucketmapjoin = true;
大表 JOIN 大表
- 解决方案: SMB(Sort Merge Bucket) Map Join
使用条件:
1- 两个表必须都是分桶表
2- 开启SMB Map Join 支持
-- 开启Bucket Map Join
set hive.optimize.bucketmapjoin = true;
-- 开启 SMB Join支持
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
-- 开启自动尝试使用SMB Join
set hive.optimize.bucketmapjoin.sortedmerge = true
3- 两个表的分桶的数量是一致的
4- 分桶列 必须是 Join条件列 , 同时必须保证按照分桶字段列进行排序操作
-- 开启强制排序
set hive.enforce.sorting=true;
5- 应用在 Bucket Map Join 场景中
建表的时候:
create table test_smb_2(
mid string,
age_id string
) CLUSTERED BY(mid) SORTED BY(mid) INTO 500 BUCKETS;
结论：
分桶 + 桶
HIVE的索引
为什么说, 索引可以加快查询效率? 思路说明
- hive的原始索引(废弃)
hive的原始索引可以针对某个列 或者某几个列构建索引信息, 构建后提升指定列的查询效率,
存在弊端: hive原始索引不会自动更新, 每次表中数据发生变化后, 都是需要手动重建索引操作, 比较耗费资源, 整体提升性能效果一般
所有在hive3.x版本已经直接将这种索引废弃掉, 无法使用了, 及时在生产中使用是hive1.x或者hive2.x版本的 不建议优先使用原始索引
- hive的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 into table lxw1234_orc2
SELECT CAST(siteid AS INT) AS id,
pcid
FROM lxw1234_text
-- 插入的数据保持排序
DISTRIBUTE BY id sort BY id;
使用:
select * from lxw1234_orc2 where siteid >100 ; --自动应用行组索引了
- bloom filter index (布隆过滤索引, 开发过滤索引)
思路:
在开启布隆过滤索引后, 可以针对某个列,或者某几个列来建立索引, 构建索引后, 会在将这一列的数据的值存储在对应script片段的索引信息中, 这样当进行=值查询数据的时候, 首先会到每一个script片段判断是否有这个值, 如果没有, 直接跳过这个script, 从而减少数据扫描量, 提升效率
条件:
1) 要求表的存储类型必须为ORC存储格式
2) 在建表的时候, 必须设置为那些列构建布隆索引
3) 仅能适用于 等值过滤查询操作
操作:
CREATE TABLE lxw1234_orc2 stored AS ORC
TBLPROPERTIES
(
'orc.compress'='SNAPPY',
'orc.create.index'='true', --行组索引
-- pcid字段开启BloomFilter索引
"orc.bloom.filter.columns"="pcid,name"
)
使用:
select * from 表 where name = '张三' and age >10 就会使用布隆索引+ 行组索引
在什么时候可以使用呢?
1- 对于行组索引, 我们建议只要数据存储格式为ORC 建议将这种索引全部开启,至于导入数据的时候, 如果能保证有序, 那最好,如果保证不了也无所谓, 大不了这个索引效率不是特别好
2- 对于布隆索引, 建议将后续会大量用于等值连接的操作字段, 建立成布隆索引, 比如说 join的字段. end_date字段
HIVE的窗口函数
语法:分析函数 over(partition by xxx order by xxx)
窗口函数一般是要和一些分析函数组合使用操作
- 第一类分析函数: row_number() rank(), dense_rank() , ntile(N)
- 作用: 对数据表标记,从1开始打, 每组都是从1开始的
- 适用场景: 求分组TOPN问题 以及求几分之几的问题
三个分析函数在进行打标记的区别:
1) row_number(): 从1开始打标记, 不关心数据的重复问题
2) rank() : 从1开始打标记, 关心数据重复问题, 如果数据重复, 会打上相同的标记, 同时还会占用后续的编号
例如: 1 2 2 2 5..
3) dense_rank(): 从1开始打标记, 关心数据重复问题,如果数据重复, 会打上相同的标记,不会占用后续的编号
例如: 1 2 2 2 3...
4) ntile(N) : 从1开始打标记, 会根据N的值, 将一组的数据划分为N份, 每一份会打上相同标记, 每一份最多相差为1
例如: 一个组内有5条数据, ntile(3)
1 1 2 2 3
DWS层实现
DWS层: 基于主题统计分析, 此层一般是用于最细粒度的统计操作
比如说:
以年 月 日 来统计, 在DWS层 仅需要按照日进行统计相关的指标即可, 进行提前聚合
在DM层: 进行上卷统计, 将 月 和年 基于日的统计宽表 计算得出
思考: 为什么不直接在DWS层根据原始表直接将 年 月 日 都统计好呢, 为什么要先统计日呢, 这样有什么好处?
主要原因, 是为了提升后续进行上卷统计的效率
如果说直接针对原始表, 假设每天有 100w条数据, 进行每日统计, 每天计算都要累计算100w次, 如果按照月来统计, 一个月有30天, 面对是30*100w数据量进行统计, 这样越上统计, 效率越差
如果先按照 日进行统计, 此时 一个月的统计结果数据, 只有30条数据, 在统计月份的时候, 每个中数据量也就只有30条的累加操作, 统计年, 也就只有300多条累加操作
如果要进行提前聚合操作, 不能对数据进行去重统计, 比如要统计用户量, 分别统计 每天 每月 和每年
本次DWS层以上, 共计有三个主题的, 分别为销售主题, 商品主题, 和 用户主题, 在实际面试中, 可以只负责其中一个主题或者二个主题即可, 无需全部负责, 但是学习中, 希望讲三个主题全部搞定
DWS层以下的层次中, 仅负责其中一个业务模块或者二个业务模块 完整的处理工作即可
4.1 销售主题的日统计宽表
可分析的主要指标有：销售收入、平台收入、配送成交额、小程序成交额、安卓APP成交额、苹果APP成交额、PC商城成交额、订单量、参评单量、差评单量、配送单量、退款单量、小程序订单量、安卓APP订单量、苹果APP订单量、PC商城订单量。
维度有：日期、城市、商圈、店铺、品牌、大类、中类、小类。
维度组合:
日期
日期+城市
日期+城市+商圈
日期+城市+商圈+店铺
日期+品牌
日期+大类
日期+大类+中类
日期+大类+中列+小类
16*8 = 128个需求指标结果
分析, 当前需要统计的这些维度和指标, 需要涉及到那些表, 以及涉及到那些字段呢?
维度字段 :
日期: dwb_order_detail: dt
城市: dwb_shop_detail: city_id 和 city_name
商圈: dwb_shop_detail: trade_area_id 和 trade_area_name
店铺: dwb_shop_detail: store_name 和 id
品牌: dwb_goods_detail: brand_id,brand_name
大类: dwb_goods_detail: max_class_name 和 max_class_id
中类: dwb_goods_detail: mid_class_name 和 mid_class_id
小类: dwb_goods_detail: min_class_name 和 min_class_id
指标字段:
单量相关指标: 订单宽表 中 order_id
销售收入(小程序, 安卓, 苹果, pc端): 订单宽表 中 order_amount
平台收入:订单宽表 plat_fee
配送费: 订单宽表 delivery_fee
涉及表:
dwb_order_detail 和 dwb_goods_detail 和 dwb_shop_detail
关联条件:
订单表.store_id = 店铺表.id
订单表.goods_id = 商品表.id
where条件:
1- 保证必须是支付状态: is_pay = 1
2- 保证订单状态: order_state 不能是 1(以下单, 没有付款) 或者 7(已取消)
可以说: 订单宽表, 就是当前主题的事实表, 其他的两个宽表其实就是维度表
首先: 先创建DWS层, 销售主题日统计宽表(维度字段+指标字段 + 经验字段)
create database dws;
-- 销售主题日统计宽表
DROP TABLE IF EXISTS dws.dws_sale_daycount;
CREATE TABLE dws.dws_sale_daycount(
city_id string COMMENT '城市id',
city_name string COMMENT '城市name',
trade_area_id string COMMENT '商圈id',
trade_area_name string COMMENT '商圈名称',
store_id string COMMENT '店铺的id',
store_name string COMMENT '店铺名称',
brand_id string COMMENT '品牌id',
brand_name string COMMENT '品牌名称',
max_class_id string COMMENT '商品大类id',
max_class_name string COMMENT '大类名称',
mid_class_id string COMMENT '中类id',
mid_class_name string COMMENT '中类名称',
min_class_id string COMMENT '小类id',
min_class_name string COMMENT '小类名称',
-- 经验字段: 用于标记每一条数据是按照哪个维度计算出来的
group_type string COMMENT '分组类型：store，trade_area，city，brand，min_class，mid_class，max_class，all',
-- =======日统计=======
-- 销售收入
sale_amt DECIMAL(38,2) COMMENT '销售收入',
-- 平台收入
plat_amt DECIMAL(38,2) COMMENT '平台收入',
-- 配送成交额
deliver_sale_amt DECIMAL(38,2) COMMENT '配送成交额',
-- 小程序成交额
mini_app_sale_amt DECIMAL(38,2) COMMENT '小程序成交额',
-- 安卓APP成交额
android_sale_amt DECIMAL(38,2) COMMENT '安卓APP成交额',
-- 苹果APP成交额
ios_sale_amt DECIMAL(38,2) COMMENT '苹果APP成交额',
-- PC商城成交额
pcweb_sale_amt DECIMAL(38,2) COMMENT 'PC商城成交额',
-- 成交单量
order_cnt BIGINT COMMENT '成交单量',
-- 参评单量
eva_order_cnt BIGINT COMMENT '参评单量comment=>cmt',
-- 差评单量
bad_eva_order_cnt BIGINT COMMENT '差评单量negtive-comment=>ncmt',
-- 配送成交单量
deliver_order_cnt BIGINT COMMENT '配送单量',
-- 退款单量
refund_order_cnt BIGINT COMMENT '退款单量',
-- 小程序成交单量
miniapp_order_cnt BIGINT COMMENT '小程序成交单量',
-- 安卓APP订单量
android_order_cnt BIGINT COMMENT '安卓APP订单量',
-- 苹果APP订单量
ios_order_cnt BIGINT COMMENT '苹果APP订单量',
-- PC商城成交单量
pcweb_order_cnt BIGINT COMMENT 'PC商城成交单量'
)
COMMENT '销售主题日统计宽表'
PARTITIONED BY(dt STRING)
ROW format delimited fields terminated BY '\t'
stored AS orc tblproperties ('orc.compress' = 'SNAPPY');
- 根据日期+城市 统计相关指标
用户下一个订单组, 后台会基于商铺将订单组拆分为多个子订单,每个子订单都是属于一个店铺
一个子订单中, 会有多个商品. 而多个商品隶属于是一个店铺的 所以 一个子订单中city一定是一样的
with t1 as (
select
-- 维度信息:
substr(o.create_date,1,10) as dt,
-- 城市
s.province_id,
s.province_name,
s.city_id,
s.city_name,
-- 指标字段
-- 单量统计
o.order_id,
-- 销售额:
o.order_amount,
-- 平台收入
o.plat_fee,
--配送费
o.delivery_fee,
-- 条件判断字段:
o.order_from,
o.evaluation_id,
o.geval_scores,
o.delievery_id,
o.refund_id,
-- 用于去重
row_number() over(partition by o.order_id) as rn
from dwb.dwb_order_detail o
left join dwb.dwb_goods_detail g
on o.goods_id = g.id
left join dwb.dwb_shop_detail s
on o.store_id = s.id
where o.is_pay = 1 and o.order_state not in (1,7)
)
select
city_id,
city_name,
null as trade_area_id,
null as trade_area_name,
null as store_id,
null as store_name,
null as brand_id,
null as brand_name,
null as max_class_id,
null as max_class_name,
null as mid_class_id,
null as mid_class_name,
null as min_class_id,
null as min_class_name,
'city' as group_type,
-- 销售收入
sum(coalesce(order_amount, 0)) sale_amt ,
-- 平台收入
sum(coalesce(plat_fee,0)) plat_amt ,
-- 配送成交额
sum(coalesce(delivery_fee,0)) deliver_sale_amt ,
-- 小程序成交额
sum(if(order_from='miniapp', coalesce(order_amount, 0), 0)) mini_app_sale_amt,
-- 安卓APP成交额
sum(if(order_from='android', coalesce(order_amount, 0), 0)) android_sale_amt ,
-- 苹果APP成交额
sum(if(order_from='ios', coalesce(order_amount, 0), 0)) ios_sale_amt,
-- PC商城成交额
sum(if(order_from='pcweb', coalesce(order_amount, 0), 0)) pcweb_sale_amt,
-- =============订单量============= -- 单量
count(order_id) order_cnt ,
-- 参评单量 : 必须评价 指的有评价的订单数量
count(if(evaluation_id is not null, order_id, null)) eva_order_cnt,
-- 差评单量: 必须有评价, 评价的分数小于 6分
count(if(evaluation_id is not null and geval_scores<=6, order_id, null)) bad_eva_order_cnt,
-- 配送单量 : 必须是配送信息
count(if(delievery_id is not null, order_id, null)) deliver_order_cnt,
-- 退款单量 : 退款id 必须有值
count(if(refund_id is not null, order_id, null)) refund_order_cnt,
-- 小程序单量: order_from 支付渠道必须为 miniapp
count(if(order_from='miniapp', order_id, null)) miniapp_order_cnt,
-- 安卓APP订单量
count(if(order_from='android', order_id, null))android_order_cnt ,
-- 苹果APP订单量
count(if(order_from='ios', order_id, null))ios_order_cnt,
-- PC商城单量
count(if(order_from='pcweb', order_id, null))pcweb_order_cnt ,
dt
from
t1
where rn = 1
group by dt, province_id, province_name,city_id,city_name;
注意:
sum在计算的时候, 如果遇到null值, 计算的结果一定是个null值
count 在遇到null值 是直接跳过的, 不统计的
- 根据 日期 + 城市 + 商圈统计相关指标
作业1 (无难度 与 上一个类似)
- 根据 日期+品牌统计相关指标
作业2 (需要思考, 因为一个订单中有多个品牌, 一个品牌下有多个订单)
4.2 如何解决数据倾斜问题
何为数据倾斜:
在hive中, 执行一条SQL语句, 最终会翻译为MR, MR中mapTask和reduceTask都可能存在多个, 数据倾斜主要指的是整个MR中reduce阶段有多个reduce , 每个reduce拿到的数据的条数并不均衡, 导致某一个或者某几个拿到了比其他的reduce更多的数据, 到底处理数据压力集中在某几个reduce上, 形成数据倾斜问题
在hive中执行什么SQL操作的时候可能会出现数据倾斜
1) 执行多表join的操作
2) 执行group by的操作
4.2.1 join数据倾斜的处理
出现倾斜的根本原因:
在reduce中,某一个, 或者某几个的分组k2对应value的数据比较多, 从而引起数据倾斜问题
- 解决方案一:
可以通过 map join, bucket map join 以及 SMB Map join 解决
注意:
通过map join来解决数据倾斜, 但是map join使用是存在条件的, 如果无法满足这些条件, 无法使用map join
- 解决方案二:
思路: 将那些产生倾斜的k2和对应value数据, 从当前这个MR移植出去, 单独找一个MR来处理即可, 处理后, 和之前MR的汇总结果即可
关键问题: 如何找出那些存在倾斜的k2数据
运行期处理方案:
思路: 在执行MR的时候, 会动态统计每一个k2的值出现的重复的数量, 当这个重复的数量达到一定阈值后, 认为当前这个k2的数据存在数据倾斜, 自动将其剔除, 交由给一个单独的MR来处理即可, 两个MR处理完成后, 将结果, 基于union all 合并在一起即可
实操 :
set hive.optimize.skewjoin=true;
set hive.skewjoin.key=100000; -- 此参考在实际生产环境中, 需要进行调整在合理的值
适用于: 并不清楚那个key容易产生倾斜, 此时可以交由系统来动态检测
编译期处理方案:
思路: 在创建这个表的时候,我们就可以预知到后续插入到这个表中, 那些key的值会产生倾斜, 在建表的时候, 将其提前配置设置好即可, 在后续运行的时候, 程序会自动将设置的k2的值数据单独找一个MR来进行单独的处理操作, 处理后, 再和原有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];
在实际生产环境中, 应该使用那种方式呢? 两种方式都会使用的
一般来说, 会将两个都开启, 编译期的明确在编译期将其设置好, 编译期不清楚, 通过 运行期动态捕获即可
- union all优化方案
说明: 不管是运行期, 还是编译期的join倾斜解决, 最终都会运行多个MR , 最终将多个MR的结果, 通过union all进行汇总, union all也是单独需要一个MR来运行的
解决方案:
让每一个MR在运行完成后, 直接将结果输出到目的地即可, 默认是输出到临时文件目录下, 通过union all合并到最终目的地
set hive.optimize.union.remove=true;
4.2.2 group by 数据倾斜
- 为什么在执行group by的时候 可能会出现数据倾斜
假设目前有这么一个表:
sid sname cid
s01 张三 c01
s02 李四 c02
s03 王五 c01
s04 赵六 c03
s05 田七 c02
s06 周八 c01
s07 李九 c01
s08 老夯 c03
需求: 请计算每个班级有多少个人
select cid, count(1) from stu group by cid;
翻译后MR是如何处理SQL的呢?
map 阶段:
mapTask 1
k2 v2
c01 {s01 张三 c01}
c02 {s02 李四 c02}
c01 {s03 王五 c01}
c03 {s04 赵六 c03}
mapTask 2
k2 v2
c02 {s05 田七 c02}
c01 {s06 周八 c01}
c01 {s07 李九 c01}
c03 {s08 老夯 c03}
reduce 阶段
reduceTask 1 : 接收 c01 和 c03
接收到的数据:
k2 v2
c01 {s01 张三 c01}
c01 {s03 王五 c01}
c03 {s04 赵六 c03}
c01 {s06 周八 c01}
c01 {s07 李九 c01}
c03 {s08 老夯 c03}
分组后:
c01 [{s01 张三 c01},{s03 王五 c01},{s06 周八 c01},{s07 李九 c01}]
c03 [{s04 赵六 c03},{s08 老夯 c03}]
结果:
c01 4
c03 2
reduceTask 2 : 接收 c02
接收到的数据:
k2 v2
c02 {s02 李四 c02}
c02 {s05 田七 c02}
分组后:
c02 [{s02 李四 c02} , {s05 田七 c02}]
结果:
c02 2
以上整个计算过程中, 发现 其中一个reduce接收到的数据量比另一个reduce接收的数据量要多得多, 认为出现了数据倾斜问题
思考: 如何解决group by的数据倾斜呢?
解决方案一: 基于MR的combiner(规约)减轻数据倾斜问题 (小combiner)
假设目前有这么一个表:
sid sname cid
s01 张三 c01
s02 李四 c02
s03 王五 c01
s04 赵六 c03
s05 田七 c02
s06 周八 c01
s07 李九 c01
s08 老夯 c03
需求: 请计算每个班级有多少个人
select cid, count(1) from stu group by cid;
翻译后MR是如何处理SQL的呢?
map 阶段:
mapTask 1
k2 v2
c01 {s01 张三 c01}
c02 {s02 李四 c02}
c01 {s03 王五 c01}
c03 {s04 赵六 c03}
规约操作: 跟reduce的操作是一致的
输出:
k2 v2
c01 2
c02 1
c03 1
mapTask 2
k2 v2
c02 {s05 田七 c02}
c01 {s06 周八 c01}
c01 {s07 李九 c01}
c03 {s08 老夯 c03}
规约操作: 跟reduce的操作是一致的
输出:
k2 v2
c02 1
c01 2
c03 1
reduce 阶段
reduceTask 1 : 接收 c01 和 c03
接收到数据:
k2 v2
c01 2
c03 1
c01 2
c03 1
分组处理:
c01 [2,2]
c03 [1,1]
结果:
c01 4
c03 2
reduceTask 2 : 接收 c02
接收到数据:
k2 v2
c02 1
c02 1
分组后:
c02 [1,1]
结果:
c02 2
通过规约来解决数据倾斜, 发现处理后, 两个reduce中从原来相差3倍, 变更为相差2倍 , 减轻了数据倾斜问题
如何配置呢?
只需要在hive中开启combiner使用配置即可:
set hive.map.aggr=true;
解决方案二: 基于负载均衡的方案解决 (大的combiner)
解决方案一: 基于MR的combiner(规约)减轻数据倾斜问题 (小combiner)
假设目前有这么一个表:
sid sname cid
s01 张三 c01
s02 李四 c02
s03 王五 c01
s04 赵六 c03
s05 田七 c02
s06 周八 c01
s07 李九 c01
s08 老夯 c03
需求: 请计算每个班级有多少个人
select cid, count(1) from stu group by cid;
翻译后MR是如何处理SQL的呢?
第一个MR的操作:
map 阶段:
mapTask 1
k2 v2
c01 {s01 张三 c01}
c02 {s02 李四 c02}
c01 {s03 王五 c01}
c03 {s04 赵六 c03}
mapTask 2
k2 v2
c02 {s05 田七 c02}
c01 {s06 周八 c01}
c01 {s07 李九 c01}
c03 {s08 老夯 c03}
mapTask执行完成后, 在进行分发数据到达reduce, 默认将相同的k2的数据发往同一个reduce, 此时采用方案是随机分发, 保证每一个reduce拿到相等的数据条数即可
reduce阶段:
reduceTask 1:
接收到数据:
k2 v2
c01 {s01 张三 c01}
c02 {s02 李四 c02}
c01 {s03 王五 c01}
c01 {s06 周八 c01}
分组操作:
c01 [{s01 张三 c01},{s03 王五 c01},{s06 周八 c01}]
c02 [{s02 李四 c02}]
结果:
c01 3
c02 1
reduceTask 2:
接收到数据:
k2 v2
c03 {s04 赵六 c03}
c01 {s07 李九 c01}
c02 {s05 田七 c02}
c03 {s08 老夯 c03}
分组操作:
c03 [{s04 赵六 c03},{s08 老夯 c03}]
c01 [{s07 李九 c01}]
c02 [{s05 田七 c02}]
结果:
c03 2
c01 1
c02 1
第二个MR进行处理: 严格按照相同k2发往同一个reduce
map阶段:
mapTask 1:
k2 v2
c01 3
c02 1
c03 2
c01 1
c02 1
reduce阶段:
reduceTask 1: 接收 c01 和 c03
接收到数据:
k2 v2
c01 3
c01 1
c03 2
结果:
c01 4
c03 2
reduceTask 2: 接收 c02
接收到的数据:
k2 v2
c02 1
c02 1
结果:
c02 2
通过负载均衡来解决数据倾斜, 发现处理后, 两个reduce中从原来相差3倍, 变更为相差1.5倍 , 减轻了数据倾斜问题
如何操作:
只需要在hive中开启负载均衡使用配置即可:
set hive.groupby.skewindata=true;
注意: 使用第二种负载均衡的解决group by的数据倾斜, 一定要注意, sql语句中不能出现多次 distinct操作, 否则hive直接报错
错误信息:
Error in semantic analysis: DISTINCT on different columns notsupported with skew in data
4.3 HIVE其他优化
hive的并行优化的内容
1) 并行编译:
hive.driver.parallel.compilation : 是否开启并行编译的操作
hive.driver.parallel.compilation.global.limit: 设置最大同时编译几个会话的SQL
如果设置为 0 或者 负值 , 此时无限制
设置方式: 建议在CM上设置
hive.driver.parallel.compilation true
hive.driver.parallel.compilation.global.limit 15
说明:
默认情况下, 如果hive有多个会话窗口, 而且多个窗口都在提交SQL, 此时hive默认只能对一个会话的SQL进行编译, 其他会话的SQL需要等待, 这样效率相对比较低
2) 并行执行:
一个SQL语句在提交给hive之后, SQL有可能会被翻译为多个阶段, 在这个过程中, 有可能出现多个阶段互不干扰的情况,这个时候, 可以安排多个阶段并行执行的操作, 以此来提升效率
如何设置呢? 推荐在 会话中设置
set hive.exec.parallel=true; 是否开启并行执行
set hive.exec.parallel.thread.number=16; 最大运行并行多少个阶段
注意:
开启了此配置, 并不代表着所有SQL一定会并行的执行, 因为是否并行执行还取决于SQL中多个阶段之间是否有依赖, 只有在没有依赖的时候, 才可能并行执行
例子: union all
select * from A order by c
union all
select * from B where xxx;
hive常规的做法:
先执行第一个阶段 : select * from A order by c 得出临时结果
接着执行第二个阶段: select * from B where xxx; 得出临时结果
最后, 将两个语句的结果 合并在一起
- 小文件合并的操作:
当HDFS中小文件过多后, 会导致整个HDFS的存储容量的下降, 因为每一个小文件都会有一个元数据, 而元数据是存储在namenode的内存中, 当内存一旦满了, 即使datanode上还有空间, 那么也是无法存储了
MR角度: 当小文件过多后, 就会导致读取数据时候, 产生大量文件的切片, 从而导致有多个mapTask的执行, 而每个mapTask处理数据量比较的少, 导致资源的浪费, 导致执行时间较长, 因为一旦没有资源, 所有map只能串行化执行
hive的解决方案: 在执行完成后, 输出的结果的文件尽量的少一些, 避免出现小文件过多问题, 可以通过设置让执行的SQL输出的文件竟可能少一些
hive.merge.mapfiles: 是否开启map端的小文件合并操作, 指的 MR只有map没有reduce的操作时候
hive.merge.mapredfiles: 是否开启reduce端的小文件合并操作, 指的普通MR
hive.merge.size.per.task: 设置文件的大小 合并后的文件最大值 比如 128M
hive.merge.smallfiles.avgsize: 当输出文件的平均大小小于此设置值时，启动一个独立的map-reduce任务进行文件merge，默认值为16M。
说明: 以上配置均可直接在CM上进行配置即可
思考点: 在执行SQL的时候, 什么样SQL 可能会出现多个reduce情况? group by join
例如:
比如一个MR输出 10个文件
1M 10M 50M 2M 3M 30M 10M 5M 6M 20M
此时请问, 是否会进行文件合并工作呢? 发现得出平均值是小于16M 的 所以会执行小文件合并操作
思考: 是将所有的文件合并为一个文件, 还是怎么做呢?
128M 8M
- 矢量化查询(批量化):
配置:
set hive.vectorized.execution.enabled=true; 默认值为true
说明:
一旦开启了矢量化查询的工作,hive执行引擎在读取数据时候, 就会采用批量化读取工作, 一次性读取1024条数据进行统一的处理工作, 从而减少读取的次数(减少磁盘IO次数), 从而提升效率
注意:
要求表的存储格式必须为 ORC
- 读取零拷贝:
一句话: 在读取数据的时候, 能少读一点 尽量 少读一些(没用的数据尽量不读)
配置:
set hive.exec.orc.zerocopy=true; 默认中为false
例子:
看如下这个SQL: 假设A表中 a b c三个字段
select a,b from A where a =xxx;
- 关联优化器: 如果多个MR之间的操作的数据都是一样的, 同样shuffle操作也是一样的, 此时可以共享shuffle
一个SQL最终翻译成MR , 请问 有没有可能翻译为多个MR的情况呢? 非常有可能
每一个MR的中间有可能都会执行shuffle的操作, 而shuffle其实比较消耗资源操作(内部存在多次 io操作)
配置项: set hive.optimize.correlation=true;
总结优化点:
set hive.exec.parallel=true; --是否开启并行执行
set hive.exec.parallel.thread.number=16; --最大运行并行多少个阶段
set hive.vectorized.execution.enabled=true; -- 矢量化查询
set hive.exec.orc.zerocopy=true; -- 读取零拷贝
set hive.optimize.skewjoin.compiletime=true; -- 开启编译期优化
set hive.optimize.skewjoin=true; -- 开启运行期数据倾斜的处理 默认值为false
set hive.skewjoin.key=100000;
set hive.optimize.union.remove=true; -- union优化
set hive.groupby.skewindata=true; -- 开启负载均衡优化
set hive.optimize.correlation=true; --开启关联优化器