ETL工具设计

ETL工具的使用者是谁？

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

ETL工具设计之三– 文件格式

以不变应万变！

    一张表，怎么表示才不会导致数据错误？

    常见的有两种方式：

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

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

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

Struct etl_file_head_t

{

}

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

下面就是各节的具体内容

DataSrcName

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

以下为定义源数据需要的节，存放最原始的数据，源数据什么样，他就是什么样，不作任何转换。比如在文本文件中，一个字段为数字型，其值为“4660”（十进制），但当其到内存中后，如果转换成数字型，则为 0x3412（x86CPU构架），但在此处，仍然存放的十进制字符串“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”（十进制），但当其到内存中后，如果转换成数字型，则为 0x3412（x86CPU构架），在此处，则是二进制串 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：接受端

        A向B发送两条记录，每条记录各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）部分也看作是转换的一部分，觉得简单。对于这部分，没有什么特别的要求，有substr和trim功能就心满意足了。其他更高、更复杂的转换函数，等有人力物力的时候再开发吧。当然NULL值过滤功能是少不了的。

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

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

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