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 3DWS层实现
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 data4.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; --开启关联优化器