ETL集群优化技术研究与实现

1、etl集群优化技术研究与实现etl集群优化技术研究与实现摘耍：介绍了 etl功能及其处理技术，探讨了库表及文件抽取 方式，数据转换技术策略以及数据加载技术，设计了 sql脚本自动解 析获取元数据的实现方法，研究了集群优化技术在etl数据处理中的 应用。关键词：etl；数据仓库；元数据；集群优化中图分类号：tp311文献标识码：a文章编号： 1009-3044 (2012) 13-2949-04research and implementation of etl cluster optimization technologyli lan-you, hu cheng-hao, zhang chun

2、-hua(office of infonnation construction and management, nanjing institute of technology, nanjing 211167, china)abstract： the etl function and process technology is introduced. the extraction type of database table file , the strategy of data conversion and data loading technology are shown . the imp

3、lementation methods of metadata analysis and obtaining with sql script automaticly are designed. the deep research of clusters optimization technique application in etl data processing is in implementation.key words： etl(extract, transfonn and load) ; data warehouse; metadata; cluster optimization&l

4、t;b>l 概述/b>随着高校网络的不断发展，学生公寓网络的web访问数据越来越 多，基于设备管理系统建设的架构为模板，运用etl技术将这些数据 信息建立目标数据仓库，以挖掘学生人群的网络交互行为及相互关系, 兴趣传播及作用关系，给学校领导管理决策提供宏观数据支持，是一 件非常有意义的事情。<b>2 etl 简介/b>etl1是数据抽取(extract)、转换(transform) 清洗(cleansing)、加载(load)的简称，作为数据仓库的核心和灵魂, 能够按照统的规则集成并提高数据的价值，是负责完成数据从数据 源向目标数据仓库转化的过程，即从数据源系统中

5、获取业务数据，通 过数据转换、数据清洗过程，将符合edm (企业数据模型)的数据加 载到数据仓库中。数据仓库的最普遍应用是给企业提供决策，由于etl 在数据仓库搭建过程中花费了大约60% 80%的工作量，由此可见 etl在建立数据仓库系统吋中是很关键的问题，可以说etl过程的执 行效率直接决定了企业决策的质量。数据抽取主要是针对分布在不同业务系统屮的分散数据，规划需 要的数据源和数据定义，在接口规范中明确的定义了数据源系统提供 的数据文件的内容、提供周期、提供方式等，etl的抽取将按照接口 规范中定义的方式从数据源系统中通过ftp传输数据文件的方式到 数据仓库系统中3。数据转换主要是针对数据仓

6、库建立的模型，通过内建的函数库、 自定义的脚本以及其它一些扩展方式将数据从业务模型转化到分析 模型中。数据转换是将原数据变为目标数据的关键环节，它包括数据 格式转换、数据类型转换、数据汇总计算、数据拼接等。数据清洗主要是针对业务系统中的各个环节可能出现的数据二 义性、重复、不完整、违反业务规则等问题，通过清洗将有问题的数 据先剔除出来，然后根据实际情况作相应的处理4。数据加载主要是将符合数据仓库要求的数据加载到数据仓库中。<b>3 etl集群优化/b>3. 1 etl功能结构etl主要功能模块包含数据源模块、数据获取模块、数据处理模 块、数据分发及etl数据调度管理模块，如图

7、1所示。数据源模块负责对数据來源的各个源系统及数据平台访问方式 和类型等信息进行维护；数据获取模块负责将数据源提供的接口数据 文件或者表接口数据传输到数据仓库系统中，并完成文件级或者表接 口数据量的审核，数据获取有ftp获取和数据库链两种方式实现；数 据处理模块包含数据转换和数据加载，数据处理模块负责将接口文件 或者表接口数据加载到数据仓库中，同吋按照一定的接口规则进行转 换处理，其具体实现可以采用datastage工具、存储过程和shell脚 本三种处理方式5；数据分发模块主要完成数据仓库数据与外网系 统的接口交互；etl数据调度管理模块贯穿整个etl功能模块的各个 环节，其数据调度功能主要

8、通过任务调度平台來实现，通过调度任务 的参数吋间周期、任务参数等配置，自动调用接口 datastge x具的 作业或者存储过程等完成数处理。etl子系统的元数据由元数据管理 系统统一管理，etl的每一个模块将通过共享内存和系统总控交互信 息。3. 2 etl集群优化模型传统的etl处理方式在一些文件量较大，数据处理实时性和及吋 性要求较高的情况下，数据处理速度已成为etl模块的一个效率瓶颈。 要想解决这些问题，就必须进行优化6。考虑到集群分布式计算的优点，将其引入到etl数据处理中，改 进原有库内ods层关联获取方式，将分析字段的判别提前到库外处理, 用多台pc服务器的集群计算模式来分摊数据库

9、服务器的计算压力。 不仅可以避免事务加载过程中过多的消耗数据库资源，同吋也加快了 处理效率，一举两得。处理流程如图2所示。图1 etl功能模块框架图图2 etl集群优化模型图规则缓冲区是将转化需要用到的规则配置信息读入内存缓冲区， 如学号信息，上网吋长信息及一卡通转账判断规则等；预处理加工引 擎是用来匹配规则缓冲区的配置信息，处理内存中的文件接口数据， 生成应用需要的共性分析字段。通过pc内存缓冲区的处理，将源文 件经过内存处理获取所需的关键判断字段信息生成目标文件，直接通 过datastage工具加载入库即可，这样即可大大提高了文件处理效率, 同时也减少了数据库资源的消耗，尤其是系统繁忙阶段

10、效果更为明显。3. 3 etl元数据管理优化通过在etl元数据管理屮引入sql脚本自动解析获取技术，将能 缓解元数据管理中手工维护难度大的问题。sql脚本自动解析获取元数据的过程可以分为数据处理日志生成、 运行日志获取、sql词法语法分析、sql语义分析生成元数据和sql 脚木元数据入库五个环节7。通过这五个环节的自动处理，将脚木 程序的变化及时传递到应用端，使应用分析的结果能够反映etl和数 据处理过程的最新情况，如图3所示。输出运行h志环节要求etl和数据处理过程在每次运行吋，按指 定方式输出运行13志，将提交执行的sql脚本以及必要的上下文信息 写入运行13志中。运行日志获取环节定期扫描

11、etl和数据处理过程所输出的日志， 提取未经处理的运行u志并触发解析处理过程。通过按照规则配置输 入参数调用sql解析公用模块即可生成对应的el志文件，主要为统一 规则的xml格式文件，以供后续语法解析和语义解析生成规范的元数 据信息。sql词法语法分析环节利用编译技术对运行日志中的脚本进行词 法语法分析，生成抽象语法树(ast)o图3 etl元数据sql脚木自动解析优化图在sql语义分析生成元数据环节，该环节对各sql脚本的抽象语 法树进行语义分析，并结合sql脚本z间上下文相关信息的处理，实 现sql脚木语义的元数据结构化描述。在sql脚本元数据入库环节，将口动解析所获取的sql脚本元数

12、据写入元数据存储库中。元数据解析获取并入库完成后，可形成源数 据表与目标表之间的关联关系，即“血缘关系” 8osql脚本口动获取元数据优化技术能使得etl元数据及时更新， 降低管理成本，提高管理效率，为各种辅助分析应用提供高质量的元 数据。3. 4 etl库表数据抽取策略etl库表抽取是将oracle. sybase和db2等各种类型数据库中 的指定列的值存入目标文件中，以完成不同数据间表数据的转换。具 体实现过程屮，在获取表列信息和次要数据项信息时考虑是否分组处 理，对于分组处理的不仅要生成select语句的fields部分，而且还 要生成其group by部分。生成的sql语句通过bulk

13、copy来进行抽取 9,并将出库记录写入目标文件，如图4所示。图4 etl库表数据抽取策略3.5 etl数据转换策略比较分析数据转换一般按照以下四个策略进行操作：在抽取过程中进行数 据处理；使用异步数据加载，以文件的方式处理；在数据加载过程中 进行数据处理；进入数据仓库以后再进行数据处理。采用在数据抽取过程中进行数据转换时，必须考虑抽取的性能以 及对业务系统性能的影响；采用异步数据加载以文件方式处理时，必 须充分考虑中间磁盘的存储量以及etl整个流程的协调性丁作，以及 大量的非sql语句的编程；采用在数据加载过程中进行数据转换时, 必须考虑加载性能；采用先将数据加载到数据仓库然后再处理时，必

14、须考虑数据仓库引擎的海量数据处理能力10。表1是转换方法在各 个比较点的展示。表1数据转换策略比较分析经过比较分析后可知，在etl数据转换优化方面，要分情况而定。 对不同类型的数据采用不同的转换方法，例如数据量特别大并且采用 文件抽取的数据清单要采用文件转换的方式；而对于采用库表抽取的 信息资料采用抽取过程中进行数据转换的方式。<b>4 结论/b>etl的主要作用在于其屏蔽了复杂的业务逻辑，从而为各种基于 数据仓库的分析和应用提供了统一的数据接口，是整个数据仓库上层 应用数据分析处理的根源。实践证明，etl集群优化技术和sql自动 解析元数据优化技术能有效降低维护成本，提高管

15、理效率。<b>参考文献:</b>1 宋旭东，闫晓岚,刘晓冰，等数据仓库etl元模型设计j.计 算机仿真,2010, 27 (9) : 106-108.2 周茂伟，邓苏，黄宏斌 基于元数据的etl工具设计与实现j 科学技术与工程，2006, 6(21) : 3503-35053 张瑞.etl数据抽取研究综述j.软件导 刊,2010, 9(10) : 164-165.4 毕棍,刘军.etl系统的设计及其研究j.软件导 刊,2010, 9(5) : 173-175.5 夏秀峰，张亮，石祥滨，等.一种改进的分布式etl体系结构 j计算机应用与软件,2010, 27(4) : 174-176.6 夜程,李善平基于etl的金融数据集成过程模型j计算机 工程与设计,2010,