ETL工具设计

ETL工具的使用者是谁?

    在大部分BI项目组,都有一个专职的ETL工程师,负责ETL的正常调度和错误处理,此人是主要使用者。

    另外就是项目组其他成员,这些人一般都会SQL语句,会写存储过程,还懂一点业务,是数据仓库建设的中坚力量,他们直接影响了数据仓库好坏,但一般不会象ETL工程师一样,深入追究具体的ETL步骤,他们只关心数据流,数据的依赖关系。ETL工具做为数据仓库的驱动器,应该着力为这批人服务,就是要清晰的展现出数据流,明确的标明数据流向。另外一点是绝大多数的转换规则、映射关系都出之这部分人,然后由专门的ETL工程师来实现,由于在交接时会产生一些理解偏差,会带来一些问题,同时,这些规则在项目初创时期会经常变动,导致ETL工程师非常被动,严重拖延进度。

    一直以来,我都有一个想法,开发一个易于部署、使用的ETL工具,弱化ETL工程师的责任,转换规则、映射关系由数据仓库设计人员自己来实现,目前已经有了比较清晰的方案,无论是前台界面还是后台结构,都初见雏形。但需要较多的人力、物力,个人是无法完成的。同时也很想证实一下,方案是不是合理,毕竟我经历了一个在ETL上遇到很大困难的BI项目和其他几个小项目,参与过数据仓库建设,维护过一个很难使用的ETL工具,参加了一个极度延时的ETL工具开发项目,经历了这么多的磨难,对ETL工具开发这块至少是积累了不少的行业经验,同时也从客户那里直接获得了不少的需求,对于功能的取舍相对理性。

    还有一些,就是客户,他们都会有专门的技术人员跟踪进度,时不时的关注一下ETL过程,毕竟ETL占有60%以上的工作量,但他们一般也只关心数据流,偶尔对数据质量有一些疑问,比如记录数变动过大,KPI效验无法通过。

    ETL中,有两种图,一种是“控制流程图,另外一个是“数据流程图”。

    常见的ETL工具,如DatastageInformatica都会展现“控制流程图”,并以此来进行调度。例如前者流程图中的每个节点是Job、命令行或是异常处理,而后者的节点是sessionworklet等,他们的节点都是"过程"。而其箭头连线表示的是控制转换,过程执行的顺序,并行还是串行。一个节点指向另一个节点,意味着,当头一个节点运行结束,或者到某种状态时,执行后一个节点,这种流程图的好处是对ETL过程了如指掌。(此段为 头头脑脑 所写,我没有用过这两个东东)

    但“数据流程图”比较少见,一般在元数据中见的比较多。这种图,关注的是数据之间的依赖关系。比如一张A表,其数据的来源是哪里,又派生出来多少数据集,派生的依据是什么。由于此图清晰的描述了数据之间的关系,在数据仓库应用中有着至关重要的作用。我刚到中国移动省级项目组时,要为一级经营分析准备数据,但当时,对业务的理解非常弱,面对纷乱复杂的数据流,根本不知道从哪里下手,每次都要问一经的负责人。现在看来,如果在ETL中提供了数据流的描述,效率要高很多。后来,新到的项目总监安排了一个人专门绘制这样图,项目组获益非常大。

    个人认为做为数据仓库驱动器的ETL工具,应该能够清晰的展现数据流图。目前绝大多数ETL工具的调度图都是按照抽取、转换、装载和清洗4大步骤展现,如此并没有大的错误,但没有真正理解ETL工具存在的最终目的。这些步骤只是手段,但最终目的是为了建设、维护数据仓库。因此,在ETL中至少应该看到如下的数据流图。


 

   在ETL工具中,对数据流进行描述是不是很困难?我看未必!目前,数据流向无非4种:表→表,表→文件,文件→表,文件→文件,应该很容易定义才对。元数据其实就是干这个工作的!如果能借鉴元数据,应该不难实现。个人认为难点在于与ETL本身的调度联系起来,对调度策略会有一些特殊的要求。一个大的数据流动才定为一个job,比如从接口到表,虽然经过了转换、清洗、装载,但这个步骤都只为一个目的服务:将数据从接口装入到表,因此可以定为一个job,而不是象常见的ETL工具,定为3job,否则就割裂了之间的联系,不便于描述数据流向。我曾看过另外一个公司的中国移动经营分析项目资料,从接口层到dw层,足有500多个任务,并以此做为宣传点。乍一看,觉得很专业,但做久了之后,觉得很不可思议,简直就是自讨苦吃,不仅割裂了数据之间的联系,也增加了维护难度,每修改一个接口都必须同时修改3处地方,苦不堪言。我以前使用的一个ETL工具也是采用类似的方法,吃尽了苦头。

ETL工具设计之二 - 狐妖之惑

相传每个被狐妖诱惑的人都将快乐的死去!

    开发ETL工具时,都会设计出4个步骤,extract(抽取)、transfer(转换)、clean(清洗)和load(装载)。但最重要的步骤是哪个?

    我想,目前90%以上的ETL工具开发人员都会认为是转换。一年前的我也是这么认为,但当我在项目组呆了一段时间,亲自参与了数据仓库建设,再也不这样想了。按重要程度递减排序,分别是load(装载)、clean(清洗)、transfer(转换)、extract(抽取)。当时在这个中国移动省级经营分析项目,用到的转换一般都是substrtrim,很少再用到其他的特殊转换。实际上,在数据仓库中,尽量避免使用复杂的转换,要不然,转的面目全非了,谁都头痛,最常见的不过是维表转换,但这个可以使用数据库的存储过程来实现,简单而且有效。

    曾经有一个ETL工具开发工程师喜滋滋的对我说,现在的转换已经做的非常不错了,每种转换都启动了一个线程,多个线程同时运行,速度非常快!呵呵,从局部来看,这个设计不错,但从整体的角度来看,没有把握住重点。ETL工具追求速度固然不错,但独独提升转换的速度就大大的不应该!ETL中最慢的是数据入库出库,而不是转换,如果使用c编程实现转换,启用数据库的直接入库方式,转换和装载的时间比一般是15,所以,即使转换的速度再快,对ETL整个速度的提升也不会起到什么作用。这个设计还忽略了另外一个问题,服务器上的CPU二级缓存一般都是1M以上,而一般的转换函数很少超过100条汇编语句,300个字节,正常情况下,转换函数都会被装入CPU缓存,用不着使用局部性原理提高速度。同时启动的线程过多,系统将在线程切换上消耗过多的时间!从项目的角度来看,设计是失败的,占用了过多的人力物力,费力不讨好!这个项目也是多灾多难,一年多过去后,开发的ETL工具依然没有达到真正可用的程度!

ETL工具设计之三 文件格式

以不变应万变!

    一张表,怎么表示才不会导致数据错误?

    常见的有两种方式:

    字符分割法:  用特殊字符做分割,比较常见的是 “ | ”符号,简单有效,但缺陷也是极其明显,一旦在数据中有分割符,将导致数据错误!

    定长分割法:  数据库中,字段的长度是固定的,只要设计得当,总能正确无误的描述所有的数据,但这个方法有一个很大的缺陷,很难表示blob字段。

    如果是开发ETL工具,用上面的方法明显是行不通的,我使过的一个ETL工具,也没有解决这个问题,但我后来辞职研究了一段时间的系统内核,研究了文件格式,发现AT&T实验室早就解决了数据展现的问题,因此,我模拟COFF文件格式,初步设计了一个新的文件格式,用来展现表状数据,以解决数据描述的问题,在文件中,各节之间使用 0x0来做分割。

Struct etl_file_head_t

{

}

文件头,存放各节的偏移量,文件都多少节,就有多少member,每个member表示一个节相对文件头的偏移量。

下面就是各节的具体内容

DataSrcName

数据源名节,存放数据源的名称,可以是文件名,也可以是数据库名

以下为定义源数据需要的节,存放最原始的数据,源数据什么样,他就是什么样,不作任何转换。比如在文本文件中,一个字段为数字型,其值为 “4660”(十进制),但当其到内存中后,如果转换成数字型,则为 0x3412x86CPU构架),但在此处,仍然存放的十进制字符串 “4660”,而不是二进制串 0x3412

Struct etl_rec_info_t

{

  int SrcRecCnt;

  int FieldCnt;

}

SrcRecCnt 源记录行数。

FieldCnt 字段个数

SrcFieldType

字段类型

Struct etl_srcfile_rec_len_t

{

  int Flag;

  int Len;

}

Flag 每行的标志位,在进行过滤后,可能会出现抛弃的情况,而且在此成员中,可以定义在哪里被抛弃,为什么被抛弃,便于最后做统计。

Len 行在数据节中的长度

DataSec

数据节,里面存放需要的每个字段以及长度。存放格式

struct etl_field_info_t

{

  int Len;    //此字段的长度

  void* pData;  //真正的数据

}

以下为定义已经转换数据需要的节,存放已经转换的数据,按二进制方式存放。比如在文本文件中,一个字段为数字型,其值为 “4660”(十进制),但当其到内存中后,如果转换成数字型,则为 0x3412x86CPU构架),在此处,则是二进制串 0x3412,,而不是十进制字符串 “4660”。(与上类似,不再重复)

    这样做,可能会带来数据量的问题,需要传送的数据成倍的增加。以一个字段为8字节计算,在32位机器上,字节数会增加2倍多一点。我以前所接触的数据,每天可达到20G的文本,所以每天需要多传送40G的数据,在主机之间,数据传送速度一般很高,我曾经测试的得到的速度是30Mbyte/s,以此速度计算,会有20多分钟的延时。但只要设计得当,采用多线程技术,传输和处理并行运行,延时将大大降低,根本不会对速度产生很大的影响。

    上面的设计,还不是很完善,如果需要在多平台下运行,必须指定结构的长度。大部分情况下,ETL程序会在不同的主机上同时运行,但这些主机型号不一定完全相同,对类型的解释就有可能不同,比如在32位和64位下, int类型的解释就有可能不一样。

ETL工具设计之四 - 万流归海

        ETL工具开发中,需要支持多主机,一般使用socket传送数据。同时,在不同主机运行的模块之间也需要传送各种控制命令,对于这样的需求,怎么设计网络层?

    网络编程其实是很大的一块,很需要设计功力,虽然我以前没有写过一行网络代码,但还是想尝试一下,在各位大大面前献丑了!勿怪!

    首先,我觉得网络数据传送机制和CPU中断处理机制极其相似:

CPU中断

网络传输

中断不可预测

网络的一端在正常情况下,也无法预测另外一端的是否有数据

经过中断处理器后,各种设备并发的中断成为串行。但经过合适的设计,串行的中断可以伪并行处理

两个节点之间,数据传送是串行的,但对数据的处理可以是并行的。

中断发生后,会改变CPU管脚的电压,直到CPU处理

数据传送后,操作系统会缓存数据,直到应用程序主动取。

    当然,也有不同点,而且非常致命:

CPU中断

网络传输

中断发生后,会向CPU发信号,CPU得到这个信号后,才详细询问这个信号的具体含义。这么做,显得非常优雅。

非常粗暴,发送端直接发送,而不管接受端当前的是否能够接受。

    我把网络模块设计成如下结构:


    操作系统传输层 此层实现socket所有的功能。

    传输控制层 ETL运行时,由于各模块处理速度不同,有可能产生数据拥塞。比如,转换的速度一般来讲都是要远大于装载速度,转换结束后,如果不加限制的传给装载模块,在装载部分就会产生数据积压,因此需要一种机制抑止此种情况的发生。

我目前想到的是采用应答机制,发送端有数据需要发送时,就向接受端发送一个有数据的信号。接受端接到信号后,就将这个发送端的状态置为有数据状态。以后接受端认为自己可以接受新的数据了,就向发送端发送一个可以发送数据的消息, 发送端接到这个消息后,然后发送数据。同样,对于消息的处理,也采用同样的机制。其实这种控制机制是借鉴了linux的中断处理机制,与之很类似。

    数据拼装/切割层 在底层socket,每次用send函数发送的数据包,一般不会超过2k大小,在ETL中,一般需要处理上G的数据,很多时候,单条记录都会超过2k,这时,就需要一个层次来拼装/切割上层应用程序的数据。比如最上层数据处理模块要求一次性的发送2M数据,在这层,就将其分割成2K大小的数据包,并按顺序编码,最后由最低层socket发送。接受端接到各个数据包后,再按顺序组装,最后供最上层应用模块使用。

    不过,在这层,会有一个隐患。在程序中,有一个原则,尽量不要动态的 分配/释放 内存,我以前在编写内核调试器的时候,做到了这一点,内存只申请一次,以后循环使用。但使用上面的设计,达不到这个要求,必须动态分配内存,因为有一个条件限制: 接受端是无法预测发送端会发送多少数据! 比如,BLOB字段,你就不知道他有多大,我曾见过一张表,它有一个BLOB字段,里面存放了日志文件,很多都超过了1M大小,也就是说,单条记录都超过了1M,正常情况下,谁会想到有这样的东东存在!而且还是在中国移动省级经分库里面!

    解析层 接受到数据后,需要判断是命令还是纯数据,并由此分发到不同的模块

    实际上,网络编程还有一个问题,如果接受端的内存过小,会出现数据截断。例如:

        A 发送端

        B 接受端

        AB发送两条记录, 每条记录各100字节,共200字节,B端的操作系统会将这200字节缓存起来。此时,运行在B上应用程序,用 recv(sockfd,pBuf, 150, flags); 函数接受数据,这个时候,只读出了150个字节,产生数据截断,第二条记录将出现问题。对于这个问题,最简单的解决办法是双方约定最大发送的字节数, 比如,在上面的例子中,如果A,B已经约定最大可以发送150字节,则A需要发送两次,每发送100字节,即一条记录。

ETL工具设计之五 - 初见雏形

    对于整个ETL程序框架,你会怎么设计?

    我的设计如下:


    这里将任务执行器和任务驱动器分开,本来,也可以设计成整体,但我总觉得分开为好,一个模块只做一件事情,便于控制和处理。

    上面这副图没有什么新意,估计绝大多数ETL工具都会设计成这样,呵呵,献丑了!

    然后呢,谈谈各部分的特点

    抽取模块(extract):如果是数据库的话,比较好办。数据库反正就那么几种,每种都写一个抽取程序就摆平了!麻烦的是数据效验,这个地方是最让我头痛的。在各项目都会有一些细微的差别,好像没有统一的解决方法,需要二次开发。如果俺们ETL售前工程师怯生生的对客户说:“我们的ETL工具需要你自己写文件效验程序”,客户听后不知会做何感想,会不会勃然大怒?呵呵!

但是,如果不是象我在另外一篇文章《在合适的地方使用合适的技术》中说的那样BT,还是有一些规律可寻的。常见的文件效验无非是个数、字节数两类。做一个统一的界面还是可以对付的。

    转换模块(transfer 我对转换的定义比较广泛,将清洗(clean)部分也看作是转换的一部分,觉得简单。对于这部分,没有什么特别的要求,有substrtrim功能就心满意足了。其他更高、更复杂的转换函数,等有人力物力的时候再开发吧。当然NULL值过滤功能是少不了的。

    装载模块(load 这个部分最影响ETL性能,速度一般只有转换的1/5。而且这一部分,必须自己写装载程序,不能依赖数据本身的工具。有一个ETL工具在ORACLE环境下运行时,就是调用的SQLLDR装载,一旦启动就没有办法控制,直到SQLLDR退出,真郁闷。这部分的速度达到30万条/分钟就可以了,即使每天4千万的数据量,2个小时多一点就可以搞定。

    统计模块(statistic 一般的ETL工具没有直接提出来这个功能,我自己加上的。有一段时间,客户突然对数据质量重视起来,天天向我要统计数据,比如源记录多少啊,转换丢失了多少,装载丢失了多少,库中有多少,后来又进行了KPI效验。这个模块可以最直接的反应出数据质量问题,将记录数一比照,问题一目了然。

    最后呢,让我来策一策怎么将这些模块拼装起来。无论是抽取,转换,还是清洗,都有一个特点,其功能都是不定的,而且可以拆分。我想将这些功能都独立开来,每个功能一个动态链接库,并且按统一的规则输出函数,比如每次动态链接库都必须有数据出口、数据入口、初始化和停止函数。运行时,根据配置动态的加载。这么做,有一个最大的好处,可以减少网络传送量,按逻辑设计,每个功能都应该非常独立,都是通过socket连接起来,有多少个功能就要传送多少次,有点过分!

 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值