day06 新零售课程笔记

1. DWB层实现

今日内容:

1- DWB层实现操作:
- 订单业务宽表
- 店铺业务宽表
- 商品业务宽表
2- HIVE的索引相关内容

1. DWB层实现
DWB层作用: 维度退化操作(降维) ```properties 指的将各个维度表或者事实表的核心字段全部汇聚成一个表操作, 形成一个宽表, 这样在后续进行统计分析的时候, 只需要操作这个合并后大宽表数据即可

对于当前项目, 此处的合并宽表过程, 与主题是无关(没有直接关系的), 更多是基于业务模块, 形成业务模块的一些宽表

 对于一些其他的项目. 可能从一开始就是直接对主题进行处理, 所以在一些其他的项目中, 可能会直接基于主题形成主题相关的宽表


创建DWB层的库:
```sql
-- 1 创建DWB层的库
create database if not exists bj59_yp_dwb_jiale;

1.1 订单明细宽表

涉及需求合并的表

核心点: 
    涉及表:  
        订单事实表: 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
   表与表之间的关系: 
       订单事实表 -- 订单组表:  订单事实表.id =  订单组表.order_id
       订单组表 -- 订单组支付信息表:  订单组表.group_id = 订单组支付信息表.group_id
       订单事实表 -- 商品快照表:  订单事实表.id = 商品快照表.order_id
       订单事实表 -- 订单副表: 订单事实表.id = 订单副表.id
       订单事实表 -- 结算信息表 :  订单事实表.id =  结算信息表.order_id
       订单事实表 -- 退款订单表:   订单事实表.id =  退款订单表.order_id
       订单事实表 -- 订单评价表:  订单事实表.id =  订单评价表.order_id
       订单事实表 -- 订单配送表:   订单事实表.id =  订单配送表.shop_order_id and 订单配送表.dispatcher_order_type = 1
       注意: 如果是拉链表, 需要筛选出所有的有效数据
   在合并的时候, 每个表都需要那些字段呢?  
       标准的状态下:  
           以主表为主要的表,如果多个表之间有相同的字段, 优先使用主表的字段 + 主表中不存在的字段
           如果发现其他表中, 有一些字段, 我们后期完全用不上的, 可以在合并宽表的时候, 之间舍弃掉, 如果不确定, 可以保留下来

创建订单表的目标表:

DROP TABLE if EXISTS bj59_yp_dwb_jiale.dwb_order_detail; 
CREATE TABLE bj59_yp_dwb_jiale.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');

订单明细宽表 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; 
--分桶 
set hive.enforce.bucketing=true; 
set hive.enforce.sorting=true;
-- map join 优化操作
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;
insert overwrite table bj59_yp_dwb_jiale.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, 
    -- 订单副表
    ad.order_amount,
    ad.discount_amount,
    ad.goods_amount,
    ad.is_delivery,
    ad.buyer_notes,
    ad.pay_time,
    ad.receive_time, 
    ad.delivery_begin_time,
    ad.arrive_store_time,
    ad.arrive_time,
    ad.create_user,
    ad.create_time,
    ad.update_user,
    ad.update_time,
    ad.is_valid,
    -- 订单组表:
    g.group_id,
    g.is_pay,
    -- 订单组支付表
    p.order_pay_amount as group_pay_amount,
    -- 退款表
    r.id as  refund_id,
    r.apply_date,
    r.refund_reason,
    r.refund_amount,
    r.refund_state,
    -- 结算表
    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,
    -- 订单评价表
    e.id as evaluation_id,
    e.user_id as evaluation_user_id,
    e.geval_scores,
    e.geval_scores_speed,
    e.geval_scores_service,
    e.geval_isanony,
    e.create_time as evaluation_time,
    -- 订单配送表
    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,
    -- 商品快照
    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,
    substr(o.create_date,1,10) as  dt
from  (select * from bj59_yp_dwd_jiale.fact_shop_order where end_date = '9999-99-99') o 
    left join bj59_yp_dwd_jiale.fact_shop_order_group g on o.id = g.order_id and g.end_date = '9999-99-99'
    left join bj59_yp_dwd_jiale.fact_order_pay p  on g.group_id = p.group_id
    left join bj59_yp_dwd_jiale.fact_shop_order_goods_details d on o.id = d.order_id and d.end_date = '9999-99-99'
    left join bj59_yp_dwd_jiale.fact_shop_order_address_detail ad on o.id = ad.id and ad.end_date  = '9999-99-99'
    left join bj59_yp_dwd_jiale.fact_order_settle s on o.id = s.order_id and s.end_date  = '9999-99-99'
    left join bj59_yp_dwd_jiale.fact_refund_order r on o.id = r.order_id and r.end_date  = '9999-99-99'
    left join bj59_yp_dwd_jiale.fact_goods_evaluation e on o.id = e.order_id and e.is_valid = 1
    left join bj59_yp_dwd_jiale.fact_order_delievery_item i on o.id = i.shop_order_id and i.dispatcher_order_type = 1 and i.is_valid = 1 and i.end_date = '9999-99-99';

1.2 店铺明细宽表

涉及表:

涉及的表: 
    店铺表: dim_store    
    商圈表: dim_trade_area
    地址信息表: dim_location
    行政区域表: dim_district

表与表之间的关系: 
  店铺表: dim_store 和  商圈表: dim_trade_area : 
      店铺表.trade_area_id = 商圈表.id
  店铺表: dim_store 和 地址信息表: dim_location :
      店铺表.id  = 地址信息表.correlation_id  and  地址信息表.type = 2
  地址信息表: dim_location 和 行政区域表:dim_district
      地址信息表.adcode = 行政区域表.id   关联后, 我们可以查看到对应县/区信息

  行政区域表自关联, 实现得到县/区 对应 城市 和 省份信息
      行政区域表.pid =  行政区域表.id


例如: 当我们已知县区编号好, 如何查询出对应的城市和省份信息: 
  select * from
    (select  * from yipin.t_district  where id = '130321') as t1
    join yipin.t_district t2 on  t1.pid = t2.id
    join yipin.t_district t3 on  t2.pid = t3.id;

构建目标表:

DROP TABLE if EXISTS bj59_yp_dwb_jiale.dwb_shop_detail;
CREATE TABLE bj59_yp_dwb_jiale.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实现

insert overwrite table bj59_yp_dwb_jiale.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)
from  (select * from bj59_yp_dwd_jiale.dim_store where end_date = '9999-99-99') s  
    left join  bj59_yp_dwd_jiale.dim_trade_area a  on s.trade_area_id  = a.id  and a.end_date = '9999-99-99'
    left join  bj59_yp_dwd_jiale.dim_location l  on s.id = l.correlation_id and l.type = 2 and  l.end_date = '9999-99-99'
    left join  bj59_yp_dwd_jiale.dim_district d1 on l.adcode = d1.id
    left join  bj59_yp_dwd_jiale.dim_district d2 on  d1.pid = d2.id
    left join  bj59_yp_dwd_jiale.dim_district d3 on  d2.pid = d3.id;

1.3 商品明细宽表

涉及表: 
    商品SKU表:  dim_goods 
    商品分类表:  dim_goods_class
    品牌表: dim_brand

表关系:  
    商品SKU表:  dim_goods  和  品牌表: dim_brand 关联:
      商品SKU表.brand_id =  品牌表.id

    商品SKU表:  dim_goods  和  商品分类表:  dim_goods_class 关联
       商品SKU表.store_class_id = 商品分类表.id     -- 正常获取到应该是三级的分类名称, 此时需要获取其二级和一级的分类需要进行自关联(类似于地区表)

    商品分类表:  dim_goods_class 和 商品分类表:  dim_goods_class 自关联   
       t1.parent_id = t2.id

创建目标表

DROP TABLE if EXISTS bj59_yp_dwb_jiale.dwb_goods_detail; 
CREATE TABLE bj59_yp_dwb_jiale.dwb_goods_detail(
  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实现操作

insert overwrite table bj59_yp_dwb_jiale.dwb_goods_detail partition (dt)
select
  -- 商品表
  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 ,
  -- 商品小类 
  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(g.create_time,1,10) as dt

from (select * from bj59_yp_dwd_jiale.dim_goods where  end_date = '9999-99-99') g 
    left join  bj59_yp_dwd_jiale.dim_brand b on  g.brand_id = b.id and b.end_date = '9999-99-99'
    left join bj59_yp_dwd_jiale.dim_goods_class c1 on g.store_class_id = c1.id and c1.end_date ='9999-99-99'
    left join bj59_yp_dwd_jiale.dim_goods_class c2 on c1.parent_id = c2.id and  c2.end_date = '9999-99-99'
    left join bj59_yp_dwd_jiale.dim_goods_class c3 on c2.parent_id = c3.id and  c3.end_date = '9999-99-99';

1. DWB层实现

1.1 订单明细宽表

1.2 店铺明细宽表

1.3 商品明细宽表