本文介绍了阿里巴巴数据团队的数据仓库分层模型，包括ODS、CDM（DWD和DWS）、ADS三层，以及范式建模和维度建模方法。通过ODS进行原始数据同步和清洗，CDM层处理明细和汇总数据，ADS层则针对个性化指标。文章还讨论了建模的规范化和反规范化策略，以及维度建模的优缺点和步骤。
模型设计:
阿里巴巴的数据团队将数据模型设计为 3 层，操作数据层 ODS、公共维度模型层 CDM、应用数据层 ADS，其中 CDM 包括明细数据层 DWD 和汇总数据层 DWS。
数据仓库的分层和各层级用途_云原生大数据计算服务 MaxCompute-阿里云帮助中心
模型层次
-
ODS：把操作系统数据几乎无处理地存放在数据仓库中（可以做一部分 clean 工作）。 同步：结构化数据增量或全量同步到 MaxCompute。 结构化：非结构化（日志）结构化处理并存储到 MaxCompute。 累计历史、清洗：根据数据业务需求及稽核或审计要求保存历史数据、清洗数据。
-
CDM 存放明细事实数据、维表数据及公共指标汇总数据，其中明细事实数据、维表数据一般根据 ODS 层数据加工生成；公共指标汇总数据一般根据维表数据和明细事实数据加工生成。 DWD 和 DWS，主要采用维度模型方法建设，多采用维度退化的方法，及将维度退化到事实表，减 少事实表和维表的关联，提高事实表的易用性；在汇总层，加强指标的维度退化，采用宽表化手段构 建公共指标数据层，提升公共指标的复用性，减少重复加工。 组合相关和相似数据：采用明细宽表，复用关联计算，较少数扫描。 公共指标统一加工：基于 OneData 体系构建命名规范、口径一致和算法统一的统计指标，为上 层数据产品、应用和服务提供公共指标；建立逻辑汇总宽表。 建立一致性维度：建立一致的数据分析维表，降低数据计算口径、算法不统一的风险。
-
ADS：存放数据产品个性化的统计指标数据，根据 CDM 层与 ODS 层加工生成。 个性化指标加工：不公用型、复杂性（指标型、比值型、排名型指标）。 基于应用的数据组装：大宽表集市、横表转纵表、趋势指标串。
范式建模法：
范式是数据库逻辑模型设计的基本理论，一个关系模型可以从第一范式到第五范式进行无损分解，这 个过程也可称为规范化。在数据仓库的模型设计中目前一般采用第三范式，它有着严格的数学定义。 从其表达的含义来看，一个符合第三范式的关系必须具有以下三个条件 : 每个属性值唯一，不具有多义性 ; 每个非主属性必须完全依赖于整个主键，而非主键的一部分 ; 每个非主属性不能依赖于其他关系中的属性，因为这样的话，这种属性应该归到其他关系中去。
建模三范式：
第一范式(INF)
在关系模式R中的每一个具体关系中，必须要有主键，并且每个属性值都是不可再分的最小数据单位，则称R是第一范式的关系。
通俗解释：一个字段只存储一项信息
例：班级:高三年1班，应改为2个字段，一个年级、一个班级，才满足第一范式
不满足第一范式
学号 姓名 班级
0001 小红 高三年1班
改成:
学号 姓名 年级 班级
0001 小红 高三年 1班
例2：
第二范式(2NF):
如果关系模式R中的所有非主属性都完全依赖于主关键字，则称关系R是属于第二范式的。
例：选课关系SCI(SNO,CNO,GRADE,CREDIT)其中SNO为学号，CNO为课程号，GRADEGE为成绩，CREDIT为学分。由以
上条件，关键字为组合关键字(SNO,CNO)
通俗解释：任意一个字段都只依赖表中的同一个字段
例:比如不符合第二范式
学生证 名称 学生证号 学生证办理时间 借书证名称 借书证号 借书证办理时间
改成2张表如下
学生证表：
学生证 学生证号 学生证办理时间
借书证表：
借书证 借书证号 借书证把你拉时间
例2：
第三范式(3NF):
关系模式R中的非主关键字不能依赖于其他非主关键字。即非主关键字之间不能有函数（传递）依赖关系。则称关系R是属于第三范式的。
通俗理解：一张表最多只存2层同类型信息
例:爸爸资料表，不满足第三范式
爸爸 儿子 女儿 女儿的小熊 女儿的海绵宝宝
改成
爸爸信息表：
爸爸 儿子 女儿
女儿信息表:
女儿 女儿的小熊 女儿的海绵宝宝
例2：
注意: 三范式只是一般设计数据库的基本理念，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，降低范式就是增加字段，减少了查询时的关联，提高查询效率，适当保留冗余数据。这就是反范式，反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的。如果完全遵守三大范式,可能会影响查询效率,所以一般不会完全遵循三大范式(只遵守前两大范式),即反范式化,反范式化一定要适度
维度建模法：
维度建模法简单描述就是按照事实表、维度表来构建数仓、集市。维度建模从分析决策的需求出发构 建模型，为分析需求服务，因此它重点关注用户如何更快速地完成需求分析，同时具有较好的大规模 复杂查询的相应性能。其典型的代表是星型模型，以及在一些特殊场景下使用的雪花模型。
维度建模的优缺点：
优点：以星型模型为例子，其针对各个维度做了大量的预处理，如按照维度进行预先的统计、分 类、排序等，从而极大的提升数据仓库的处理能力；
另一个优点是维度建模非常直观，紧紧围绕 着业务模型，可以直观的反映出业务模型中的业务问题。不需要经过特别的抽象处理，即可以完 成维度建模。
缺点：由于在构建星型模式之前需要进行大量的数据预处理，因此会导致大量的数据处理工作。 而且，当业务发生变化，需要重新进行维度的定义时，往往需要重新进行维度数据的预处理。而 在这些预处理过程中，往往会导致大量的数据冗余；另外一个维度建模法的缺点就是，如果只是 依靠单纯的维度建模，不能保证数据来源的一致性和准确性，而且在数据仓库的底层，不是特别 适用于维度建模的方法。 所以维度建模的领域主要适用于数据集市层，它的最大的作用其实是为了解决数据仓库建模中的性能 问题。维度建模很难能够提供一个完整地描述真实业务实体之间的复杂关系的抽象方法
维度建模大致分为一下几个步骤：
-
选择需要进行分析决策的业务过程。业务过程可以是单个业务时间，比如交易的支付、退款等； 也可以是某个事件的状态，比如当前的账户余额等；还可以是一系列相关业务时间组成的业务流 程，具体需要看我们分析的是某些时间发生情况，还是当前状态或是时间流转效率
-
选择粒度。在事件分析中，我们需要预判所有分析需求细分的程度，从而决定选择的粒度，粒度 是维度的一个组合。
-
识别维表。选择好粒度之后，就需要基于粒度设计维表，包括维度属性，用于分析时进行分组和 筛选。
-
选择事实。确定分析需要衡量的指标。
DWD层的建模与开发:
DWD：数仓明细层
DWD层的特点：数据的粒度和ODS层保持一致
相对于ODS，DWD层的特点：字段规范化，数据清洗、字段补充（常用维度信息补充）
可以根据ODS层的实际情况：ODS数据按照主题进切分。
维度补充：
将日期信息关联时间维表，都到更详细的时间维度分析:
年、月、日、旬、周、季度、周年日，双十一期间。
将pageid，等相关id，关联相关的字典维表:
页面标题、页面所属栏目、页面所属频道。
主题划分：可以根据经验和需要按照主题划分:
流量分析主题明细表
交互事件分析主题明细表
广告主题分析明细表
大致分层：
ODS
DWD 或DIM
DWS
ADS
DWD建立的表:
全局数据明细表：DWD_APP_GLB_DTL: 相对于ODS增加了公共的时间维度信息
流量分析主题明细表：DWD_APP_TFC_DTL:只包含页面浏览事件数据，增加页面描述相关维度信息。（栏目、频道、类别）
交互事件分析主题明细表：DWD_APP_ITR_DTL:只包含各类交互事件数据（点赞、分享、收藏、评论）
广告事件分析主题明细表：DWD_APP_ADV_DTL：只包含各类广告事件，增加页面维度信息，广告维度
完整例：ODS层建表(ODS_APP_LOG_DEMO)
数据文件:
链接：https://pan.baidu.com/s/1s1gytjzZXbWRkWJYUaADTA
提取码：oxmu
解压后将文件夹内的数据放到hdfs上，hadoop hdfs -put xxx xxxhive>create external table ODS_APP_LOG_DEMO
(
guid bigint,
eventid String,
event Map<String, String>,
uid String,
imei String,
mac String,
imsi String,
osName String,
osVer String,
androidId String,
resolution String,
deviceType String,
deviceId String,
uuid String,
appid String,
appVer String,
release_ch String,
promotion_ch String,
areacode String,
longtitude Double,
latitude Double,
carrier String,
netType String,
cid_sn String,
ip String,
sessionId String,
`timestamp` bigint,
province String,
city String,
district String
)
partitioned by (dt string)
stored as parquet;
hive>load data inpath '/mycluster/user/applogs' into table ODS_APP_LOG_DEMO partition(dt='2023-03-01'); //文件在hdfs上
hive> desc formatted ODS_APP_LOG_DEMO;
hive> select count(1) from ODS_APP_LOG_DEMO;DWD层的 DWD_APP_TFC_DTL_DEMO、DWD_APP_ITR_DTL_DEMO 模拟关联维度信息
DWD_APP_TFC_DTL_DEMO(流量明细表)
hive>create table DWD_APP_TFC_DTL_DEMO(
guid bigint,
eventid String,
event Map<String, String>,
uid String,
imei String,
mac String,
imsi String,
osName String,
osVer String,
androidId String,
resolution String,
deviceType String,
deviceId String,
uuid String,
appid String,
appVer String,
release_ch String,
promotion_ch String,
areacode String,
longtitude Double,
latitude Double,
carrier String,
netType String,
cid_sn String,
ip String,
sessionId String,
`timestamp` bigint,
province String,
city String,
district String,
year string,
month string,
day string,
datestr string
)
partitioned by (dt string)
stored as parquet;
--计算
-- 原表 DWD_APP_LOG_DEMO
-- 目标 DWD_APP_TFC_DTL_DEMO
hive>insert into table DWD_APP_TFC_DTL_DEMO partition(dt='2023-03-01')
select
guid ,
eventid ,
event ,
uid ,
imei ,
mac ,
imsi ,
osName ,
osVer ,
androidId ,
resolution ,
deviceType ,
deviceId ,
uuid ,
appid ,
appVer ,
release_ch ,
promotion_ch ,
areacode ,
longtitude ,
latitude ,
carrier ,
netType ,
cid_sn ,
ip ,
sessionId ,
`timestamp` ,
province ,
city ,
district ,
year(from_unixtime(cast (`timestamp`/1000 as bigint))) as year ,
month(from_unixtime(cast (`timestamp`/1000 as bigint))) as month ,
day(from_unixtime(cast (`timestamp`/1000 as bigint))) as day,
from_unixtime(cast (`timestamp`/1000 as bigint),'yyyy-MM-dd') as datestr
from ODS_APP_LOG_DEMO where dt='2023-03-01' and eventid='pgviewEvent';广告维表的建表和数据 AIM_AD_INFO_DEMO
hive>create table AIM_AD_INFO_DEMO
(
adId string,
name string,
medium string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
data:
linux>vi AimData.txt
0,user0,medium0
1,user1,medium1
2,user2,medium2
3,user3,medium3
4,user4,medium4
5,user5,medium5
6,user6,medium6
7,user7,medium7
8,user8,medium8
9,user9,medium9
hive> load data local inpath '/root/tmp_data/data/AimData.txt' into table AIM_AD_INFO_DEMO; //本地广告主题明细表增加维度案例:
广告主题明细表(DWD_APP_ADV_DTL_ADD_DIM_DEMO)
hive>create table DWD_APP_ADV_DTL_ADD_DIM_DEMO(
guid bigint,
eventid String,
event Map<String, String>,
uid String,
imei String,
mac String,
imsi String,
osName String,
osVer String,
androidId String,
resolution String,
deviceType String,
deviceId String,
uuid String,
appid String,
appVer String,
release_ch String,
promotion_ch String,
areacode String,
longtitude Double,
latitude Double,
carrier String,
netType String,
cid_sn String,
ip String,
sessionId String,
`timestamp` bigint,
province String,
city String,
district String,
adv_name string,
adv_medium string
)
partitioned by (dt string)
stored as parquet;
计算
原表:ODS_APP_LOG_DEMO
目标表:DWD_APP_ADV_DTL_ADD_DIM_DEMO
hive>with t as(
select * from ODS_APP_LOG_DEMO
where dt='2023-03-01' and (eventid='adClickEvent' or eventid='adShowEvent')
)
insert into table DWD_APP_ADV_DTL_ADD_DIM_DEMO partition(dt='2023-03-01')
select
t.guid ,
t.eventid ,
t.event ,
t.uid ,
t.imei ,
t.mac ,
t.imsi ,
t.osName ,
t.osVer ,
t.androidId ,
t.resolution ,
t.deviceType ,
t.deviceId ,
t.uuid ,
t.appid ,
t.appVer ,
t.release_ch ,
t.promotion_ch ,
t.areacode ,
t.longtitude ,
t.latitude ,
t.carrier ,
t.netType ,
t.cid_sn ,
t.ip ,
t.sessionId ,
t.`timestamp` ,
t.province ,
t.city ,
t.district ,
d.name as adv_name,
d.medium as adv_medium
from t
join AIM_AD_INFO_DEMO d
where t.event['adId']=d.adId;DWS_APP_TFC_AGS_DEMO 流量会话聚合表:
流量会话聚合表 DWS_APP_TFC_AGS_DEMO
hive支持的文件格式：text file/sequence file/orc file /parguet file
hive>create table DWS_APP_TFC_AGS_DEMO(
guid bigint,
sessionId string,
start_time bigint,
end_time bigint,
pv_counts int,
province string,
city string,
district string,
devicetype string,
osname string,
osVer string,
release_ch string,
promotion_ch string
)
partitioned by (dt string)
stored as parquet;
hive>insert into table DWS_APP_TFC_AGS_DEMO partition(dt='2023-03-01')
select
guid,
sessionId,
min(`timestamp`) as start_time,
max(`timestamp`) as end_time,
count(1) as pv_counts,
max(province) as province,
max(city) as city,
max(district) as district,
max(devicetype) as devicetype,
max(osname) as osname,
max(osVer) as osVer,
max(release_ch) as release_ch,
max(promotion_ch) as promotion_ch
from ODS_APP_LOG_DEMO where dt='2023-03-01'
group by guid,sessionId;DWS_APP_TFC_AGU_DEMO 流量用户聚合表
流量用户聚合表 DWS_APP_TFC_AGU_DEMO
hive>create table DWS_APP_TFC_AGU_DEMO(
guid bigint,
sesion_counts int,
access_time bigint,
pv_counts bigint,
province string,
city string,
district string,
devicetype string,
osname string,
osVer string,
release_ch string,
promotion_ch string
)
partitioned by (dt string)
stored as parquet;计算
源表：流量会话聚合表 DWS_APP_TFC_AGS_DEMO
hive>insert into table DWS_APP_TFC_AGU_DEMO partition(dt='2023-03-01')
select
guid,
count(1) as sesion_counts,
sum(end_time-start_time) as access_time,
sum(pv_counts) as pv_counts,
max(province) as province,
max(city) as city,
max(district) as district,
max(devicetype) as devicetype,
max(osname) as osname,
max(osVer) as osVer,
max(release_ch) as release_ch,
max(promotion_ch) as promotion_ch
from DWS_APP_TFC_AGS_DEMO where dt='2023-03-01'
group by guid;多维分析
建表 流量概况报表 ADS_APP_TFC_OVM_DEMO
hive>create table ADS_APP_TFC_OVM_DEMO(
dt string,
total_pv int, --总pv数
total_uv int, --总访客数
total_session int, --总访问次数
avg_times_session double, --平均每次访问时长
avg_num_user double, --人均访问次数
avg_pv_user double, --人均访问深度
avg_time_user double, --人均访问时长
returncustomer_ratio double --回头客占比
)
stored as parquet;计算
源表:DWS_APP_TFC_AGU_DEMO
hive>insert into ADS_APP_TFC_OVM_DEMO
select
'2023-03-01' as dt,
sum(pv_counts) as total_pv,
count(1) as total_uv,
sum(sesion_counts) as total_session,
sum(access_time)/sum(sesion_counts) as avg_times_session,
sum(sesion_counts)/count(1) as avg_num_user,
sum(pv_counts)/count(1) as avg_pv_user,
sum(access_time)/count(1) as avg_time_user,
count(if(sesion_counts>2,1,null))/count(1) as returncustomer_ratio
from DWS_APP_TFC_AGU_DEMO where dt='2023-03-01';
扫一扫
2380
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
