数仓之模型开发实战

        本小结主要根据一个实际的分析需求场景，用离线数仓的分层建模思维及预处理思想加速用户在查询数据报表时的效果及性能。

        首先我们有如下几张表：

销售流水表sale

字段	类型	描述
id	int	主键自增id
commodity_id	int	商品id
area_id	int	区域id
price	numeric(14,2)	成交价格
created_date	timestamp	销售时间

商品表commodity

字段	类型	描述
id	int	主键自增id
commodity_class	varchar(64)	商品类型
name	varchar(64)	商品名称
selling_price	numeric(14,2)	售价
created_date	timestamp	商品创建时间
updated_date	timestamp	商品更新时间

地区表area

字段	类型	描述
id	int	主键自增id
area_code	varchar(16)	区域编码
area_name	varchar(64)	区域名称
level	int	级别
province	varchar(64)	省份
city	varchar(64)	城市

假设销售流水表 1亿数据，商品表80万， 地区表4千，流水数据时间跨度为1年。

现在系统页面需要开发一个报表查询功能，用以查询历史销售流水在日期、省份、商品类型三个维度下分组统计的金额，并且支持这三个维度条件查询，当天能查到昨天以前的数据即可（即允许数据延迟T+1），查询速度要求在300ms内，查询的结果样例如下。

销售日期	省份	商品类型	成交价格
2022-09-01	北京	电子设备	59787.5
2022-09-01	上海	美妆	65894
2022-09-01	上海	食品	6589
2022-09-02	北京	电子设备	69787.5
2022-09-02	上海	美妆	35894
2022-09-02	上海	食品	3589

一般最直接的开发方式就是编写一个查询sql支持后端系统查询库表数据，以pg sql举例如下:

select 
s.to_char(s.created_date,'yyyy-mm-dd') as sale_date, 
c.commodity_class,
area.province,
sum(s.price) as s_price
from sale s
join commodity c
on s.commodity_id =c.id
join area
on s.area_id=area.id
group by s.to_char(s.created_date,'yyyy-mm-dd'), c.commodity_class,area.province

但是我们很难让这样的sql在postgresql或者mysql数据中以300ms的时间返回结果，即时我们做了表分区也只能优化部分查询条件的性能。

接下来我们以离线数仓思路实现

首先我们将抽象一个最终结果表，用以支持上述需求的查询，它的结构如下：

字段	类型	描述
sale_date	Date	销售日期
commodity_class	varchar(64)	商品类型
province	varchar(64)	省份
price	numeric(14,2)	成交价格

这张表命名为dm_s_sale 集市销售汇总表。我们以这张表为查询目标支撑上述需求条件完全能够达到，因为查询的数量级由最初的亿级缩减到万级，三张表变为一张表。

那么在数仓中我们如何从原始库表生成这张聚合表呢。

首先我们在数仓的ods层构建三张与业务库表结构一致的表分别为ods_sale、ods_commodity、ods_area，我们使用前文提到的datax从业务库将三张表的全量数据同步到数仓的ods库表，同时将商品和地区两张维度表的全量同步任务配置到azkaban定时，而对于销售流水表则基于销售时间进行增量同步。

然后我们在DW同样的构建三张dw_sale、dw_commodity、dw_area表，他们的表结构首先是包含ods所有字段的。同时我们可以生成一些扩展字段，比如dw_sale增加sale_date销售日期字段。同时从ods抽取放入dw库表时我们也会做一些数据清洗和异常数据处理，例如将price字段为null的值转换为0等。

最后根据dw的多张明细表聚合统计生成最终的dm_s_sale表，后续也是基于时间定时增量生成新数据。

从ods到dm库表的整个处理流程我们可以基于kettle组件实现处理。这就是一个简单的基于分析需求构建数仓多层表的整体流程，最后速度快的核心就在于我们通过预处理的方式把原始表进行聚合处理生成一个小数量级的统计表。