关于ETL的经验总结[经典]
ETL的考虑
做数据仓库系统，ETL是关键的⼀环。说⼤了，ETL是数据整合解决⽅案，说⼩了，就是倒数据的⼯具。回忆⼀下⼯作这么些年来，处理数据迁移、转换的⼯作倒还真的不少。但是那些⼯作基本上是⼀次性⼯作或者很⼩数据量，使⽤access、DTS或是⾃⼰编个⼩程序搞定。可是在数据仓库系统中，ETL上升到了⼀定的理论⾼度，和原来⼩打⼩闹的⼯具使⽤不同了。究竟什么不同，从名字上就可以看到，⼈家已经将倒数据的过程分成3个步骤，E、T、L分别代表抽取、转换和装载。
其实ETL过程就是数据流动的过程，从不同的数据源流向不同的⽬标数据。但在数据仓库中，ETL有⼏个特点，⼀是数据同步，它不是⼀次性倒完数据就拉到，它是经常性的活动，按照固定周期运⾏的，甚⾄现在还有⼈提出了实时ETL的概念。⼆是数据量，⼀般都是巨⼤的，值得你将数据流动的过程拆分成E、T和L。
现在有很多成熟的⼯具提供ETL功能，例如datastage、powermart等，且不说他们的好坏。从应⽤⾓度来说，ETL的过程其实不是⾮常复杂，这些⼯具给数据仓库⼯程带来和很⼤的便利性，特别是开发的便利和维护的便利。但另⼀⽅⾯，开发⼈员容易迷失在这些⼯具中。举个例⼦，VB是⼀种⾮常简单的语⾔并且也是⾮常易⽤的编程⼯具，上⼿特别快，但是真正VB的⾼⼿有多少？微软设计的产品通常有个原则
是"将使⽤者当作傻⽠"，在这个原则下，微软的东西确实⾮常好⽤，但是对于开发者，如果你⾃⼰也将⾃⼰当作傻⽠，那就真的傻了。ETL ⼯具也是⼀样，这些⼯具为我们提供图形化界⾯，让我们将主要的精⼒放在规则上，以期提⾼开发效率。从使⽤效果来说，确实使⽤这些⼯具能够⾮常快速地构建⼀个job来处理某个数据，不过从整体来看，并不见得他的整体效率会⾼多少。问题主要不是出在⼯具上，⽽是在设计、开发⼈员上。他们迷失在⼯具中，没有去探求ETL的本质。
可以说这些⼯具应⽤了这么长时间，在这么多项⽬、环境中应⽤，它必然有它成功之处，它必定体现了ETL的本质。如果我们不透过表⾯这些⼯具的简单使⽤去看它背后蕴涵的思想，最终我们作出来的东西也就是⼀个个独⽴的job，将他们整合起来仍然有巨⼤的⼯作量。⼤家都知道“理论与实践相结合”，如果在⼀个领域有所超越，必须要在理论⽔平上达到⼀定的⾼度
探求ETL本质之⼀
ETL的过程就是数据流动的过程，从不同异构数据源流向统⼀的⽬标数据。其间，数据的抽取、清洗、转换和装载形成串⾏或并⾏的过程。ETL的核⼼还是在于T这个过程，也就是转换，⽽抽取和装载⼀般可以作为转换的输⼊和输出，或者，它们作为⼀个单独的部件，其复杂度没有转换部件⾼。和OLTP系统中不同，那⾥充满这单条记录的insert、update和select等操作，ETL过程⼀般都是批量操作，例如它的装载多采⽤批量装载⼯具，⼀般都是DBMS系统⾃⾝附带的⼯具，例如Oracle SQLLoader和DB2的autoloader等。
ETL本⾝有⼀些特点，在⼀些⼯具中都有体现，下⾯以datastage和powermart举例来说。
1、静态的ETL单元和动态的ETL单元实例。⼀次转换指明了某种格式的数据如何格式化成另⼀种格式的数据，对于数据源的物理形式在设计时可以不⽤指定，它可以在运⾏时，当这个ETL单元创建⼀个实例时才指定。对于静态和动态的ETL单元，Datastage没有严格区分，它的⼀个Job就是实现这个功能，在早期版本，⼀个Job同时不能运⾏两次，所以⼀个Job相当于⼀个实例，在后期版本，它⽀持multiple instances，⽽且还不是默认选项。Powermart中将这两个概念加以区分，静态的叫做Mapping，动态运⾏时叫做Session。
2、ETL元数据。元数据是描述数据的数据，他的含义⾮常⼴泛，这⾥仅指ETL的元数据。主要包括每次转换前后的数据结构和转换的规
3、数据流程的控制。要有可视化的流程编辑⼯具，提供流程定义和流程监控功能。流程调度的最⼩单位是ETL单元实例，ETL单元是不能在细分的ETL过程，当然这由开发者来控制，例如可以将抽取、转换放在⼀个ETL单元中，那样这个抽取和转换只能同时运⾏，⽽如果将他们分作两个单元，可以分别运⾏，这有利于错误恢复操作。当然，ETL单元究竟应该细分到什么程度应该依据具体应⽤来看，⽬前还没有到很好的细分策略。⽐如，我们可以规定将装载⼀个表的功能作为⼀个ETL单元，但是不可否认，这样的ETL单元之间会有很多共同的操作，例如两个单元共⽤⼀个Hash表，要将这个Hash表装⼊内存两次。
4、转换规则的定义⽅法。提供函数集提供常⽤规则⽅法，提供规则定义语⾔描述规则。
5、对数据的快速索引。⼀般都是利⽤Hash技术，将参照关系表提前装⼊内存，在转换时查这个hash表。Datastage中有Hash⽂件技术，Powermart也有类似的Lookup功能。
探求ETL本质之⼆(分类)
昨在IT-Director上阅读⼀篇报告，关于ETL产品分类的。⼀般来说，我们眼中的ETL⼯具都是价格昂贵，能够处理海量数据的家伙，但是这是其中的⼀种。它可以分成4种，针对不同的需求，主要是从转换规则的复杂度和数据量⼤⼩来看。它们包括:
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时，中间两种的选择就开始了，在选择过程中，必须要考虑到开发效率、维护⽅⾯、性能、学习曲线、⼈员技能等各⽅⾯因素，当然还有最重要也是最现实的因素就是客户的意象。
探求ETL本质之三(转换)
ETL探求之⼀中提到，ETL过程最复杂的部分就是T，这个转换过程，T过程究竟有哪些类型呢？
⼀、宏观输⼊输出
从对数据源的整个宏观处理分，看看⼀个ETL过程的输⼊输出，可以分成下⾯⼏类：
1、⼤⼩交。这种处理在数据清洗过程是常见了，例如从数据源到ODS阶段，如果数据仓库采⽤维度建模，⽽且维度基本采⽤代理键的话，必然存在代码到此键值的转换。如果⽤SQL实现，必然需要将⼀个⼤表和⼀堆⼩表都Join起来，当然如果使⽤ETL⼯具的话，⼀般都是先将⼩表读⼊内存中再处理。这种情况，输出数据的粒度和⼤表⼀样。
2、⼤⼤交。⼤表和⼤表之间关联也是⼀个重要的课题，当然其中要有⼀个主表，在逻辑上，应当是主表Left Join辅表。⼤表之间的关联存在最⼤的问题就是性能和稳定性，对于海量数据来说，必须有优化的⽅法来处理他们的关联，另外，对于⼤数据的处理⽆疑会占⽤太多的系统资源，出错的⼏率⾮常⼤，如何做到有效错误恢复也是个问题。对于这种情况，我们建议还是尽量将⼤表拆分成适度的稍⼩⼀点的表，形成⼤⼩交的类型。这类情况的输出数据粒度和主表⼀样。
3、站着进来，躺着出去。事务系统中为了提⾼系统灵活性和扩展性，很多信息放在代码表中维护，所以它的"事实表"就是⼀种窄表，⽽在数据仓库中，通常要进⾏宽化，从⾏变成列，所以称这种处理情况叫做"站着进来，躺着出去"。⼤家对Decode肯定不陌⽣，这是进⾏宽表化常见的⼿段之⼀。窄表变宽表的过程主要体现在对窄表中那个代码字段的操作。这种情况，窄表是输⼊，宽表是输出，宽表的粒度必定要⽐窄表粗⼀些，就粗在那个代码字段上。
4、聚集。数据仓库中重要的任务就是沉淀数据，聚集是必不可少的操作，它是粗化数据粒度的过程。聚集本⾝其实很简单，就是类似SQL中Group by的操作，选取特定字段（维度），对度量字段再使⽤某种聚集函数。但是对于⼤数据量情况下，聚集算法的优化仍是探究的⼀个课题。例如是直接使⽤SQL的Group by，还是先排序，在处理。大数据etl工具有哪些
⼆、微观规则
从数据的转换的微观细节分，可以分成下⾯的⼏个基本类型，当然还有⼀些复杂的组合情况，例如先运算，在参照转换的规则，这种基于基本类型组合的情况就不在此列了。ETL的规则是依赖⽬标数据的，⽬标数据有多少字段，就有多少条规则。
1、直接映射。原来是什么就是什么，原封不动照搬过来，对这样的规则，如果数据源字段和⽬标字段长度或精度不符，需要特别注意看是否真的可以直接映射还是需要做⼀些简单运算。
2、字段运算。数据源的⼀个或多个字段进⾏数学运算得到的⽬标字段，这种规则⼀般对数值型字段⽽⾔。
3、参照转换。在转换中通常要⽤数据源的⼀个或多个字段作为Key，去⼀个关联数组中去搜索特定值，⽽且应该只能得到唯⼀值。这个关联数组使⽤Hash算法实现是⽐较合适也是最常见的，在整个ETL开始之前，它就装⼊内存，对性能提⾼的帮助⾮常⼤。
4、字符串处理。从数据源某个字符串字段中经常可以获取特定信息，例如⾝份证号。⽽且，经常会有数值型值以字符串形式体现。对字符串的操作通常有类型转换、字符串截取等。但是由于字符类型字段的随意性也造成了脏数据的隐患，所以在处理这种规则的时候，⼀定要加上异常处理。
5、空值判断。对于空值的处理是数据仓库中⼀个常见问题，是将它作为脏数据还是作为特定⼀种维成
员？这恐怕还要看应⽤的情况，也是需要进⼀步探求的。但是⽆论怎样，对于可能有NULL值的字段，不要采⽤“直接映射”的规则类型，必须对空值进⾏判断，⽬前我们的建议是将它转换成特定的值。
6、⽇期转换。在数据仓库中⽇期值⼀般都会有特定的，不同于⽇期类型值的表⽰⽅法，例如使⽤8位整型20040801表⽰⽇期。⽽在数据源中，这种字段基本都是⽇期类型的，所以对于这样的规则，需要⼀些共通函数来处理将⽇期转换为8位⽇期值、6位⽉份值等。
7、⽇期运算。基于⽇期，我们通常会计算⽇差、⽉差、时长等。⼀般数据库提供的⽇期运算函数都是基于⽇期型的，⽽在数据仓库中采⽤特定类型来表⽰⽇期的话，必须有⼀套⾃⼰的⽇期运算函数集。
8、聚集运算。对于事实表中的度量字段，他们通常是通过数据源⼀个或多个字段运⽤聚集函数得来的，这些聚集函数为SQL标准中，包括sum,count,avg,min,max。
9、既定取值。这种规则和以上各种类型规则的差别就在于它不依赖于数据源字段，对⽬标字段取⼀个固定的或是依赖系统的值。
探求ETL本质之四(数据质量)
“不要绝对的数据准确，但要知道为什么不准确。”
这是我们在构建BI系统是对数据准确性的要求。确实，对绝对的数据准确谁也没有把握，不仅是系统集成商，包括客户也是⽆法确定。准确的东西需要⼀个标准，但⾸先要保证这个标准是准确的，⾄少现在还没有这样⼀个标准。客户会提出⼀个相对标准，例如将你的OLAP数据结果和报表结果对⽐。虽然这是⼀种不太公平的⽐较，你也只好认了吧。
⾸先在数据源那⾥，已经很难保证数据质量了，这⼀点也是事实。在这⼀层有哪些可能原因导致数据质量问题？可以分为下⾯⼏类：
1、数据格式错误。例如缺失数据、数据值超出范围或是数据格式⾮法等。要知道对于同样处理⼤数据量的数据源系统，他们通常会舍弃⼀些数据库⾃⾝的检查机制，例如字段约束等。他们尽可能将数据检查在⼊库前保证，但是这⼀点是很难确保的。这类情况诸如⾝份证号码、⼿机号、⾮⽇期类型的⽇期字段等。
2、数据⼀致性。同样，数据源系统为了性能的考虑，会在⼀定程度上舍弃外键约束，这通常会导致数据不⼀致。例如在帐务表中会出现⼀个⽤户表中没有的⽤户ID，在例如有些代码在代码表中不到等。
3、业务逻辑的合理性。这⼀点很难说对与错。通常，数据源系统的设计并不是⾮常严谨，例如让⽤户开户⽇期晚于⽤户销户⽇期都是有
构建⼀个BI系统，要做到完全理解数据源系统根本就是不可能的。特别是数据源系统在交付后，有更多维护⼈员的即兴发挥，那更是要花⼤量的时间去寻原因。以前曾经争辩过设计⼈员对规则描述的问题，有⼈提出要在ETL开始之前务必将所有的规则弄得⼀清⼆楚。我并不同意这样的意见，倒是认为在ETL过程要有处理这些质量有问题数据的保证。⼀定要正⾯这些脏数据，是丢弃还是处理，⽆法逃避。如果没有质量保证，那么在这个过程中，错误会逐渐放⼤，抛开数据源质量问题，我们再来看看ETL过程中哪些因素对数据准确性产⽣重⼤影响。
1、规则描述错误。上⾯提到对设计⼈员对数据源系统理解的不充分，导致规则理解错误，这是⼀⽅⾯。另⼀⽅⾯，是规则的描述，如果⽆⼆义性地描述规则也是要探求的⼀个课题。规则是依附于⽬标字段的，在探求之三中，提到规则的分类。但是规则总不能总是⽤⽂字描述，必须有严格的数学表达⽅式。我甚⾄想过，如果设计⼈员能够使⽤某种规则语⾔来描述，那么我们的ETL单元就可以⾃动⽣成、同步，省去很多⼿⼯操作了。
2、ETL开发错误。即时规则很明确，ETL开发的过程中也会发⽣⼀些错误，例如逻辑错误、书写错误等。例如对于⼀个分段值，开区间闭区间是需要指定的，但是常常开发⼈员没注意，⼀个⼤于等于号写成⼤于号就导致数据错误。
3、⼈为处理错误。在整体ETL流程没有完成之前，为了图省事，通常会⼿⼯运⾏ETL过程，这其中⼀
个重⼤的问题就是你不会按照正常流程去运⾏了，⽽是按照⾃⼰的理解去运⾏，发⽣的错误可能是误删了数据、重复装载数据等。
探求ETL本质之五(质量保证)
上回提到ETL数据质量问题，这是⽆法根治的，只能采取特定的⼿段去尽量避免，⽽且必须要定义出度量⽅法来衡量数据的质量是好还是坏。对于数据源的质量，客户对此应该更加关⼼，如果在这个源头不能保证⽐较⼲净的数据，那么后⾯的分析功能的可信度也都成问题。数据源系统也在不断进化过程中，客户的操作也在逐渐规范中，BI系统也同样如此。本⽂探讨⼀下对数据源质量和ETL处理质量的应对⽅法。
如何应对数据源的质量问题？记得在onteldatastage列表中也讨论过⼀个话题——“-1的处理”，在数据仓库模型维表中，通常有⼀条-1记录，表⽰“未知”，这个未知含义可⼴了，任何可能出错的数据，NULL数据甚⾄是规则没有涵盖到的数据，都转成-1。这是⼀种处理脏数据的⽅法，但这也是⼀种掩盖事实的⽅法。就好像写⼀个函数FileOpen(filename)，返回⼀个错误码，当然，你可以只返回⼀种错误码，如-1，但这是⼀种不好的设计，对于调⽤者来说，他需要依据这个错误码进⾏某些判断，例如是⽂件不存在，还是读取权限不够，都有相应的处理逻辑。数据仓库中也是⼀样，所以，建议将不同的数据质量类型处理结果分别转换成不同的值，譬如，在转换后，-1表⽰参照不上，-2表⽰NULL数据等。
不过这仅仅对付了上回提到的第⼀类错误，数据格式错误。对于数据⼀致性和业务逻辑合理性问题，这仍有待探求。但这⾥有⼀个原则就是“必须在数据仓库中反应数据源的质量”。
对于ETL过程中产⽣的质量问题，必须有保障⼿段。从以往的经验看，没有保障⼿段给实施⼈员带来⿇烦重重。实施⼈员对于反复装载数据⼀定不会陌⽣，甚⾄是最后数据留到最后的Cube，才发现了第⼀步ETL其实已经错了。这个保障⼿段就是数据验证机制，当然，它的⽬的是能够在ETL过程中监控数据质量，产⽣报警。这个模块要将实施⼈员当作是最终⽤户，可以说他们是数据验证机制的直接收益者。
⾸先，必须有⼀个对质量的度量⽅法，什么是⾼质什么是低质，不能靠感官感觉，但这却是在没有度量⽅法条件下通常的做法。那经营分析系统来说，联通总部曾提出测试规范，这其实就是⼀种度量⽅法，例如指标的误差范围不能⾼于5%等，对系统本⾝来说其实必须要有这样的度量⽅法，先不要说这个度量⽅法是否科学。对于ETL数据处理质量，他的度量⽅法应该⽐联通总部测试规范定义的⽅法更要严格，因为他更多将BI系统看作⼀个⿊盒⼦，从数据源到展现的数据误差允许⼀定的误差。⽽ETL数据处理质量度量是⼀种⽩盒的度量，要注重每⼀步过程。因此理论上，要求输⼊输出的指标应该完全⼀致。但是我们必须正⾯完全⼀致只是理想，对于有误差的数据，必须到原因。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。