最近公司的项目要用到数据交换(ETL)
所以对ETL进行了一下研究:
一、ETL:E、T、L分别代表抽取、转换和装载
二、ETL的架构:同步、异步
1、同步:源数据库---ETL--->文本文件 -------网络--------- 文本文件 ---ETLà目标数据库;
2、异步:源数据库 ---ETL---->目标数据库。
三、分类:
从转换规则的复杂度和数据量大小来看。
1、交互式运行环境,你可以指定数据源、目标数据,指定规则,立马ETL。这种交互式的操作无疑非常方便,但是只能适合小数据量和复杂度不高的ETL过程,因为一旦规则复杂了,可能需要语言级的描述,不能简简单单拖拖拽拽就可以的。还有数据量的问题,这种交互式必然建立在解释型语言基础上,另外他的灵活性必然要牺牲一定的性能为代价。所以如果要处理海量数据的话,每次读取一条记录,每次对规则进行解释执行,每次在写入一条记录,这对性能影响是非常大的。
2、专门编码型的,它提供了一个基于某种语言的程序框架,你可以不必将编程精力放在一些周边的功能上,例如读文件功能、写数据库的功能,而将精力主要放在规则的实现上面。这种近似手工代码的性能肯定是没话说,除非你的编程技巧不过关(这也是不可忽视的因素之一)。对于处理大数据量,处理复杂转换逻辑,这种方式的ETL实现是非常直观的。
3、代码生成器型的,它就像是一个ETL代码生成器,提供简单的图形化界面操作,让你拖拖拽拽将转换规则都设定好,其实他的后台都是生成基于某种语言的程序,要运行这个ETL过程,必须要编译才行。Datastage就是类似这样的产品,设计好的job必须要编译,这避免了每次转换的解释执行,但是不知道它生成的中间语言是什么。以前我设计的ETL工具大挪移其实也是归属于这一类,它提供了界面让用户编写规则,最后生成C++语言,编译后即可运行。这类工具的特点就是要在界面上下狠功夫,必须让用户轻松定义一个ETL过程,提供丰富的插件来完成读、写和转换函数。大挪移在这方面就太弱了,规则必须手写,而且要写成标准c++语法,这未免还是有点难为最终用户了,还不如做成一个专业编码型的产品呢。另外一点,这类工具必须提供面向专家应用的功能,因为它不可能考虑到所有的转换规则和所有的读写,一方面提供插件接口来让第三方编写特定的插件,另一方面还有提供特定语言来实现高级功能。例如Datastage提供一种类Basic的语言,不过他的Job的脚本化实现好像就做的不太好,只能手工绘制job,而不能编程实现Job。
4、最后还有一种类型叫做数据集线器,顾名思义,他就是像Hub一样地工作。将这种类型分出来和上面几种分类在标准上有所差异,上面三种更多指ETL实现的方法,此类主要从数据处理角度。目前有一些产品属于EAI(Enterprise Application Integration),它的数据集成主要是一种准实时性。所以这类产品就像Hub一样,不断接收各种异构数据源来的数据,经过处理,在实施发送到不同的目标数据中去。
虽然,这些类看似各又千秋,特别在BI项目中,面对海量数据的ETL时,中间两种的选择就开始了,在选择过程中,必须要考虑到开发效率、维护方面、性能、学习曲线、人员技能等各方面因素,当然还有最重要也是最现实的因素就是客户的意象
四、微观规则
1、直接映射,原来是什么就是什么,原封不动照搬过来,对这样的规则,如果数据源字段和目标字段长度或精度不符,需要特别注意看是否真的可以直接映射还是需要做一些简单运算。
2、字段运算,数据源的一个或多个字段进行数学运算得到的目标字段,这种规则一般对数值型字段而言。
3、参照转换,在转换中通常要用数据源的一个或多个字段作为Key,去一个关联数组中去搜索特定值,而且应该只能得到唯一值。这个关联数组使用Hash算法实现是比较合适也是最常见的,在整个ETL开始之前,它就装入内存,对性能提高的帮助非常大。
4、字符串处理,从数据源某个字符串字段中经常可以获取特定信息,例如身份证号。而且,经常会有数值型值以字符串形式体现。对字符串的操作通常有类型转换、字符串截取等。但是由于字符类型字段的随意性也造成了脏数据的隐患,所以在处理这种规则的时候,一定要加上异常处理。
5、空值判断,对于空值的处理是数据仓库中一个常见问题,是将它作为脏数据还是作为特定一种维成员?这恐怕还要看应用的情况,也是需要进一步探求的。但是无论怎样,对于可能有NULL值的字段,不要采用“直接映射”的规则类型,必须对空值进行判断,目前我们的建议是将它转换成特定的值。
6、日期转换,在数据仓库中日期值一般都会有特定的,不同于日期类型值的表示方法,例如使用8位整型20040801表示日期。而在数据源中,这种字段基本都是日期类型的,所以对于这样的规则,需要一些共通函数来处理将日期转换为8位日期值、6位月份值等。
7、日期运算,基于日期,我们通常会计算日差、月差、时长等。一般数据库提供的日期运算函数都是基于日期型的,而在数据仓库中采用特定类型来表示日期的话,必须有一套自己的日期运算函数集。
8、聚集运算,对于事实表中的度量字段,他们通常是通过数据源一个或多个字段运用聚集函数得来的,这些聚集函数为SQL标准中,包括sum,count,avg,min,max。
9、既定取值,这种规则和以上各种类型规则的差别就在于它不依赖于数据源字段,对目标字段取一个固定的或是依赖系统的值。
五、可能导致数据质量问题原因:
1、数据格式错误,例如缺失数据、数据值超出范围或是数据格式非法等。要知道对于同样处理大数据量的数据源系统,他们通常会舍弃一些数据库自身的检查机制,例如字段约束等。他们尽可能将数据检查在入库前保证,但是这一点是很难确保的。这类情况诸如身份证号码、手机号、非日期类型的日期字段等。
2、数据一致性,同样,数据源系统为了性能的考虑,会在一定程度上舍弃外键约束,这通常会导致数据不一致。例如在帐务表中会出现一个用户表中没有的用户ID,在例如有些代码在代码表中找不到等。
3、业务逻辑的合理性,这一点很难说对与错。通常,数据源系统的设计并不是非常严谨,例如让用户开户日期晚于用户销户日期都是有可能发生的,一个用户表中存在多个用户ID也是有可能发生的。
其他的错误:
1、规则描述错误。上面提到对设计人员对数据源系统理解的不充分,导致规则理解错误,这是一方面。另一方面,是规则的描述,如果无二义性地描述规则也是要探求的一个课题。规则是依附于目标字段的,在探求之三中,提到规则的分类。但是规则总不能总是用文字描述,必须有严格的数学表达方式。我甚至想过,如果设计人员能够使用某种规则语言来描述,那么我们的ETL单元就可以自动生成、同步,省去很多手工操作了。
2、ETL开发错误。即时规则很明确,ETL开发的过程中也会发生一些错误,例如逻辑错误、书写错误等。例如对于一个分段值,开区间闭区间是需要指定的,但是常常开发人员没注意,一个大于等于号写成大于号就导致数据错误。
3、人为处理错误。在整体ETL流程没有完成之前,为了图省事,通常会手工运行ETL过程,这其中一个重大的问题就是你不会按照正常流程去运行了,而是按照自己的理解去运行,发生的错误可能是误删了数据、重复装载数据等。
七、有人建议:
1、提供前端。将开发实施人员当作用户,同样也要为之提供友好的用户界面。《稽核技术》一文中指出测试报告的形式,这种形式还是要依赖人为判断,在一堆数据中去找规律。到不如用OLAP的方式提供界面,不光是加上测试统计出来的指标结果,并且配合度量方法的计算。例如误差率,对于误差率为大于0的指标,就要好好查一下原因了。
2、提供框架。数据验证不是一次性工作,而是每次ETL过程中都必须做的。因此,必须有一个框架,自动化验证过程,并提供扩展手段,让实施人员能够增加验证范围。有了这样一个框架,其实它起到规范化操作的作用,开发实施人员可以将主要精力放在验证脚本的编写上,而不必过多关注验证如何融合到流程中,如何展现等工作。为此,要设计一套表,类似于DM表,每次验证结果数据都记录其中,并且自动触发多维分析的数据装载、发布等。这样,实施人员可以在每次装载,甚至在流程过程中就可以观察数据的误差率。特别是,如果数据仓库的模型能够统一起来,甚至数据验证脚本都可以确定下来,剩下的就是规范流程了。
3、规范流程。上回提到有一种ETL数据质量问题是由于人工处理导致的,其中最主要原因还是流程不规范。开发实施人员运行单独一个ETL单元是很方便的,虽然以前曾建议一个ETL单元必须是“可重入”的,这能够解决误删数据,重复装载数据问题。但要记住数据验证也是在流程当中,要让数据验证能够日常运作,就不要让实施者感觉到他的存在。总的来说,规范流程是提高实施效率的关键工作,这也是以后要继续探求的。
八、ETL工具:
开源的有kettle、octopus;
商业的:beeload、datastage(IBM)、informatic、Reiking、ODI;