离线数仓（八）【DWD 层开发】（4）-阿里云开发者社区

离线数仓（八）【DWD 层开发】（3）https://developer.aliyun.com/article/1532418

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

1.11.1、建表语句

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

DROP TABLE IF EXISTS dwd_interaction_favor_add_inc;
CREATE EXTERNAL TABLE dwd_interaction_favor_add_inc
(
    `id`          STRING COMMENT '编号',
    `user_id`     STRING COMMENT '用户id',
    `sku_id`      STRING COMMENT 'sku_id',
    `date_id`     STRING COMMENT '日期id',
    `create_time` STRING COMMENT '收藏时间'
) COMMENT '收藏事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_interaction_favor_add_inc/'
    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 那么就是取消收藏操作（但是我们这里并没有建立取消收藏事实表）。

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_interaction_favor_add_inc partition(dt)
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(data.create_time,'yyyy-MM-dd') date_id,
    data.create_time,
    date_format(data.create_time,'yyyy-MM-dd')
from ods_favor_info_inc
where dt='2020-06-14'
and type = 'bootstrap-insert';

1.11.3、每日装载

insert overwrite table dwd_interaction_favor_add_inc partition(dt='2020-06-15')
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(data.create_time,'yyyy-MM-dd') date_id,
    data.create_time
from ods_favor_info_inc
where dt='2020-06-15'
and type = 'insert';

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

1.12.1、建表语句

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

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

1.12.2、首日装载

insert overwrite table dwd_interaction_comment_inc partition(dt)
select
    id,
    user_id,
    sku_id,
    order_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    appraise,
    dic_name,
    date_format(create_time,'yyyy-MM-dd')
from
(
    select
        data.id,
        data.user_id,
        data.sku_id,
        data.order_id,
        data.create_time,
        data.appraise
    from ods_comment_info_inc
    where dt='2020-06-14'
    and type='bootstrap-insert'
)ci
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-14'
    and parent_code='12'
)dic
on ci.appraise=dic.dic_code;

1.12.3、每日装载

insert overwrite table dwd_interaction_comment_inc partition(dt='2020-06-15')
select
    id,
    user_id,
    sku_id,
    order_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    appraise,
    dic_name
from
(
    select
        data.id,
        data.user_id,
        data.sku_id,
        data.order_id,
        data.create_time,
        data.appraise
    from ods_comment_info_inc
    where dt='2020-06-15'
    and type='insert'
)ci
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-15'
    and parent_code='12'
)dic
on ci.appraise=dic.dic_code;

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

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

1.13.1、建表语句

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

DROP TABLE IF EXISTS dwd_traffic_page_view_inc;
CREATE EXTERNAL TABLE dwd_traffic_page_view_inc
(
    `province_id`    STRING COMMENT '省份id',
    `brand`          STRING COMMENT '手机品牌',
    `channel`        STRING COMMENT '渠道',
    `is_new`         STRING COMMENT '是否首次启动',
    `model`          STRING COMMENT '手机型号',
    `mid_id`         STRING COMMENT '设备id',
    `operate_system` STRING COMMENT '操作系统',
    `user_id`        STRING COMMENT '会员id',
    `version_code`   STRING COMMENT 'app版本号',
    `page_item`      STRING COMMENT '目标id ',
    `page_item_type` STRING COMMENT '目标类型',
    `last_page_id`   STRING COMMENT '上页类型',
    `page_id`        STRING COMMENT '页面ID ',
    `source_type`    STRING COMMENT '来源类型',
    `date_id`        STRING COMMENT '日期id',
    `view_time`      STRING COMMENT '跳入时间',
    `session_id`     STRING COMMENT '所属会话id',
    `during_time`    BIGINT COMMENT '持续时间毫秒'
) COMMENT '页面日志表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_traffic_page_view_inc'
    TBLPROPERTIES ('orc.compress' = 'snappy');

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

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

1.13.2、数据装载

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

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

set hive.cbo.enable=false;
insert overwrite table dwd_traffic_page_view_inc partition (dt='2020-06-14')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_type,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
    during_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        page.source_type,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from ods_log_inc
    where dt='2020-06-14'
    and page is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ods_base_province_full
    where dt='2020-06-14'
)bp
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、建表语句

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

1.14.2、数据装载

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

set hive.cbo.enable=false;
insert overwrite table dwd_traffic_start_inc partition(dt='2020-06-14')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    entry,
    open_ad_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time,
    loading_time,
    open_ad_ms,
    open_ad_skip_ms
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        `start`.entry,
        `start`.loading_time,
        `start`.open_ad_id,
        `start`.open_ad_ms,
        `start`.open_ad_skip_ms,
        ts
    from ods_log_inc
    where dt='2020-06-14'
    and `start` is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ods_base_province_full
    where dt='2020-06-14'
)bp
on log.area_code=bp.area_code;

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

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

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

1.15.1、建表语句

DROP TABLE IF EXISTS dwd_traffic_action_inc;
CREATE EXTERNAL TABLE dwd_traffic_action_inc
(
    `province_id`      STRING COMMENT '省份id',
    `brand`            STRING COMMENT '手机品牌',
    `channel`          STRING COMMENT '渠道',
    `is_new`           STRING COMMENT '是否首次启动',
    `model`            STRING COMMENT '手机型号',
    `mid_id`           STRING COMMENT '设备id',
    `operate_system`   STRING COMMENT '操作系统',
    `user_id`          STRING COMMENT '会员id',
    `version_code`     STRING COMMENT 'app版本号',
    `during_time`      BIGINT COMMENT '持续时间毫秒',
    `page_item`        STRING COMMENT '目标id ',
    `page_item_type`   STRING COMMENT '目标类型',
    `last_page_id`     STRING COMMENT '上页类型',
    `page_id`          STRING COMMENT '页面id ',
    `source_type`      STRING COMMENT '来源类型',
    `action_id`        STRING COMMENT '动作id',
    `action_item`      STRING COMMENT '目标id ',
    `action_item_type` STRING COMMENT '目标类型',
    `date_id`          STRING COMMENT '日期id',
    `action_time`      STRING COMMENT '动作发生时间'
) COMMENT '动作日志表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_traffic_action_inc'
    TBLPROPERTIES ('orc.compress' = 'snappy');

1.15.2、装载语句

set hive.cbo.enable=false;
insert overwrite table dwd_traffic_action_inc partition(dt='2020-06-14')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    during_time,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_type,
    action_id,
    action_item,
    action_item_type,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        page.source_type,
        action.action_id,
        action.item action_item,
        action.item_type action_item_type,
        action.ts
    from ods_log_inc lateral view explode(actions) tmp as action
    where dt='2020-06-14'
    and actions is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ods_base_province_full
    where dt='2020-06-14'
)bp
on log.area_code=bp.area_code;

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

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

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

1.16.1、建表语句

DROP TABLE IF EXISTS dwd_traffic_display_inc;
CREATE EXTERNAL TABLE dwd_traffic_display_inc
(
    `province_id`       STRING COMMENT '省份id',
    `brand`             STRING COMMENT '手机品牌',
    `channel`           STRING COMMENT '渠道',
    `is_new`            STRING COMMENT '是否首次启动',
    `model`             STRING COMMENT '手机型号',
    `mid_id`            STRING COMMENT '设备id',
    `operate_system`    STRING COMMENT '操作系统',
    `user_id`           STRING COMMENT '会员id',
    `version_code`      STRING COMMENT 'app版本号',
    `during_time`       BIGINT COMMENT 'app版本号',
    `page_item`         STRING COMMENT '目标id ',
    `page_item_type`    STRING COMMENT '目标类型',
    `last_page_id`      STRING COMMENT '上页类型',
    `page_id`           STRING COMMENT '页面ID ',
    `source_type`       STRING COMMENT '来源类型',
    `date_id`           STRING COMMENT '日期id',
    `display_time`      STRING COMMENT '曝光时间',
    `display_type`      STRING COMMENT '曝光类型',
    `display_item`      STRING COMMENT '曝光对象id ',
    `display_item_type` STRING COMMENT 'app版本号',
    `display_order`     BIGINT COMMENT '曝光顺序',
    `display_pos_id`    BIGINT COMMENT '曝光位置'
) COMMENT '曝光日志表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_traffic_display_inc'
    TBLPROPERTIES ('orc.compress' = 'snappy');

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

离线数仓（八）【DWD 层开发】（5）https://developer.aliyun.com/article/1532421

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

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

1.11.1、建表语句

1.11.2、首日装载

1.11.3、每日装载

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

1.12.1、建表语句

1.12.2、首日装载

1.12.3、每日装载

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

1.13.1、建表语句

1.13.2、数据装载

Bug - struct is not null

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

1.14.1、建表语句

1.14.2、数据装载

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

1.15.1、建表语句

1.15.2、装载语句

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

1.16.1、建表语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

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

1.11.1、建表语句

1.11.2、首日装载

1.11.3、每日装载

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

1.12.1、建表语句

1.12.2、首日装载

1.12.3、每日装载

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

1.13.1、建表语句

1.13.2、数据装载

Bug - struct is not null

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

1.14.1、建表语句

1.14.2、数据装载

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

1.15.1、建表语句

1.15.2、装载语句

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

1.16.1、建表语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景