vlambda
2021-08-10

数据中台之数据仓库架构设计|清风

前言

在企业数字化转型过程中，企业会面临不少问题，如90%以上的表建在同一个数据库（XXX库4000张表，表逻辑层次不清），表定义不规范，标准不统一（表命名形式多样化，时间分区形式不统一），数据建模意识差，缺乏对业务的高度抽象（数据报表生产直接依赖底层数表，数据、主题域划分不清晰，缺乏数据沉淀，重复计算浪费资源），烟囱式开发产生数据孤岛（数据调研不足，指标定义混乱，重复开发），各个业务团队各自建设数仓（风格多样化，管理维护成本高）。

面对这样的问题，本数仓的需求分析是这样的：

在本文中，笔者会按照架构五视图进行数仓的设计，下面我们一起按照逻辑架构、数据架构、开发架构、运行架构、物理架构的思路来遛一圈。在玩中学，在学中玩，在读书中学，在写作中学，在体系中学，跳出舒适区，跳出路径依赖，啊哈，灵机一动，good idea，go go go。

第一章逻辑架构

【本章主要包括：用例模型、用例描述、事件流、业务需求列表、需求规格说明、技术可行性分析】

一、名词解释

序号	名词	说明
1	用户	用户以设备为判断标准，在移动统计中，每个独立设备认为是一个独立用户。Android系统根据IMEI号，IOS系统根据OpenUDID来标识一个独立用户，每部手机一个用户。
2	新增用户	首次联网使用应用的用户。如果一个用户首次打开某APP，那这个用户定义为新增用户；卸载再安装的设备，不会被算作一次新增。新增用户包括日新增用户、周新增用户、月新增用户。
3	活跃用户	打开应用的用户即为活跃用户，不考虑用户的使用情况。每天一台设备打开多次会被计为一个活跃用户。
4	周（月）活跃用户	某个自然周（月）内启动过应用的用户，该周（月）内的多次启动只记一个活跃用户。
5	月活跃率	月活跃用户与截止到该月累计的用户总和之间的比例。
6	沉默用户	用户仅在安装当天（次日）启动一次，后续时间无再启动行为。该指标可以反映新增用户质量和用户与APP的匹配程度。
7	版本分布	不同版本的周内各天新增用户数，活跃用户数和启动次数。利于判断APP各个版本之间的优劣和用户行为习惯。
8	本周回流用户	上周未启动过应用，本周启动了应用的用户。
9	连续n周活跃用户	连续n周，每周至少启动一次。
10	忠诚用户	连续活跃5周以上的用户
11	连续活跃用户	连续2周及以上活跃的用户
12	近期流失用户	连续n(2<= n <= 4)周没有启动应用的用户。（第n+1周没有启动过）
13	留存用户	某段时间内的新增用户，经过一段时间后，仍然使用应用的被认作是留存用户；这部分用户占当时新增用户的比例即是留存率。例如，5月份新增用户200，这200人在6月份启动过应用的有100人，7月份启动过应用的有80人，8月份启动过应用的有50人；则5月份新增用户一个月后的留存率是50%，二个月后的留存率是40%，三个月后的留存率是25%。
14	用户新鲜度	每天启动应用的新老用户比例，即新增用户数占活跃用户数的比例。
15	单次使用时长	每次启动使用的时间长度。
16	日使用时长	累计一天内的使用时间长度。
17	启动次数计算标准
18	GMV	一定时间段内的成交总额，包括付款和未付款部分
19	转换率	实际下单的用户在单日总活跃用户中的比例，即单日消费用数/单日日活数量，其他统计口径包括：新访问用户转换率=单日新访问设备数/日活数；新注册用户转换率=单日新注册用户/日活数；新付费用户转换率=单日新付费用户/日活数
20	数据仓库	数据仓库方法论分为两大流派，一派为KimBall，另一派为InMon。KimBall采用自底向上（以终为始），从业务需求出发，采用敏捷开发模型，即从数据集市到数据仓库再到数据源的一个开发流程，适合数据稳定但数据间关系复杂的场景。Inmon模式采用自顶向下，从技术架构出发，采用瀑布模型开发，即从数据源到数据仓库再到数据集市，适合数据清洗多的场景，如网络爬虫数据采集。
21	数据构建流程	总体上看分为：业务建模->领域建模->逻辑建模->物理建模，详细来看则是梳理业务流程->数仓分域/主题->指标体系梳理->实体关系调研->维度梳理->数仓分层->模型建立。

二、项目需求

1、用户行为数据采集

2、业务数据采集平台搭建

3、数据仓库维度建模

4、分析GMV成绩总额、转化率、复购率，统计的报表指标近100个

5、采用即席查询工具，随时进行指标分析

6、对集群性能进行监控，发生异常需要报警

7、元数据管理

8、质量监控

三、用例视图

（1）用户行为数据采集用例图

数据中台之数据仓库架构设计|清风

（2）BI用例图

数据中台之数据仓库架构设计|清风

（3）业务元数据用例图

数据中台之数据仓库架构设计|清风

（4）技术元数据用例图

数据中台之数据仓库架构设计|清风

（5）操作元数据用例图

数据中台之数据仓库架构设计|清风

第二章数据架构

【本章主要包括：UML类图、数据库ER模型、模型映射关系有一对一，一对多，多对一，多对多、数据的持久化方式、数据存储的非功能质量实现】

一、MySQL业务数据库

MySQL数据库名称为gmall。

（1）订单表设计order_info

字段名称	字段类型	字段说明
id	BIGINT(20)	编号
consignee	VARCHAR(100)	收货人
consignee_tel	VARCHAR(20)
total_amount	DECIMAL(10,2)	总金额
order_status	VARCHAR(20)	订单状态
user_id	BIGINT(20)	用户id
payment_way	VARCHAR(20)	付款方式
delivery_address	VARCHAR(1000)
order_comment	VARCHAR(200)	订单备注
out_trade_no	VARCHAR(50)	订单交易编号（第三方支付用)
trade_body	VARCHAR(200)	订单描述(第三方支付用)
tracking_no	VARCHAR(100)	物流单编号
parent_order_id	BIGINT(20)	父订单编号
img_url	VARCHAR(200)	图片路径
create_time	DATETIME	创建时间
operate_time	DATETIME	操作时间
expire_time	DATETIME	订单失效时间

（2）订单详情表设计order_detail

字段名称	字段类型	字段说明
id	BIGINT(20)	编号
order_id	BIGINT(20)	订单编号
sku_id	BIGINT(20)	sku_id
sku_name	VARCHAR(200)	sku名称（冗余)
img_url	VARCHAR(200)	图片名称（冗余)
order_price	DECIMAL(10,2)	购买价格(下单时sku价格）
sku_num	VARCHAR(200)	购买个数

（3）Sku表设计sku_info

字段名称	字段类型	字段说明
id	BIGINT(20)	库存id(itemID)
spu_id	BIGINT(20)	商品id
price	DECIMAL(10,0)	价格
sku_name	VARCHAR(200)	sku名称
sku_desc	VARCHAR(2000)	商品规格描述
weight	DECIMAL(10,2)	重量
tm_id	BIGINT(20)	品牌(冗余)
category3_id	BIGINT(20)	三级分类id（冗余)
sku_default_img	VARCHAR(200)	默认显示图片(冗余)
create_time	DATETIME	创建时间
operate_time	DATETIME	操作时间

（4）用户表设计user_info

字段名称	字段类型	字段说明
id	BIGINT(20)	编号
login_name	VARCHAR(200)	用户名称
nick_name	VARCHAR(200)	用户昵称
passwd	VARCHAR(200)	用户密码
name	VARCHAR(200)	用户姓名
phone_num	VARCHAR(200)	手机号
email	VARCHAR(200)	邮箱
head_img	VARCHAR(200)	头像
user_level	VARCHAR(200)	用户级别
birthday	DATE	用户生日
gender	VARCHAR(1)	性别 M男,F女
create_time	DATETIME	创建时间
operate_time	DATETIME	操作时间

（5）支付流水表设计payment_info

字段名称	字段类型	字段说明
id	BIGINT	编号
out_trade_no	VARCHAR(20)	对外业务编号
order_id	VARCHAR(20)	订单编号
user_id	VARCHAR(20)	用户编号
alipay_trade_no	VARCHAR(20)	支付宝交易流水编号
total_amount	DECIMAL(16,2)	支付金额
subject	VARCHAR(20)	交易内容
payment_type	VARCHAR(20)	支付方式
payment_time	VARCHAR(20)	支付时间

（6）一级分类表设计base_category1

字段名称	字段类型	字段说明
id	BIGINT	编号
name	VARCHAR(10)	分类名称

（7）二级分类表设计base_category2

字段名称	字段类型	字段说明
id	BIGINT	编号
name	VARCHAR(10)	二级分类名称
category1_id	BIGINT	一级分类编号

（8）三级分类表设计base_category3

字段名称	字段类型	字段说明
id	BIGINT	编号
name	VARCHAR(10)	三级分类名称
Category2_id	BIGINT	二级分类编号

二、数据仓库

1、ODS层

（1）订单表设计ods_order_info

字段名称	字段类型	字段说明
id	string	订单编号
total_amount	decimal(10,2)	订单金额
order_status	string	订单状态
user_id	string	用户id
payment_way	string	支付方式
out_trade_no	string	支付流水号
create_time	string	创建时间
operate_time	string	操作时间

使用创建时间作为分区字段，字段分隔符使用'\t' ，表压缩方式使用snappy。

（2）订单详情表ods_order_detail

字段名称	字段类型	字段说明
id	string	编号
order_id	string	订单编号
user_id	string	用户id
sku_id	string	sku_id
sku_name	string	sku名称（冗余)
order_price	string	购买价格(下单时sku价格）
sku_num	string	购买个数
create_time	string	创建时间

使用创建时间作为分区字段，字段分隔符使用'\t' ，表压缩方式使用snappy。

（3）Sku表设计ods_sku_info

字段名称	字段类型	字段说明
id	string	库存id(itemID)
spu_id	string	商品id
price	string	价格
sku_name	string	sku名称
sku_desc	string	商品规格描述
weight	string	重量
tm_id	string	品牌(冗余)
category3_id	string	三级分类id（冗余)
create_time	string	创建时间

使用创建时间作为分区字段，字段分隔符使用'\t' ，表压缩方式使用snappy。

（4）创建用户表ods_user_info

字段名称	字段类型	字段说明
id	string	编号
name	string	用户姓名
email	string	邮箱
user_level	string	用户级别
birthday	string	用户生日
gender	string	性别 M男,F女
create_time	string	创建时间

使用创建时间作为分区字段，字段分隔符使用'\t' ，表压缩方式使用snappy。

（5）创建商品一级分类表ods_base_category1

字段名称	字段类型	字段说明
id	string	编号
name	string	分类名称

使用创建时间作为分区字段，字段分隔符使用'\t' ，表压缩方式使用snappy。

（6）创建商品二级分类表ods_base_category2

字段名称	字段类型	字段说明
id	string	编号
name	string	二级分类名称
category1_id	string	一级分类编号

使用创建时间作为分区字段，字段分隔符使用'\t' ，表压缩方式使用snappy。

（7）创建商品三级分类表ods_base_category3

字段名称	字段类型	字段说明
id	string	编号
name	string	三级分类名称
Category2_id	string	二级分类编号

使用创建时间作为分区字段，字段分隔符使用'\t' ，表压缩方式使用snappy。

（8）创建支付流水表ods_payment_info

字段名称	字段类型	字段说明
id	string	编号
out_trade_no	string	对外业务编号
order_id	string	订单编号
user_id	string	用户编号
alipay_trade_no	string	支付宝交易流水编号
total_amount	decimal(16,2)	支付金额
subject	string	交易内容
payment_type	string	支付方式
payment_time	string	支付时间

使用创建时间作为分区字段，字段分隔符使用'\t' ，表压缩方式使用snappy。

2、DWD层

积压ODS层对数据进行判空过滤。对商品分类表进行维度退化(降维)。

（1）订单表设计dwd_order_info

字段名称	字段类型	字段说明
id	string	订单编号
total_amount	decimal(10,2)	订单金额
order_status	string	订单状态
user_id	string	用户id
payment_way	string	支付方式
out_trade_no	string	支付流水号
create_time	string	创建时间
operate_time	string	操作时间

（2）订单详情表dwd_order_detail

字段名称	字段类型	字段说明
id	string	编号
order_id	string	订单编号
user_id	string	用户id
sku_id	string	sku_id
sku_name	string	sku名称（冗余)
order_price	string	购买价格(下单时sku价格）
sku_num	string	购买个数
create_time	string	创建时间

（3）用户表dwd_user_info

字段名称	字段类型	字段说明
id	string	编号
name	string	用户姓名
email	string	邮箱
user_level	string	用户级别
birthday	string	用户生日
gender	string	性别 M男,F女
create_time	string	创建时间

（4）支付流水表dwd_payment_info

字段名称	字段类型	字段说明
id	string	编号
out_trade_no	string	对外业务编号
order_id	string	订单编号
user_id	string	用户编号
alipay_trade_no	string	支付宝交易流水编号
total_amount	decimal(16,2)	支付金额
subject	string	交易内容
payment_type	string	支付方式
payment_time	string	支付时间

（5）创建商品表（增加分类）dwd_sku_info

字段名称	字段类型	字段说明
id	string	库存id(itemID)
spu_id	string	商品id
price	string	价格
sku_name	string	sku名称
sku_desc	string	商品规格描述
weight	string	重量
tm_id	string	品牌(冗余)
category3_id	string	三级分类id（冗余)
category2_id	string	2id
category1_id	string	1id
category3_name	string	三级分类名词
category2_name	string	二级分类名词
category1_name	string	一级分类名词
create_time	string	创建时间

3、DWS层

（1）用户行为宽表dws_user_action

字段名称	字段类型	字段说明
user_id	string	用户 id
order_count	bigint	下单次数
order_amount	decimal(16,2)	下单金额
payment_count	bigint	支付次数
payment_amount	decimal(16,2)	支付金额
comment_count	bigint	评论次数

（2）品牌复购率用户购买商品明细表（宽表）dws_sale_detail_daycount

字段名称	字段类型	字段说明
user_id	string	用户 id
sku_id	string	商品 Id
user_gender	string	用户性别
user_age	string	用户年龄
user_level	string	用户等级
order_price	decimal(10,2)	订单价格
sku_name	string	商品名称
sku_tm_id	string	品牌id
sku_category3_id	string	商品三级品类id
sku_category2_id	string	商品二级品类id
sku_category1_id	string	商品一级品类id
sku_category3_name	string	商品三级品类名称
sku_category2_name	string	商品二级品类名称
sku_category1_name	string	商品一级品类名称
spu_id	string	商品 spu
sku_num	int	购买个数
order_count	string	当日下单单数
order_amount	string	当日下单金额

4、ADS层

（1）GMV成交总额ads_gmv_sum_day

字段名称	字段类型	字段说明
dt	string	统计日期
gmv_count	bigint	当日gmv订单个数
gmv_amount	decimal(16,2)	当日gmv订单总金额
gmv_payment	decimal(16,2)	当日支付金额

（2）转化率新增用户占日活跃用户比率ads_user_convert_day

字段名称	字段类型	字段说明
dt	string	统计日期
uv_m_count	bigint	当日活跃设备
new_m_count	bigint	当日新增设备
new_m_ratio	decimal(10,2)	当日新增占日活的比率

（3）用户行为漏斗分析ads_user_action_convert_day

字段名称	字段类型	字段说明
dt	string	统计日期
total_visitor_m_count	bigint	总访问人数
order_u_count	bigint	下单人数
visitor2order_convert_ratio	decimal(10,2)	访问到下单转化率
payment_u_count	bigint	支付人数
order2payment_convert_ratio	decimal(10,2)	下单到支付的转化率

（4）品牌复购率ads_sale_tm_category1_stat_mn

字段名称	字段类型	字段说明
tm_id	string	品牌id
category1_id	string	1级品类id
category1_name	string	1级品类名称
buycount	bigint	购买人数
buy_twice_last	bigint	两次以上购买人数
buy_twice_last_ratio	decimal(10,2)	单次复购率
buy_3times_last	bigint	三次以上购买人数
buy_3times_last_ratio	decimal(10,2)	多次复购率
stat_mn	string	统计月份
stat_date	string	统计日期

第三章开发架构

【本章节内容主要包括：系统规划、接口定义、系统分层、技术选型、代码规范】

3.1、技术选型

在技术选型时我们主要从几个维度去考虑：数据量大小、业务需求、行业内经验、技术成熟度、开发维护成本、总成本预算。

建立一个完整的离线电商，我们可以使用的框架包括：

数据采集传输：Flume、Kafka、Sqoop、Logstash、DataX、Flinkx、kettle

数据存储：MySQL、postgresql、Greenplum、HDFS、HBase、Redis、MongoDB

数据计算：Hive、Tez、Spark、Flink、Storm

数据查询：Presto、Kylin、Impala、Druid、ElasticSearch

数据可视化：Echars、Superset、FineReport、QuickBI、DataV

任务调度：Azkaban、Oozie、Yarn、kubernetes

集群监控：Zabbix

元数据管理：Atlas

那我们如何选择Hadoop的框架版本呢？

（1）Apache版本，运维麻烦，组件间兼容性需要自己调研，一般是大厂使用，有技术实力，有专业运维人员

（2）CDH，国内使用最多的版本，但CM不开源，从2021年1月31日开始，要付费才能使用

（3）HDP，开源，可以进行二次开发，但是没有CDH稳定，国内使用的少

在选择版本时，需要着重考虑以下几点：

（1）是否开源

（2）是否有稳定版本

（3）是否经实践认证

（4）是否有强大社区支持

结合需求考量，本系统版本选型最终如下表格所示：

产品	版本
Hadoop	3.1.3
Flume	1.9.0
Kafka	2.4.1
Hive	3.1.2
Sqoop	1.4.6
Java	1.8
Zookeeper	3.5.7
Presto	0.189
Superset	1.0

3.2、整体架构

下面我们看看系统整体架构：

数据中台之数据仓库架构设计|清风

下面我们对数仓分层架构进行说明：

数据中台之数据仓库架构设计|清风

3.3、开发规范

3.3.1、分层规范

ODS（原始数据层）：ODS层是数据仓库准备区，为DWD层提供基础原始数据。

DWD（明细数据层）：和ODS粒度一致的明细数据，对数据进行去重，脏数据过滤，空处理，保证数据质量。

DWS（服务数据层）：轻度汇总数据及建宽表存放数据。

ADS（应用数据层）：存放应用类表数据。

3.3.2、表规范

维表命名形式：dim_描述；

事实表命名形式：fact_描述_[AB]；

临时表命名形式：tmp_ 正式表名_ [C自定义序号]；

宽表命名形式：dws_主题_描述_[AB]；

备份表命名形式：正式表名_bak_yyyymmdd；

存储格式使用默认PARQUET格式；

字符集统一使用utf-8；

表命名解释：

1）表名使用英文小写字母，单词之间用下划线分开，长度不超过40个字符，命名一般控制在小于等于6级。

2）其中ABC第一位"A"时间粒度：使用"c"代表当前数据，"h"代表小时数据，"d"代表天数据，"w"代表周数据，"m"代表月数据，"q"代表季度数据， "y"代表年数据。第二位"B"表示对象属性，用"t"表示表，用"v"表示视图。第三位"C"自定义序号用于标识多个临时表的跑数顺序。

业务规范层	数据库名称	数据库释义	物理表命名规范	数据存储格式
ODS	jg_ods_global	从公司各业务 MySQL 表同步的快照信息和运营埋点数据	埋点日志:ods_log{埋点说明}{更新方式}{时间粒度} 运营数据库:ods_{业务数据库表}_{更新方式}{时间粒度}	Text
DWD	jg_dwd_global	包含公司各业务经过 ETL 后的基础事实明细表	埋点日志:dwd_log业务过程更新方式_时间粒度运营数据库:dwd_{数据库类型 (mysql\hbase\wtable\redis)}_{业务过程}_{更新方式}_{时间粒度}多数据源:dwd_{业务过程}_{更新方式}_{时间粒度}	Parquet
DWS	jg_dws_global	按数据、业务专题进行划分的轻度汇总数据	dws_{业务主题域}_{业务过程}_{更新方式}_{时间粒度}	Parquet
DIM	jg_dim_global	业务维度相关的字典数据	dim_{维度类型 (cate\city\channel\group)}_{更新方式}_{时间粒度}	Text
TMP	jg_tmp_global	存放数据计算过程中的临时结果表	tmp_{数据层类型(dwd\dws\ads)_{业务过程描述}	Parquet
ADS	jg_ads_global	存放面向各业务应用分析的通用结果表	ads_{数据主题域}_{业务过程描述}_{更新方式}_{时间粒度}	Text\Parquet
ADS	jg_ads_{部门/ 产品线}	业务方个性化应用数据表	ads_{数据主题域}_{业务过程描述}_{更新方式}_{时间粒度}	Text\Parquet

3.3.3、代码规范

sql编码

1）关键字右对齐，代码注释详尽，查询字段时每行不超过三个字段，缩进时空四格等相关书写规范。

2）明细数据层依赖于ods层，应用数据层依赖于服务数据层，原则上，不允许跨层查询。

3）如果SQL语句连接多表时，应使用表的别名来引用列。

4）WHERE条件中参数与参数值使用的类型应当匹配，避免进行隐式类型转化。

5）在SELECT语句中只获取实际需要的字段。

shell脚本

调度脚本主要是通过跑shell脚本，shell脚本的注意点：

1）命名与所跑的目标表名相同，注释要完善，后缀以.sh结尾。

2）脚本头需要加上分割线、作者、日期、目的、描述等信息。

第四章运行架构

【本章主要包括：属性→场景→决策，其中属性主要有性能、可靠性、安全性、可扩展性、易用性、架构演化、同步还是异步，并发还是串行】

数据流图

系统整体运行架构如下图所示：

采用KimBall数仓建模方法论建模。数据源包括前端埋点日志，OLTP系统，半结构化文档数据，经过ETL处理后装载到数据仓库，再将数据推送到数据集市，提供给OLAP系统处理，最pull/push到应用系统。

时序图

第五章物理架构

【本章主要包括：部署，集中式还是分布式，组件部署、网络，网络拓扑，安全组划分、性能：可伸缩性，可靠性】

Hadoop采用1+1+3 集群方式，1主1备3个数据节点。

Hive部署在主备节点上。

服务器规划

服务名称	子服务	服务器 s100	服务器 s10	服务器 s1	服务器 s2	服务器 s3	服务器s4
HDFS	NameNode	√
	SecondaryNameNode		√
	DataNode			√	√	√
Yarn	NodeManager			√	√	√
	Resourcemanager	√
Zookeeper	Zookeeper Server	√
Flume(采集日志)	Flume		√
Kafka	Kafka			√	√	√
Flume（消费Kafka）	Flume						√
Hive	Hive	√	√
MySQL	MySQL	√
Sqoop	Sqoop	√	√
Presto	Coordinator	√
	Worker			√	√	√
Hbase	HMaster	√
	HRegionServer			√	√	√
Superset			√
应用服务							√
服务数总计		8	5	5	5	5	3

以上，就是一个完整的数仓架构实施路径。下一篇，将会show you my code，搞起搞起。