本文详细介绍了离线数仓DWD层流量域的事实表设计，包括页面浏览、启动、动作、曝光和错误五个事务事实表的建表语句、session_id获取分析及数据装载过程。通过这些表，可以全面跟踪和分析用户在应用中的行为，如页面浏览时长、启动加载时间、动作事件和错误信息等。
离线数仓—DWD层流量域事实表实现
前言
前面完成了互动域2张事务事实表的设计，下面继续进行DWD层流量域事务事实表的设计
一、流量域页面浏览事务事实表
1.建表语句
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');
字段说明：
1）表中的设备id字段是需要的，不能单独用user_id来进行判断用户，因为有些人就没有登录，处于访客模式
2）表中的session_id是需要的，这是根据后面的需求决定的
注意：所有的页面都要有session_id
2.session_id字段获取分析
session_id是一个会话的id，就比如打开一个浏览器就产生了一个session。加入一天中打开浏览器一次，然后关闭，再打开一次，这样在一天内就产生了两个session。
session_id值的命名：采用mid+ts的方式来命名，mid可以区分出来是不同的设备，而每一次生成session的ts肯定不同，所以mid+ts可以精准定位到每一个设备每一次会话的session_id
错误的思路：最容易想到的思路是，使用开窗函数，按mid进行分类，再按时间ts进行排序，选取mid和最早的ts作为整个会话的session_id，如果一天内一个mid有两个会话，这样才产生了一个session_id，第二个session_id被忽略了
正确的思路，思路一：每一个会话的第一个页面的last_page_id是null，所以可以根据mid和last_page_id来判断一个设备每天有几个会话，如下图，假定是一个mid的两个会话，根据c4这个字段使用开窗函数里的last_value可以获取每个会话的session_id。最终session_id的数据格式：mid+ts
正确的思路，思路二：前面的思路第一个思路一样，只不过构造的字段是如果是第一个页面就规定是1，如果不是第一个页面就规定是0，然后使用开窗函数sum()，求从第一行到当前行的和，这样也能够获取正确的session_id。最终session_id的数据格式：mid+sum()
3.数据装载语句
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;
二、流量域启动事务事实表
启动日志信息跟业务数据库信息不一样，首日不会存在多天的数据，首日数据也是当天的数据，所以不区分首日数据装载和每日数据装载。
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');
字段说明：
open_ad_ms是广告的属性，属于维度信息
open_ad_skip_ms是度量值
2.数据装载
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;
注意事项：
1）上面语句有一个变量名start，是一个关键字，在DataGrip中要加引号，但是在shell脚本中要对引号进行转义。
三、流量域动作事务事实表
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）中间的页面相关的信息是为了让维度更加丰富才添加上的，还有一点是一条日志就是发生在浏览一个页面时产生的，因此所有的日志相关的事实表都可以加上页面相关的维度信息
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）actions字段是一个数组，数组的每一个元素是一个struct，而这张表的粒度时一个页面的一个动作，所以要使用explode函数将actions炸裂开来
四、流量域曝光事务事实表
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 '页面持续时间',
`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 '曝光对象类型',
`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');
2.数据装载
set hive.cbo.enable=false;
insert overwrite table dwd_traffic_display_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,
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') display_time,
display_type,
display_item,
display_item_type,
display_order,
display_pos_id
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,
display.display_type,
display.item display_item,
display.item_type display_item_type,
display.`order` display_order,
display.pos_id display_pos_id,
ts
from ods_log_inc lateral view explode(displays) tmp as display
where dt='2020-06-14'
and displays 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.建表语句
DROP TABLE IF EXISTS dwd_traffic_error_inc;
CREATE EXTERNAL TABLE dwd_traffic_error_inc
(
`province_id` STRING COMMENT '地区编码',
`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 '来源类型',
`entry` STRING COMMENT 'icon手机图标 notice 通知',
`loading_time` STRING COMMENT '启动加载时间',
`open_ad_id` STRING COMMENT '广告页ID ',
`open_ad_ms` STRING COMMENT '广告总共播放时间',
`open_ad_skip_ms` STRING COMMENT '用户跳过广告时点',
`actions` ARRAY<STRUCT<action_id:STRING,item:STRING,item_type:STRING,ts:BIGINT>> COMMENT '动作信息',
`displays` ARRAY<STRUCT<display_type :STRING,item :STRING,item_type :STRING,`order` :STRING,pos_id
:STRING>> COMMENT '曝光信息',
`date_id` STRING COMMENT '日期id',
`error_time` STRING COMMENT '错误时间',
`error_code` STRING COMMENT '错误码',
`error_msg` STRING COMMENT '错误信息'
) COMMENT '错误日志表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dwd/dwd_traffic_error_inc'
TBLPROPERTIES ('orc.compress' = 'snappy');
建表语句说明：
1）error字段相关的维度有common的所有字段、page的所有字段、displays的所有字段、actions的所有字段，这里没有把数组炸裂开，因为这是错误表，一行代表发生了一个错误，如果炸裂开，就代表发生了多个错误。
2）error出现的情况有两种，一种是页面浏览日志，一种是启动日志。
2.数据装载
set hive.cbo.enable=false;
set hive.execution.engine=mr;
insert overwrite table dwd_traffic_error_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,
entry,
loading_time,
open_ad_id,
open_ad_ms,
open_ad_skip_ms,
actions,
displays,
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') error_time,
error_code,
error_msg
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,
`start`.entry,
`start`.loading_time,
`start`.open_ad_id,
`start`.open_ad_ms,
`start`.open_ad_skip_ms,
actions,
displays,
err.error_code,
err.msg error_msg,
ts
from ods_log_inc
where dt='2020-06-14'
and err is not null
)log
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;
注意：运行的时候可能会报错，要切换计算引擎。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
