[转]ETL随笔（一）

http://www.alidw.com/?p=205

谈到ETL，很多人都认为最重要的只是涉及到抽取、转换、加载那些脚本以及脚本的效率这几个方面，其实不尽然， ETL其实包含了很多方面，现在我谈谈个人在ETL方面的心得。

（一）数据源。在跨系统做数据抽取的时候往往忽视了几个重要的原则，其一，源表结构的稳定性。如果源表结构有改变，如何保证ETL不受影响。其二，数据接口。源系统如何把数据提供给目标，如果是文本形式，那么就有必要对文本的格式进行约定，同时就如何保证文本的到达通讯、文本的完整性做好相应的策略，通常的做法都是由源提供一个标志文件，目标到固定未知去监测这个文件何时到达，并读取文件中的信息：数据日期、数据大小、记录数、增量or全量。如果是直接到源系统抽取，那么就要确保何时可以去抽取。通常的做法都是配置任务依赖。其三，数据源的选择。许多的ETL工程师往往忽视这个，认为只要数据源能满足自己的业务需求就可以，其实是错误的想法。在许多情况下，大量的数据汇总、拆分、解析都是十分耗时间的，如果库中已经有了相关的汇总、解析好的数据，建议直接使用这些数据，因为从那些数据源上抽取要比你直接从明细数据上面去汇总、解析要省事省时得多，这也是为什么dw中一般都有通用汇总层的原因之一。
   （二）抽取。谈到抽取想的最多就是抽取规则，源表是该增量抽取还是全量抽取，如果是增量抽取，该如何判断增量呢？通常的做法通过业务规则来判断，但是这种方法必须确保源系统的时效性非常高，否则容易造成数据的丢失。还有的做法是源系统上有触发器机制，触发器会根据预先设定好的增量规则把增量记录打上标签。还有一种做法就是目标系统在抽取的时候做hash比较。
   （三）转换。转换的过程是一个复杂的过程，中间涉及到数据的清洗、加工策略。所谓清洗就是对源数据中的脏数据的处理、不合规范数据的标准化、合法化过程。常见的处理有：
        1）超出值域的部分的处理。比如某个属性根据业务方的规则应该只有1、2两个属性值，但是抽取过来的数据却出现了3、4等，这时候就需要通知业务方一起商定一个处理策略了
        2）重复数据的处理。
        3）null值、0值的处理。
        4）不合法时间的处理。
        5）乱码、不符合业务规定的数据的清理等
   （四）加载。常见的加载策略有：
        1）trunctae and insert.直接清空目标表，然后把新的数据加载进去。
        2) append.先根据规则清除当天的记录（防止回滚），然后把当天的新数据追加进去。
        3) update and insert.用新数据与目标表中的历史数据进行比较，有变化的则更新，新记录则直接插入到目标表中。
        4) keep history change.保持历史的变化，通常做法中有两种方式，其一是用新数据与目标表中的历史数据进行比较，有变化的数据则更新关闭日期为当前日期（该日期根据需要可自由设定），同时把新的记录插入到目标表，这种加载策略往往都有开始日期、结束日期。其二是在目标表中增加序列号，对于变化的记录打上新的序列号后再插入到目标表。
   （五）ETL流程。实际上ETL流程可以看成是对ETL策略的进阶，也是影响一个ETL作业的效率高低的最重要因素。ETL流程实际就是在ETL过程中，数据流的路径，好的数据流可以大幅度的提高ETL作业的效率。下面列举一种情况的ETL流程设计方案：
         1）dw中的当前表。对于这种表不需要保存历史变化的轨迹，只需要保证表中的记录最全、当前状态最新就可以了，如果每天抽取的源数据是全量的，那么你就应该采用直接把目标表清空，然后加载新的记录就可以了，而不是用源数据和目标表记录比较然后再更新。但是如果源数据是增量的或者无法确定是增量还是全量，那么你就需要考虑何种策略来加载了，加载方法有a）用源与目标关联update。b）用源与目标merge。c）用源与目标比较找出变化的记录，这时候你可以选择先删除目标中那些变化的记录，然后再把新记录插到目标中，也可以选择把发生变化的记录所对应的新记录以及没变化的记录、新增的记录都插入一个临时表中，然后再清空目标并把临时表数据加载进去或者用设个临时表与目标表之间交换表名。
       不难看出，上面有多种加载方法，不同的方法在不同情况下各有优势，要看具体情况来进行取舍了，比如源、目标的数据量大小以及机器、数据库的配置等，因此在做ETL的时候多想想，花费那些时间在这上面是很有必要，对于ETL流程设计以及如何合理的拆分作业流这两个议题我将在后续的文章中继续补充，敬请关注。