ETL的可扩展性和可维护性

ETL的过程我想很多人都谈过了，最近我在比较SSIS，OWB和infomatica，结合以前的项目，加深了我对ETL过程的理解和认识。

其实这三种工具，除去应用平台以为，大同小异，各有利弊。今天我想分享一下我的经验，主要在扩展和维护方面。

1：数据落地和ELT

很多人谈到数据落地的概念，说白了，就是ODS或者DWH中，有数据input或者output的，都先把要操作的数据放到临时表里面，而数据传输的两端的表的结构都是一样的，这样的操作比较便捷，几乎不用考虑转换的问题。

同时我们还要提到的就是ELT，ELT和ETL其实各有好处，一个是用工具来转化数据，一个用SP，工具一般都是一行行的处理，而SP一般都是一列列的处理。我个人认为ELT，用SP处理数据比较好，因为我们的测试或者将来的维护，都需要经常改变表间的mapping关系。而SP只是需要在数据库里面做一些修改和操作，而且还比较容易调试，去发现问题的根源。

2：脚本文件

这里的脚本文件可以分为FTP script，table Script，Stored proc Script还有Shell脚本，不管是dos shell 还是unix shell，这些都是一些操作系统的控制文件，我们可以暂时不谈。为什么要谈谈这些脚本了，其实我们在设计和开发结构的时候很简单，但是我们修改的时候，就比较麻烦了，特别是ETL工具在打开一个package的时候，需要加载和验证，有的时候很慢很慢，如果我们只是修改一些小参数的话，花长时间就不值得，如果我们将脚本文件都放在一起的话，那么打开txt或者bat文件就很方便。还有就是我们对于一些临时表，有的时候需要将数据全部删掉，我们可以用 truncate table，有的时候用drop和create也是不错的选择。

当然，如果用脚本文件的话，主机的安全性一定要好，而且对于账号的表级操作权限也要分配好。

3. 策略表

我们按照不同的频率，定时执行package，有的时候可能遇到error，有的时候可能由于其他的问题不能按时执行。其实我们可以做一张策略表，存储每个package的执行的时间规则，然后每天开始执行前，将所有的package初始化出当天的执行情况，记录开始结束时间，还有成功标记。执行时，可以先读取上一个执行日的执行情况，将未完成的package，延续的到当日。

4. 日志表

准确的来说，ETL工作中，从package执行开始的那一刻，每一个小步都要记录日志，记录发生了什么，做了什么，还有出错的信息和控制情况，必要时，我们还要按照数据库的事物处理方式，将相关的包的执行数据全部释放掉。这样日志表和策略表join，我们就可以清楚的看到我们执行的性能，效率和准确的详细的执行信息。根据package的优先级，我们可以将日志报表和紧急的错误，通过SMS或者email，发给相关的人员。

5. 重新执行

重新刷新数据是经常遇到的，我们发现数据不准确，就有可能需要重新执行，重新执行有中断重新执行和完全重新执行，甚至需要全部重新执行。

我们用batchid来控制每一天或者每一批的数据，但是有的时候由于这样那样的问题，倒是我们的package在执行中中断，我们就需要去寻找日志，解决问题，然后重新执行。这一步的手工操作比较多，完全重新执行就是重新执行某天的某个packge，需要将对应的数据表做一些操作，比如删除，全部重新执行，就是将数据仓库里面的数据全部重新创新，相当于从0开始。

这几种操作都是我们会遇到的，我们在设计包的时候，尽量的让package自己去完成，我们人为的控制尽量的减少。可以用一些参数的方式去控制。

6. 团队协作性

ETL的工作占整个数据仓库工作量的50%-70%，所以团队协作性一定要好。而ETL包含E，T，L还有日志的控制，数据模型，原数据验证，数据质量等等方面。例如我们要整合一个企业亚太区的数据，但是每个国家都有自己的数据源，有的是ERP，有的是Access，而且数据库都不一样，好要考虑网络的性能问题，如果直接用ODBC去连接两地的数据源，这样的做法很显然是不合理的，因为网络不好，经常连接，很容易数据库链接不能释放导致死机。如果我们在各地区的服务器放置一个数据导出为access或者flat file的程序，这样文件就比较方便的通过FTP的方式进行传输。我们就需要有几项工作：

1）有人写一个通用的数据导出工具，可以用java，可以用VB，或者其他的工具，总而言之就是要通用，可以通过不同的脚本文件来控制，使各地区的不同数据库导出的文件格式是一样的。而且还可以实现并行操作。

2）有人写FTP的程序，可以用bat，可以用ETL工具，可以用其他的方式，总之要准确，而且方便调用和控制。

3）有人设计数据模型，包括1）点中的导出后的结构，还有ODS和DWH中的表结构

4）有人写SP，包括ETL中需要用到的SP还有日常维护系统的SP，比如检查数据质量之类的

5）有人分析原数据，包括表结构，数据质量，空值还有业务逻辑

6）有人负责开发流程，包括实现各种功能，还有日志的记录等等。

7）有人测试

真正好的ETL，都是团队来完成的，一个人的力量是有限的