一种对数据仓库友好的数据库设计

更多深度文章，请关注云计算频道：https://yq.aliyun.com/cloud

系统可以分为两种：一种是 联机交易系统（OLTP），一种是在线分析系统（OLAP）。OLTP用来收集数据，然后把数据同步到OLAP，在OLAP中进行分析数据。

OLAP可以减少OLTP的负载，提高对数据的利用率。

数据同步方法

将数据从OLTP同步到OLAP，有两种方法：全量同步和增量同步。

• 全量同步：每次把全表数据同步到OLAP对应的表

• 增量同步：每次把有变动的数据同步到OLAP对应的表

与增量同步相比，全量同步每次都要同步所有的数据，花费的时间大，对系统的负载高。

与全量同步相比，增量同步的难点在与如何识别出增量数据。

OLTP中友好的数据库设计

在建表的时候，包含如下字段：

• 主键：每个表都有一个主键

• created_time：表明数据是什么时候创建的，以后一直不变

• modified_time：表明数据是什么时候修改的，每次修改，这个字段都会更新成最新的时间

• deleted_flag：不物理删除，如果想要删除数据，就在将这个字段设为true。只要设置为true之后，以后一直不变，且modified_time也不再改变

同步过程

数据仓库一般分为ODS层和DW层。ODS存储OLTP中的原始数据，同步的过程主要发生在ODS层。方法如下：

只要modified_time为当天的记录，就是当天的增量数据。

所以，在同步的时候，只要对modified_time进行判断即可。

可能出现的异常：

比如，有一条记录A，创建日期是在20170304，但是在20170305 00:20有修改，然后修改时间变成了20170305。

同步任务本来是在20170305 00:00进行同步，但是由于调度延迟的问题，导致同步任务在20170305 00:30开始执行。

这个时候，就会漏掉记录A。因为在同步的时候，记录A的修改时间已经变成了20170305了。

一种解决办法是，将同步的条件修改为：

只要modified_time为当天的记录，或者created_time为当天的记录，就是当天的增量数据。

去重过程

在DW层对ODS层的表进行汇总需要去重。因为同一条记录可能经过多次修改，这些修改是发生在不同的日期中的。

去重的方法是使用分析函数row_number()over(partition by primary_key order by modified_time desc)。即，根据主键，取modified_time最新的记录。

同时在这个过程中，剔除deleted_flag为true的记录。

数据校验过程

在DW层对ODS层数据去重之后，就得到了和生产相同的数据。是否真的正确，可以使用如下的方法验证：

DW层的数据量 = 生产上所有的数据量(total) - 今天创建的数据量(created) - 今天之前删除的数据量(deleted)

以表table_a为例子

total = select count(*) from table_a

在这个过程中，不太好理解的是删除的数据量(deleted)的计算方法。可以这样想：

如果modified_time为今天，那么可以确定在今天之前，这条数据是存在的，因为deleted_flag为true后modified_time就不变了。