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

摘要：介绍了ETL功能及其处理技术，探讨了库表及文件抽取方式，数据转换技术策略以及数据加载技术，设计了SQL脚本自动解析获取元数据的实现方法，研究了集群优化技术在ETL数据处理中的应用。

关键词: ETL；数据仓库；元数据；集群优化

中图分类号：TP311文献标识码：A文章编号：1009-3044(2012)13-2949-04

Research and Implementation of ETL Cluster Optimization Technology

LI Lan-you,HU Cheng-hao,ZHANG Chun-hua

(Office of Information 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 implementation 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, Transform and Load）;data warehouse; metadata; cluster optimization

1概述

随着高校网络的不断发展，学生公寓网络的web访问数据越来越多，基于设备管理系统建设的架构为模板，运用ETL技术将这些数据信息建立目标数据仓库，以挖掘学生人群的网络交互行为及相互关系，兴趣传播及作用关系，给学校领导管理决策提供宏观数据支持，是一件非常有意义的事情。

2 ETL简介

ETL[1]是数据抽取（Extract）、转换（Transform）、清洗（Cleansing）、加载（Load）的简称，作为数据仓库的核心和灵魂，能够按照统一的规则集成并提高数据的价值，是负责完成数据从数据源向目标数据仓库转化的过程，即从数据源系统中获取业务数据，通过数据转换、数据清洗过程，将符合EDM（企业数据模型）的数据加载到数据仓库中。数据仓库的最普遍应用是给企业提供决策,由于ETL在数据仓库搭建过程中花费了大约60% ~ 80%的工作量[2],由此可见, ETL在建立数据仓库系统时中是很关键的问题,可以说ETL过程的执行效率直接决定了企业决策的质量。

数据抽取主要是针对分布在不同业务系统中的分散数据，规划需要的数据源和数据定义，在接口规范中明确的定义了数据源系统提供的数据文件的内容、提供周期、提供方式等，ETL的抽取将按照接口规范中定义的方式从数据源系统中通过FTP传输数据文件的方式到数据仓库系统中[3]。

数据转换主要是针对数据仓库建立的模型，通过内建的函数库、自定义的脚本以及其它一些扩展方式将数据从业务模型转化到分析模型中。数据转换是将原数据变为目标数据的关键环节，它包括数据格式转换、数据类型转换、数据汇总计算、数据拼接等。

数据清洗主要是针对业务系统中的各个环节可能出现的数据二义性、重复、不完整、违反业务规则等问题，通过清洗将有问题的数据先剔除出来，然后根据实际情况作相应的处理[4]。

数据加载主要是将符合数据仓库要求的数据加载到数据仓库中。

3 ETL集群优化

3.1 ETL功能结构

ETL主要功能模块包含数据源模块、数据获取模块、数据处理模块、数据分发及ETL数据调度管理模块，如图1所示。

数据源模块负责对数据来源的各个源系统及数据平台访问方式和类型等信息进行维护；数据获取模块负责将数据源提供的接口数据文件或者表接口数据传输到数据仓库系统中，并完成文件级或者表接口数据量的审核，数据获取有FTP获取和数据库链两种方式实现；数据处理模块包含数据转换和数据加载，数据处理模块负责将接口文件或者表接口数据加载到数据仓库中，同时按照一定的接口规则进行转换处理，其具体实现可以采用DATASTAGE工具、存储过程和shell脚本三种处理方式[5]；数据分发模块主要完成数据仓库数据与外网系统的接互；ETL数据调度管理模块贯穿整个ETL功能模块的各个环节，其数据调度功能主要通过任务调度平台来实现，通过调度任务的参数时间周期、任务参数等配置，自动调用接口DATASTGE工具的作业或者存储过程等完成数处理。ETL子系统的元数据由元数据管理系统统一管理，ETL的每一个模块将通过共享内存和系统总控交互信息。

3.2 ETL集群优化模型

传统的ETL处理方式在一些文件量较大，数据处理实时性和及时性要求较高的情况下，数据处理速度已成为ETL模块的一个效率瓶颈。要想解决这些问题，就必须进行优化[6]。

考虑到集群分布式计算的优点，将其引入到ETL数据处理中，改进原有库内ODS层关联获取方式，将分析字段的判别提前到库外处理，用多台PC服务器的集群计算模式来分摊数据库服务器的计算压力。不仅可以避免事务加载过程中过多的消耗数据库资源，同时也加快了处理效率，一举两得。处理流程如图2所示。

图1 ETL功能模块框架图

图2 ETL集群优化模型图

规则缓冲区是将转化需要用到的规则配置信息读入内存缓冲区，如学号信息，上网时长信息及一卡通转账判断规则等；预处理加工引擎是用来匹配规则缓冲区的配置信息，处理内存中的文件接口数据，生成应用需要的共性分析字段。通过PC内存缓冲区的处理，将源文件经过内存处理获取所需的关键判断字段信息生成目标文件，直接通过DATASTAGE工具加载入库即可，这样即可大大提高了文件处理效率，同时也减少了数据库资源的消耗，尤其是系统繁忙阶段效果更为明显。

3.3 ETL元数据管理优化

通过在ETL元数据管理中引入SQL脚本自动解析获取技术，将能缓解元数据管理中手工维护难度大的问题。

SQL脚本自动解析获取元数据的过程可以分为数据处理日志生成、运行日志获取、SQL词法语法分析、SQL语义分析生成元数据和SQL脚本元数据入库五个环节[7]。通过这五个环节的自动处理，将脚本程序的变化及时传递到应用端，使应用分析的结果能够反映ETL和数据处理过程的最新情况，如图3所示。

输出运行日志环节要求ETL和数据处理过程在每次运行时，按指定方式输出运行日志，将提交执行的SQL脚本以及必要的上下文信息写入运行日志中。

运行日志获取环节定期扫描ETL和数据处理过程所输出的日志，提取未经处理的运行日志并触发解析处理过程。通过按照规则配置输入参数调用SQL解析公用模块即可生成对应的日志文件，主要为统一规则的XML格式文件，以供后续语法解析和语义解析生成规范的元数据信息。

SQL词法语法分析环节利用编译技术对运行日志中的脚本进行词法语法分析，生成抽象语法树（AST）。

图3 ETL元数据SQL脚本自动解析优化图

在SQL语义分析生成元数据环节，该环节对各SQL脚本的抽象语法树进行语义分析，并结合SQL脚本之间上下文相关信息的处理，实现SQL脚本语义的元数据结构化描述。

在SQL脚本元数据入库环节，将自动解析所获取的SQL脚本元数据写入元数据存储库中。元数据解析获取并入库完成后，可形成源数据表与目标表之间的关联关系，即“血缘关系”[8]。

SQL脚本自动获取元数据优化技术能使得ETL元数据及时更新，降低管理成本，提高管理效率，为各种辅助分析应用提供高质量的元数据。

3.4 ETL库表数据抽取策略

ETL库表抽取是将Oracle、Sybase和DB2等各种类型数据库中的指定列的值存入目标文件中，以完成不同数据间表数据的转换。具体实现过程中，在获取表列信息和次要数据项信息时考虑是否分组处理，对于分组处理的不仅要生成select语句的fields部分，而且还要生成其group by部分。生成的SQL语句通过BulkCopy来进行抽取[9]，并将出库记录写入目标文件，如图4所示。

图4 ETL库表数据抽取策略

3.5 ETL数据转换策略比较分析

数据转换一般按照以下四个策略进行操作：在抽取过程中进行数据处理；使用异步数据加载，以文件的方式处理；在数据加载过程中进行数据处理；进入数据仓库以后再进行数据处理。

采用在数据抽取过程中进行数据转换时，必须考虑抽取的性能以及对业务系统性能的影响；采用异步数据加载以文件方式处理时，必须充分考虑中间磁盘的存储量以及ETL整个流程的协调性工作，以及大量的非SQL语句的编程；采用在数据加载过程中进行数据转换时，必须考虑加载性能；采用先将数据加载到数据仓库然后再处理时，必须考虑数据仓库引擎的海量数据处理能力[10]。表1是转换方法在各个比较点的展示。

表1数据转换策略比较分析

经过比较分析后可知，在ETL数据转换优化方面，要分情况而定。对不同类型的数据采用不同的转换方法，例如数据量特别大并且采用文件抽取的数据清单要采用文件转换的方式；而对于采用库表抽取的信息资料采用抽取过程中进行数据转换的方式。

4结论

ETL的主要作用在于其屏蔽了复杂的业务逻辑，从而为各种基于数据仓库的分析和应用提供了统一的数据接口，是整个数据仓库上层应用数据分析处理的根源。实践证明，ETL集群优化技术和SQL自动解析元数据优化技术能有效降低维护成本，提高管理效率。

参考文献:

[1]宋旭东,闫晓岚,刘晓冰,等.数据仓库ETL元模型设计[J].计算机仿真,2010,27(9):106-108.

[2]周茂伟,邓苏,黄宏斌.基于元数据的ETL工具设计与实现[J].科学技术与工程,2006,6(21):3503-3505.

[3]张瑞.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,31(4):2070-2072.

[7]管丽娟.数据ETL软件中元数据模型设计与管理[J].电脑知识与技术,2007,2(8):469-475.

[8]郑丹青.基于元数据的数据仓库ETL系统设计与研究[J].吉林师范大学学报:自然科学版,2010,(2):43-45.

[9]陈硕.基于ETL技术在ODS系统中的应用[J].电脑知识与技术,2009,5(26):7330-7332.

[10]彭璐.基于数据仓库的ETL过程优化[J].计算机与数字工程,2010,38(5):166-168.