数据仓库之ETL

注：参考xiaohai798的专栏、http://superlxw1234.iteye.com/blog/1666960

一、ETL是什么

        ETL是Extract Transform Load三个英文单词的缩写 中文意思就是抽取、转换、加载。说到ETL就必须提到数据仓库。

1.1 背景：

       信息是现代企业的重要资源，是企业运用科学管理、决策分析的基础。目前，大多数企业花费大量的资金和时间来构建联机事务处理OLTP的业务系统和办公自动化系统（例如电信行业的各种运营支撑系统、购物网站系统），用来记录事务处理的各种相关数据。据统计，数据量每2～3年时间就会成倍增长，这些数据蕴含着巨大的商业价值，而企业所关注的通常只占在总数据量的2%～4%左右。因此，企业仍然没有最大化地利用已存在的数据资源，以致于浪费了更多的时间和资金，也失去制定关键商业决策的最佳契机。

        在这个背景下，能够给企业所有级别的决策制定过程提供支持的所有类型数据的战略集合应运而生，他就是数据仓库。数据仓库的英文简写是Data Warehouse。数据仓库就是把OLTP系统产生的数据 整合到一起 发掘其中的商业价值和提供决策支持用。举个电信行业的例子 电信有系统每天会有客户投诉的信息、宽带群体性障碍、客户号码的停机恢复时间记录等等。这些数据都在各自的生产环境系统里面。他们每个月会把这些数据整合到一起处理加工到数据仓库里面形成报表 其中有一个功能是可以对哪些用户有离网销号的倾向做出大概的判断。这就是数据仓库的价值所在。 

        那么怎么把数据弄到数据仓库里去呢，其中用到的一个技术就是ETL。

1.2 ETL的详细解释

        ETL(Extract-Transform-Load的缩写，即数据抽取、转换、装载的过程)作为DW的核心和灵魂，能够按照统一的规则集成并提高数据的价值，是负责完成数据从数据源向目标数据仓库转化的过程，是实施数据仓库的重要步骤。如果说数据仓库的模型设计是一座大厦的设计蓝图，数据是砖瓦的话，那么ETL就是建设大厦的过程。在整个项目中最难部分是用户需求分析和模型设计，而ETL规则设计和实施则是工作量最大的，约占整个项目的60%～80%，这是国内外从众多实践中得到的普遍共识。
        ETL是数据抽取（Extract）、清洗（Cleaning）、转换（Transform）、装载（Load）的过程。是构建数据仓库的重要一环，用户从数据源抽取出所需的数据，经过数据清洗,最终按照预先定义好的数据仓库模型，将数据加载到数据仓库中去。
        于是，企业如何通过各种技术手段，并把数据转换为信息、知识，已经成了提高其核心竞争力的主要瓶颈。而ETL则是主要的一个技术手段。

        做数据仓库系统，ETL是关键的一环。说大了，ETL是数据整合解决方案，说小了，就是倒数据的工具。ETL技术常用的工具有Informatica、Datastage、Beeload、Kettle等。

二、ETL执行流程

        大多数据仓库的数据架构可以概括为：数据源-->ODS(操作型数据存储)-->DW-->DM(data mart)。ETL贯穿其各个环节。

 

​2.1 数据抽取：

       可以理解为是把源数据的数据抽取到ODS或者DW中。

       1. 源数据类型：

           关系型数据库，如Oracle,MySQL,Sqlserver等;

           文本文件，如用户浏览网站产生的日志文件，业务系统以文件形式提供的数据等；

           其他外部数据，如手工录入的数据等；

       2. 抽取的频率：

           大多是每天抽取一次，​也可以根据业务需求每小时甚至每分钟抽取，当然得考虑源数据库系统能否承受；

       3. 抽取策略：

           个人感觉这是数据抽取中最重要的部分，可分为全量抽取和增量抽取。

           全量抽取适用于那些数据量比较小，并且不容易判断其数据发生改变的诸如关系表，维度表，配置表等；

           增量抽取，一般是由于数据量大，不可能采用全量抽取，或者为了节省抽取时间而采用的抽取策略；

               如何判断增量，这是增量抽取中最难的部分，一般包括以下几种情况：

               a) 通过时间标识字段抽取增量；源数据表中有明确的可以标识当天数据的字段的流水表，

                   如createtime，updatetime等；

               b) 根据上次抽取结束时候记录的自增长ID来抽取增量；无createtime,但有自增长类型字段的流水表，

                   如自增长的ID，抽取完之后记录下最大的ID，

                   下次抽取可根据上次记录的ID来抽取；

               c)  通过分析数据库日志获取增量数据，无时间标识字段，无自增长ID的关系型数据库中的表；

               d)  通过与前一天数据的Hash比较，比较出发生变化的数据，这种策略比较复杂，在这里描述一下，

                     比如一张会员表，它的主键是memberID,而会员的状态是有可能每天都更新的，

                     我们在第一次抽取之后，生成一张备用表A，包含两个字段，第一个是memberID,

                     第二个是除了memberID之外其他所有字段拼接起来，再做个Hash生成的字段，

                     在下一次抽取的时候，将源表同样的处理,生成表B,将B和A左关联，Hash字段不相等的

                     为发生变化的记录，另外还有一部分新增的记录，

                     根据这两部分记录的memberID去源表中抽取对应的记录；

               e) 由源系统主动推送增量数据；例如订单表，交易表，

                   有些业务系统在设计的时候，当一个订单状态发生变化的时候，是去源表中做update，

                   而我们在数据仓库中需要把一个订单的所有状态都记录下来，

                   这时候就需要在源系统上做文章，数据库​触发器一般不可取。我能想到的方法是在业务系统上做些变动，

                   当订单状态发生变化时候，记一张流水表，可以是写进数据库，也可以是记录日志文件。

               当然肯定还有其他抽取策略，至于采取哪种策略，需要考虑源数据系统情况，

               抽取过来的数据在数据仓库中的存储和处理逻辑，抽取的时间窗口等等因素。

 

2.2 数据清洗：

       顾名思义​，就是把不需要的，和不符合规范的数据进行处理。数据清洗最好放在抽取的环节进行，

       这样可以节约后续的计算和存储成本；

       当源数据为数据库时候，其他抽取数据的SQL中就可以进行很多数据清洗的工作了。

       ​数据清洗主要包括以下几个方面：

       1. 空值处理；根据业务需要，可以将空值替换为特定的值或者直接过滤掉；

       2. 验证数据正确性；主要是把不符合​业务含义的数据做一处理，比如，把一个表示数量的字段中的字符串

           替换为0，把一个日期字段的非日期字符串过滤掉等等；

       3. 规范数据格式；比如，把所有的日期都格式化成YYYY-MM-DD的格式等；

       4. ​数据转码；把一个源数据中用编码表示的字段，通过关联编码表，转换成代表其真实意义的值等等；

       5. 数据标准，统一；比如在源数据中表示男女的方式有很多种，在抽取的时候，直接根据模型中定义的值做转化，

           统一表示男女；

       6. 其他业务规则定义的数据清洗。。。

 

2.3 数据转换和加载：

       很多人理解的ETL是在经过前两个部分之后，加载到数据仓库的数据库中就完事了。

       数据转换和加载不仅仅是在源数据-->ODS这一步，ODS-->DW, DW-->DM包含更为重要和复杂的ETL过程。

       1. 什么是ODS？

           ODS（Operational Data Store）是数据仓库体系结构中的一个可选部分，

           ODS具备数据仓库的部分特征和OLTP系统的部分特征，

           它是“面向主题的、集成的、当前或接近当前的、 不断变化的”数据。​---摘自百度百科

           其实大多时候，ODS只是充当了一个数据临时存储，数据缓冲的角色。一般来说，

           数据由源数据加载到ODS之后，会保留一段时间，当后面的数据处理逻辑有问题，需要重新计算的时候，

           可以直接从ODS这一步获取，而不用再从源数据再抽取一次，减少对源系统的压力。

           另外，ODS还会直接给DM或者前端报表提供数据，比如一些维表或者不需要经过计算和处理的数据；

           还有，ODS会完成一些其他事情，比如，存储一些明细数据以备不时之需等等；

       2. 数据转换(刷新)：

           数据转换，更多的人把它叫做数据刷新，就是用ODS中的增量或者全量数据来刷新DW中的表。

           DW中的表基本都是按照事先设计好的模型创建的，如事实表，维度表，汇总表等，

           每天都需要把新的数据更新到这些表中。

           更新这些表的过程(程序)都是刚开始的时候开发好的，每天只需要传一些参数,如日期，来运行这些程序即可。

       3. 数据加载：

           个人认为，每insert数据到一张表，都可以称为数据加载，至于是delete+insert、truncate+insert、

           还是merge，这个是由业务规则决定的，这些操作也都是嵌入到数据抽取、转换的程序中的。

 

三、ETL工具

        在传统行业的数据仓库项目中，大多会采用一些现成的ETL工具，如Informatica、Datastage、微软SSIS等。

        这三种工具我都使用过，优点有：图形界面，开发简单，数据流向清晰；缺点：局限性，不够灵活，

        处理大数据量比较吃力，查错困难，昂贵的费用；

        选择ETL工具需要充分考虑源系统和数据仓库的环境，当然还有成本，如果源数据系统和数据仓库都采用

        oracle，那么我觉得所有的ETL，都可以用存储过程来完成了。。

        在大一点的互联网公司，由于数据量大，需求特殊，ETL工具大多为自己开发，

        或者在开源工具上再进行一些二次开发，在实际工作中，

        一个存储过程，一个shell/perl脚本，一个Java程序等等，都可以作为ETL工具。