close
当前位置: 物联网在线 > IT技术 > 商业智能 >

从事务性数据到数据仓库数据的抽取方式

 从事务性数据到数据仓库的 ETL的过程中,存在一个普遍的又非常棘手的问题,就是如何将事务性数据完整无缺但又高性能(本文不考虑因硬件因素影响的性能,仅从数据抽取方式上探讨)的导入到数据仓库中去。如果不考虑性能,无论表数据大小都可以全量抽取加载,但事实上受多种因素的影响,将事务性数据库全量导入 是不现实的。而且即便是全量抽取,如果处理不当,仍然会带来意想不到的问题,比如如果以天为周期,从事务性数据库中抽取数据库到数据仓库中,而加载方式又是覆盖式,既先做TRUNCATE,再做APPEND,那么对于那些状态数据(比如用户表中的属性数据)的历史状态就会丢失,数据仓库就不能反映出数据的历史性。对于一些大表数据,全量抽取与加载显得不太现实,于是就提出了增量抽取,那么在事务性数据中如何才能体现增量数据呢?也就是说增量抽 取需要依赖事务性数据是否支持增量数据。

1.事务性数据库中的数据分类

在决定以何种方式抽取并加载数据之前,对事务性数据库的数据做一番分析是有必要的,作为一个通用的事务性数据库,库中的数据可以按如下方式分类

 根据操作类型:INSERT数据、UPDATE数据、DELETE数据

 根据数据的可更新性:可更新数据(INSERT UPDATE)、不可更新数据(INSERT

可更新数据一般都是指数据状态的更新,比如用户表中的用户属性就是属于可更新数据,像用户住址属性值会随着用户乔迁地址而改变,职位属性值会随着用户的职位改变而改变

不可更新数据一般指流水型数据,比如通话产生的流水数据,登录日志数据等,这些数据一般都是一次性发生,不会对其做UPDATE操作。

 根据数据的变更频率:数据性数据(INSERT)、配置性数据(SELECT

数据性数据是指在数据库表中频繁做INSERT操作的数据,比如流水数据

置性数据是指一次性能完成的数据输入,或者数据在系统中比较稳定,比如邮政编码数据,这样的数据基本上是保持不变的;再比如系统菜单配置数据,一般都是稳定的,只有在系统升级时才能对菜单项作出修改。

 根据数据量的大小:小表数据、大表数据

小表数据:数据量相对较少,不足100W条记录

表数据:数据量相对较大,超过100W条记录

2. 数据接入数据仓库中的流程

根据上述分析,我们可以按如下步骤做数据抽取工作了

STEP 1 判断接入数据仓库中的表是属于大表还是小表,如果是小表,则全量抽取;如果是大表则进行第二步

STEP 2 判断数据是可更新数据还是不可更新数据,如果是不可更新数据,则根据数据库表中的数据录入时间做增量抽取,也就是只抽取最新插入的数据;如果数据库表中没有录入时间戳,则进行第三步;如果是可更新数据则进行第四步

STEP 3、如果数据库表中没有录入时间戳,则进行录入日志分析,选择抽取最新录入的数据

STEP 4、分析可更新数据中INSERT数据,按照第二步进行;对可更新表中的UPDATE数据,进行第五步分析

STEP 5、分析可更新表是否有更新时间戳,如果是则抽取最新更新的数据,如果没有更新时间戳,则进行第六步分析

STEP 6、如果数据库表中没有更新时间戳,则进行更新日志分析,选择抽取最近更新的数据

3. 对事务性数据库建表约束

从上面的流程图中可以看出,最简单的抽取方式只全量抽取,最复杂的方式是分析日志抽取。普遍存在的是增量抽取。为了使增量抽取变得流畅些,显然需要来自事务性数据库本身结构的支持,为了能有效的支持数据仓库的数据抽取,对事务性数据库的设计做如下约束:

1INSERT数据都必须要有录入时间戳(CREATE_DATE)。录入时间戳(CREATE_DATE)在一般的事务性数据库中都有设计,每条数 据记录都会有录入时间;但是也有个别顽固性事务性数据库里面没有时间戳的概念,因为对他们(数据库设计人员)来说,增加时间戳字段就违背了规范化原则(时间戳在理论上是可以唯一标识数据记录的)

2UPDATE数据都必须要有更新时间戳(UPDATE_DATE)。更新时间戳(UPDATE_DATE)很少有在事务性数据库中有设计,因为对事 务性数据库而言,他们只关心最新的状态数据,记录数据的更新时间戳放在表中没有太大的现实意义,看起来只会增加存储冗余而已,而对日后的数据仓库而言,意义就显得非常重大了。

3DELETE数据必须要保留。在设计事务性数据库时,有必要对DELETE数据专门建立对应的虚拟表进行存储。可以用基于DELETE TRIGGER来完成转存。将DELETE掉的数据转存起来是必要的,因为数据仓库在STAGE存储层要求数据越明细越好,数据越明细就越能满足不同角度不同业务需求的要求,也越能反映当时的真实的历史事实。

4、数据仓库中的处理

因为数据仓库存储的是历史数据,其中的一个功能就是能反映历史的状态,比如在用户表中,某个用户在去年一年内反复乔迁住址,市场部门的决策者可能对此有兴趣,因此他们需要一年内乔迁的明细记录,分析乔迁住址对使用本企业业务的影响。这个在事务性数据库中肯定不会有记录,因为事务性数据库中只保留了最新的状 态,既该用户最新的住址。然而这个在数据仓库中很容易得到,因为数据仓库中保留了该用户每次乔迁住址的明细流水记录。

1、用分析函数ROW_NUMBER,可以得到与事务性数据库最接近的状态数据。如果如果要得到某用户最新的住址,则就可以根据用户编码分组,根据状态变更时间戳排降序,只获取序号为1的状态数据既为最新的住址数据

2、用合并操作MERGER,针对INSERTUPDATEDELETE三种事务性操作,分别编码为123,然后用全量数据与增量数据做MERGER,这样得到的就是最新的快照数据

5 .总结

数据仓库的构建是漫长的过程,是基于事务性数据库的数据存储。随着事务性数据库与数据仓库在整个企业信息中扮演的角色越来越明确化,随着企业决策层越来越接受并依赖数据仓库,数据仓库承受的压力也越来越大,她需要满足并提供给决策者突如其来的信息需求:即时的、历史的。因此数据仓库必须要包含企业的所有业 务数据,用时间戳为主线贯彻始终。这些数据都在事务性数据库中有表现。因此,企业的事务性数据库和数据仓库又是紧密联系的,在设计事务性数据库的时候一定要考虑到日后企业数据仓库的需要,要尽可能的保留全部的数据,以供数据仓库做分析之用。


you might also like

  • 关于移动互联网产品的指标分析初探
  • 进行数据挖掘的8个最佳开源工具
  • 创业公司做数据分析(六)数据仓库的建设
  • 2017 Gartner数据科学魔力象限出炉,16位上榜公司花落谁家?
  • 一个资深数据人对数据挖掘解读
  • 数据科学的基本内容
  • kylin与superset集成实现数据可视化
  • 想从事大数据、海量数据处理相关的工作,如何自学打基础?
  • 追踪 | 获投5千万 他为华为拆解数据联系 自然语言绘用户画像定制内容
  • 我们找了 4 家大数据公司技术 Leader,聊了聊算法和数据挖掘工程师的机会和选择

  • (责任编辑:ioter)