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,都是团队来完成的,一个人的力量是有限的
联系客服