《ETL应用浅析》doc版.doc

蚀肇肇薀薆蚄腿莃蒂蚃芁薈螁螂羁莁蚇螁肃薇薃螀芅莀蕿蝿莈节袇蝿肇蒈螃螈膀芁虿螇节蒆薅螆羂艿蒁袅肄蒄螀袄膆芇蚆袃荿蒃蚂袃肈莆薈袂膁薁蒄袁芃莄螂袀羃蕿蚈罿肅莂薄羈膇薈蒀羇艿莀蝿羇聿膃螅羆膁葿蚁羅芄芁薇羄羃蒇蒃羃肆芀螂肂膈蒅蚈肁芀芈薄肁羀蒄蒀肀膂芆袈聿芅薂螄肈莇莅蚀肇肇薀薆蚄腿莃蒂蚃芁薈螁螂羁莁蚇螁肃薇薃螀芅莀蕿蝿莈节袇蝿肇蒈螃螈膀芁虿螇节蒆薅螆羂艿蒁袅肄蒄螀袄膆芇蚆袃荿蒃蚂袃肈莆薈袂膁薁蒄袁芃莄螂袀羃蕿蚈罿肅莂薄羈膇薈蒀羇艿莀蝿羇聿膃螅羆膁葿蚁羅芄芁薇羄羃蒇蒃羃肆芀螂肂膈蒅蚈肁芀芈薄肁羀蒄蒀肀膂芆袈聿芅薂螄肈莇莅蚀肇肇薀薆蚄腿莃蒂蚃芁薈螁螂羁莁蚇螁肃薇薃螀芅莀蕿蝿莈节袇蝿肇蒈螃螈膀芁虿螇节蒆薅螆羂艿蒁袅肄蒄螀袄膆芇蚆袃荿蒃蚂袃肈莆薈袂膁薁蒄袁芃莄螂袀羃蕿蚈罿肅莂薄羈膇薈蒀羇艿莀蝿羇聿膃螅羆膁葿蚁羅芄芁薇羄羃蒇蒃羃肆芀螂肂膈蒅蚈肁芀芈薄肁羀蒄蒀肀膂芆袈聿芅薂螄肈莇莅蚀肇肇薀薆蚄腿莃蒂蚃芁薈螁螂羁莁蚇螁肃薇薃螀芅莀蕿蝿莈节袇蝿肇蒈螃螈膀 ETL应用浅析ETL应用浅析 ePJvq,ec 6xwcQ 信息是现代企业的重要资源，是企业运用科学管理、决策分析的基础。目前，大多数企业花费大量的资金和时间来构建联机事务处理OLTP的业务系统和办公自动化系统，用来记录事务处理的各种相关数据。据统计，数据量每23年时间就会成倍增长，这些数据蕴含着巨大的商业价值，而企业所关注的通常只占在总数据量的24左右。因此，企业仍然没有最大化地利用已存在的数据资源，以致于浪费了更多的时间和资金，也失去制定关键商业决策的最佳契机。于是，企业如何通过各种技术手段，并把数据转换为信息、知识，已经成了提高其核心竞争力的主要瓶颈。而ETL则是主要的一个技术手段。如何正确选择ETL工具？如何正确应用ETL？本期专题将作详细描述。 O b4,9 B3;s!2 数据仓库（DataWare，即DW）系统是在业务系统的基础上发展而来的，其内部存储的数据来自于事务处理的业务系统和外部数据源。而企业内各源数据又缺少统一的标准，因企业的业务系统是在不同时期、不同背景，面对不同应用、不同开发商等各种客观前提下建立的，其数据结构、存储平台、系统平台均存在很大的异构性。因而其数据难以转化为有用的信息，原始数据的不一致性导致决策时其可信度的降低。 UnHaIK mX4mxsr2a ETL(Extract-Transform-Load的缩写，即数据抽取、转换、装载的过程)作为BI/DW（Business Intelligence）的核心和灵魂，能够按照统一的规则集成并提高数据的价值，是负责完成数据从数据源向目标数据仓库转化的过程，是实施数据仓库的重要步骤。如果说数据仓库的模型设计是一座大厦的设计蓝图，数据是砖瓦的话，那么ETL就是建设大厦的过程。在整个项目中最难部分是用户需求分析和模型设计，而ETL规则设计和实施则是工作量最大的，约占整个项目的60%80%，这是国内外从众多实践中得到的普遍共识。 w*mi%3v epjtYqFe 目前，ETL工具的典型代表有:Informatica、Datastage、OWB、微软DTS +|56Y nb 1=0KR 数据集成：快速实现ETL (b1vAD+E X=w=SWX ETL的质量问题具体表现为正确性、完整性、一致性、完备性、有效性、时效性和可获取性等几个特性。而影响质量问题的原因有很多，由系统集成和历史数据造成的原因主要包括:业务系统不同时期系统之间数据模型不一致；业务系统不同时期业务过程有变化；旧系统模块在运营、人事、财务、办公系统等相关信息的不一致；遗留系统和新业务、管理系统数据集成不完备带来的不一致性。 o;3 LSfv /b3t=q2. 实现ETL，首先要实现ETL转换的过程。它可以集中地体现为以下几个方面： V D+-$ eYsef 空值处理 可捕获字段空值，进行加载或替换为其他含义数据，并可根据字段空值实现分流加载到不同目标库。 +7Lbh,uW e FbYpB 规范化数据格式 可实现字段格式约束定义，对于数据源中时间、数值、字符等数据，可自定义加载格式。 ,1)i8 6w*Pk 拆分数据 依据业务需求对字段可进行分解。例，主叫号 861084613409，可进行区域码和电话号码分解。 ex9f 0s 6 Kg=4Mf* 验证数据正确性 可利用Lookup及拆分功能进行数据验证。例如，主叫号861084613409，进行区域码和电话号码分解后，可利用Lookup返回主叫网关或交换机记载的主叫地区，进行数据验证。 ,;W ,Q8s4 IER 数据替换 对于因业务因素，可实现无效数据、缺失数据的替换。 4Mj8+mY? 3sK+axv4H Lookup 查获丢失数据 Lookup实现子查询，并返回用其他手段获取的缺失字段，保证字段完整性。 K g0X. TgG7YC 建立ETL过程的主外键约束 对无依赖性的非法数据，可替换或导出到错误数据文件中，保证主键惟一记录的加载。 J=$8F* +qp-sO 为了能更好地实现ETL，笔者建议用户在实施ETL过程中应注意以下几点： RAG#&j M$8#Jb 第一，如果条件允许，可利用数据中转区对运营数据进行预处理，保证集成与加载的高效性； 2H/ZC*8 4SO d*txx0 第四，关键数据标准至关重要。目前，ETL面临的最大挑战是当接收数据时其各源数据的异构性和低质量。以电信为例，A系统按照统计代码管理数据，B系统按照账目数字管理，C系统按照语音ID管理。当ETL需要对这三个系统进行集成以获得对客户的全面视角时，这一过程需要复杂的匹配规则、名称/地址正常化与标准化。而ETL在处理过程中会定义一个关键数据标准，并在此基础上，制定相应的数据接口标准。 3E|7Pk zaV)pM ETL过程在很大程度上受企业对源数据的理解程度的影响，也就是说从业务的角度看数据集成非常重要。一个优秀的ETL设计应该具有如下功能： xDjx =kvxPWf 管理简单；采用元数据方法，集中进行管理；接口、数据格式、传输有严格的规范；尽量不在外部数据源安装软件；数据抽取系统流程自动化，并有自动调度功能；抽取的数据及时、准确、完整；可以提供同各种数据系统的接口，系统适应性强；提供软件框架系统，系统功能改变时，应用程序很少改变便可适应变化；可扩展性强。 c DOh=6 -L4O$TR | 3rML*p? 数据模型：标准定义数据 A4sZKf5ca VO/&bd U 合理的业务模型设计对ETL至关重要。数据仓库是企业惟一、真实、可靠的综合数据平台。数据仓库的设计建模一般都依照三范式、星型模型、雪花模型，无论哪种设计思想，都应该最大化地涵盖关键业务数据，把运营环境中杂乱无序的数据结构统一成为合理的、关联的、分析型的新结构，而ETL则会依照模型的定义去提取数据源，进行转换、清洗，并最终加载到目标数据仓库中。 s+jJ%aY %G.wtQ 模型的重要之处在于对数据做标准化定义，实现统一的编码、统一的分类和组织。标准化定义的内容包括：标准代码统一、业务术语统一。ETL依照模型进行初始加载、增量加载、缓慢增长维、慢速变化维、事实表加载等数据集成，并根据业务需求制定相应的加载策略、刷新策略、汇总策略、维护策略。 oRH$g1 5-cg:$ (3hGfg:% 元数据：拓展新型应用 e=vx_ /(W).m 对业务数据本身及其运行环境的描述与定义的数据，称之为元数据（metadata）。元数据是描述数据的数据。从某种意义上说，业务数据主要用于支持业务系统应用的数据，而元数据则是企业信息门户、客户关系管理、数据仓库、决策支持和B2B等新型应用所不可或缺的内容。 mb)m#H a731 元数据的典型表现为对象的描述，即对数据库、表、列、列属性（类型、格式、约束等）以及主键/外部键关联等等的描述。特别是现行应用的异构性与分布性越来越普遍的情况下，统一的元数据就愈发重要了。“信息孤岛”曾经是很多企业对其应用现状的一种抱怨和概括，而合理的元数据则会有效地描绘出信息的关联性。 iY rP R :x )6*Z 而元数据对于ETL的集中表现为：定义数据源的位置及数据源的属性、确定从源数据到目标数据的对应规则、确定相关的业务逻辑、在数据实际加载前的其他必要的准备工作，等等，它一般贯穿整个数据仓库项目，而ETL的所有过程必须最大化地参照元数据，这样才能快速实现ETL。 &b,SyXf0 H&f4C6p ETL体系结构 ?5,tl6 .#ZZxZ 下图为ETL体系结构，它体现了主流ETL产品框架的主要组成部分。ETL是指从源系统中提取数据，转换数据为一个标准的格式，并加载数据到目标数据存储区，通常是数据仓库。 !Dt2PgP gu$ESsy z4,.hR# ETL体系结构图 PYrJ 3cj =SZil wW_&KxN Transform 开发者将提取的数据，按照业务需要转换为目标数据结构，并实现汇总。 2#eEQIMB w|6 Load 加载经转换和汇总的数据到目标数据仓库中，可实现SQL或批量加载。 /,ez=v?gH xM#sLu 数据抽取 V i; G4 ?.A7 数据抽取主要是针对各个业务系统及不同网点的分散数据，充分理解数据定义后，规划需要的数据源及数据定义，制定可操作的数据源，制定增量抽取的定义。 ,?oour?0, rf:#$ U. 确定如何抽取或查询源数据并非易事，因为它往往存储在多个地方，可能是一个RDMS、一个文本文件、一个Excel文件、一个DBF文件或其他类型文件，我们在连接时可以通过标准的连接工具比如ODBC或FTP等，而从设计的角度讲最好忽略一些连接问题，把主要精力集中在源数据如何映射目标数据上。 fK,t)y%q .+ uz!Bt/ 在移动的经营分析系统中，数据抽取的数据源是BOSS系统、OA系统等，而为了保证生产系统稳定地运行，ETL所抽取的数据就变成了对数据文件的处理。文件主要分为两类：一类是BOSS中的表文件，如用户信息表，要按照一定的规则周期性提取放到一个目录下（可以是固定目录）；另一个是文本文件，如话单文件，要把它原样移到一个固定的目录下。在实施中，其步骤包括：建立系统的连接；读取数据提取规则参数表，确定提取的源表；根据源表、提取规则、最后一次提取的时间戳或流水号，生成提取动态SQL语句；查询出的数据根据输出文件拆分规则生成相应的文件（包含汇总记录），如果当天数据已全部生成，生成汇总记录文件；将生成的文件转移到公共文件夹中（报表系统获取数据目录），同时复制一份到备份文件夹。 :8i21aKV cE=0Op k8BwOX26 数据转换和清洗 itahA M :p 数据转换主要是针对数据仓库建立的模型，通过一系列的转换来实现将数据从业务模型到分析模型，通过内建的库函数、自定义脚本或其他的扩展方式，实现了各种复杂的转换，并且支持调试环境，清楚地监控数据转换的状态。数据转换是真正将源数据变为目标数据的关键环节，它包括数据格式转换、数据类型转换、数据汇总计算、数据拼接等等。但这些工作可以在不同的过程中处理视具体情况而定，比如，可以在数据抽取时转换，也可以在数据加载时转换。 Sspa/Zy Y(*! E hJDcz R nDTHdVI 统一调度 $xv7 7O X;ud 统一调度是ETL中较为重要的功能。它不但可以将源数据加载到数据仓库中，而且还可以更新数据集市。更重要的是，它还可以更新OLAP Server和挖掘模型数据。它在整个BI方案中扮演着总指挥、总调度的角色。其过程如下图所示。 tc 1DNCS %B3An %Sh)hR0 ETL统一调度的过程 =AXyd* LlLgW: 监控 +-Y5= j W 数据监控主要是指监控ETL的整个过程。通过扫描ETL各模块、日志中的关键值，如记录时间等信息与当前的状态作比较，如果超过某一个值，则认为该模块运行可能出现问题，应告警。 Z96oN6!4es s3 $.Iv 数据仓库的架构大体可以分为三部分：后台是数据存储和计算引擎，前端是数据展现分析的用户界面，还有一个重要的部分就是ETL。 4B_idu|-f 126 AIrJq 正确选择ETL工具，可以从ETL对平台的支持、对数据源的支持、数据转换功能、管理和调度功能、集成和开放性、对元数据管理等功能出发，具体如下。 wDH 6Dsr$: 支持平台 a.3uARo i;?VSz 随着各种应用系统数据量的飞速增长和对业务可靠性等要求的不断提高，人们对数据抽取工具的要求往往是将几十、上百个GB的数据在有限的几个小时内完成抽取转换和装载工作，这种挑战势必要求抽取工具对高性能的硬件和主机提供更多支持。 8Ku|cZi oXX4?) 因此，我们可以从数据抽取工具支持的平台，来判断它能否胜任企业的环境，目前主流的平台包括SUN Solaris、HP-UX、IBM AIX、AS/400、OS/390、Sco UNIX、Linux、Windows等。 .mQYKKBP jdnd: 支持数据源 )mNPNWw R)-oVXS 对数据源支持的重要性不言而喻，因此这个指标必须仔细地考量。首先，我们需要对项目中可能会遇到的各种数据源有一个清晰的认识；其次对各种工具提供的数据源接口类型也要有深入了解，比如，针对同一种数据库，使用通用的接口（如ODBC/JDBC）还是原厂商自己的专用接口，数据抽取效率都会有很大差别，这直接影响到我们能不能在有限的时间内完成ETL任务。 Opofk &tPwEn# 这里，笔 者列出一些常见的数据源供参考：DB2、Informix、Oracle、Sybase、SQL Server、Teredata、OleDB、SAS、Text、Excel、SAP、Peoplesoft、IMS、VSAM、QSAM on 390、FTP、XML、MessageQueue、Weblog等。 1VX7 du x+jNH 数据转换功能 TW(UIO! Fj5GB.X 数据转换是ETL中最令人头疼的问题，由于业务系统的开发一般有一个较长的时间跨度，这就造成一种数据在业务系统中可能会有多种完全不同的存储格式，甚至还有许多数据仓库分析中所要求的数据在业务系统中并不直接存在，而是需要根据某些公式对各部分数据进行计算才能得到。因此，这就要求ETL工具必须对所抽取的数据进行灵活的计算、合并、拆分等转换操作。 50vBAGR LAG9HvU 通常情况下，我们遇到的ETL转换要求包括：字段映射；映射的自动匹配；字段的拆分；多字段的混合运算；跨异构数据库的关联；自定义函数；多数据类型支持；复杂条件过滤；支持脏读；数据的批量装载；时间类型的转换；对各种码表的支持；环境变量是否可以动态修改；去重复记录；抽取断点；记录间合并或计算；记录拆分；抽取的字段是否可以动态修改；行、列变换；排序；统计；度量衡等常用的转换函数；代理主键的生成；调试功能；抽取远程数据；增量抽取的处理方式；制造样品数据；在转换过程中是否支持数据比较的功能；数据预览；性能监控；数据清洗及标准化；按行、按列的分组聚合等。 T5K 7AZ- 2n(Ac? 管理和调度功能 B ,l5fcH 7Sn 由于我们对数据抽取的要求越来越高以及专业ETL工具的不断涌现，ETL过程早已不再是一个简单的小程序就可完成的，目前主流的工具都采用像多线程、分布式、负载均衡、集中管理等高性能高可靠性与易管理和扩展的多层体系架构。因此，这就要求ETL在管理和调度功能上都具备相应的功能。 z-rDo r z)ioFvo 管理和调度的基本功能包括：抽取过程的备份与恢复；升级；版本管理；开发和发布；支持统一以及自定义的管理平台；支持时间触发方式；支持事件触发方式；支持命令行执行方式；支持用户对计算机资源的管理和分配；负载均衡；文档的自动生成；调度过程中能否执行其他任务等。 f+x/n; W/r 2!1 集成和开放性 v ayKh/77 8Xu S;%Xx 上述问题就要求ETL工具能提供很好的集成性和开放性，可以从几方面考量：与OLAP集成；与前端工具集成；与建模工具集成；开放的API可将产品集成到统一界面；是否能调用各种外部应用，包括存储过程、各种流行语言开发的应用程序等；是否支持客户化定制的转换过程；是否支持与统计分析工具的集成等。 IWVfo N0PA QQ 管理元数据 RdUfgxC:v 9V,!co) 元数据是关于数据的数据，尤其对于ETL来说尤其重要。ETL中大量的数据源定义、映射规则、转换规则、装载策略等都属于元数据范畴，如何妥善地存储这些信息已经关系到ETL过程能否顺利完成而且影响到后期的使用和维护。任何业务逻辑的微小改变最终都落实为相应元数据的调整，初期没有一个完善的元数据管理功能而后期作类似调整几乎是“不可完成的任务”。基于元数据的重要性，国际组织提出一些统一的元数据存储标准，比较知名的如CWM等，为不同厂商工具之间互操作提供了可能性，相信也是今后的发展趋势。 p)GNYn mML8e/QZ 针对ETL的元数据管理，笔者认为应包括：元数据存储的开放性；元数据存储的可移植性；提供多种方式访问元数据；元数据的版本控制；支持开放的元数据标准；支持XML进行元数据交换；支持分布式的元数据访问和管理；生成元数据报表；对于ETL过程的冲突分析；基于元数据的查询功能；元数据的广播和重用；对于ETL过程的流程分析等。 zWST8.Z JL6Q;V2 v8-h 主流的ETL工具 ?$7*r=Z ;l|/=EI 目前市场上主流的ETL工具可以分为两大类：一类是专业ETL厂商的产品，这类产品一般都具备较完善的体系结构和久经考验的产品，产品的功能之复杂和详尽，往往能令初次接触的人膛目，但其高昂的价格也会使一般用户望而却步；另一类是整体数据仓库方案供应商，他们在提供数据仓库存储、设计、展现工具的同时也提供相应的ETL工具，这类产品一般对自己厂商的相关产品有很好的支持并能发挥出其最大效率，但结构相对封闭，对其他厂商产品的支持也很有限。 M;sHE46 Ap RX C 专业ETL厂商和产品包括Ascential公司的DataStageXE、Sagen