1.数据仓库及数仓中的数据模型及操作描述
在大数据设计与开发过程中，数据仓库是必不可少的一部分。但很多开发者将数仓理解为很多业务库和业务表的汇总集合，这是不全面的。数仓存在很多设计，架构，业务建模等多个维度的问题。关于数仓的大体介绍可以参照之前的文章(https://www.cnblogs.com/jiashengmei/p/12092691.html), 本篇只分享数据仓库中的数据模型及操作。落地到实际中就是在某些业务场景下，数仓的表怎样去设计。
有过大数据开发经验的开发者都明白，在大数据环节，数据的操作不能像RDMS数据库那样。比如最常见的，在RDMS库中，假设是mysql数据库，对数据的单条或根据一定条件修改更新，单条或根据一定条件删除等等一些操作是再正常不过的。可能会因为数据库表记录多存在一定的性能问题。而在大数据环节，很多开发者都知道，大数据的很多数据服务组件其实不支持单条删除或者更新操作的，比如hive，clickhouse等等数据组件。虽然某些大数据组件在高版本也做到对数据进行特殊的删除修改，但是也是不建议使用的。为什么会有这样的问题或者设计？，很多大数据开发人员都清楚，在大数据开发中，往往是面对TB、PB甚至更多的数据，也会面对光一个表就可能是10亿、100亿甚至更多的条数的数据。在这种情况下，如果不按分而治之的思想去对数据进行操作，而是采用传统的RDMS数据组件的方式去操作，有一半的操作是没法进行的，同时会对数据质量，数据的一致性、完整性、时效性造成破坏，同时会对数据服务带来问题。而分而治之的设计其实就是对数据进行分区，最常见的有按天分区(T-1)模式，按小时分区(H-1)模式。在这些前提下，数仓的数据模型，表结构，及表对数据的存储和操作都需要区别于传统的RDMS单独设计。当然，如何设计一定是站在业务的基础上，只是某些设计方案和方法具备共用性。下面就举例介绍在以hive为数仓基础上的几个常用的数据模型表设计。
2.数仓之拉链表
以电商中的订单为例，经常会遇到这样的需求
a.表中的部分字段会被update,最常见的如订单的状态
b.需要查看某一个时间点或者时间段的历史快照信息，比如查看某一个订单在历史某一个时间点的状态,比如查看某一个用户在过去某一段时间内，更新过几次。
c.变化的比例和频率不是很大，比如，总共有1000万的会员，每天新增和发生变化的有10万左右。
举个简单例子,比如有一张订单表,6月20号有3条记录:
| 订单创建日期 | 订单编号 | 订单状态 |
|---|---|---|
| 2020-06-20 | 001 | 创建订单 |
| 2020-06-20 | 002 | 创建订单 |
| 2020-06-20 | 003 | 支付完成 |
到6月21日,表中有5条记录:
| 订单创建日期 | 订单编号 | 订单状态 |
|---|---|---|
| 2020-06-20 | 001 | 支付完成(从创建到支付) |
| 2020-06-20 | 002 | 创建订单 |
| 2020-06-20 | 003 | 支付完成 |
| 2020-06-21 | 004 | 创建订单 |
| 2020-06-21 | 005 | 创建订单 |
可以看出从2020-06-20到2020-06-21,订单编号为001的订单状态有修改操作，订单编号为004、005是新增操作
到6月22日，表中有6条记录：
| 订单创建日期 | 订单编号 | 订单状态 |
|---|---|---|
| 2020-06-20 | 001 | 支付完成(从创建到支付) |
| 2020-06-20 | 002 | 创建订单 |
| 2020-06-20 | 003 | 已发货(从支付到发货) |
| 2020-06-21 | 004 | 创建订单 |
| 2020-06-21 | 005 | 支付完成(从创建到支付) |
| 2020-06-22 | 006 | 创建订单 |
如果按照上述方式数据存储下来，会遇到以下几个问题
a.只保留一份全量，如当前时间为6月22号，数据如6月22日的记录一样，如果需要查看6月21日订单003的状态，则无法满足。
b.每天都保留一份全量，即将每天的状态作全量的保留，则数据仓库中的该表共有14条记录，有记录是重复的，没有任务变化，如订单002,004，数据量大了，会造成很大的存储浪费
基于以上问题，采用拉链表来设计订单表
从6月20号到6月22号拉链表的记录如下::
| 订单创建日期 | 订单编号 | 订单状态 | dw_begin_date | dw_end_date |
|---|---|---|---|---|
| 2012-06-20 | 001 | 创建订单 | 2012-06-20 | 2012-06-20 |
| 2012-06-20 | 001 | 支付完成 | 2012-06-21 | 9999-12-31 |
| 2012-06-20 | 002 | 创建订单 | 2012-06-20 | 9999-12-31 |
| 2012-06-20 | 003 | 支付完成 | 2012-06-20 | 2012-06-21 |
| 2012-06-20 | 003 | 已发货 | 2012-06-22 | 9999-12-31 |
| 2012-06-21 | 004 | 创建订单 | 2012-06-21 | 9999-12-31 |
| 2012-06-21 | 005 | 创建订单 | 2012-06-21 | 2012-06-21 |
| 2012-06-21 | 005 | 支付完成 | 2012-06-22 | 9999-12-31 |
| 2012-06-22 | 006 | 创建订单 | 2012-06-22 | 9999-12-31 |
拉链表字段描述
a. dw_begin_date表示该条记录的生命周期开始时间,dw_end_date表示该条记录的生命周期结束时间。
b. dw_end_date='9999-12-31'表示该条记录目前处于有效状态。
下面来几个查询：
a.查询当前订单的最新有效状态
select * from order_his where dw_end_date='9999-12-31';
查询结果
b.如果查询2020-06-21的历史快照
select * from order_his where dw_begin_date<='2020-06-21' and dw_end_date>='2020-06-21';
查询结果
从以上结果可以看出,这样的拉链表,既能满足对历史数据的需求,又能很大程度的节省存储资源。
3.拉链表的更新操作
现在有如下需求：
a.数据仓库中订单历史表的刷新频率为一天，当天更新前一天的增量数据。
b.如果一个订单在一天内有多次状态变化，则只会记录最后一个状态的历史。
c.订单状态包括三个：创建、支付、完成。
d.创建时间和修改时间只取到天，如果源订单表中没有状态修改时间，那么抽取增量就比较麻烦，需要有个机制来确保能抽取到每天的增量数据。
基于以上需求，需要创建三张表
源系统中订单表
CREATE TABLE orders(
order_id string,
create_time string,
modified_time string,
status string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
数仓的ODS层,有一张订单的增量数据表,按天分区,存放每天的增量数据
CREATE TABLE t_ods_orders_inc(
order_id string,
create_time string,
modified_time string,
status string)
PARTITIONED BY (day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
数仓的DW层,有一张订单的历史数据拉链表,存放订单的历史状态数据
CREATE TABLE t_dw_orders_his (
order_id string,
create_time string,
modified_time string,
status string,
dw_start_date string,
dw_end_date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
orders表在2020-08-21的全量数据
在2020-08-22这天抽取orders全量数据到t_ods_orders_inc
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2020-08-21')
SELECT order_id,create_time,modified_time,status
FROM orders WHERE create_time<='2020-08-21';
在2020-08-22这天抽取t_ods_orders_inc全量数据到t_dw_orders_his
INSERT overwrite TABLE t_dw_orders_his
SELECT order_id,create_time,modified_time,status,
create_time AS dw_start_date,
'9999-12-31' AS dw_end_date
FROM t_ods_orders_inc WHERE day = '2020-08-21';
t_dw_orders_his数据明细如下
因为这是第一次通过拉链表同步数据，可能看不出什么特殊的情况，现在来演示增量更新
orders表在2020-08-22的全量数据
在2020-08-23号只做增量同步抽取2020-08-22的数据到t_ods_orders_inc
INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2020-08-22')
SELECT order_id,create_time,modified_time,status
FROM orders
WHERE create_time = '2020-08-22' OR modified_time = '2020-08-22';
查询结果如下
通过DW历史数据(数据日期为2015-08-21)，和ODS增量数据(2020-08-22)，刷新历史表
先把数据放到一张临时表中：
DROP TABLE IF EXISTS t_dw_orders_his_tmp;
CREATE TABLE t_dw_orders_his_tmp AS
SELECT order_id,
create_time,
modified_time,
status,
dw_start_date,
dw_end_date
FROM (
SELECT a.order_id,
a.create_time,
a.modified_time,
a.status,
a.dw_start_date,
CASE WHEN b.order_id IS NOT NULL AND a.dw_end_date > '2020-08-22' THEN '2020-08-21' ELSE a.dw_end_date END AS dw_end_date
FROM t_dw_orders_his a
left outer join (SELECT * FROM t_ods_orders_inc WHERE day = '2020-08-22') b
ON (a.order_id = b.order_id)
UNION ALL
SELECT order_id,
create_time,
modified_time,
status,
modified_time AS dw_start_date,
'9999-12-31' AS dw_end_date
FROM t_ods_orders_inc
WHERE day = '2020-08-22'
) x
ORDER BY order_id,dw_start_date;
最后把临时表中数据插入历史表
INSERT overwrite TABLE t_dw_orders_his SELECT * FROM t_dw_orders_his_tmp;
最后t_dw_orders_his全表的数据如下：
现在来几个查询，看拉链表是否能够满足基本的业务需求
查询订单编号为001的整个状态流程
查询订单编号为002的目前状态
查询2020-08-21的历史快照
说明
a.在实际实践中，只要数据到了DW层的拉链表,其他临时相关的表就可以删除了，以便减少数据的存储
b.实际开发设计时，上述过程是完全自动化的
4.数仓模型中其他几种类型的表
全量表：每天的所有的最新状态的数据
增量表：每天的新增数据
流水表：对于表中的每一个修改都会记录，可以用于反映实际记录的变更