Alex的个人Blog

在08年底的时候vertica 宣布了它的ETL世界纪录，在57分21.51秒时间内装载了5.4TB 的记录，这项记录由Syncsort（一个DI 供应商）和Vertica 共同发布. 最近Greenplum 在mysql/fox interactive media 的应用也发布了一个ETL装载记录4TB/h.
vertica 的记录由16 two quad-core server blades and 16 storage blades in HP BladeSystem c-Class enclosures ， 运行在Red hat Linux operation system 上.
Greenplum 则是在一小时装载4TB , 由40台Sun fire 4500 运行的greenplum 集群组成,Sun fire 4500 有2 dual-core AMD .

两个声明都是由供应商自己发出，取自客户的实例，只不过greenplum 的应用是在myspace/fox 上. 更加实际,Vertica 更像是一种实验性质的测试.
阅读全文…

不少ETL工具为了控制复杂的元数据，都有一个中央的控制台，像是OWB 的Control Center 应该每个使用OWB 的开发人员都见过了， Informatica 特有一个Matadata Manager 作为它的一个附加组件，ODI 同样也有一个基于WEB 的控制台oracledimn 可以让你连接到ODI 的repository 查看元数据.

ETL 的控制中心都是为了方便开发人员能在众多的元数据中更好的管理ETL ，其中有3个主要的目的是每个ETL 工具都想达到的， 调度，执行报告和可视化的数据流 （或者叫专业一点Data Lineage).

调度就是让开发人员能够定时的执行ETL 任务，或者手工的启动一个ETL 任务， 现在定时框架这么多（比如java 的quartz）, 这个功能算是最基本的.

执行报告则是报告每个ETL 任务的执行次数，执行时间，执行者，执行结果，输入的参数，输出的参数，成功装载多少条，更新多少条，失败多少条，删除多少条等等.

Data Lineage 则是搞清楚整个数据的流向问题，某个转换数据源有哪些字段，输出到哪里，这可以帮助ETL 开发人员更好的理清ETL 开发的思路。

 

OWB 的控制台

在OWB 里面，你执行一个ETL 任务之后，你可以查看执行的结果，上图中的Row Activity 显示了数据的插入数，更新数，删除数，如果执行失败了，当然也会在下面面板的Job Summary 里面显示.

 

OWB 的data lineage 图形可视化做的非常标准，里面可以非常清楚的看到对象与对象之间的关系，你想要查看任何一个转换使用了那些数据源字段和目标字段都一清二楚.

 

ODI 的控制台

ODI 在安装包内也提供了一个WEB 的控制台，在oracledimn 文件夹下，你可以把它放到任何一个java web 容器里埋你比如tomcat 的webapp 目录下就算部署好了, 它需要能够连接你的ODI 使用的repository 的数据库. 比如它默认环境的demo repository (hsqldb) , 然后进入http://localhost:8080/oradimn , 选择repository 就是OracleDI local repository , 默认的用户名密码就是ODI 的demo repository 的admin 用户名和密码( SUPERVISOR和SUNOPSIS ) , 如果你使用的是自己建立的repository , 修改它WEB-INF 目录下的snps_login_work.xml 文件就可以了.

登录进去之后的界面差不多就上图， 左边导航栏包含了所有ODI 的元数据管理项了， 其中 执行菜单 下面的计划程序里面就可以安排定时执行的任务， 而 元数据菜单 下的 数据沿袭 就是展现ETL 任务的数据流的 (Data Lineage 翻译成沿袭还真是不太习惯)

 

Informatica Matadata Manager

informatica 的元数据控制台是matadata manager , 默认是不包含在powercenter 里面的，跟它的Data Analysis 是属于额外组件，需要另外的license .

如果谁有informatica data analysis/matadata manager 的license 请给我一份(8.1.1 sp4 的)

 

 

 

 

 

 

 

informatica 的控制台可以说是做的最好的一个，其中它的metadata report 就有不少，从各个角度报告元数据的使用情况，而且查询条件非常细.

它的data lineage 也是非常的强，一个字段，一个字段都计算的非常的细， 最后一个图还显示了它的workflow 的其中一个transformation 的数据流动（浅黄颜色表示），字段，输入，输出，表达式.

 

 

最后

ETL 的元数据管理已经越来越重要，而且不少公司都已经认识到了不光要做好ETL工具的使用，而且管理元数据也同样重要. 商业的ETL 工具在这方面已经做得非常好了， 上面介绍的3种算是非常优秀的， 相比之下，开源的ETL 工具在这方面就要差很多. 不过成本也是另外一个非常重要的考虑方面，就看各自的需求了.

除了使用成熟的ETL工具之外， 不少公司对于ETL 也有自己的一套架构，比如使用ETL 编程框架 或是手工代码+sql 类型， 不太清楚这方面如果公司需要管理元数据会有怎么样的一个思路，感觉上如果使用ETL 编程框架或者手工代码+sql 的方式，元数据产生的过程不容易收集，如果只是调度还比较简单， 但是执行报告 尤其是 data lineage 怎么做就……  ,  如果哪位有这方面经验不妨交流交流.

数据质量已经成为企业数据信息最重要的一部分, 而ETL工具提供的数据质量控制工具一直都是比较ETL工具强弱的重要部分.

数据质量的控制一般分为两个阶段，Data Profile 和Data Quality , Data Profile 简单来说就是计算出数据的各种统计值，比如唯一值，频率等等， 然后我们通过Profile 出来的结果判断出那些是有质量问题的数据，然后选择相应的解决方式. Data Quality 指的就是在我们知道了Data Profile 的结果之后，通过ETL过程清理掉那些有质量问题的数据.

Oracle 提供的两款ETL 工具OWB 和 ODI 都已经提供了数据质量控制的组件 ， 现在来看一下这两个工具的功能如何.

分析的都是oracle 自带的示例方案, sh schema 里面的

OWB 的Data Profile 组件:

OWB 的界面布局和操作更加紧凑一些，喜欢所有东西放一起.

OWB 的Aggregation面板， 里面显示的是每一列的总体信息，最大值，最小值，唯一值个数， 其中上面的Six-sigma 是OWB 特有的, 数值为1-6 ，越高表示质量越好. 上面全部是7 ， 表示完美，因为这个示例数据本身已经过滤过了的.

 

 

OWB 默认的配置选项是探测数据类型的，上图还显示了每一列符合它所属的数据类型的百分比.

 

 

OWB 最重要的一个界面.Domain 面板，Domain 在OWB 里面表示每一列的唯一值，上图就是显示了sales 表的PROD_ID 的Domain 统计情况， 注意左边设置里面的 "Domain Value Compliance Min Rows Percentage" 选的是1 ,  所以右边的PROD_ID 的唯一值的频率如果小于1% 就认为是有问题的数据（红色表示），

 

 

ODI 的Data Profile 组件：

ODI 的操作界面和操作方式简单而高效， 基本所有的方式都是点击下转. 而且界面响应速度要比OWB 快很多，整体的运行速度也比OWB 快， OWB 使用java swing 做的界面虽然摆脱了swing 做出的默认比较丑的样子，但是界面响应速度上总是感觉有延时. ODI 则响应速度快多了.

默认ODI 安装的时候不让选择安装语言，它会根据你的操作系统的语言安装相应语言， 而且进入ODI 后也不能设置语言环境，OWB 这点则好多了, 可以选择自己想要显示的语言.

 

想要进行Profile 项目首先创建Entity (实体), 里面分析的仍然是sh 的sales 表. 上面ODI 显示了， 总行数，最长行，最短行等.

 

在左边项目里点击Entity 的属性，列出当前实体的所有属性总的统计信息. 之后在你想要看的某一列上右键点击，就可以选择下转到对应列的详细信息了.

 

上图中就列出了sh 的sales 表Amount Sold 列的信息, 其中列出唯一值，最小值，最大值，空值等.

 

你可以在列的唯一值上右键单击， 选择下转到详细的唯一值数据

 

在选择的唯一值数据中，可以继续下转，细化到真正数据库中满足这些唯一值的数据

 

ODI 的一大特色就是可以分析两表之间连接列的属性, 上图就显示了sales 表的PROD_ID 列和Products 表的PROD_ID 列的连接属性， 左侧匹配行，右侧匹配行，内部连接行等等. 你还可以点击连接属性选择生成维恩图. (维恩图大概从离开初中之后就没听过了，这是第一次听到，看来我初中的东西还没忘光，嘿嘿！！！）

 

 

ODI Quality Project 设定:

OWB 和ODI 都可以即时从Profile 结果中创建Quality Project ,  上图中看到的就是ODI 设置Quality Project  里面转换的属性，比如对Products 表不容许空值超过多少个，不容许某些特定列的特定值出现等.

 

你也可以设置更细致的ODI 属性， 默认的ODI 是不进行数据类型检查的，OWB 会默认检查. 你也可以修改这些设定.

 

OWB 和 ODI 的性能

从操作上来说，OWB 的操作更为麻烦一些，任何东西都要先部署到Control Center , 然后还要编译， 然后才能运行使用。 ODI 的操作相对简单， 它不在一个界面显示过多的东西，任何信息都是一步一步点击， 界面的响应速度也很快.

从OWB 和ODI 运行Profile 的速度来看，ODI 也具有明显优势， 上面同样分析的是sh 的sales 表，OWB 即使已经编译部署到了Control Center , 对于92万条记录的数据量还是使用了180多秒， 而ODI 即使包括操作时间在内也只花了不到15秒时间，整个运行的速度还是差距很大.

 

OWB 和 ODI 在Data Quality Project 上比较

OWB 在数据清理上基本按照如下流程：

从Profile –> Data Rules –> Corrections –> Mappings –> Load Cleanse 一条直线，其中Profile 之后可以选择自动让OWB 生成Data Rules, 它的Corrections 和 Mappings 也基本上都是向导操作，比较简单。而ODI 则没有类似功能，你要自己创建Quality Project, 然后手工创建ETL操作清理操作，不过好在速度很快，使用起来也很简单.

另外一个ODI 特有的功能则是ODI 有Time Series Data Quality Project, 它可以针对随着时间变化而持续跟踪数据质量的变化，这个功能主要针对对时间要求比较严的数据质量项目，可以提供更准确的随着时间变化数据质量是变好还是变坏的报告.

 

参考资料：

http://www.rittmanmead.com/2009/02/11/comparing-odi-and-owbs-data-profiling-and-quality-options/

建议大家看看rittmanmead 的这篇owb 与odi 的文章. 毕竟人家是owb 方面的专家.

 

http://www.gemini5201314.net/?p=17

我翻译OWB 教程的其中一篇，介绍如何进行data profile 的.

 

http://www.oracle.com/technology/obe/11gr1_owb/index.htm

otn 上介绍owb 使用的文章.

在infobright 看到一个关于ETL工具选择的一个投票,  结果如下

• Kettle 75 votes

  18.99%

• PHP 64 votes

  16.2%

• Perl 48 votes

  12.15%

• Other Language 38 votes

  9.62%

• Python 35 votes

  8.86%

• Ruby 27 votes

  6.84%

• C/C++/C# 24 votes

  6.08%

• Talend 24 votes

  6.08%

• Other Commercial ETL Tool 18 votes

  4.56%

• Other Open Source ETL Tool 13 votes

  3.29%

• Informatica 13 votes

  3.29%

• DataStage 9 votes

  2.28%

• Ab Initio 7 votes

  1.77%

Total Votes: 395

 

同时我们也看一下gartner 在2008年12月发布的Data Integration 工具的一个调查报告.

 

总结上面两份资料, 可以看出Informatica 和IBM 的DataStage 仍然都是ETL界的老大, 而且很难有撼动的趋势,尤其是电信,金融,银行,基本都是选用这种工具, 而其他的商业ETL提供商都是各有长短, 尤其是有自己商业产品线的公司如Microsoft ,Oracle ,SAS, SAP 都是能够很好的与自己的产品线补充,

而在开源ETL产品中也不缺乏好的产品支持, 最有名的两个就是Kettle 和Talend 了,基本上都是各有千秋, 而且最总要的两点就是:社区和商业支持, 看看其他成功的开源产品如mysql ,linux , 都是在这两点上做的非常成功,才能成为开源界的典范, 其他也有一些不错的开源ETL 产品虽然在社区和商业支持上没有前两个有名,比如xaware, 在infoq 上也有几次他的报道.

 

国内也有一个开源的ETL工具dengues (http://code.google.com/p/dengues/) ,由国人开发, 基本上还在起步阶段, 可以作为开源ETL爱好者的一个学习项目.

 

参考资料

http://mediaproducts.gartner.com/reprints/sas/vol5/article4/article4.html

 

一些可以用于生产环境的ETL工具:

http://www.ketl.org

http://www.cloveretl.org/

http://www.xaware.org/

http://www.apatar.org/

http://www.enhydra.org/

我之前的一篇文章写到Kettle 的一些性能测试的说明,

http://www.gemini5201314.net/?p=129

主要比较了kettle 和talend 在读和写方面的性能测试, 由于有代码可以自己运行, 每个人都可以试一下结果,

一个ETL转换肯定会有写的步骤,所以主要说明一下同时有读和写的情况

文件大小为2.4GB , Kettle 单步骤运行的时候, 读大概:12 – 18M/s , 写大概14M/s , 平均0.7GB/min.

 

 

2步骤运行的时候, 读还是12-18 M/s , 写大概18M/s ,平均1.0GB/min , 写是在两个文件上, 但是还是由一个物理磁盘来写的.

运行这个测试的机器是普通的笔记本电脑,估计硬盘的缓存大小为16M/s , 由于写的时候是Linux 系统(kubuntu 8.10), 所以写的速度最大可以略微超过一点.

 

我在自己的电脑上也测试过这个转换任务, 读大概是18-20M/s , 写只有接近600M/min , 之所以速度慢的原因是我的物理磁盘比较慢, 磁盘是Maxtor 的, 缓存大小只有8M/s , 所以一分钟的最大速度只可能有8*60M/min = 480M/min , 所以我的写速度大概是600M /min 不到是正常的.

由于这个任务的CPU 利用率也比较高,都会达到100% , 所以更强的CPU 也可以提高速度.

关于查看硬盘的信息由多种方式,我是在windows 下, 所以可以使用windows 优化大师的硬件查看功能,如下图: 缓存只有8M/s , 所以速度不是很快, 如果有更快的物理磁盘,这个转换任务会更快.

Linux 下可以使用iostat 命令,比如原测试环境就是使用iostat –k 5 来测试的,硬盘缓存大小为16M/s , 其他的Linux 命令比如hdparm 也可以帮你了解你的硬盘(hdpart –T /dev/sda ) , 或者使用一些专用的IO 测试工具,比如oracle 的orion 之类的.

另外注意这个转换有一些参数需要注意一下, 文件读写尽量使用CSV 文件或者Fixed Text 文件, 这是使用NIO 优化过了的, 注意NIO Buffer 的大小,里面设置的是50M , 文件大的时候就设置大一点, lazy convertion 开启了, 文件格式记得要fast data dump (不开启的话,比较耗CPU,根据你需要决定),

 

另外不要误以为左边Slave Server 有几个server 就认为这个转换是在cluster 下执行的, 左边的slave server 跟这个完全没关系, 上面CSV 步骤上写的 "x2" 是指这个转换步骤的copy number ,你在这个转换步骤上右键点击选"change number of copys to start" 里面填上你需要的copy number ,  集群的配置是根据你的slaver 数量来决定的,你是不能手工输入多少slaver 来执行,而且他是在步骤的右上角写"Cx2" ,而不是左上角的"x2" .   当然, 虽然这个转换本身是单机的,但是即使换在cluster 环境下执行,也是同时可以既有集群数,也有copy number 的.

 

这个转换是以文件为主, 如果你在大批量的导入文件到数据库的时候,还是建议使用数据库本身的特有的Bulk Loader , Kettle 也有不少的插件可以支持特定数据库的Bulk Loader 操作, 比如目前已经有Oracle Bulker Loader, Greenplum Bulker ,Postgresql Bulk Loader ,LucideDB Bulk Loader . 相当于在数据库的客户端(Kettle 运行转换的服务器端)执行, 本身要指定你的数据库安装的目录文件位置. 集群的话当然也要保证在每一个Kettle 运行的服务器端都安装好了数据库的客户端软件,不然转换肯定会出错.

Greenplum 和 Aster Data 都已经宣称自己成功将MapReduce 集成进自己的数据库产品中, 从而都宣称自己的产品比其他竞争对手快至少10倍以上, 并且都雄心勃勃的要进入PB 级数据仓库行列,而作为商业智能另外一个重要领域的ETL工具又有多少手段能够并行执行它们的ETL任务呢?

 

1. 第一种方式当然是MapReduce , 通过建立一个MapReduce 的集群, 然后再通过手工编程的方式用JDBC或ODBC 进行数据库操作, 至少听起来可行,不过不知道有没有人真正试过.
2. 使用某种编程ETL API, 开源的ETL编程型ETL API Clover ETL 宣称自己是具有并行执行能力的,通过多线程模型和管道并行(pipeline-parallelism) 是可以很容易伸缩的. 而在另一个文章中读到Pervasive Software 也提供一个商业编程ETL API 可以很容易并行执行ETL任务, 它的性能上的突出特性包括延迟加载(late-binding) 动态决定并行执行度,既根据你服务器启动时收集到的CPU 数量和内存数量来动态决定开多少线程,并行执行策略等等, 这个公司同时也有自己的ETL GUI设计工具和数据清理工具Data profile (本来打算下来看看的,结果注册的时候即使假装自己是美国的都违反什么出口条约不让下,所以就算了). 它目前还在公测阶段,可以免费获得. 正式版之后还是收费的,不过他宣称如果没有30倍速度提升,可以退款.
3. 数据库的编程语言. 储存过程作为数据库内置的最强大编程语言, 当然也少不了并行执行这个杀手锏, 有些工具同时也被声称为ELT 工具, Extract/Load/Transform , 主要就是通过先批量装载数据然后利用数据库的储存过程和数据库本身的并行执行功能进行转换,其中最有名的大概就是oracle 的两款ELT 工具, oracle warehouse builder 和oracle data integrator. 通过利用oracle 强大的并行处理能力和pl/sql 的并行处理能力解决大数据量的转换工作, 通过parallel query , parallel dml/ddl ,parallel data loading和 pl/sql 中的pipelined table 函数, oracle 对付大量数据同样也是可以伸缩的.

           如果你在linux shell下同一时间执行多个任务可以通过

           nohup sqlplus system/manager exec xxx &

           windows 的话大概就是 start 命令了.

           虽然这不能算是真的数据库级别并行执行,不过也算一个小tip 了.

4. ETL 工具本身的支持. ETL可视化工具中也不缺乏对并行执行的支持,像是业界大老informatica 和 datastage 都早已经是可以支持cluster 运行了, 而开源产品中Kettle 则是早已支持cluster 运行,其余ETL产品则不太清楚.

 

 

 

1. http://www.dbms2.com/2008/08/26/three-approaches-to-parallelizing-data-transformation/

   数据转换与并行处理

2. http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Table_Functions_Cursor_Expressions/start.htm

   oracle 官方的pipelined pl/sql函数文档

3. http://www.akadia.com/services/ora_parallel_processing.html

   oracle parallel processing

4. http://blog.chinaunix.net/u1/57759/showart_458451.html

   oracle Pipelined表函数

随着越来越多的企业开始重视数据仓库的建设,而在非数据仓库方面,SOA的流行越发对数据集成有更多的依赖,所以不少的ETL工具提供商都开始称自己的产品有"数据集成解决方案". 传统的ETL大概有以下几种实现方式:

1. 图形化设计器生成XML设计文件,然后有个engine 执行
2. 图形化设计器生成某种编程语言代码,然后执行代码
3. 使用某种ETL 引擎框架, 通过编程来执行ETL任务
4. 手工写代码执行

 

每一种方式实际上都不能完全解决各种复杂的企业数据集成的需求,图形设计器设计出来虽然方便,维护轻松,但是不够灵活,对于传参数,动态判断条件,跟企业已有系统集成较为困难,而使用编程或ETL执行引擎虽然能解决前面的缺点,但是太过复杂,维护成本高,需要比较多的技巧.

Kettle 作为第一种ETL工具, 虽然在功能和易用性上做的不错,但是如果你要从一个已有的程序里面传参数,或者根据一些动态条件来修改设计文件,甚至完全不依赖设计器用编程的方式实现ETL任务基本上都是不可能的.

新出现的kettle jdbc driver 则一部分解决了上述问题, 通过像sql 一样的语法从一个kettle 转换里面取出结果,然后在程序里面使用. 它可以取出任意步骤的结果.

 

它里面提供的演示是基于pentaho 的,如果不是很懂pentaho 的话建议用文本编辑器打开看一下就好了,而且都是报表的演示,可能不一定都看得懂. 如果想用的话还需要研究一下.

 

这个项目在google 上, 同时有国内的一位朋友qinhui99 参与, 有兴趣可以去看一下他的个人主页:

http://qinhui99.itpub.net/

 

 

参考资料

http://code.google.com/p/jdbckettle/

jdbc kettle 的主页

我个人重来都是没有碰到过这个错误的,不过最近有个朋友从itput 看到我写的关于kettle 的文章,问我这个问题, 后来找到几种可能性, 主要可能是发生在Windows 2000 上, 我用的是xp  ,还没碰到过, windows 2000 的command line 最大可以有2047 个字符,xp是8091 , kettle 3.0 之后加载的东西太多了,所以有些jar 加不进去,解决办法如下:

1. 把kettle 尽量放在顶层目录比如 E:/pdi 下,减少字符长度

2. 把libext/JDBC 目录下的不用的数据库驱动删掉,比如你只用mysql 和oracle 就只保留mysql-connect-xxx.jar 和ojdbc12.jar 其他的都删掉,

3. 在xp 系统下把cmd.exe 文件拷贝到2000 下, 这个字符串长度限制是写死的,xp 的长度限制为8091.

 

另外需要注意的是至少需要jdk 1.5 以上才能运行, 1.4 是不行的, 我用的是jdk 1.6 的, 点那个kettle.exe 文件它还报错说要jdk 1.5 , 不用exe 文件启动,直接点spoon.bat 启动就是好的.

 

 

参考资料

http://tech.it168.com/d/2008-03-21/200803211924754.shtml

开源ETL工具kettle系列之增量更新设计技巧

 

http://tech.it168.com/n/2008-03-21/200803211716994.shtml

开源ETL工具kettle系列之建立缓慢增长维

 

http://tech.it168.com/d/2008-03-18/200803171550713.shtml

开源ETL工具kettle系列之动态转换

 

http://wiki.pentaho.com/display/EAI/Windows+2000

pentaho wiki 上说的解决方法

 

http://support.microsoft.com/kb/830473

windows 长度限制