devbox
我家自动化
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

离线数仓（八）【DWD 层开发】

作者：我家自动化 | 2024-08-07 15:01:37

赞

踩

dwd

前言

1、DWD 层开发

DWD层设计要点：

（1）DWD层的设计依据是维度建模理论（主体是事务型事实表（选择业务过程 -> 声明粒度 -> 确定维度 -> 确定事实），另外两种周期型快照事实表和累积型事务事实表按需求选择），该层存储维度模型的事实表。

（2）DWD层的数据存储格式为orc列式存储+snappy压缩（和DIM层、DWS层都是一样的）。

（3）DWD层表名的命名规范为dwd_数据域_表名_单分区增量全量标识（inc/full）（划分数据域的目的是为了通过对数据分类使得从业务系统中可以快速的找到我们希望得到的数据；划分数据域的标准是按照业务过程，将若干个业务过程划分到一个数据域里面，其实所谓的划分数据域就是在划分事实表，因为一个业务过程对应一个事实表）。

1.1、交易域加购事务事实表

        加购指的是加入购物车这个业务过程，我们按照设计事务事实表的过程来设计它的表结构：

1.1.1、选择业务过程

        就是加购物车这个行为

1.1.2、声明粒度

        要求尽可能选择最细粒度，声明粒度将来指代的是将来这张表的每一行所代表的内容。这里我们声明的粒度就是：谁+在什么时候+把什么商品加到了购物车

1.1.3、确认维度

        我们首先能想到的就是用户、时间和商品，至于其它维度我们在设计事实表的时候尽可能多的考虑到，避免后期一些指标无法分析。

        确认了维度，我们就确认了我们这张事务事实表的维度外键。

1.1.4、确认事实

        确认事实就是确认事实表的度量值，对于这里的加购操作度量值主要就是加购的商品件数。

1.1.5、建表语句

        这里我们的表名由几部分组成：dwd 代表这张表是 dwd 层的事实表；trade 代表数据域是交易域；cart_add 代表这张事实表的业务过程是加购操作；inc 因为这张表是事务型事实表所以我们一般都是增量表。

        再看字段：其中 id 选取的是我们业务系统中 cart_info 的 id；user_id sku_id date_id 是我们的维度外键；sku_num 是我们的度量值。剩下的 create_time 是具体的加购时间，精确到秒，它区别于维度外键 date_id，date_id 是日期，只能精确到哪一天；source_id source_type_code source_type_name 这些字段都是来自我们业务系统的，我们之前说 DIM 层和 DWD 层的维度表和事实表都是业务驱动的，所以它们各自表的设计字段的选择来源于我们的业务系统中表，这里的加购事务事实表就是对应我们业务系统当中的 cart_info：

通过查询字典表可以看到：

不同的 source_type_code 代表不同类型的加购操作（用户通过哪种来源类型来加购的，比如用户通过广告来加购的，那通过哪个广告呢，所以这里我们还指定了 source_id），这些字段都算是维度，但是我们并没有对这些属性做一个维度表，而是直接放到事实表里（也就是维度退化，而维度退化要求不能只有编码，一般都是编码和文字描述共存的；所以这里我们保留了来源类型编码和来源类型名称）。

  1. DROP TABLE IF EXISTS dwd_trade_cart_add_inc;
  2. CREATE EXTERNAL TABLE dwd_trade_cart_add_inc
  3. (
  4.     `id`               STRING COMMENT '编号',
  5.     `user_id`          STRING COMMENT '用户id',
  6.     `sku_id`           STRING COMMENT '商品id',
  7.     `date_id`          STRING COMMENT '时间id',
  8.     `create_time`      STRING COMMENT '加购时间',
  9.     `source_id`        STRING COMMENT '来源类型ID',
  10.     `source_type_code` STRING COMMENT '来源类型编码',
  11.     `source_type_name` STRING COMMENT '来源类型名称',
  12.     `sku_num`          BIGINT COMMENT '加购物车件数'
  13. ) COMMENT '交易域加购物车事务事实表'
  14.     PARTITIONED BY (`dt` STRING)
  15.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  16.     STORED AS ORC
  17.     LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc/'
  18.     TBLPROPERTIES ('orc.compress' = 'snappy');

1.1.6、数据流向

        现在我们表设计好了下一步就是数据源从哪来（从 ods 层的哪些表来），以及我们怎么向这张表进行装载（insert + select），这就需要我们了解一下这张表的不同维度的数据应该来自于哪些表。

        我们首先需要了解业务系统中 order_info 这张表：

        其实，对于加购这个过程无非只有两种情况：1、原本购物车没有这个商品然后进行加购，这是对数据库来说是一个 insert 操作；2、原本购物车就有这个商品然后加购，这对数据库来说是一个 update 操作。显式的变化就是 sku_num 的值的变化。

        这样我们就搞清楚了加购这个业务过程，会对 order_info（或者可以说是 ods_order_info_inc） 产生影响，产生的影响就是 sku_num 字段的值发生变化。

现在我们不仅要知道数据从张表来，还得知道数据从具体哪个分区流向哪个分区：

首日数据：

        所有的增量表都要在首日做一个全量同步，这里我们的加购表当前存储的就是首日的全量数据，我们需要对字段 create_time 做一个动态分区来把不同时间的加购信息放到不同的分区：

每日数据：

        解决了首日全量数据的分区问题，我们之后 ods 层每天的分区中存储的就是 Maxwell 监听的每日的增量数据，所以这些都是加购信息，我们直接存储当当日分区即可：

1.1.7、首日装载语句

  1. -- 动态分区需要设置非严格模式
  2. set hive.exec.dynamic.partition.mode=nonstrict;
  3. insert overwrite table dwd_trade_cart_add_inc partition (dt)
  4. select
  5.     id,
  6.     user_id,
  7.     sku_id,
  8.     date_format(create_time,'yyyy-MM-dd') date_id,
  9.     create_time,
  10.     source_id,
  11.     source_type,
  12.     dic.dic_name,
  13.     sku_num,
  14.     date_format(create_time, 'yyyy-MM-dd')
  15. from
  16. (
  17.     select
  18.         data.id,
  19.         data.user_id,
  20.         data.sku_id,
  21.         data.create_time,
  22.         data.source_id,
  23.         data.source_type,
  24.         data.sku_num
  25.     from ods_cart_info_inc
  26.     where dt = '2020-06-14'
  27.     and type = 'bootstrap-insert'
  28. )ci
  29. left join
  30. (
  31.     select
  32.         dic_code,
  33.         dic_name
  34.     from ods_base_dic_full
  35.     where dt='2020-06-14'
  36.     and parent_code='24'
  37. )dic
  38. on ci.source_type=dic.dic_code;

1.1.8、每日装载语句

        我们比如要装载 06-15 这一天的数据，首先我们需要保证 type 为 insert 或者 update 类型的操作，并且加购后的 sku_num 的值要大于旧的 sku_num 值。

  1. insert overwrite table dwd_trade_cart_add_inc partition(dt='2020-06-15')
  2. select
  3.     id,
  4.     user_id,
  5.     sku_id,
  6.     date_id,
  7.     create_time,
  8.     source_id,
  9.     source_type_code,
  10.     source_type_name,
  11.     sku_num
  12. from
  13. (
  14.     select
  15.         data.id,
  16.         data.user_id,
  17.         data.sku_id,
  18.         date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd') date_id,
  19.         date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd HH:mm:ss') create_time,
  20.         data.source_id,
  21.         data.source_type source_type_code,
  22.         if(type='insert',data.sku_num,data.sku_num-old['sku_num']) sku_num
  23.     from ods_cart_info_inc
  24.     where dt='2020-06-15'
  25.     and (type='insert'
  26.     or(type='update' and old['sku_num'] is not null and data.sku_num>cast(old['sku_num'] as int)))
  27. )cart
  28. left join
  29. (
  30.     select
  31.         dic_code,
  32.         dic_name source_type_name
  33.     from ods_base_dic_full
  34.     where dt='2020-06-15'
  35.     and parent_code='24'
  36. )dic
  37. on cart.source_type_code=dic.dic_code;

        这里在判断是否修改了 order_info 中 sku_num 值的时候我们还可以通过下面的方式来确认修改之前包含该字段：

type='update' and array_contains(map_keys(old),'sku_num')

        这里我们不能使用 create_time 作为加购时间，因为 create_time 是我们创建这个购物车的时间，而不是真正的加购时间，所以我们应该用 ods_cart_info_inc 中的 ts 自动，因为它代表的是订单的变动时间：

        对于不同的操作类型（insert 或 update）sku_num 的值也是不一样的，对于 insert 操作，这个商品是第一次加入购物车，所以 sku_num 就是加购的件数；但是对于 update，sku_num 指的是加购后的件数，所以实际加购的数量=old['sku_num']-data.sku_num 。

        所以只要我们清楚了加购这个业务过程对订单表的影响，装载这张表就很简单了。 

1.2、交易域下单事务事实表

同样分两步：设计表结构（4步），编写装载语句

1.2.1、设计表结构

  1. 选择业务过程：我们选择的是下单这个业务过程
  2. 声明粒度：要求尽可能最细粒度，所以这里的粒度应该是一个订单中的一个商品项而不是一整个订单，所以我们也就可以想到这张事实表对应业务系统中的 order_detail 表。
  3. 确认维度：关于下单操作，我们能想到的维度比如：时间、用户、商品、地区、活动和优惠券。以及下单方式，我们之前说过：如果某些维度表的维度属性很少（比如支付方式表没有必要去单独创建一个维度表，因为它就一个支付方式字段），则可不创建该维度表，而把该表的维度属性直接增加到与之相关的事实表中对于维度退化。确认维度是个灵活的过程，它并不取决于我们要分析什么指标，而是取决于我们业务系统能提供什么信息，比如这里的 order_detail 中包含了下单地区、该订单参与的活动以及使用优惠券的信息才能支持我们确认这样的维度。
  4. 确认事实：也就是确认度量值，这里的度量值可以是：下单件数、下单原始金额、下单的最终金额、活动优惠金额和优惠券优惠金额等。

1.2.2、建表语句

        同样，这里的维度可以分为四部分：

  1. 从业务系统表当中直接拿过来的字段（比如 id、order_id 、create_time、source_id、source_type）
  2. 用作关联维度表的维度外键（user_id、sku_id、province_id、activity_id（算是退化字段，因为它和activity_rule_id都在同一张表，但是activity_rule_id的粒度更细）、activity_rule_id 、coupo_id、date_id等）
  3. 度量指标字段（比如 sku_num、split_xxx ...）
  4. 维度退化字段（比如 activity_id、source_type_code、source_id、source_type_name 等）
  1. DROP TABLE IF EXISTS dwd_trade_order_detail_inc;
  2. CREATE EXTERNAL TABLE dwd_trade_order_detail_inc
  3. (
  4.     `id`                    STRING COMMENT '编号',
  5.     `order_id`              STRING COMMENT '订单id',
  6.     `user_id`               STRING COMMENT '用户id',
  7.     `sku_id`                STRING COMMENT '商品id',
  8.     `province_id`           STRING COMMENT '省份id',
  9.     `activity_id`           STRING COMMENT '参与活动规则id',
  10.     `activity_rule_id`      STRING COMMENT '参与活动规则id',
  11.     `coupon_id`             STRING COMMENT '使用优惠券id',
  12.     `date_id`               STRING COMMENT '下单日期id',
  13.     `create_time`           STRING COMMENT '下单时间',
  14.     `source_id`             STRING COMMENT '来源编号',
  15.     `source_type_code`      STRING COMMENT '来源类型编码',
  16.     `source_type_name`      STRING COMMENT '来源类型名称',
  17.     `sku_num`               BIGINT COMMENT '商品数量',
  18.     `split_original_amount` DECIMAL(16, 2) COMMENT '原始价格',
  19.     `split_activity_amount` DECIMAL(16, 2) COMMENT '活动优惠分摊',
  20.     `split_coupon_amount`   DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
  21.     `split_total_amount`    DECIMAL(16, 2) COMMENT '最终价格分摊'
  22. ) COMMENT '交易域下单明细事务事实表'
  23.     PARTITIONED BY (`dt` STRING)
  24.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  25.     STORED AS ORC
  26.     LOCATION '/warehouse/gmall/dwd/dwd_trade_order_detail_inc/'
  27.     TBLPROPERTIES ('orc.compress' = 'snappy');

1.2.3、数据流向

        在数据装载之前我们同样需要了解数据的流向，下单这个业务过程会对哪些表产生影响我们就从哪个表去获取数据。

       这里的下单这个操作会影响到 order_info（会插入一条订单数据）、order_detail（会插入多条数据，取决于订单的中的商品数量）、order_detail_coupon（用户使用优惠券就会在这张表中插入数据）和 order_detail_activity（用户参与活动就会往这张表插入数据）。

        相比较上面的加购操作，这里的下单操作虽然涉及到的表更多，但是它都是插入（insert）操作，所以事实上装载的逻辑要比加购简单。同时我们依然要区分首日和每日的数据。

1.2.4、首日数据装载

逻辑还是比较简单的，就是把 order_detail 作为主表不断进行 left join ：

  1. set hive.exec.dynamic.partition.mode=nonstrict;
  2. insert overwrite table dwd_trade_order_detail_inc partition (dt)
  3. select
  4.     od.id,
  5.     order_id,
  6.     user_id,
  7.     sku_id,
  8.     province_id,
  9.     activity_id,
  10.     activity_rule_id,
  11.     coupon_id,
  12.     date_format(create_time, 'yyyy-MM-dd') date_id,
  13.     create_time,
  14.     source_id,
  15.     source_type,
  16.     dic_name,
  17.     sku_num,
  18.     split_original_amount,
  19.     split_activity_amount,
  20.     split_coupon_amount,
  21.     split_total_amount,
  22.     date_format(create_time,'yyyy-MM-dd')
  23. from
  24. (
  25.     select
  26.         data.id,
  27.         data.order_id,
  28.         data.sku_id,
  29.         data.create_time,
  30.         data.source_id,
  31.         data.source_type,
  32.         data.sku_num,
  33.         data.sku_num * data.order_price split_original_amount,
  34.         data.split_total_amount,
  35.         data.split_activity_amount,
  36.         data.split_coupon_amount
  37.     from ods_order_detail_inc
  38.     where dt = '2020-06-14'
  39.     and type = 'bootstrap-insert'
  40. ) od
  41. left join
  42. (
  43.     select
  44.         data.id,
  45.         data.user_id,
  46.         data.province_id
  47.     from ods_order_info_inc
  48.     where dt = '2020-06-14'
  49.     and type = 'bootstrap-insert'
  50. ) oi
  51. on od.order_id = oi.id
  52. left join
  53. (
  54.     select
  55.         data.order_detail_id,
  56.         data.activity_id,
  57.         data.activity_rule_id
  58.     from ods_order_detail_activity_inc
  59.     where dt = '2020-06-14'
  60.     and type = 'bootstrap-insert'
  61. ) act
  62. on od.id = act.order_detail_id
  63. left join
  64. (
  65.     select
  66.         data.order_detail_id,
  67.         data.coupon_id
  68.     from ods_order_detail_coupon_inc
  69.     where dt = '2020-06-14'
  70.     and type = 'bootstrap-insert'
  71. ) cou
  72. on od.id = cou.order_detail_id
  73. left join
  74. (
  75.     select
  76.         dic_code,
  77.         dic_name
  78.     from ods_base_dic_full
  79.     where dt='2020-06-14'
  80.     and parent_code='24'
  81. )dic
  82. on od.source_type=dic.dic_code;

1.2.5、每日装载语句 

         对于之后的每日装载，我们只需要确保每张表的 type 为 insert、dt = 当天即可。

  1. insert overwrite table dwd_trade_order_detail_inc partition (dt='2020-06-15')
  2. select
  3.     od.id,
  4.     order_id,
  5.     user_id,
  6.     sku_id,
  7.     province_id,
  8.     activity_id,
  9.     activity_rule_id,
  10.     coupon_id,
  11.     date_id,
  12.     create_time,
  13.     source_id,
  14.     source_type,
  15.     dic_name,
  16.     sku_num,
  17.     split_original_amount,
  18.     split_activity_amount,
  19.     split_coupon_amount,
  20.     split_total_amount
  21. from
  22. (
  23.     select
  24.         data.id,
  25.         data.order_id,
  26.         data.sku_id,
  27.         date_format(data.create_time, 'yyyy-MM-dd') date_id,
  28.         data.create_time,
  29.         data.source_id,
  30.         data.source_type,
  31.         data.sku_num,
  32.         data.sku_num * data.order_price split_original_amount,
  33.         data.split_total_amount,
  34.         data.split_activity_amount,
  35.         data.split_coupon_amount
  36.     from ods_order_detail_inc
  37.     where dt = '2020-06-15'
  38.     and type = 'insert'
  39. ) od
  40. left join
  41. (
  42.     select
  43.         data.id,
  44.         data.user_id,
  45.         data.province_id
  46.     from ods_order_info_inc
  47.     where dt = '2020-06-15'
  48.     and type = 'insert'
  49. ) oi
  50. on od.order_id = oi.id
  51. left join
  52. (
  53.     select
  54.         data.order_detail_id,
  55.         data.activity_id,
  56.         data.activity_rule_id
  57.     from ods_order_detail_activity_inc
  58.     where dt = '2020-06-15'
  59.     and type = 'insert'
  60. ) act
  61. on od.id = act.order_detail_id
  62. left join
  63. (
  64.     select
  65.         data.order_detail_id,
  66.         data.coupon_id
  67.     from ods_order_detail_coupon_inc
  68.     where dt = '2020-06-15'
  69.     and type = 'insert'
  70. ) cou
  71. on od.id = cou.order_detail_id
  72. left join
  73. (
  74.     select
  75.         dic_code,
  76.         dic_name
  77.     from ods_base_dic_full
  78.     where dt='2020-06-15'
  79.     and parent_code='24'
  80. )dic
  81. on od.source_type=dic.dic_code;

1.3、交易域取消订单事务事实表

注意是取消订单，不是退单，还没完成支付呢。

1.3.1、设计表结构

依然是那4个步骤：

  1. 选择业务过程：取消订单
  2. 声明粒度：谁+在什么时候+取消了哪个商品
  3. 确认维度：时间、用户、商品、地区、活动、优惠券
  4. 确认事实：取消的商品件数、取消的金额、下单的原始金额、最终下单金额、活动优惠金额、优惠券金额

可以看到，取消订单表的大部分维度和下单表是一致的，只是语义不一样而已。

1.3.2、建表语句

  1. DROP TABLE IF EXISTS dwd_trade_cancel_detail_inc;
  2. CREATE EXTERNAL TABLE dwd_trade_cancel_detail_inc
  3. (
  4.     `id`                    STRING COMMENT '编号',
  5.     `order_id`              STRING COMMENT '订单id',
  6.     `user_id`               STRING COMMENT '用户id',
  7.     `sku_id`                STRING COMMENT '商品id',
  8.     `province_id`           STRING COMMENT '省份id',
  9.     `activity_id`           STRING COMMENT '参与活动规则id',
  10.     `activity_rule_id`      STRING COMMENT '参与活动规则id',
  11.     `coupon_id`             STRING COMMENT '使用优惠券id',
  12.     `date_id`               STRING COMMENT '取消订单日期id',
  13.     `cancel_time`           STRING COMMENT '取消订单时间',
  14.     `source_id`             STRING COMMENT '来源编号',
  15.     `source_type_code`      STRING COMMENT '来源类型编码',
  16.     `source_type_name`      STRING COMMENT '来源类型名称',
  17.     `sku_num`               BIGINT COMMENT '商品数量',
  18.     `split_original_amount` DECIMAL(16, 2) COMMENT '原始价格',
  19.     `split_activity_amount` DECIMAL(16, 2) COMMENT '活动优惠分摊',
  20.     `split_coupon_amount`   DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
  21.     `split_total_amount`    DECIMAL(16, 2) COMMENT '最终价格分摊'
  22. ) COMMENT '交易域取消订单明细事务事实表'
  23.     PARTITIONED BY (`dt` STRING)
  24.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  25.     STORED AS ORC
  26.     LOCATION '/warehouse/gmall/dwd/dwd_trade_cancel_detail_inc/'
  27.     TBLPROPERTIES ('orc.compress' = 'snappy');

1.3.3、 数据流向

        我们需要知道取消订单会对哪些表产生影响，事实上，只会对 order_info 产生影响（改变 order_info 的 order_status 字段 ），同样，我们的首日数据（存在 ods_order_info_inc 当中）当中也存在一些取消订单的数据需要我们进行分区处理。

不同 order_status 对应的状态：

        这里需要注意的是：对于已取消、已完成、退款完成这些操作表示的都是最终的操作，这些订单的状态不会再发生变化；但是对于未支付、已支付、退款中这些订单状态都还可能会发送变化。所以当我们在找支付成功的订单时，不仅要考虑订单状态是已支付状态，还有退款完成、已支付这些状态也经历过已支付状态。

1.3.4、首日数据装载

  1. set hive.exec.dynamic.partition.mode=nonstrict;
  2. insert overwrite table dwd_trade_cancel_detail_inc partition (dt)
  3. select
  4.     od.id,
  5.     order_id,
  6.     user_id,
  7.     sku_id,
  8.     province_id,
  9.     activity_id,
  10.     activity_rule_id,
  11.     coupon_id,
  12.     date_format(canel_time,'yyyy-MM-dd') date_id,
  13.     canel_time,
  14.     source_id,
  15.     source_type,
  16.     dic_name,
  17.     sku_num,
  18.     split_original_amount,
  19.     split_activity_amount,
  20.     split_coupon_amount,
  21.     split_total_amount,
  22.     date_format(canel_time,'yyyy-MM-dd')
  23. from
  24. (
  25.     select
  26.         data.id,
  27.         data.order_id,
  28.         data.sku_id,
  29.         data.source_id,
  30.         data.source_type,
  31.         data.sku_num,
  32.         data.sku_num * data.order_price split_original_amount,
  33.         data.split_total_amount,
  34.         data.split_activity_amount,
  35.         data.split_coupon_amount
  36.     from ods_order_detail_inc
  37.     where dt = '2020-06-14'
  38.     and type = 'bootstrap-insert'
  39. ) od
  40. join
  41. (
  42.     select
  43.         data.id,
  44.         data.user_id,
  45.         data.province_id,
  46.         data.operate_time canel_time
  47.     from ods_order_info_inc
  48.     where dt = '2020-06-14'
  49.     and type = 'bootstrap-insert'
  50.     and data.order_status='1003'
  51. ) oi
  52. on od.order_id = oi.id
  53. left join
  54. (
  55.     select
  56.         data.order_detail_id,
  57.         data.activity_id,
  58.         data.activity_rule_id
  59.     from ods_order_detail_activity_inc
  60.     where dt = '2020-06-14'
  61.     and type = 'bootstrap-insert'
  62. ) act
  63. on od.id = act.order_detail_id
  64. left join
  65. (
  66.     select
  67.         data.order_detail_id,
  68.         data.coupon_id
  69.     from ods_order_detail_coupon_inc
  70.     where dt = '2020-06-14'
  71.     and type = 'bootstrap-insert'
  72. ) cou
  73. on od.id = cou.order_detail_id
  74. left join
  75. (
  76.     select
  77.         dic_code,
  78.         dic_name
  79.     from ods_base_dic_full
  80.     where dt='2020-06-14'
  81.     and parent_code='24'
  82. )dic
  83. on od.source_type=dic.dic_code;

1.3.5、每日数据装载

        这里需要注意：我们取消的订单可能是前一天下的，所以我们从当天的 ods_order_detail_inc 中是查不到的，所以我们查询的时候还需要从当天的前一天订单明细中也查一份，而且为了和 order_detail 进行 join 关联（防止关联不上），我们的其它表也需要查询前一天的数据。

  1. insert overwrite table dwd_trade_cancel_detail_inc partition (dt='2020-06-15')
  2. select
  3.     od.id,
  4.     order_id,
  5.     user_id,
  6.     sku_id,
  7.     province_id,
  8.     activity_id,
  9.     activity_rule_id,
  10.     coupon_id,
  11.     date_format(canel_time,'yyyy-MM-dd') date_id,
  12.     canel_time,
  13.     source_id,
  14.     source_type,
  15.     dic_name,
  16.     sku_num,
  17.     split_original_amount,
  18.     split_activity_amount,
  19.     split_coupon_amount,
  20.     split_total_amount
  21. from
  22. (
  23.     select
  24.         data.id,
  25.         data.order_id,
  26.         data.sku_id,
  27.         data.source_id,
  28.         data.source_type,
  29.         data.sku_num,
  30.         data.sku_num * data.order_price split_original_amount,
  31.         data.split_total_amount,
  32.         data.split_activity_amount,
  33.         data.split_coupon_amount
  34.     from ods_order_detail_inc -- 取消订单不会影响订单明细表
  35.     where (dt='2020-06-15' or dt=date_add('2020-06-15',-1))
  36.     and (type = 'insert' or type= 'bootstrap-insert') -- 过了第2天就可以不写bootstrap-insert
  37. ) od
  38. join
  39. (
  40.     select
  41.         data.id,
  42.         data.user_id,
  43.         data.province_id,
  44.         data.operate_time canel_time
  45.     from ods_order_info_inc
  46.     where dt = '2020-06-15'
  47.     and type = 'update'
  48.     and data.order_status='1003'
  49.     and array_contains(map_keys(old),'order_status')
  50. ) oi
  51. on order_id = oi.id
  52. left join
  53. (
  54.     select
  55.         data.order_detail_id,
  56.         data.activity_id,
  57.         data.activity_rule_id
  58.     from ods_order_detail_activity_inc
  59.     where (dt='2020-06-15' or dt=date_add('2020-06-15',-1))
  60.     and (type = 'insert' or type= 'bootstrap-insert')
  61. ) act
  62. on od.id = act.order_detail_id
  63. left join
  64. (
  65.     select
  66.         data.order_detail_id,
  67.         data.coupon_id
  68.     from ods_order_detail_coupon_inc
  69.     where (dt='2020-06-15' or dt=date_add('2020-06-15',-1))
  70.     and (type = 'insert' or type= 'bootstrap-insert')
  71. ) cou
  72. on od.id = cou.order_detail_id
  73. left join
  74. (
  75.     select
  76.         dic_code,
  77.         dic_name
  78.     from ods_base_dic_full
  79.     where dt='2020-06-15'
  80.     and parent_code='24'
  81. )dic
  82. on od.source_type=dic.dic_code;

1.4、交易域支付成功事务事实表

        我们要求事实表对应的业务过程必须是原子操作的，也就是不可再切分的，所以这里说的是支付成功这个业务过程，而不是支付这个行为，因为支付可能成功、可能失败。

1.4.1、设计表结构

  1. 选择业务过程：支付成功
  2. 声明粒度：谁+什么时候+成功支付了哪个商品
  3. 确认维度：时间、用户、商品、地区、活动、优惠券、支付方式（维度退化）
  4. 确认事实：支付件数、支付金额、最终支付金额、活动优惠金额、优惠券金额

1.4.2、建表语句

        同样是维度外键+度量值+退化字段： 
  1. DROP TABLE IF EXISTS dwd_trade_pay_detail_suc_inc;
  2. CREATE EXTERNAL TABLE dwd_trade_pay_detail_suc_inc
  3. (
  4.     `id`                    STRING COMMENT '编号',
  5.     `order_id`              STRING COMMENT '订单id',
  6.     `user_id`               STRING COMMENT '用户id',
  7.     `sku_id`                STRING COMMENT '商品id',
  8.     `province_id`           STRING COMMENT '省份id',
  9.     `activity_id`           STRING COMMENT '参与活动规则id',
  10.     `activity_rule_id`      STRING COMMENT '参与活动规则id',
  11.     `coupon_id`             STRING COMMENT '使用优惠券id',
  12.     `payment_type_code`     STRING COMMENT '支付类型编码',
  13.     `payment_type_name`     STRING COMMENT '支付类型名称',
  14.     `date_id`               STRING COMMENT '支付日期id',
  15.     `callback_time`         STRING COMMENT '支付成功时间',
  16.     `source_id`             STRING COMMENT '来源编号',
  17.     `source_type_code`      STRING COMMENT '来源类型编码',
  18.     `source_type_name`      STRING COMMENT '来源类型名称',
  19.     `sku_num`               BIGINT COMMENT '商品数量',
  20.     `split_original_amount` DECIMAL(16, 2) COMMENT '应支付原始金额',
  21.     `split_activity_amount` DECIMAL(16, 2) COMMENT '支付活动优惠分摊',
  22.     `split_coupon_amount`   DECIMAL(16, 2) COMMENT '支付优惠券优惠分摊',
  23.     `split_payment_amount`  DECIMAL(16, 2) COMMENT '支付金额'
  24. ) COMMENT '交易域成功支付事务事实表'
  25.     PARTITIONED BY (`dt` STRING)
  26.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  27.     STORED AS ORC
  28.     LOCATION '/warehouse/gmall/dwd/dwd_trade_pay_detail_suc_inc/'
  29.     TBLPROPERTIES ('orc.compress' = 'snappy');

1.4.3、数据流向

        支付成功会影响哪些表？其实只会影响一张表：当支付成功后 ，order_info 的 order_status 字段就会变成 1002，payment_info 的 order_status 字段会变成 1602 。

        事实表要求我们的粒度是最小的，所以我们需要找到支付成功的订单明细，而不是订单信息。所以我们需要先从 order_info 中找到支付成功的订单，然后去和 order_detail 关联得到该订单的所有商品 ...

        尽管支付成功后 order_info 的 order_status 字段会变成 1002，但是我们并不能把它当做过滤条件，因为它并不是一个最终状态，我们应该去 payment_info 去找更合适。

        当我们点击支付按钮的时候，payment_info 中就会插入一条数据，此时的 payment_status 值为 1601 （待支付状态），create_time 也会插入当前时间；但是 callback_time 字段为空，callback_content 字段同样为空。当支付成功的时候， payment_status 值变为 1602 （支付成功状态）， callback_time 字段为支付成功的时间，callback_content 字段为回调内容。

        所以当我们每日装载数据的时候，就需要从 ods_payment_info_inc 的当日分区中过滤 type 为 update 的数据（首日装载另做判断，因为首日装载都是 bootstrap-insert ，我们需要判断支 payment_status ）。

1.4.4、首日装载语句

  1. insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt)
  2. select
  3.     od.id,
  4.     od.order_id,
  5.     user_id,
  6.     sku_id,
  7.     province_id,
  8.     activity_id,
  9.     activity_rule_id,
  10.     coupon_id,
  11.     payment_type,
  12.     pay_dic.dic_name,
  13.     date_format(callback_time,'yyyy-MM-dd') date_id,
  14.     callback_time,
  15.     source_id,
  16.     source_type,
  17.     src_dic.dic_name,
  18.     sku_num,
  19.     split_original_amount,
  20.     split_activity_amount,
  21.     split_coupon_amount,
  22.     split_total_amount,
  23.     date_format(callback_time,'yyyy-MM-dd')
  24. from
  25. (
  26.     select
  27.         data.id,
  28.         data.order_id,
  29.         data.sku_id,
  30.         data.source_id,
  31.         data.source_type,
  32.         data.sku_num,
  33.         data.sku_num * data.order_price split_original_amount,
  34.         data.split_total_amount,
  35.         data.split_activity_amount,
  36.         data.split_coupon_amount
  37.     from ods_order_detail_inc
  38.     where dt = '2020-06-14'
  39.     and type = 'bootstrap-insert'
  40. ) od
  41. join
  42. (
  43.     select
  44.         data.user_id,
  45.         data.order_id,
  46.         data.payment_type,
  47.         data.callback_time
  48.     from ods_payment_info_inc
  49.     where dt='2020-06-14'
  50.     and type='bootstrap-insert'
  51.     and data.payment_status='1602'
  52. ) pi
  53. on od.order_id=pi.order_id
  54. left join
  55. (
  56.     select
  57.         data.id,
  58.         data.province_id
  59.     from ods_order_info_inc
  60.     where dt = '2020-06-14'
  61.     and type = 'bootstrap-insert'
  62. ) oi
  63. on od.order_id = oi.id
  64. left join
  65. (
  66.     select
  67.         data.order_detail_id,
  68.         data.activity_id,
  69.         data.activity_rule_id
  70.     from ods_order_detail_activity_inc
  71.     where dt = '2020-06-14'
  72.     and type = 'bootstrap-insert'
  73. ) act
  74. on od.id = act.order_detail_id
  75. left join
  76. (
  77.     select
  78.         data.order_detail_id,
  79.         data.coupon_id
  80.     from ods_order_detail_coupon_inc
  81.     where dt = '2020-06-14'
  82.     and type = 'bootstrap-insert'
  83. ) cou
  84. on od.id = cou.order_detail_id
  85. left join
  86. (
  87.     select
  88.         dic_code,
  89.         dic_name
  90.     from ods_base_dic_full
  91.     where dt='2020-06-14'
  92.     and parent_code='11'
  93. ) pay_dic
  94. on pi.payment_type=pay_dic.dic_code
  95. left join
  96. (
  97.     select
  98.         dic_code,
  99.         dic_name
  100.     from ods_base_dic_full
  101.     where dt='2020-06-14'
  102.     and parent_code='24'
  103. )src_dic
  104. on od.source_type=src_dic.dic_code;

1.4.5、每日装载语句

        直接从 payment_info 中过滤出 type=update 的记录，然后在关联 oder_detail  的时候需要考虑不只是获取当日分区的订单明细，因为可能今天的支付成功订单是昨天创建的，所以需要从两个分区（当日和前一天）获取订单明细数据。在关联 order_info 的时候不需要获取前一天的分区数据，因为只要 payment_info 发生变化，order_info 的 order_status 也会发生变化，所以只需要过滤出 ods_order_info_inc 中的 type=update 的数据。 
  1. insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt='2020-06-15')
  2. select
  3.     od.id,
  4.     od.order_id,
  5.     user_id,
  6.     sku_id,
  7.     province_id,
  8.     activity_id,
  9.     activity_rule_id,
  10.     coupon_id,
  11.     payment_type,
  12.     pay_dic.dic_name,
  13.     date_format(callback_time,'yyyy-MM-dd') date_id,
  14.     callback_time,
  15.     source_id,
  16.     source_type,
  17.     src_dic.dic_name,
  18.     sku_num,
  19.     split_original_amount,
  20.     split_activity_amount,
  21.     split_coupon_amount,
  22.     split_total_amount
  23. from
  24. (
  25.     select
  26.         data.id,
  27.         data.order_id,
  28.         data.sku_id,
  29.         data.source_id,
  30.         data.source_type,
  31.         data.sku_num,
  32.         data.sku_num * data.order_price split_original_amount,
  33.         data.split_total_amount,
  34.         data.split_activity_amount,
  35.         data.split_coupon_amount
  36.     from ods_order_detail_inc
  37.     where (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))
  38.     and (type = 'insert' or type = 'bootstrap-insert')
  39. ) od
  40. join
  41. (
  42.     select
  43.         data.user_id,
  44.         data.order_id,
  45.         data.payment_type,
  46.         data.callback_time
  47.     from ods_payment_info_inc
  48.     where dt='2020-06-15'
  49.     and type='update'
  50.     and array_contains(map_keys(old),'payment_status')
  51.     and data.payment_status='1602'
  52. ) pi
  53. on od.order_id=pi.order_id
  54. left join
  55. (
  56.     select
  57.         data.id,
  58.         data.province_id
  59.     from ods_order_info_inc
  60.     where (dt = '2020-06-15')
  61.     and (type = 'update')
  62.     and array_contains(map_keys(old),'order_status')
  63.     and order_status='1002'
  64. ) oi
  65. on od.order_id = oi.id
  66. left join
  67. (
  68.     select
  69.         data.order_detail_id,
  70.         data.activity_id,
  71.         data.activity_rule_id
  72.     from ods_order_detail_activity_inc
  73.     where (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))
  74.     and (type = 'insert' or type = 'bootstrap-insert')
  75. ) act
  76. on od.id = act.order_detail_id
  77. left join
  78. (
  79.     select
  80.         data.order_detail_id,
  81.         data.coupon_id
  82.     from ods_order_detail_coupon_inc
  83.     where (dt = '2020-06-15' or dt = date_add('2020-06-15',-1))
  84.     and (type = 'insert' or type = 'bootstrap-insert')
  85. ) cou
  86. on od.id = cou.order_detail_id
  87. left join
  88. (
  89.     select
  90.         dic_code,
  91.         dic_name
  92.     from ods_base_dic_full
  93.     where dt='2020-06-15'
  94.     and parent_code='11'
  95. ) pay_dic
  96. on pi.payment_type=pay_dic.dic_code
  97. left join
  98. (
  99.     select
  100.         dic_code,
  101.         dic_name
  102.     from ods_base_dic_full
  103.     where dt='2020-06-15'
  104.     and parent_code='24'
  105. )src_dic
  106. on od.source_type=src_dic.dic_code;

1.5、交易域退单事务事实表

我们这里只考虑申请退单，不考虑申请退单后卖家怎么处理，退单状态怎么变化。

1.5.1、设计表结构

  1. 选择业务过程：退单
  2. 声明粒度：谁+什么时候+退了哪件商品
  3. 确认维度：时间、用户、商品、退单类型、退单原因类型
  4. 确认事实：退单件数、退单金额

1.5.2、建表语句

  1. DROP TABLE IF EXISTS dwd_trade_order_refund_inc;
  2. CREATE EXTERNAL TABLE dwd_trade_order_refund_inc
  3. (
  4.     `id`                      STRING COMMENT '编号',
  5.     `user_id`                 STRING COMMENT '用户ID',
  6.     `order_id`                STRING COMMENT '订单ID',
  7.     `sku_id`                  STRING COMMENT '商品ID',
  8.     `province_id`             STRING COMMENT '地区ID',
  9.     `date_id`                 STRING COMMENT '日期ID',
  10.     `create_time`             STRING COMMENT '退单时间',
  11.     `refund_type_code`        STRING COMMENT '退单类型编码',
  12.     `refund_type_name`        STRING COMMENT '退单类型名称',
  13.     `refund_reason_type_code` STRING COMMENT '退单原因类型编码',
  14.     `refund_reason_type_name` STRING COMMENT '退单原因类型名称',
  15.     `refund_reason_txt`       STRING COMMENT '退单原因描述',
  16.     `refund_num`              BIGINT COMMENT '退单件数',
  17.     `refund_amount`           DECIMAL(16, 2) COMMENT '退单金额'
  18. ) COMMENT '交易域退单事务事实表'
  19.     PARTITIONED BY (`dt` STRING)
  20.     STORED AS ORC
  21.     LOCATION '/warehouse/gmall/dwd/dwd_trade_order_refund_inc/'
  22.     TBLPROPERTIES ("orc.compress" = "snappy");

1.5.3、数据流向

        同样需要分析退单这个业务过程会对哪些表产生影响：order_info 的 order_status 字段会发生变化（变为 1005 （退款中）），order_refund_info 表中会插入一条数据。

1.5.4、首日装载

  1. insert overwrite table dwd_trade_order_refund_inc partition(dt)
  2. select
  3.     ri.id,
  4.     user_id,
  5.     order_id,
  6.     sku_id,
  7.     province_id,
  8.     date_format(create_time,'yyyy-MM-dd') date_id,
  9.     create_time,
  10.     refund_type,
  11.     type_dic.dic_name,
  12.     refund_reason_type,
  13.     reason_dic.dic_name,
  14.     refund_reason_txt,
  15.     refund_num,
  16.     refund_amount,
  17.     date_format(create_time,'yyyy-MM-dd')
  18. from
  19. (
  20.     select
  21.         data.id,
  22.         data.user_id,
  23.         data.order_id,
  24.         data.sku_id,
  25.         data.refund_type,
  26.         data.refund_num,
  27.         data.refund_amount,
  28.         data.refund_reason_type,
  29.         data.refund_reason_txt,
  30.         data.create_time
  31.     from ods_order_refund_info_inc
  32.     where dt='2020-06-14'
  33.     and type='bootstrap-insert'
  34. )ri
  35. left join
  36. (
  37.     select
  38.         data.id,
  39.         data.province_id
  40.     from ods_order_info_inc    --为了拿到 province_id
  41.     where dt='2020-06-14'
  42.     and type='bootstrap-insert'
  43. )oi
  44. on ri.order_id=oi.id
  45. left join
  46. (
  47.     select
  48.         dic_code,
  49.         dic_name
  50.     from ods_base_dic_full
  51.     where dt='2020-06-14'
  52.     and parent_code = '15'
  53. )type_dic
  54. on ri.refund_type=type_dic.dic_code
  55. left join
  56. (
  57.     select
  58.         dic_code,
  59.         dic_name
  60.     from ods_base_dic_full
  61.     where dt='2020-06-14'
  62.     and parent_code = '13'
  63. )reason_dic
  64. on ri.refund_reason_type=reason_dic.dic_code;

1.5.5、每日装载语句

  1. insert overwrite table dwd_trade_order_refund_inc partition(dt='2020-06-15')
  2. select
  3.     ri.id,
  4.     user_id,
  5.     order_id,
  6.     sku_id,
  7.     province_id,
  8.     date_format(create_time,'yyyy-MM-dd') date_id,
  9.     create_time,
  10.     refund_type,
  11.     type_dic.dic_name,
  12.     refund_reason_type,
  13.     reason_dic.dic_name,
  14.     refund_reason_txt,
  15.     refund_num,
  16.     refund_amount
  17. from
  18. (
  19.     select
  20.         data.id,
  21.         data.user_id,
  22.         data.order_id,
  23.         data.sku_id,
  24.         data.refund_type,
  25.         data.refund_num,
  26.         data.refund_amount,
  27.         data.refund_reason_type,
  28.         data.refund_reason_txt,
  29.         data.create_time
  30.     from ods_order_refund_info_inc
  31.     where dt='2020-06-15'
  32.     and type='insert'
  33. )ri
  34. left join
  35. (
  36.     select
  37.         data.id,
  38.         data.province_id
  39.     from ods_order_info_inc    
  40.     where dt='2020-06-15'
  41.     and type='update'
  42.     and array_contains(map_keys(old),'old_status')
  43.     and order_status='1005'
  44. )oi
  45. on ri.order_id=oi.id
  46. left join
  47. (
  48.     select
  49.         dic_code,
  50.         dic_name
  51.     from ods_base_dic_full
  52.     where dt='2020-06-15'
  53.     and parent_code = '15'
  54. )type_dic
  55. on ri.refund_type=type_dic.dic_code
  56. left join
  57. (
  58.     select
  59.         dic_code,
  60.         dic_name
  61.     from ods_base_dic_full
  62.     where dt='2020-06-15'
  63.     and parent_code = '13'
  64. )reason_dic
  65. on ri.refund_reason_type=reason_dic.dic_code;

1.6、交易域退款成功事务事实表

 1.6.1、设计表结构

  1. 选择业务过程：退款成功
  2. 声明粒度：谁+什么时候+哪件商品退款成功
  3. 确认维度：用户、地区、时间、商品、退款方式
  4. 确认事实：退款件数、退款金额

1.6.2、建表语句

  1. DROP TABLE IF EXISTS dwd_trade_refund_pay_suc_inc;
  2. CREATE EXTERNAL TABLE dwd_trade_refund_pay_suc_inc
  3. (
  4.     `id`                STRING COMMENT '编号',
  5.     `user_id`           STRING COMMENT '用户ID',
  6.     `order_id`          STRING COMMENT '订单编号',
  7.     `sku_id`            STRING COMMENT 'SKU编号',
  8.     `province_id`       STRING COMMENT '地区ID',
  9.     `payment_type_code` STRING COMMENT '支付类型编码',
  10.     `payment_type_name` STRING COMMENT '支付类型名称',
  11.     `date_id`           STRING COMMENT '日期ID',
  12.     `callback_time`     STRING COMMENT '支付成功时间',
  13.     `refund_num`        DECIMAL(16, 2) COMMENT '退款件数',
  14.     `refund_amount`     DECIMAL(16, 2) COMMENT '退款金额'
  15. ) COMMENT '交易域提交退款成功事务事实表'
  16.     PARTITIONED BY (`dt` STRING)
  17.     STORED AS ORC
  18.     LOCATION '/warehouse/gmall/dwd/dwd_trade_refund_pay_suc_inc/'
  19.     TBLPROPERTIES ("orc.compress" = "snappy");

1.6.3、数据流向

        用户退款会对哪些表产生影响：order_info 的 order_status 字段会发生变化（1006），refund_payment 的 redund_status 也会发生变化。

        和上面的退单一样，我们都需要精确到商品，因为退单可能是退订单中的一件或多件商品。

1.6.4、首日装载

  1. insert overwrite table dwd_trade_refund_pay_suc_inc partition(dt)
  2. select
  3.     rp.id,
  4.     user_id,
  5.     rp.order_id,
  6.     rp.sku_id,
  7.     province_id,
  8.     payment_type,
  9.     dic_name,
  10.     date_format(callback_time,'yyyy-MM-dd') date_id,
  11.     callback_time,
  12.     refund_num,
  13.     total_amount,
  14.     date_format(callback_time,'yyyy-MM-dd')
  15. from
  16. (
  17.     select
  18.         data.id,
  19.         data.order_id,
  20.         data.sku_id,
  21.         data.payment_type,
  22.         data.callback_time,
  23.         data.total_amount
  24.     from ods_refund_payment_inc
  25.     where dt='2020-06-14'
  26.     and type = 'bootstrap-insert'
  27.     and data.refund_status='1602'
  28. )rp
  29. left join
  30. (
  31.     select
  32.         data.id,
  33.         data.user_id,
  34.         data.province_id
  35.     from ods_order_info_inc
  36.     where dt='2020-06-14'
  37.     and type='bootstrap-insert'
  38. )oi
  39. on rp.order_id=oi.id
  40. left join
  41. (
  42.     select
  43.         data.order_id,
  44.         data.sku_id,
  45.         data.refund_num
  46.     from ods_order_refund_info_inc
  47.     where dt='2020-06-14'
  48.     and type='bootstrap-insert'
  49. )ri
  50. on rp.order_id=ri.order_id
  51. and rp.sku_id=ri.sku_id    --必须保证同一订单同一商品
  52. left join
  53. (
  54.     select
  55.         dic_code,
  56.         dic_name
  57.     from ods_base_dic_full
  58.     where dt='2020-06-14'
  59.     and parent_code='11'
  60. )dic
  61. on rp.payment_type=dic.dic_code;

1.6.5、每日装载

  1. insert overwrite table dwd_trade_refund_pay_suc_inc partition(dt='2020-06-15')
  2. select
  3.     rp.id,
  4.     user_id,
  5.     rp.order_id,
  6.     rp.sku_id,
  7.     province_id,
  8.     payment_type,
  9.     dic_name,
  10.     date_format(callback_time,'yyyy-MM-dd') date_id,
  11.     callback_time,
  12.     refund_num,
  13.     total_amount
  14. from
  15. (
  16.     select
  17.         data.id,
  18.         data.order_id,
  19.         data.sku_id,
  20.         data.payment_type,
  21.         data.callback_time,
  22.         data.total_amount
  23.     from ods_refund_payment_inc
  24.     where dt='2020-06-15'
  25.     and type = 'update'
  26.     and array_contains(map_keys(old),'refund_status')
  27.     and data.refund_status='1602'
  28. )rp
  29. left join
  30. (
  31.     select
  32.         data.id,
  33.         data.user_id,
  34.         data.province_id
  35.     from ods_order_info_inc
  36.     where dt='2020-06-15'
  37.     and type='update'
  38.     and array_contains(map_keys(old),'order_status')
  39.     and data.order_status='1006'
  40. )oi
  41. on rp.order_id=oi.id
  42. left join
  43. (
  44.     select
  45.         data.order_id,
  46.         data.sku_id,
  47.         data.refund_num
  48.     from ods_order_refund_info_inc
  49.     where dt='2020-06-15'
  50.     and type='update'
  51.     and array_contains(map_keys(old),'refund_status')
  52.     and data.refund_status='0705'
  53. )ri
  54. on rp.order_id=ri.order_id
  55. and rp.sku_id=ri.sku_id
  56. left join
  57. (
  58.     select
  59.         dic_code,
  60.         dic_name
  61.     from ods_base_dic_full
  62.     where dt='2020-06-15'
  63.     and parent_code='11'
  64. )dic
  65. on rp.payment_type=dic.dic_code;

1.7、交易域购物车周期快照表

        所谓周期快照表，区别于我们前面的事务事实表，周期快照表是全量表，一般用来解决存量型指标（比如库存、余额等，这是事务事实表的不足）。

1.7.1、设计表结构

        对于事务型事实表，通常都是一张事实表对应一个业务过程；对于累积快照事实表，一张表对应多个业务过程。对于这里的周期快照表，并没有讨论的意义。因为无法确定它对应几个业务过程。

  1. DROP TABLE IF EXISTS dwd_trade_cart_full;
  2. CREATE EXTERNAL TABLE dwd_trade_cart_full
  3. (
  4.     `id`       STRING COMMENT '编号',
  5.     `user_id`  STRING COMMENT '用户id',
  6.     `sku_id`   STRING COMMENT '商品id',
  7.     `sku_name` STRING COMMENT '商品名称',
  8.     `sku_num`  BIGINT COMMENT '加购物车件数'
  9. ) COMMENT '交易域购物车周期快照事实表'
  10.     PARTITIONED BY (`dt` STRING)
  11.     ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  12.     STORED AS ORC
  13.     LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_full/'
  14.     TBLPROPERTIES ('orc.compress' = 'snappy');

1.7.2、装载语句

        对于购物车表，我们在 ODS 层建了两张表（增量和全量），这里我们选择全量表作为数据来源：

  1. insert overwrite table dwd_trade_cart_full partition(dt='2020-06-14')
  2. select
  3.     id,
  4.     user_id,
  5.     sku_id,
  6.     sku_name,
  7.     sku_num
  8. from ods_cart_info_full
  9. where dt='2020-06-14'
  10. and is_ordered='0';

        这里的 is_ordered 是一个删除标记，当它的值为 1 的时候代表用户已下单，也就意味着从购物车删除了该商品。所以我们每日装载的时候需要注意筛选出 is_ordered = 0 的数据。

1.8、工具域优惠券领取事务事实表

        有关优惠券的业务过程：领券、使用券下单、使用券支付。它们都会对表 coupon_use 产生影响：领券后表 coupon_use 会插入一条新的记录；使用券下单时会对字段 order_id、coupon_status、using_time 都会发生变化；使用券支付时会对字段 coupon_status、used_time 发生变化。

 1.8.1、建表语句

        需要注意，对于优惠券事实表，它并没有一个明显的度量值。虽然我们说事实表是由维度外键和度量值组成的，但规矩也不是死的，这里的度量值是隐含的，也就是一行代表一个优惠券的记录。

  1. DROP TABLE IF EXISTS dwd_tool_coupon_get_inc;
  2. CREATE EXTERNAL TABLE dwd_tool_coupon_get_inc
  3. (
  4.     `id`        STRING COMMENT '编号',
  5.     `coupon_id` STRING COMMENT '优惠券ID',
  6.     `user_id`   STRING COMMENT 'userid',
  7.     `date_id`   STRING COMMENT '日期ID',
  8.     `get_time`  STRING COMMENT '领取时间'
  9. ) COMMENT '优惠券领取事务事实表'
  10.     PARTITIONED BY (`dt` STRING)
  11.     STORED AS ORC
  12.     LOCATION '/warehouse/gmall/dwd/dwd_tool_coupon_get_inc/'
  13.     TBLPROPERTIES ("orc.compress" = "snappy");

1.8.2、首日装载 

  1. insert overwrite table dwd_tool_coupon_get_inc partition(dt)
  2. select
  3.     data.id,
  4.     data.coupon_id,
  5.     data.user_id,
  6.     date_format(data.get_time,'yyyy-MM-dd') date_id,
  7.     data.get_time,
  8.     date_format(data.get_time,'yyyy-MM-dd')
  9. from ods_coupon_use_inc
  10. where dt='2020-06-14'
  11. and type='bootstrap-insert';

1.8.3、每日装载 

        只要是 insert 就一定是领券操作，只要是 update 就是使用券操作。

  1. insert overwrite table dwd_tool_coupon_get_inc partition (dt='2020-06-15')
  2. select
  3.     data.id,
  4.     data.coupon_id,
  5.     data.user_id,
  6.     date_format(data.get_time,'yyyy-MM-dd') date_id,
  7.     data.get_time
  8. from ods_coupon_use_inc
  9. where dt='2020-06-15'
  10. and type='insert';

1.9、工具域优惠券使用(下单)事务事实表

1.9.1、建表语句

  1. DROP TABLE IF EXISTS dwd_tool_coupon_order_inc;
  2. CREATE EXTERNAL TABLE dwd_tool_coupon_order_inc
  3. (
  4.     `id`         STRING COMMENT '编号',
  5.     `coupon_id`  STRING COMMENT '优惠券ID',
  6.     `user_id`    STRING COMMENT 'user_id',
  7.     `order_id`   STRING COMMENT 'order_id',
  8.     `date_id`    STRING COMMENT '日期ID',
  9.     `order_time` STRING COMMENT '使用下单时间'
  10. ) COMMENT '优惠券使用下单事务事实表'
  11.     PARTITIONED BY (`dt` STRING)
  12.     STORED AS ORC
  13.     LOCATION '/warehouse/gmall/dwd/dwd_tool_coupon_order_inc/'
  14.     TBLPROPERTIES ("orc.compress" = "snappy");

1.9.2、首日装载

        根据 used_time 是否为空或者 coupon_status 是否等于 1402 都是可以的。

  1. insert overwrite table dwd_tool_coupon_order_inc partition(dt)
  2. select
  3.     data.id,
  4.     data.coupon_id,
  5.     data.user_id,
  6.     data.order_id,
  7.     date_format(data.using_time,'yyyy-MM-dd') date_id,
  8.     data.using_time,
  9.     date_format(data.using_time,'yyyy-MM-dd')
  10. from ods_coupon_use_inc
  11. where dt='2020-06-14'
  12. and type='bootstrap-insert'
  13. and data.using_time is not null;

1.9.3、每日装载

  1. insert overwrite table dwd_tool_coupon_order_inc partition(dt='2020-06-15')
  2. select
  3.     data.id,
  4.     data.coupon_id,
  5.     data.user_id,
  6.     data.order_id,
  7.     date_format(data.using_time,'yyyy-MM-dd') date_id,
  8.     data.using_time
  9. from ods_coupon_use_inc
  10. where dt='2020-06-15'
  11. and type='update'
  12. and array_contains(map_keys(old),'using_time');

1.10、工具域优惠券使用(支付)事务事实表

1.10.1、建表语句

  1. DROP TABLE IF EXISTS dwd_tool_coupon_pay_inc;
  2. CREATE EXTERNAL TABLE dwd_tool_coupon_pay_inc
  3. (
  4.     `id`           STRING COMMENT '编号',
  5.     `coupon_id`    STRING COMMENT '优惠券ID',
  6.     `user_id`      STRING COMMENT 'user_id',
  7.     `order_id`     STRING COMMENT 'order_id',
  8.     `date_id`      STRING COMMENT '日期ID',
  9.     `payment_time` STRING COMMENT '使用下单时间'
  10. ) COMMENT '优惠券使用支付事务事实表'
  11.     PARTITIONED BY (`dt` STRING)
  12.     STORED AS ORC
  13.     LOCATION '/warehouse/gmall/dwd/dwd_tool_coupon_pay_inc/'
  14.     TBLPROPERTIES ("orc.compress" = "snappy");

1.10.2、首日装载

        根据 used_time 是否为空或者 coupon_status 是否等于 1403 都是可以的。

  1. insert overwrite table dwd_tool_coupon_pay_inc partition(dt)
  2. select
  3.     data.id,
  4.     data.coupon_id,
  5.     data.user_id,
  6.     data.order_id,
  7.     date_format(data.used_time,'yyyy-MM-dd') date_id,
  8.     data.used_time,
  9.     date_format(data.used_time,'yyyy-MM-dd')
  10. from ods_coupon_use_inc
  11. where dt='2020-06-14'
  12. and type='bootstrap-insert'
  13. and data.used_time is not null;

1.10.3、每日装载

  1. insert overwrite table dwd_tool_coupon_pay_inc partition(dt='2020-06-15')
  2. select
  3.     data.id,
  4.     data.coupon_id,
  5.     data.user_id,
  6.     data.order_id,
  7.     date_format(data.used_time,'yyyy-MM-dd') date_id,
  8.     data.used_time
  9. from ods_coupon_use_inc
  10. where dt='2020-06-15'
  11. and type='update'
  12. and array_contains(map_keys(old),'used_time');

1.11、互动域收藏商品事务事实表

1.11.1、建表语句

        对于商品收藏，同样没有度量字段，因为一行就相当于一个隐含的度量值——一个商品收藏。

  1. DROP TABLE IF EXISTS dwd_interaction_favor_add_inc;
  2. CREATE EXTERNAL TABLE dwd_interaction_favor_add_inc
  3. (
  4.     `id`          STRING COMMENT '编号',
  5.     `user_id`     STRING COMMENT '用户id',
  6.     `sku_id`      STRING COMMENT 'sku_id',
  7.     `date_id`     STRING COMMENT '日期id',
  8.     `create_time` STRING COMMENT '收藏时间'
  9. ) COMMENT '收藏事实表'
  10.     PARTITIONED BY (`dt` STRING)
  11.     STORED AS ORC
  12.     LOCATION '/warehouse/gmall/dwd/dwd_interaction_favor_add_inc/'
  13.     TBLPROPERTIES ("orc.compress" = "snappy");

1.11.2、首日装载

        同样我们需要分析收藏这个行为会对哪些表产生影响：事实上只会对 favor_info 产生影响：

        当用户收藏商品的时候，favor_info 中会插入一条数据，当用户取消收藏时，favor_info 中的 is_cancal 字段修改为为 1 ，同时 cancal_time 设置为当前时间。所以只要 type=insert 就是商品收藏操作，只要是 type=update 并且 is_cancal=1 并且 cancal_time is not null 那么就是取消收藏操作（但是我们这里并没有建立取消收藏事实表）。

  1. set hive.exec.dynamic.partition.mode=nonstrict;
  2. insert overwrite table dwd_interaction_favor_add_inc partition(dt)
  3. select
  4.     data.id,
  5.     data.user_id,
  6.     data.sku_id,
  7.     date_format(data.create_time,'yyyy-MM-dd') date_id,
  8.     data.create_time,
  9.     date_format(data.create_time,'yyyy-MM-dd')
  10. from ods_favor_info_inc
  11. where dt='2020-06-14'
  12. and type = 'bootstrap-insert';

1.11.3、每日装载

  1. insert overwrite table dwd_interaction_favor_add_inc partition(dt='2020-06-15')
  2. select
  3.     data.id,
  4.     data.user_id,
  5.     data.sku_id,
  6.     date_format(data.create_time,'yyyy-MM-dd') date_id,
  7.     data.create_time
  8. from ods_favor_info_inc
  9. where dt='2020-06-15'
  10. and type = 'insert';

1.12、互动域评价事务事实表

1.12.1、建表语句

        我们的这张表的粒度应该是 谁+什么时候+哪个订单+哪个商品+评论相关的维度属性/度量（比如好评还是差评，可以算是一个维度（被用在 SQL 的 where 过滤条件当中）也可以算是度量值（被用在聚合函数当中），具体看使用场景）

  1. DROP TABLE IF EXISTS dwd_interaction_comment_inc;
  2. CREATE EXTERNAL TABLE dwd_interaction_comment_inc
  3. (
  4.     `id`            STRING COMMENT '编号',
  5.     `user_id`       STRING COMMENT '用户ID',
  6.     `sku_id`        STRING COMMENT 'sku_id',
  7.     `order_id`      STRING COMMENT '订单ID',
  8.     `date_id`       STRING COMMENT '日期ID',
  9.     `create_time`   STRING COMMENT '评价时间',
  10.     `appraise_code` STRING COMMENT '评价编码',
  11.     `appraise_name` STRING COMMENT '评价名称(好评/中评/差评/自动)'
  12. ) COMMENT '评价事务事实表'
  13.     PARTITIONED BY (`dt` STRING)
  14.     STORED AS ORC
  15.     LOCATION '/warehouse/gmall/dwd/dwd_interaction_comment_inc/'
  16.     TBLPROPERTIES ("orc.compress" = "snappy");

1.12.2、首日装载

  1. insert overwrite table dwd_interaction_comment_inc partition(dt)
  2. select
  3.     id,
  4.     user_id,
  5.     sku_id,
  6.     order_id,
  7.     date_format(create_time,'yyyy-MM-dd') date_id,
  8.     create_time,
  9.     appraise,
  10.     dic_name,
  11.     date_format(create_time,'yyyy-MM-dd')
  12. from
  13. (
  14.     select
  15.         data.id,
  16.         data.user_id,
  17.         data.sku_id,
  18.         data.order_id,
  19.         data.create_time,
  20.         data.appraise
  21.     from ods_comment_info_inc
  22.     where dt='2020-06-14'
  23.     and type='bootstrap-insert'
  24. )ci
  25. left join
  26. (
  27.     select
  28.         dic_code,
  29.         dic_name
  30.     from ods_base_dic_full
  31.     where dt='2020-06-14'
  32.     and parent_code='12'
  33. )dic
  34. on ci.appraise=dic.dic_code;

1.12.3、每日装载

  1. insert overwrite table dwd_interaction_comment_inc partition(dt='2020-06-15')
  2. select
  3.     id,
  4.     user_id,
  5.     sku_id,
  6.     order_id,
  7.     date_format(create_time,'yyyy-MM-dd') date_id,
  8.     create_time,
  9.     appraise,
  10.     dic_name
  11. from
  12. (
  13.     select
  14.         data.id,
  15.         data.user_id,
  16.         data.sku_id,
  17.         data.order_id,
  18.         data.create_time,
  19.         data.appraise
  20.     from ods_comment_info_inc
  21.     where dt='2020-06-15'
  22.     and type='insert'
  23. )ci
  24. left join
  25. (
  26.     select
  27.         dic_code,
  28.         dic_name
  29.     from ods_base_dic_full
  30.     where dt='2020-06-15'
  31.     and parent_code='12'
  32. )dic
  33. on ci.appraise=dic.dic_code;

1.13、流量域页面浏览事务事实表

        流量域里面的业务过程一般都来自用户行为日志，因为我们一个网页的访问量、一个按钮的点击量不会存到数据库，一般都是前端埋点写到日志里面。

1.13.1、建表语句

        这里我们的业务过程（页面浏览）的粒度是 谁（这里指的主要是设备id，因为很多时候并不需要登录才能浏览） + 什么时候 + 浏览了哪个页面

  1. DROP TABLE IF EXISTS dwd_traffic_page_view_inc;
  2. CREATE EXTERNAL TABLE dwd_traffic_page_view_inc
  3. (
  4.     `province_id`    STRING COMMENT '省份id',
  5.     `brand`          STRING COMMENT '手机品牌',
  6.     `channel`        STRING COMMENT '渠道',
  7.     `is_new`         STRING COMMENT '是否首次启动',
  8.     `model`          STRING COMMENT '手机型号',
  9.     `mid_id`         STRING COMMENT '设备id',
  10.     `operate_system` STRING COMMENT '操作系统',
  11.     `user_id`        STRING COMMENT '会员id',
  12.     `version_code`   STRING COMMENT 'app版本号',
  13.     `page_item`      STRING COMMENT '目标id ',
  14.     `page_item_type` STRING COMMENT '目标类型',
  15.     `last_page_id`   STRING COMMENT '上页类型',
  16.     `page_id`        STRING COMMENT '页面ID ',
  17.     `source_type`    STRING COMMENT '来源类型',
  18.     `date_id`        STRING COMMENT '日期id',
  19.     `view_time`      STRING COMMENT '跳入时间',
  20.     `session_id`     STRING COMMENT '所属会话id',
  21.     `during_time`    BIGINT COMMENT '持续时间毫秒'
  22. ) COMMENT '页面日志表'
  23.     PARTITIONED BY (`dt` STRING)
  24.     STORED AS ORC
  25.     LOCATION '/warehouse/gmall/dwd/dwd_traffic_page_view_inc'
  26.     TBLPROPERTIES ('orc.compress' = 'snappy');

        这里从 province_id 到 version_code 字段都是从日志中退化到事实表的字段，它们存在于日志数据中的 common 属性里面，都是环境信息。这里退化的原因是我们的数据源是日志，而日志中既包含了环境这些维度信息，又包含了业务过程信息，如果把它们单独分开分别创建多张维度表和事实表的话，当我们需要从日志中读取再装载的时候，又需要把它们 join 起来。所以反正它们本来就在一起，还不如直接放一起做一个维度退化，免得分开还得 join。

        这张表几乎所有字段都来组我们的日志数据，除了 province_id 需要通过关联 area_code 来间接得到、session_id 需要加工得到。

1.13.2、数据装载

        我们需要通过判断 page 属性是否为空来判断当前日志是不是页面日志（page 为空是启动日志，不为空则为页面日志）。

        关于页面浏览表的分区，因为我们的页面浏览是没有历史数据的，所以我们并不需要首日、每日装载的区分。

  1. set hive.cbo.enable=false;
  2. insert overwrite table dwd_traffic_page_view_inc partition (dt='2020-06-14')
  3. select
  4.     province_id,
  5.     brand,
  6.     channel,
  7.     is_new,
  8.     model,
  9.     mid_id,
  10.     operate_system,
  11.     user_id,
  12.     version_code,
  13.     page_item,
  14.     page_item_type,
  15.     last_page_id,
  16.     page_id,
  17.     source_type,
  18.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
  19.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
  20.     concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
  21.     during_time
  22. from
  23. (
  24.     select
  25.         common.ar area_code,
  26.         common.ba brand,
  27.         common.ch channel,
  28.         common.is_new is_new,
  29.         common.md model,
  30.         common.mid mid_id,
  31.         common.os operate_system,
  32.         common.uid user_id,
  33.         common.vc version_code,
  34.         page.during_time,
  35.         page.item page_item,
  36.         page.item_type page_item_type,
  37.         page.last_page_id,
  38.         page.page_id,
  39.         page.source_type,
  40.         ts,
  41.         if(page.last_page_id is null,ts,null) session_start_point
  42.     from ods_log_inc
  43.     where dt='2020-06-14'
  44.     and page is not null
  45. )log
  46. left join
  47. (
  48.     select
  49.         id province_id,
  50.         area_code
  51.     from ods_base_province_full
  52.     where dt='2020-06-14'
  53. )bp
  54. on log.area_code=bp.area_code;

Bug - struct is not null

描述：

        例如struct是一个结构体，它有一些字段比如user_id，page_id等等，在Hive3.x版本中，使用struct is not null时没有把结构体为null的数据筛选掉。

原因：

        这是Hive3.x中的一个bug，在语句的执行计划中，这个判断结构体是否为空的过滤条件直接被忽略了。
        在数据库中，有RBO（基于规则的优化策略）和CBO（基于代价的优化策略）两种优化策略。实际上就是因为CBO这个优化策略导致的，Hive中默认使用了CBO优化策略。

解决方案：

1）方案一：已知了结构体struct里的字段名称，直接判断结构体里的字段是否为null即可
2）方案二：在Hive4.0版本中修复了此bug，因此使用Hive4.0版本即可，或者根据Hive4.0修复这部分的代码，在自己所用的Hive版本中修改对应的代码
3）方案三：在Hive中禁用CBO优化set hive.cbo.enable=false;

1.14、流量域启动事务事实表

        对于启动这个操作而言，只有移动端的应用才有这个操作，PC 端并没有。

1.14.1、建表语句

  1. DROP TABLE IF EXISTS dwd_traffic_start_inc;
  2. CREATE EXTERNAL TABLE dwd_traffic_start_inc
  3. (
  4.     `province_id`     STRING COMMENT '省份id',
  5.     `brand`           STRING COMMENT '手机品牌',
  6.     `channel`         STRING COMMENT '渠道',
  7.     `is_new`          STRING COMMENT '是否首次启动',
  8.     `model`           STRING COMMENT '手机型号',
  9.     `mid_id`          STRING COMMENT '设备id',
  10.     `operate_system`  STRING COMMENT '操作系统',
  11.     `user_id`         STRING COMMENT '会员id',
  12.     `version_code`    STRING COMMENT 'app版本号',
  13.     `entry`           STRING COMMENT 'icon手机图标 notice 通知',
  14.     `open_ad_id`      STRING COMMENT '广告页ID ',
  15.     `date_id`         STRING COMMENT '日期id',
  16.     `start_time`      STRING COMMENT '启动时间',
  17.     `loading_time_ms` BIGINT COMMENT '启动加载时间',
  18.     `open_ad_ms`      BIGINT COMMENT '广告总共播放时间',
  19.     `open_ad_skip_ms` BIGINT COMMENT '用户跳过广告时点'
  20. ) COMMENT '启动日志表'
  21.     PARTITIONED BY (`dt` STRING)
  22.     STORED AS ORC
  23.     LOCATION '/warehouse/gmall/dwd/dwd_traffic_start_inc'
  24.     TBLPROPERTIES ('orc.compress' = 'snappy');

1.14.2、数据装载

同样，为了防止结构体字段 is not null 不生效，这里需要关闭 CBO ：

  1. set hive.cbo.enable=false;
  2. insert overwrite table dwd_traffic_start_inc partition(dt='2020-06-14')
  3. select
  4.     province_id,
  5.     brand,
  6.     channel,
  7.     is_new,
  8.     model,
  9.     mid_id,
  10.     operate_system,
  11.     user_id,
  12.     version_code,
  13.     entry,
  14.     open_ad_id,
  15.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
  16.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time,
  17.     loading_time,
  18.     open_ad_ms,
  19.     open_ad_skip_ms
  20. from
  21. (
  22.     select
  23.         common.ar area_code,
  24.         common.ba brand,
  25.         common.ch channel,
  26.         common.is_new,
  27.         common.md model,
  28.         common.mid mid_id,
  29.         common.os operate_system,
  30.         common.uid user_id,
  31.         common.vc version_code,
  32.         `start`.entry,
  33.         `start`.loading_time,
  34.         `start`.open_ad_id,
  35.         `start`.open_ad_ms,
  36.         `start`.open_ad_skip_ms,
  37.         ts
  38.     from ods_log_inc
  39.     where dt='2020-06-14'
  40.     and `start` is not null
  41. )log
  42. left join
  43. (
  44.     select
  45.         id province_id,
  46.         area_code
  47.     from ods_base_province_full
  48.     where dt='2020-06-14'
  49. )bp
  50. on log.area_code=bp.area_code;

注意： 我们这里的 start 是 hive 中的关键字所以需要使用反引号，但是这个命令将来会被写到 shell 脚本里，而在 shell 脚本中反引号是执行反引号中的 shell 命令的意思，所以到时候我们还需要通过 \` 来转义。

1.15、流量域动作事务事实表

这里的动作主要采集的是用户的领券、加购、收藏这些动作。

1.15.1、建表语句

  1. DROP TABLE IF EXISTS dwd_traffic_action_inc;
  2. CREATE EXTERNAL TABLE dwd_traffic_action_inc
  3. (
  4.     `province_id`      STRING COMMENT '省份id',
  5.     `brand`            STRING COMMENT '手机品牌',
  6.     `channel`          STRING COMMENT '渠道',
  7.     `is_new`           STRING COMMENT '是否首次启动',
  8.     `model`            STRING COMMENT '手机型号',
  9.     `mid_id`           STRING COMMENT '设备id',
  10.     `operate_system`   STRING COMMENT '操作系统',
  11.     `user_id`          STRING COMMENT '会员id',
  12.     `version_code`     STRING COMMENT 'app版本号',
  13.     `during_time`      BIGINT COMMENT '持续时间毫秒',
  14.     `page_item`        STRING COMMENT '目标id ',
  15.     `page_item_type`   STRING COMMENT '目标类型',
  16.     `last_page_id`     STRING COMMENT '上页类型',
  17.     `page_id`          STRING COMMENT '页面id ',
  18.     `source_type`      STRING COMMENT '来源类型',
  19.     `action_id`        STRING COMMENT '动作id',
  20.     `action_item`      STRING COMMENT '目标id ',
  21.     `action_item_type` STRING COMMENT '目标类型',
  22.     `date_id`          STRING COMMENT '日期id',
  23.     `action_time`      STRING COMMENT '动作发生时间'
  24. ) COMMENT '动作日志表'
  25.     PARTITIONED BY (`dt` STRING)
  26.     STORED AS ORC
  27.     LOCATION '/warehouse/gmall/dwd/dwd_traffic_action_inc'
  28.     TBLPROPERTIES ('orc.compress' = 'snappy');

1.15.2、装载语句

  1. set hive.cbo.enable=false;
  2. insert overwrite table dwd_traffic_action_inc partition(dt='2020-06-14')
  3. select
  4.     province_id,
  5.     brand,
  6.     channel,
  7.     is_new,
  8.     model,
  9.     mid_id,
  10.     operate_system,
  11.     user_id,
  12.     version_code,
  13.     during_time,
  14.     page_item,
  15.     page_item_type,
  16.     last_page_id,
  17.     page_id,
  18.     source_type,
  19.     action_id,
  20.     action_item,
  21.     action_item_type,
  22.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
  23.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time
  24. from
  25. (
  26.     select
  27.         common.ar area_code,
  28.         common.ba brand,
  29.         common.ch channel,
  30.         common.is_new,
  31.         common.md model,
  32.         common.mid mid_id,
  33.         common.os operate_system,
  34.         common.uid user_id,
  35.         common.vc version_code,
  36.         page.during_time,
  37.         page.item page_item,
  38.         page.item_type page_item_type,
  39.         page.last_page_id,
  40.         page.page_id,
  41.         page.source_type,
  42.         action.action_id,
  43.         action.item action_item,
  44.         action.item_type action_item_type,
  45.         action.ts
  46.     from ods_log_inc lateral view explode(actions) tmp as action
  47.     where dt='2020-06-14'
  48.     and actions is not null
  49. )log
  50. left join
  51. (
  52.     select
  53.         id province_id,
  54.         area_code
  55.     from ods_base_province_full
  56.     where dt='2020-06-14'
  57. )bp
  58. on log.area_code=bp.area_code;

注意：这里用到了炸裂函数，关于炸裂函数的使用仅仅这里写一遍是远远不够的，还是得下去多练！

1.16、流量域曝光事务事实表

        这里的曝光指的是系统给我们推送内容的曝光行为，比如广告、轮播图、推荐。

1.16.1、建表语句

  1. DROP TABLE IF EXISTS dwd_traffic_display_inc;
  2. CREATE EXTERNAL TABLE dwd_traffic_display_inc
  3. (
  4.     `province_id`       STRING COMMENT '省份id',
  5.     `brand`             STRING COMMENT '手机品牌',
  6.     `channel`           STRING COMMENT '渠道',
  7.     `is_new`            STRING COMMENT '是否首次启动',
  8.     `model`             STRING COMMENT '手机型号',
  9.     `mid_id`            STRING COMMENT '设备id',
  10.     `operate_system`    STRING COMMENT '操作系统',
  11.     `user_id`           STRING COMMENT '会员id',
  12.     `version_code`      STRING COMMENT 'app版本号',
  13.     `during_time`       BIGINT COMMENT 'app版本号',
  14.     `page_item`         STRING COMMENT '目标id ',
  15.     `page_item_type`    STRING COMMENT '目标类型',
  16.     `last_page_id`      STRING COMMENT '上页类型',
  17.     `page_id`           STRING COMMENT '页面ID ',
  18.     `source_type`       STRING COMMENT '来源类型',
  19.     `date_id`           STRING COMMENT '日期id',
  20.     `display_time`      STRING COMMENT '曝光时间',
  21.     `display_type`      STRING COMMENT '曝光类型',
  22.     `display_item`      STRING COMMENT '曝光对象id ',
  23.     `display_item_type` STRING COMMENT 'app版本号',
  24.     `display_order`     BIGINT COMMENT '曝光顺序',
  25.     `display_pos_id`    BIGINT COMMENT '曝光位置'
  26. ) COMMENT '曝光日志表'
  27.     PARTITIONED BY (`dt` STRING)
  28.     STORED AS ORC
  29.     LOCATION '/warehouse/gmall/dwd/dwd_traffic_display_inc'
  30.     TBLPROPERTIES ('orc.compress' = 'snappy');

这里我们的曝光时间 直接取的进入页面的时间，因为我们模拟的前端埋点并没有添加曝光时间属性。

1.16.2、装载语句

  1. set hive.cbo.enable=false;
  2. insert overwrite table dwd_traffic_display_inc partition(dt='2020-06-14')
  3. select
  4.     province_id,
  5.     brand,
  6.     channel,
  7.     is_new,
  8.     model,
  9.     mid_id,
  10.     operate_system,
  11.     user_id,
  12.     version_code,
  13.     during_time,
  14.     page_item,
  15.     page_item_type,
  16.     last_page_id,
  17.     page_id,
  18.     source_type,
  19.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
  20.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') display_time,
  21.     display_type,
  22.     display_item,
  23.     display_item_type,
  24.     display_order,
  25.     display_pos_id
  26. from
  27. (
  28.     select
  29.         common.ar area_code,
  30.         common.ba brand,
  31.         common.ch channel,
  32.         common.is_new,
  33.         common.md model,
  34.         common.mid mid_id,
  35.         common.os operate_system,
  36.         common.uid user_id,
  37.         common.vc version_code,
  38.         page.during_time,
  39.         page.item page_item,
  40.         page.item_type page_item_type,
  41.         page.last_page_id,
  42.         page.page_id,
  43.         page.source_type,
  44.         display.display_type,
  45.         display.item display_item,
  46.         display.item_type display_item_type,
  47.         display.`order` display_order,
  48.         display.pos_id display_pos_id,
  49.         ts
  50.     from ods_log_inc lateral view explode(displays) tmp as display
  51.     where dt='2020-06-14'
  52.     and displays is not null
  53. )log
  54. left join
  55. (
  56.     select
  57.         id province_id,
  58.         area_code
  59.     from ods_base_province_full
  60.     where dt='2020-06-14'
  61. )bp
  62. on log.area_code=bp.area_code;

1.17、流量域错误事务事实表

        错误日志有可能是来自于页面日志，也有可能是来自启动日志。

1.17.1、建表语句

  1. DROP TABLE IF EXISTS dwd_traffic_error_inc;
  2. CREATE EXTERNAL TABLE dwd_traffic_error_inc
  3. (
  4.     `province_id`     STRING COMMENT '地区编码',
  5.     `brand`           STRING COMMENT '手机品牌',
  6.     `channel`         STRING COMMENT '渠道',
  7.     `is_new`          STRING COMMENT '是否首次启动',
  8.     `model`           STRING COMMENT '手机型号',
  9.     `mid_id`          STRING COMMENT '设备id',
  10.     `operate_system`  STRING COMMENT '操作系统',
  11.     `user_id`         STRING COMMENT '会员id',
  12.     `version_code`    STRING COMMENT 'app版本号',
  13.     `page_item`       STRING COMMENT '目标id ',
  14.     `page_item_type`  STRING COMMENT '目标类型',
  15.     `last_page_id`    STRING COMMENT '上页类型',
  16.     `page_id`         STRING COMMENT '页面ID ',
  17.     `source_type`     STRING COMMENT '来源类型',
  18.     `entry`           STRING COMMENT 'icon手机图标  notice 通知',
  19.     `loading_time`    STRING COMMENT '启动加载时间',
  20.     `open_ad_id`      STRING COMMENT '广告页ID ',
  21.     `open_ad_ms`      STRING COMMENT '广告总共播放时间',
  22.     `open_ad_skip_ms` STRING COMMENT '用户跳过广告时点',
  23.     `actions`         ARRAY<STRUCT<action_id:STRING,item:STRING,item_type:STRING,ts:BIGINT>> COMMENT '动作信息',
  24.     `displays`        ARRAY<STRUCT<display_type :STRING,item :STRING,item_type :STRING,`order` :STRING,pos_id
  25.                                    :STRING>> COMMENT '曝光信息',
  26.     `date_id`         STRING COMMENT '日期id',
  27.     `error_time`      STRING COMMENT '错误时间',
  28.     `error_code`      STRING COMMENT '错误码',
  29.     `error_msg`       STRING COMMENT '错误信息'
  30. ) COMMENT '错误日志表'
  31.     PARTITIONED BY (`dt` STRING)
  32.     STORED AS ORC
  33.     LOCATION '/warehouse/gmall/dwd/dwd_traffic_error_inc'
  34.     TBLPROPERTIES ('orc.compress' = 'snappy');

        这里的 actions 和 displays 字段并没有使用炸裂函数，因为这样会破坏我们的粒度，如果将来需要用到这个数据的时候就需要炸裂函数了。 

1.17.2、装载语句

  1. set hive.cbo.enable=false;
  2. set hive.execution.engine=mr;
  3. insert overwrite table dwd_traffic_error_inc partition(dt='2020-06-14')
  4. select
  5.     province_id,
  6.     brand,
  7.     channel,
  8.     is_new,
  9.     model,
  10.     mid_id,
  11.     operate_system,
  12.     user_id,
  13.     version_code,
  14.     page_item,
  15.     page_item_type,
  16.     last_page_id,
  17.     page_id,
  18.     source_type,
  19.     entry,
  20.     loading_time,
  21.     open_ad_id,
  22.     open_ad_ms,
  23.     open_ad_skip_ms,
  24.     actions,
  25.     displays,
  26.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
  27.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') error_time,
  28.     error_code,
  29.     error_msg
  30. from
  31. (
  32.     select
  33.         common.ar area_code,
  34.         common.ba brand,
  35.         common.ch channel,
  36.         common.is_new,
  37.         common.md model,
  38.         common.mid mid_id,
  39.         common.os operate_system,
  40.         common.uid user_id,
  41.         common.vc version_code,
  42.         page.during_time,
  43.         page.item page_item,
  44.         page.item_type page_item_type,
  45.         page.last_page_id,
  46.         page.page_id,
  47.         page.source_type,
  48.         `start`.entry,
  49.         `start`.loading_time,
  50.         `start`.open_ad_id,
  51.         `start`.open_ad_ms,
  52.         `start`.open_ad_skip_ms,
  53.         actions,
  54.         displays,
  55.         err.error_code,
  56.         err.msg error_msg,
  57.         ts
  58.     from ods_log_inc
  59.     where dt='2020-06-14'
  60.     and err is not null
  61. )log
  62. join
  63. (
  64.     select
  65.         id province_id,
  66.         area_code
  67.     from ods_base_province_full
  68.     where dt='2020-06-14'
  69. )bp
  70. on log.area_code=bp.area_code;
  71. set hive.execution.engine=spark;

注意：这里的 actions、displays 对应到 Java 中是 List 类型，在 Hive On Spark 中报错 不期望的类型的列，这是 Hive On Spark 的一个 bug，我们需要通过切换 Hvie 引擎来解决。

1.18、用户域用户注册事务事实表

1.18.1、建表语句

  1. DROP TABLE IF EXISTS dwd_user_register_inc;
  2. CREATE EXTERNAL TABLE dwd_user_register_inc
  3. (
  4.     `user_id`        STRING COMMENT '用户ID',
  5.     `date_id`        STRING COMMENT '日期ID',
  6.     `create_time`    STRING COMMENT '注册时间',
  7.     `channel`        STRING COMMENT '应用下载渠道',
  8.     `province_id`    STRING COMMENT '省份id',
  9.     `version_code`   STRING COMMENT '应用版本',
  10.     `mid_id`         STRING COMMENT '设备id',
  11.     `brand`          STRING COMMENT '设备品牌',
  12.     `model`          STRING COMMENT '设备型号',
  13.     `operate_system` STRING COMMENT '设备操作系统'
  14. ) COMMENT '用户域用户注册事务事实表'
  15.     PARTITIONED BY (`dt` STRING)
  16.     STORED AS ORC
  17.     LOCATION '/warehouse/gmall/dwd/dwd_user_register_inc/'
  18.     TBLPROPERTIES ("orc.compress" = "snappy");

1.18.2、首日装载

        注册成功会影响哪些表：显然用户信息表会插入一条记录，日志中会多一条页面为注册页面并且带有用户id的记日志录。

        但是如果只用 user_info 去设计这张注册事实表维度信息太少了（只有用户和时间维度），我们希望尽可能丰富维度属性，所以这里我们会从地区表、日志表去获取更多的维度信息。

        这里我们的表当然是以表为主，而不是以日志为主，因为业务数据更加靠谱（比如业务数据库有事务保证，比如涉及到金额的操作，我们绝不会用日志去传输）

  1. set hive.exec.dynamic.partition.mode=nonstrict;
  2. insert overwrite table dwd_user_register_inc partition(dt)
  3. select
  4.     ui.user_id,
  5.     date_format(create_time,'yyyy-MM-dd') date_id,
  6.     create_time,
  7.     channel,
  8.     province_id,
  9.     version_code,
  10.     mid_id,
  11.     brand,
  12.     model,
  13.     operate_system,
  14.     date_format(create_time,'yyyy-MM-dd')
  15. from
  16. (
  17.     select
  18.         data.id user_id,
  19.         data.create_time
  20.     from ods_user_info_inc
  21.     where dt='2020-06-14'
  22.     and type='bootstrap-insert'
  23. )ui
  24. left join
  25. (
  26.     select
  27.         common.ar area_code,
  28.         common.ba brand,
  29.         common.ch channel,
  30.         common.md model,
  31.         common.mid mid_id,
  32.         common.os operate_system,
  33.         common.uid user_id,
  34.         common.vc version_code
  35.     from ods_log_inc
  36.     where dt='2020-06-14'
  37.     and page.page_id='register'
  38.     and common.uid is not null
  39. )log
  40. on ui.user_id=log.user_id
  41. left join
  42. (
  43.     select
  44.         id province_id,
  45.         area_code
  46.     from ods_base_province_full
  47.     where dt='2020-06-14'
  48. )bp
  49. on log.area_code=bp.area_code;

1.18.3、每日装载

  1. insert overwrite table dwd_user_register_inc partition(dt='2020-06-15')
  2. select
  3.     ui.user_id,
  4.     date_format(create_time,'yyyy-MM-dd') date_id,
  5.     create_time,
  6.     channel,
  7.     province_id,
  8.     version_code,
  9.     mid_id,
  10.     brand,
  11.     model,
  12.     operate_system
  13. from
  14. (
  15.     select
  16.         data.id user_id,
  17.         data.create_time
  18.     from ods_user_info_inc
  19.     where dt='2020-06-15'
  20.     and type='insert'
  21. )ui
  22. left join
  23. (
  24.     select
  25.         common.ar area_code,
  26.         common.ba brand,
  27.         common.ch channel,
  28.         common.md model,
  29.         common.mid mid_id,
  30.         common.os operate_system,
  31.         common.uid user_id,
  32.         common.vc version_code
  33.     from ods_log_inc
  34.     where dt='2020-06-15'
  35.     and page.page_id='register'
  36.     and common.uid is not null
  37. )log
  38. on ui.user_id=log.user_id
  39. left join
  40. (
  41.     select
  42.         id province_id,
  43.         area_code
  44.     from ods_base_province_full
  45.     where dt='2020-06-15'
  46. )bp
  47. on log.area_code=bp.area_code;

1.19、用户域用户登录事务事实表

1.19.1、建表语句

  1. DROP TABLE IF EXISTS dwd_user_login_inc;
  2. CREATE EXTERNAL TABLE dwd_user_login_inc
  3. (
  4.     `user_id`        STRING COMMENT '用户ID',
  5.     `date_id`        STRING COMMENT '日期ID',
  6.     `login_time`     STRING COMMENT '登录时间',
  7.     `channel`        STRING COMMENT '应用下载渠道',
  8.     `province_id`    STRING COMMENT '省份id',
  9.     `version_code`   STRING COMMENT '应用版本',
  10.     `mid_id`         STRING COMMENT '设备id',
  11.     `brand`          STRING COMMENT '设备品牌',
  12.     `model`          STRING COMMENT '设备型号',
  13.     `operate_system` STRING COMMENT '设备操作系统'
  14. ) COMMENT '用户域用户登录事务事实表'
  15.     PARTITIONED BY (`dt` STRING)
  16.     STORED AS ORC
  17.     LOCATION '/warehouse/gmall/dwd/dwd_user_login_inc/'
  18.     TBLPROPERTIES ("orc.compress" = "snappy");

1.19.2、数据装载

首先，数据来自于日志，日志没有历史数据，所以我们并不需要区分首日次日。

我们的登录场景根据日志中有没有 user_id 可以大致分我三类：从始至终没有登录、浏览到一半登录，打开网页时后台自动登录。

  1. insert overwrite table dwd_user_login_inc partition(dt='2020-06-14')
  2. select
  3.     user_id,
  4.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
  5.     date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
  6.     channel,
  7.     province_id,
  8.     version_code,
  9.     mid_id,
  10.     brand,
  11.     model,
  12.     operate_system
  13. from
  14. (
  15.     select
  16.         user_id,
  17.         channel,
  18.         area_code,
  19.         version_code,
  20.         mid_id,
  21.         brand,
  22.         model,
  23.         operate_system,
  24.         ts
  25.     from
  26.     (
  27.         select
  28.             user_id,
  29.             channel,
  30.             area_code,
  31.             version_code,
  32.             mid_id,
  33.             brand,
  34.             model,
  35.             operate_system,
  36.             ts,
  37.             row_number() over (partition by session_id order by ts) rn
  38.         from
  39.         (
  40.             select
  41.                 user_id,
  42.                 channel,
  43.                 area_code,
  44.                 version_code,
  45.                 mid_id,
  46.                 brand,
  47.                 model,
  48.                 operate_system,
  49.                 ts,
  50.                 concat(mid_id,'-',last_value(session_start_point,true) over(partition by mid_id order by ts)) session_id
  51.             from
  52.             (
  53.                 select
  54.                     common.uid user_id,
  55.                     common.ch channel,
  56.                     common.ar area_code,
  57.                     common.vc version_code,
  58.                     common.mid mid_id,
  59.                     common.ba brand,
  60.                     common.md model,
  61.                     common.os operate_system,
  62.                     ts,
  63.                     if(page.last_page_id is null,ts,null) session_start_point
  64.                 from ods_log_inc
  65.                 where dt='2020-06-14'
  66.                 and page is not null
  67.             )t1
  68.         )t2
  69.         where user_id is not null
  70.     )t3
  71.     where rn=1
  72. )t4
  73. left join
  74. (
  75.     select
  76.         id province_id,
  77.         area_code
  78.     from ods_base_province_full
  79.     where dt='2020-06-14'
  80. )bp
  81. on t4.area_code=bp.area_code;

脚本省略，注意：严格模式最好直接配到配置文件里去：

  1. <property>
  2.     <name>hive.exec.dynamic.partition.mode</name>
  3.     <value>nonstrict</value>
  4. </property>

总结

        至此，DWD 层搭建完毕，DWD 层一般都是和 DIM 层配置使用的，一个负责提供业务过程相关行为信息，一个负责提供该业务过程的维度信息。

        距离 DIM 层开发过去了整整两周，这两部分内容太重要了，所以学的很慢，但还是要回头慢慢再消化消化。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/943114
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号