ETL系统设计（共7篇）
ETL系统设计 篇1
0 引 言
现代企业在信息化建设过程中,逐渐面临如何有效地利用企业中大量数据的问题,通过近几年的研究,数据仓库可以提供一个集成的数据平台,它为数据分析和决策提供了有效地支持,但数据进入数据仓库之前必须要解决异构多数据源的集成,因为数据源常常是异地、异构的,还要克服数据一致性和质量等诸多问题,ETL系统就是为数据仓库提供一个解决上述问题的系统。经验告诉我们,即使整个数据仓库的企业模型构架非常理想,如不能够解决上述数据问题,这样的数据仓库也是“空中楼阁”。因此设计一套结构合理、性能高效的ETL系统尤为重要。
本文基于ETL基本理论和公共数据仓库元模型CWM(Common Warehouse Metamodel)标准,从实践角度设计了ETL系统的模型框架和调度模型,并提出了一种在异地、异构的多数据源的数据抽取和集成的方法。上述研究在实际项目中已得到应用,并取得较好的效果。
1 ETL系统
1.1 ETL系统原理概述
从数据源到数据仓库或操作型数据存储ODS(Operational Data Store)的过程中,需要对数据进行抽取、转换、装载处理, 这个处理系统就是ETL系统,其主要功能包括:
(1) 数据的抽取 从多数据源中读取源数据中的元数据、接入数据和抽取数据。
(2) 数据的转换 通过对数据清洗、数据转换(合并、转换和聚合等)以满足目标数据的数据模型和数据质量的需要。
(3) 数据的加载 将转换完的数据按照目标数据模型定义的表结构加载到目标数据源中。
1.2 ETL系统面临的挑战
ETL系统是一个既简单又复杂的系统,将数据从各种业务处理系统导入数据仓库是一个复杂的过程。
(1) 对于ETL开发人员来说,采用什么开发方法才能提高数据处理模块的重用性,并加快开发周期一直是需要面临的挑战,因此需要建立一个ETL框架模型来指导实际开发中的架构和建模。
(2) ETL系统中数据处理模块通常不是一个简单的数据处理单元,通常是存在着大量的处理过程,并且其中存在着非常复杂的关系(依赖与被依赖的逻辑关系),这也意味着需要解决数据处理的调度问题。
(3) ETL系统面临着不同的网络、操作系统平台、数据库、数据格式的问题。如何解决对数据源处在多处异地的抽取问题,也时常困扰ETL的开发者。
2 ETL系统设计与实现中的关键技术
2.1 ETL系统模型框架
2.1.1 设计思想
OMG(Object Management Group)提出CWM元数据标准主要目的是实现对组成数据仓库系统的各个部分之间元数据的交换和共享的最大化[1],故完全可以把这个标准的模型作为ETL系统的框架的基础。因为,第一现在许多数据库管理系统都支持这个模型,这样构造的ETL系统能有更好的兼容性;第二采用这个标准可以达到元数据的最大重用性,但CWM只是针对统一的数据仓库标准模型,并不是完全针对ETL系统的标准模型,所以必须进行一定的修改,以真正适应ETL系统环境。图1就是基于CWM的ETL系统框架模型。
2.1.2 功能结构及其流程介绍
(1) 功能结构
如图1所示,这个模型总体结构分为三个层次,从下到上依次为资源层、分析层和管理层。每一层都是由若干块组成,每一块代表一个元模型(包)。根据OMG组织的定义,元模型是一个保存了元-元数据的模型,元-元数据是描述元数据的元数据,因为元数据它自己本身也是一类信息[1]。
1) 资源层 描述各种不同类型数据资源的元数据的模型,元数据是指对数据的抽象信息。其中包括了根据数据源不同的类型模型:关系和文本型的元模型。
关系元模型是指关系型数据源中的元模型。它由表信息元数据(表名、表创建日期、表所属数据库、表描述信息)、字段信息元数据(字段名、字段类型、字段长度、字段描述、所属表名),数据库元数据(数据库名、用户名、密码、端口、数据库创建日期、数据库描述信息)组成。表信息元数据通过表所属数据库名关联到数据库元数据。字段信息元数据通过所属表名关联到表信息元数据[2]。关系元模型还包括业务信息元数据,它是指从业务的角度描述了数据存储中的数据,通常是对数据语义上的描述。它是保证用户能够正确方便地理解和使用数据存储的元数据。
文本元模型是指文本类型数据源中的元模型。它由文本字段元数据(包括字段名、字段类型和纪录分隔符信息)和文本连接元数据(文件目录和连接信息)。同样它也包含了业务元数据。
2) 分析层 该层定义资源层中模型的进一步描述(包括它们之间关系)和各种规则(抽取,转换和加载)元数据,其中包括的资源层中元模型的映射及转换。该层是这个ETL框架模型中最关键的部分,包括了抽取、转换和加载元模型。抽取和加载元模型包括了数据源中的元数据、抽取和加载规则定义(其实例通常为程序),比如针对操作型数据源来说是一组事务。转换元模型是分析层中最为重要的部分,它主要是完成数据集成和聚集工作。数据集成是指对多个数据源进行重构集成,清洗和转换,转换为满足目标数据源(数据仓库)模型的定义。其中包括解决数据源的异构问题,通常分为四个层次:系统、语法、结构和语义[3]。如数据从数据源到数据仓库要进行时间格式数据转换;数据源中的纪录中属性的语义通常是不同的,如需要解决空值、重复值、不同的计量单位问题。聚集是指对数据进行汇总和综合,即加大数据粒度。
3) 管理层 定义了分析层元数据调度和执行方面的元数据模型,其中包括了运行监控、作业调度管理、恢复管理等模型。
(2) 处理流程
通过上述对ETL系统框架模型的定义,已经确立了ETL系统的整个架构。以下论述数据如何从源数据通过这个架构模型实现ETL过程最终加载到目标数据源中。
1) 首先异构数据源中的数据通过资源层中的关系/文本元模型这个数据接口①,由分析层中的抽取元模型对这些数据进行抽取②,这个元模型定义了抽取规则,即那些数据要抽取和怎样抽取。
2) 对于抽取完成的数据经过转换元模型的转换处理③,其中转换元模型定义了源数据和目标数据存储中的转换元数据。这些转换元数据包含了转换规则元数据,通常是由面向领域的业务规则和数据仓库的物理表(目标数据存储格式)决定的。领域规则是在用户参与的情况下定制,这样可以避免破坏系统的业务规则完整性。
3) 经过转换元模型后的数据,通过保存了加载规则的加载元模型进行数据加载处理工作④。
4) 最后通过资源层中的元数据定义进入目标数据存储中⑤。
在很多情况下,数据在分析层中的处理都是很复杂的,比如一个转换元模型的实例通常的输入不仅仅有抽取元数据的结果,有时会是另一个分析层中的元模型的输出结果,如抽取元模型或者是转换元模型的结果或者部分结果。
2.2 ETL处理调度模型
2.2.1 设计思想
如何保证数据能够按照预期的方式正确地进行抽取、转化和加载到目标数据存储中,也是ETL系统中的关键性问题。本文通过对分析元模型的调度来建立作业调度管理元模型,从而实现对ETL作业的调度管理。
2.2.2 作业调度的实现
如图2所示,作业调度元模型包括了分析元模型、控制流程和判断元数据构成。
分析元模型是最小的流程调度单元,当然这由开发者来控制,例如可以将抽取、转换元模型放在一个分析元模型中,那样的话只能同时对抽取、转换同时进行调度,而如果将他们分作两个单元,可以分别运行,这样有利于错误恢复操作。判断元数据定义了下一个分析元模型执行的条件。它由一些条件和触发规则的组成,比如触发处理下一分析元模型中的判断条件。控制流程是定义了处理的方向。
对于作为整个作业调度模块来说,存在的很多的数据流,每一个数据流有至少有一起点和终点。在这个数据流中的分析元模型是存在着先与后的关系(如图2中的分析元模型A与B),比如分析元模型B执行的前提是被依赖的分析元数据A必须被正常执行,或者没有被正常执行,或者执行结果满足一定的条件,都是经过一个判断元数据的求值过程,然后根据这个求值的结果和触发规则执行下一个处理动作。这种依赖关系通常很复杂,如图2中的分析元模型D同时依赖B、C,而分析元数据B却只依赖A。
对于判断元数据常常是对条件求值的结果进行判断,通常有两种情况:一种是正常情况,执行下个子分析元模型即如图2中从判断1触发分析元模型B执行。另一种情况非正常情况,通常是不能执行下个分析元模型(这由触发规则决定),整个数据流中止并转入异常处理中,其中定义了数据流异常后需处理的操作,比如写错误日志和异常操作等。在实际ETL开发工具中除了可以对分析元模型外,还有一些特殊的调度模型,如定时器和等待特定文件等模型。综上所述,对于一个作业调度元模型来说,流程调度单元之间的逻辑关系和判断元数据是该模型的核心,所以构建作业调度模型来说,必须对两者进行正确的和高效的定义。
2.3 异构数据源的抽取元模型
伴随着企业的业务发展,会在异地开设分公司,同时会开始形成在异地的业务系统。如何将多个异地数据源进行数据抽取是一个经常面临的问题,下面论述如何基于ETL框架模型,使用抽取-传输-缓冲-合并的抽取技术来解决上述问题,如图3所示。
第一步读取每一个数据源中资源层中的关系/文本元模型①。第二步是经过远端抽取元模型对数据进行抽取处理②,它是由抽取核心、日志模块和抽取配置元数据构成。抽取核心中的抽取方式可以分为全量和增量抽取,全量不是日常事务,通常是第一次抽取才发生。日常事务都是增量抽取,其中的算法有基于日志的,基于业务系统的时间戳等。抽取核心依赖于抽取配置元数据,它由需要抽取表名、字段、类型、数据库连接信息、抽取数据时间窗等信息。日志模型是用来纪录抽取处理的日志。第三步是通过传输元模型来实现将某个抽取的数据从远端传输到中心端③,其中也包括对传输处理的日志。第四步是将传输到中心端的数据缓存到异构临时存储区④,这是因为通常是有多个数据源需要抽取,抽取的进程通常是异步的,故需要有一个数据缓冲来同步这个过程,同时也为了数据恢复,这样不会因为一端数据抽取失败而造成所有前端的数据重新抽取。最后将这些各个数据源中抽取的数据进行合并,形成一个统一的抽取文件⑤。
这个抽取-传输-缓冲-合并的抽取技术是在各个前端分别抽取,然后将成功抽取的文件传输到中心端的数据缓冲区,再将这些数据进行合并,这样有效地解决了在异地不同的数据源抽取数据的一致性和完整性问题,因为在传输之前先对抽取文件过程的状态进行检查,这就保证了数据抽取过程的正确,同时检查传输运行状态,如果不成功传输元模型将自动重传,并纪录运行日志。
3 实际实施和效果
通过上述对ETL模型框架的研究,已在某保险公司的ODS建设中进行实际的应用,以下就是在对实际项目上应用的介绍。
该公司在全国各地都部署了本地的业务系统,而且这些系统的操作系统和数据库系统都各异,它们通过VPN(Virtual Private Network)相连,这些系统作为ETL的数据源。目标数据存储是采用AIX和oracle9i的平台,该目标系统是要建立面向以客户为主题的ODS,它为客户服务系统提供集成和统一的数据平台。从执行效能和数据稳可靠性考虑将ETL服务器和目标数据存储分开,ETL服务器采用AIX操作系统平台,并部署DataStage服务器,它是作为执行各个ETL程序的服务器。
对于数据抽取部分来说,因为各个数据源处于不同的物理地址,可以通过采用前端抽取-传输-缓冲-合并的技术建立抽取模型。针对日常抽取,远端抽取元模型使用增量抽取方式,并基于时间戳的方法一一确定时间窗的开始和结束时间,时间窗的结束时间通常是根据用户定义的时间点,开始时间是由上次成功进行抽取时间窗的结束时间。远端抽取元模型是一个由C程序编译后的程序,部署在远端应用系统中,它在每天自动发起抽取增量数据并将抽取状况写入Log中。对于传输模块采用Ftp的方式,因为考虑各个分公司的日增量数据比较少,通常是在50Mb以内,Ftp对这样的数据量来说,传输发生错误的可能性比较小,并在使用Ftp传输过程中,先将文件更名为临时文件,传输完成后再更改为原来名字,保证文件传输的正确性及完整性。其中将各个公司的抽取数据进行合并。
在基于上述提出的ETL框架模型下,建立“TL”系统,第一步通过ETL开发工具(在本项目中使用DataStage开发工具)建立资源层中的元模型,其中包括数据源和目标数据存储的关系/文本元数据。第二步针对车险、寿险等险种分别建立分析层中的抽取、转换和加载元模型,其中主要是开发映射关系和转换规则,以整合各个数据源实现向面向客户为主题的ODS加载一致的数据。第三步通过根据业务规则和分析层元模型逻辑关系建立作业调度元模型,对各个险种中的分析层元模型和险种之间的处理进行调度,实现各个险种手动和自动地处理。第四步生成元模型实例,即设置连接元数据(数据库连接信息、对于文本文件来说是纪录信息中分隔符等)。最后开发环境读取元模型实例,解析元模型中的提取、转换和加载规则,生成可执行代码,然后通过Datastage服务器来执行。
笔者使用上述方法对寿险部分的ETL系统进行开发,并通过测试。整个系统已成功投入生产环境。整个系统每天可以成功将增量数据(约200MB-300MB)加载的ODS中,达到了设计系统的预期目的。
4 结 论
本文研究了ETL系统的框架模型,通过建立这个框架模型实现ETL系统的构建,并基于这个框架模型建立作业调度元模型来实现ETL系统中的调度问题,最后提出了前端抽取-传输-缓冲-合并的抽取技术,有效地解决了异地多数据源的数据抽取问题。随着数据仓库的进一步发展,ETL系统越发重要,因此在未来需要对ETL系统进一步完善,比如提高其数据的质量,对ETL系统的监控和对系统错误处理和恢复。
参考文献
[1]Common Warehouse Metamodel(CWM)Specification Version 1.0,2February,2001.
[2]王磊,李一凡,赵怀慈.银联数据仓库系统中ETL的设计和实现[J].微电子学与计算机,2007,24(5).
[3]Sheth A P.Changing Focus on Interoperability in Information Systems:From System,Syntax,Structure to Semantics[A].Interoperating Geo-graphic Information Systems[C].Kluwer Academic Publishers.1998,5.
ETL系统设计 篇2
随着信息技术的蓬勃发展,各供电公司为了保障电网安全、可靠、稳定、经济的运行,针对不同应用需求建立了各类业务系统,相关的数据信息是多源异构的,即数据的存储介质、存储形式、存储位置以及所处的网络条件都不尽相同。当前电网企业管理模式由分散化向集中化、精益化转变,对电网系统做全面深层次的分析就需要整合各类电网数据,建立数据中心以便为用户提供全局统一的数据视图。ETL是构建数据中心的重要环节[1],在跨越多个平台的复杂网络条件下,依赖供应商的ETL产品往往难以满足电网实时数据迁移与同步的需要,这就需要实现一个独立的ETL系统。
在为某省电网公司建立电网实时数据监测系统数据中心的过程中,需要从盐密监测系统(Oracle),微气象系统(Access),蓄电池监测系统(Sqlserver),杆塔线路监测系统(Excel)等众多的数据源中获取实时数据。以往的做法是通过编写一系列专门的接口程序来实现的,为了降低此类项目的实施难度和开发成本,我们开发了一个独立的ETL系统来满足电网实时数据的迁移与同步更新的需要。
1 ETL及其实现相关技术背景
1.1 ETL系统介绍
ETL (Extraction Transformation Loading)是抽取-转换-加载的英文缩写,具体来说,是从各种类型的数据源中抽取数据,并对其进行清洗与转换,最终将满足要求的数据内容装载入数据仓库的过程[2]。
抽取(Extraction)是从分布式的异构数据源中抽取业务数据的过程。数据源包括Oracle,Sqlserver,Sybase,DB2,Access等关系型数据库以及Excel,Xm1等数据文件。
转换(Transform)是将抽取所得的数据经过一系列的变换加工转变为符合目标数据模式的过程,包括数据质量的检查,数据的清洗,数据的集成与合并等,同时,在此过程中使用的转换规则分别在元数据中得以描述和保存。ETL数据转换过程如图1所示。
加载(Loading)是指将转换好的符合目标模式的数据载入数据仓库的过程[4]。
1.2 相关技术
数据库访问技术JDBC:JDBC (Java Data Base Connectivity)由一组用Java语言编写的类和接口组成,是一种用于执行SQL语句的Java API,可以为各种关系数据库提供统一的访问接口。
并发线程包:来自JSR-166 (Java Specification Requests)的Util.Concurrent并发线程包提供了一系列高性能并发程序开发的实用模型,已经成为J2SE5.0的一部分。
SWT图形组件技术:SWT (Standard Widget Toolkit)是一个独立于平台的,可以脱离Eclipse框架单独使用的图形组件。SWT通过Java本地接口(JNI)直接使用操作系统自身的各种图形控件,开发出来的应用程序具有本机应用程序的观感。
2 系统的总体架构与设计
2.1 系统的整体结构
电网实时数据通常是以关系型数据库表或者数据文件的形式存在,它们共同构成了企业的领域知识和行为规则库。本文所指的ETL系统是将这些数据进行处理并加载入数据仓库的过程。系统的构架图如图2所示。
系统按照功能划分为三个模块,分别是元数据管理模块、数据处理模块、任务调度及监控模块。
元数据管理模块:ETL任务的生成及数据的处理过程都是基于元数据驱动的,元数据管理是ETL系统的重要组成部分,对元数据进行合理有效地管理,才能很好地实现数据的访问、清洗、转换与加载。元数据管理模块的功能是负责各种类型元数据的定义、存取及管理维护。
数据处理模块:数据处理模块是ETL系统的核心模块,系统通过JDBC连接各种数据源,从其中抽取数据,对数据进行清洗转换(数据的筛选,数据质量的校验,数据属性及域的转换,数据的合并等),最终将数据按照预先定义的目标模式装载入数据仓库之中。
任务调度及监控模块:鉴于数据的ETL是一个反复进行的过程,而且各类电网实时数据的数据密度也不尽相同,往往需要定义不同周期不同频度的作业任务,任务的调度保证了任务的自动、高效、准确的运行。此外针对ETL任务执行的过程中可能出现各种各样的异常,比如网络的中断,任务的超时等情况进行及时的处理,并支持断点续传的功能。系统可以对任务的全生命周期进行监控,并将其执行过程记录日志,运行日志的主要功能是记录任务执行过程中发生的操作以及错误、警告等信息,以便于在事后进行分析和跟踪处理。
2.2 元数据定义
元数据(Metadata)即关于数据的数据。系统中元数据定义主要包括数据库的驱动信息、用户名、密码、连接URL、表、列的属性(类型,格式、约束条件等)、源数据到目标数据字段间的映射关系、数据的转换规则、任务的参数定义等等。以下给出了一个字段间映射关系xm1片段:
上述xm1片段所示的是将YMD字段映射到目标字段Time上,其中YMD字段属性是Date型,Time字段属性域是varchar (20),两者都是非空的。数据处理程序是按映射规则将源数据转换为目标数据模式。
系统可以自动匹配字段间的映射关系,也能够人工地进行配置,可以为任务设定周期及参数。方案配置以xm1文件的形式保存。图3是方案的配置界面。
2.3 并发任务的实现
ETL系统是面向多任务的,多线程编程是其中的重点及难点,线程的互斥、同步事件的响应、跨线程的数据通信以及异步任务调度都是需要解决的问题。我们利用Util.concurrent并发线程包提供的锁、互斥、队列、线程池、轻量级任务等一系列的并发构件很好地解决了上述问题。
程序中用到了Util.concurrent包提供的Executor、Callable和Future等几个基本的接口。通过Executor接口提供的最基本的任务执行方法,实现了任务的submit ()和shutdown ()等操作;通过Callable接口获得任务抛出的异常和返回结果;使用Future接口获取任务的句柄,从而得到任务执行的中间结果,终止任务的执行等,可以很方便地对任务执行过程进行全生命周期的监控;此外还使用了Util.concurrent包提供的一些辅助工具类。
public static ScheduledThreadPoolExecutor scheduler;
scheduler.schedule(event,delay,TimeUnit.MILLISECONDS);
以上代码片段是将一个任务event提交给了线程池ScheduledThreadPoolExecutor,由线程池来负责任务的调度,event将在指定延时delay后得到执行。
2.4 跨平台运行
ETL系统的开发和设计与计算机操作系统平台密不可分,电力系统出于安全和稳定性的考虑运行着各种平台,能够在多种平台下运行,是电网实时数据ETL系统的必然要求。系统的开发采用Java语言实现,数据库操作采用纯JDBC,通过加载不同的数据库驱动,实现了对不同数据库的访问,弥补了ODBC依赖于特定平台的缺点。系统利用SWT提供的API实现了图形用户界面,具有比AWT/Swing应用程序更快的响应速度。
系统具有良好的可移植性,图4是部署在Kylin Linux上的盐密监测系统数据迁移方案实例,从图中可以看出系统具有Linux桌面应用程序的观感。
3 数据迁移与同步模式
现行各类电网实时系统中的数据量动辄就成千上万条,其中既有动态的,也有静态的数据,而数据迁移与同步操作是一个定期的、频繁的工作,数据的更新频度可能是每月、每周、每天甚至是5 s这样一个很小的时间间隔,为了最大限度地利用系统资源,提高系统的性能,并满足实际应用的需要,系统实现了多种模式的数据迁移方案;同时为了方案定义的灵活性,本文提出了一种自定义的表达式,后台程序可以通过解析表达式中的关键字参数,生成代码参数或动态SQL语句[5]。具体实现方式如下:
①全量模式:全量更新模式将源数据表中的数据整体向目的表迁移,主要用于静态数据表的更新。根据实际应用需要实现了两种方式:一种是将目的表所有数据整体删除,然后再更新;另一种可以通过关键字段“逐条比对”的方式,实现数据整体同步更新,倘若关键字段值相同则更新记录,否则插入记录。后者效率较低,但能够保护已有的业务记录。全量模式表达式如下:
Total=:{key=#,Condition=#;#}
其中:“#”表示默认不设置情况;key对应目的表的主键ID的生成规则,比如针对Oracle数据库可设定一个序列名seq_salt;Condition为数据筛选条件,通过“;”分隔可设置多个条件。
②增量模式:即保证上一次已经传输过的数据只要在此期间没有任何变动,下一次传输时将被忽略,当前的技术难以实现通用的增量数据抽取[6]。针对电网实时数据的特点,本文提出了增量标识字段的方式,实现了数据的增量迁移。标识字段可以是时间戳字段,ID自增字段以及任何具有增量性质的字段。倘若数据源表不含具有增量性质的字段,则采用关键字段(主键)“逐条比对”的方式实现数据迁移,如果关键字段值在目的表中已存在则忽略此记录,否则插入新记录。增量模式表达式如下:
Incremental=:{key=#,Condition=#;#,Inc=#}
式中:“Inc”用来标识增量字段,比如“Inc=YMD”,表示将“YMD”时间字段作为增量标识字段;任务执行完毕后,系统将记录“YMD”字段的当前值以供下次任务执行时生成sql语句。比如“select*from saltreal where YMD>2009-02-26 08:30:20”,其中“2009-02-26 08:30:20”就是系统记录的“YMD”字段的值。
③主细表模式:在数据库应用系统的开发过程中,往往用外码来保证参照实体间的数据一致性,这种有参照关系的表称为主细表。主细表模式可以保证数据的完整性,一致性,可用性,避免“脏”数据的产生,具有很高的实用意义。主细表模式表达式如下:
Main-sub=:{Key=#,Inc=#,Condition=#;#
MT=#,SF=#:#;#:#,RF=#:#}
MT对应的是主表名(静态信息表);SF对应源表字段与主表字段的映射关系;RF对应主表字段与细表字段的映射关系;数据迁移执行过程中将根据SF的设置先查询主表,然后根据RF的设置将查询结果集映射到细表中。
为了提高系统的应用性能,实现了单表与多表两种数据迁移方案。单表方案是将对参数或更新频度有特殊要求的任务进行单独配置,任务对应一个独立的线程;多表方案是将更新频度相近且实时性要求不高的若干任务组成一个组方案,任务共享同一个线程顺序执行。具体实例在此不再详述。
4 应用实例
盐密实时监测系统中的实时监测表共有历史数据63 000余条,在方案配置时选用增量数据迁移模式,将time字段作为增量标识字段,任务执行周期设定为1 h。该方案在小型机(IBM P5 570 H,8 G,4 CPU,Kylin Linux)上运行,首次执行需要迁移全部数据,共耗时58 s,平均每秒处理1 000条以上记录;此后按增量方式运行,每次同步更新6条记录,系统已连续正常运行6个多月,实际应用表现出了很高的数据处理效率和稳定性。
5 结语
本文介绍了电网实时数据ETL系统实现的相关技术,阐述了系统的设计及实现方法。根据电网实时数据的特点,系统实现了多种模式的数据迁移方案,具备实用性与灵活性。ETL是数据中心构建的重要环节,目前系统已经取得了实际的应用,并很好地满足了电网实时数据中心构建的需要,该系统对电力及其他行业数据中心ETL的实施有一定的借鉴意义。
摘要：以某省电网实时数据监测系统的数据中心建设项目为背景,提出了一种轻量级的,适合于电网实时数据迁移与同步更新的ETL系统解决方案。系统支持多种关系型数据库和数据文件,提供全量、增量、主细表等多种自定义模式数据迁移方案。系统利用JDBC数据库访问技术、JSR-166的Util.Concurrent并发线程包以及SWT技术,结合元数据的管理,解决了异构数据的快速抽取、清洗转换与加载、任务的调度和跨平台运行的问题,并在实际应用中表现出了实用性和稳定性。
关键词：ETL系统,异构数据,增量,任务调度,跨平台
参考文献
[1]白莉珍.ETL在青海省电力公司数据中心系统中的应用[J].青海电力,2008,27(2):66-68. BAI Li-zhen.Application of ETL in information center of Qinghai electric power company[J].Qinghai Electric Power,2008,27(2):66-68.
[2]Panos Vassiliadis,Alkis Simitsis,Spiros Skiadopoulos. Conceptual modeling for ETL processes[C].//ACM SIGIR.Proc.of DOLAP'02.Virginia(USA),New York(USA):2002:14-21.
[3]Leon Gong,Mike Olivas,Christine Posluszny,et al. Deliver an effective and flexible data warehouse solution [EB/OL].[2005-08-04].http://www.ibm.com/developerwo rks/data/library/techarticle/dm-0508gong/index.html.
[4]屈志毅,张延堂,王戈.一种金融系统专用ETL工具的研究与实现[J].计算机工程,2008,34(20):86-87, 91. QU Zhi-yi,ZHANG Yan-tang,WANG Ge.Study and implementation of special ETL tool for finance system[J]. Computer Engineering,2008,34(20):86-87,91.
[5]刘海英,冯文秀,杜晓通.管理信息系统升级过程中数据迁移的研究及实现[J],电力自动化设备,2005,25 (5):37-39. LIU Hai-ying,FENG Wen-xiu,DU Xiao-tong.Study and implementation of data transfer in management information system upgrade[J].Electric Power Automation Equipment,2005,25(5):37-39.
ETL系统设计 篇3
随着我国金融体系的日益完善和经营风险的日益增加,分散的数据和作业系统难以在以下方面发挥应用的作用:(1)个性化的成本控制和效益管理。(2)指标管理体系虽建立,但及时准确的得到指标中数据相对比较困难。(3)对客户的交易行为分析手段有限,难以实现“一对一”的服务。(4)在现有的银行管理系统当中,查询效率不能得到提高。(5)作业统计口径多,数据的一致性不能保证。
因此,以在线分析(OLAP)和决策支持(DSS)为目的银行数据中心(BDC)BI系统,正开始成为近年银行电子化建设的新热点。数据中心主要功能是实现全行数据的有机整合,进而支持决策分析。ETL是开发数据中心系统中最重要的环节之一。通常情况下,ETL会占据整个数据中心项目三分之一或更多的时间,ETL设计的好坏将直接关系到整个数据中心系统的成效。
2 ETL技术
ETL分别是抽取(Extraction)、转换(Transformation)和装载(Loading),是将分散、零乱,标准不统一,码制不一致的数据整合到目标数据库中的技术[1]。
2.1 数据抽取
外部数据源的数据并不是全部都有用,进入数据中心数据库的只是决策分析所需要的数据。在构建数据中心时,我们只需提取出决策分析所须的那些数据。数据是否有价值,取决于与分析主题的相关度。
2.2 数据转换与清洗
外部数据源往往是异构的,数据类型并不一致。抽取到目标数据库之前需要对其进行统一的转换。这就是数据转换的过程。且外部数据源中的数据并不是完美的,存在着“脏数据”——数据有空缺、不正确、不一致等。这些数据也必须经过规则清洗后才能进入数据中心数据库,否则会对决策分析造成严重影响。
2.3 数据装载
在完成数据的清洗和转换之后,L步骤将数据移至数据中心数据库中,使其成为分析型数据。
2.4 调度管理及日志文件
在使用ETL工具的过程中,调度管理涵盖了整个过程,它是整个ETL过程的主轴,协调着每一个过程。调度管理以日志形式记录和标示了各个过程的信息,用以查看每个任务的状态及日志。
3 数据中心BI项目中的ETL设计
数据中心中的数据按照主题来划分,存放于不同主题的集市表中。集市表的数据来源于各源表数据的直接映射或逻辑运算而产生。我们参与的数据中心BI项目采用了如图1的流程图[2]。首先明确做决策分析需要展现的表样,统计出生成该表样需用到的源表并抽取到数据中心中,然后通过sql关联处理,再经ETL过程生成所需要的集市表。通过Cognos工具实现报表展现和在线分析。基于数据中心,还可以通过KDD(知识发现)等各种算法,对操作型数据实现更深层次的数据挖掘,得到更多更有价值的、人们事先不知道而又潜在的商业信息。这是BI思想最有价值的体现,也是BI商业用途最具发展潜力的方向。
4 ETL在数据中心项目中的应用
下文以需展示的表样—“自助设备交易情况表”(表1)的开发、生成的过程,说明ETL的具体工作。
我们使用的ETL工具是Data Stage。明确了表样的构成后,设计目标集市表的结构,通过集市表最终展现出该表样。集市表的设计,必须考虑灵活、全面且做到尽可能少有冗余字段。对于上述表样,结合银行的相关业务,设计生成的集市表字段说明如下:
字段结构:时间戳,交易时间,机构号,机构名称,上级机构号,上级机构名称,设备代码,原交易码,ATM本代本取款交易金额,ATM本代本取款交易笔数,ATM本代他取款交易金额,ATM本代他取款交易笔数,ATM本代本转账交易金额,ATM本代本转账交易笔数,CDM存款交易金额,CDM存款交易笔数,缴费交易金额,缴费交易笔数。
由于该表字段较多,考虑创建中间表先进行交易笔数和金额的汇总,再过渡到目标表。根据上述字段分析出需要关联到以下的三张源表,说明如下:
源表1:卡交易流水表。所需字段:TXDATE(交易日期)、TXTYP(业务类型)、DEVID(设备代码)、ANGTYP(交易性质)、DEVTYP(设备类型)、PTXCDE(原交易码)
源表2:机构信息表。所需字段:SBNAME(机构名称)、AUNODE(汇总机构)、ORG(机构)
源表3:机构汇总信息表。所需字段:DEVID(设备代码)、BRC(机构号)
以上三张表都是银行原业务系统里的源表,可直接抽取到数据中心数据库。抽取完之后,在DS客户端创建如下的Server Job(图2),包括以下工作:
4.1 生成source表
图中source用于访问上述三个源表的关联字段;target为生成的中间表。双击source编写SQL[3]如下:SELECT a.TXDATE,b.ORG,SUM(a.TXAMT),a.DEVID,a.TXTYP,b.SBNAME,b.AUNODE,count(*)cnt,a.agntyp,a.devtyp,a.ptxcde FROM卡交易流水表a,机构信息表b,机构汇总信息表c WHERE a.TXDATE=CURRENT_DATE-1 day AND a.devtyp in('A','B','D')AND A.DEVID=c.devid AND a.agntyp in('1','2')AND c.BRC=b.ORG GROUP BY a.TXDATE,b.ORG,a.DEVID,b.AUNODE,a.devtyp,b.SBNAME,a.pexcde,a.agntyp
注释:(1)a.agntyp取1或2表示本行的自助设备代理本行的卡业务或代理他行的卡业务;(2)a.DEVID=c.devid表示c表字段devid是a表中字段devid的外键;(3)c.BRC=b.ORG表示b表字段ORG是c表中字段BRC的外键;(4)a.devtyp等于A、B或C,分别表示ATM、BST和CDM设备。上述语句完成后,创建对应的映射。
4.2 生成target中间表
双击target,输入中间表表名,设置临时缓冲区(缓冲的作用是优化job的抽取,提高抽取速度),设置完后进行编译。编译通过后抽取数据,当抽取完成后,日志信息显示抽取状态为“finished”,表明此时中间表中已完成了关于“设备类型+业务类型+交易性质”的交易金额与交易笔数的汇总。
4.3 创建数据集市表
完成上述汇总后,创建中间表到集市表的job,关联机构信息表得到的SBNAME(机构名称)数据。双击中间表的stage,编写如下sql关联语句:
SELECT a.TXDATE,a.TXORG,a.ORGNAME,a.SUPCOLORG,a.DEVID,a.TXAMT,a.CNT,a.PTXCDE,b.SBNAME ASSUPORGNAME,a.txtyp,a.agntyp,a.devtyp
FROM中间表a,机构信息表b
where a.SUPCOLORG=b.ORG
and a.txdate=current_date-1 day
编写完后导入中间表和机构信息表中所需字段。导入成功后,编写逻辑运算条件并创建映射[4]。
4.4 逻辑运算和映射说明
(1)TXAMT(交易金额)字段分别映射于集市表中的5个字段:
·ATM本代本取款交易金额。映射条件:If LK_01.DEVTYP='A'and LK_01.TXTYP='32'and LK_01.AGNTYP='1'Then LK_01.TXAMT Else 0.00。DEVTYP='A'代表设备类型是ATM;TXTYP='32'代表业务类型是取款;AGNTYP='1'代表交易性质是本行设备代理本行卡业务。
·ATM本代他取款交易金额。映射条件:If LK_01.DEVTYP='A'and LK_01.TXTYP='32'and LK_01.AGNTYP='2'Then LK_01.TXAMT Else 0.00。DEVTYP='A'代表设备类型是ATM;TXTYP='32'代表业务类型是取款;AGNTYP='2'代表交易性质是本行设备代理它行卡业务。
·ATM本代本转账交易金额。映射条件:If LK_01.DEVTYP='A'and(LK_01.TXTYP='33'ORLK_01.TXTYP='34')and LK_01.AGNTYP='1'Then LK_01.TXAMT Else 0.00。DEVTYP='A'代表设备类型是ATM;TXTYP='33'OR TXTYP='34'代表业务类型是转账;AGNTYP='1'代表交易性质是本行设备代理本行卡业务。
·CDM存款交易金额。映射条件:If LK_01.DEVTYP='D'and LK_01.TXTYP='31'Then LK_01.TXAMT Else 0.00。DEVTYP='D'代表设备类型是CDM;TXTYP='31'代表业务类型是存款。
·缴费交易金额。映射条件:If LK_01.TXTYP='34'Then LK_01.TXAMT Else 0.00。其中TXTYP='34'代表业务类型是缴费。
(2)CNT(交易笔数)字段分别映射于目标表中的5个字段:
·ATM本代本取款交易笔数。映射条件同ATMBDBTXAMT,交易金额换成笔数。
·ATM本代他取款交易笔数。映射条件同ATMBDTTXAMT,交易金额换成笔数。
·ATM本代本转账交易笔数。映射条件同CARDBDBTXAMT,交易金额换成笔数。
·CDM存款交易笔数。映射条件同CDMCKTXAMT,交易金额换成笔数。
·缴费交易笔数。转换逻辑条件同JFTXAMT,交易金额换成笔数。
(3)TXTYP(业务类型)、AGNTYP(交易性质)、DEVTYP(设备类型)三个字段分别映射到对应的目标字段,以满足逻辑运算条件的数据需要。
(4)TXDATE、TXORG、ORGNAME、SUPCOLORG、SUORGNAME、PTXCDE、DEVID字段为直接映射到集市表中。
完成上述映射之后,编译并执行该Job。结果如图3所示。
此时目标集市表已经生成在数据中心数据库里了。利用Cognos工具展现出该表,通过查询该表,决策人员可以清楚了解到各银行机构不同交易时间段的自助设备交易情况,为决策分析提供了强有力的支持与保证。这正是数据中心的核心功能所在。通过ETL工具的自动批量抽取程序,可以定时更新源数据以及上述中间表,目标集市表的数据,从而保证了展现出来的“自助设备交易情况表”数据更及时更准确。至此,使用ETL工具用于抽取、关联源表,创建中间表,以及生成目标表完成。我们参与的某商业银行数据中心一期项目,从设计模型到表样展现,历时七个月后正式上线。上线后,数据中心系统对于该行的各种经营指标现状和趋势分析,风险估算,产品市场化营销和提供个性化客户服务,以及绩效考核等方面都起到了很好的支持作用。ETL工具的使用,使得整个数据中心BI系统的报表生成过程变得思路清晰,直观高效
5 结语
本文通过参与开发的数据中心项目,阐明ETL在数据中心项目中的设计思路以及在实际应用中所发挥的重要作用。如今银行业务数据源越来越多,格式越来越复杂,如何保证数据的一致性,更好理解数据的业务含义,真正做到跨越多平台、多系统地整合数据,最大可能地提高数据的质量,迎合业务需求不断变化的特性,这就是未来ETL工具的技术发展关键所在[5]。
参考文献
[1]W.H.Inmon(美).Building the data warehouse=数据仓库.王志海,译.北京:人民邮电出版社,2004:68～70
[2]潘华,项同德.数据仓库与数据挖掘原理、工具及应用.北京:中国电力出版社,2007:25～27
[3]李志伟.DB2基础教程.北京:清华大学出版社,2005:15～18
[4]Michael L.Gonzales(美).IBM-DW及IBM-BI工具.吴刚,董志国,等,译.北京:北京大学出版社,2007:90～91
ETL系统设计 篇4
税务系统由于有复杂的财务关系、财务流程等，不可避免在事物扭转等过程中会产生错误数据。贵州省地税局九个市（州、地）的数据已经集中到市（州、地），但是由于地税局的相关业务系统经过多次改版和升级等原因，造成了很多数据不一致等数据质量问题，并且各市（州、地）的数据质量参差不齐，对省级数据集中和在省级数据集中基础上的其它应用都会带来很大的麻烦和困难。因此对九个市（州、地）的原始数据进行数据质量监控与分析，保证原始数据的数据质量就显得非常重要，也是省局通知中规定的八个目标之一。因此，数据质量监控与分析系统作为贵州省地税局省级数据集中项目中的一个子系统，肩负着其它目标实现质量好坏的基础性任务。
1 数据清洗错误数据产生原因
在税务系统中，“脏数据”产生的原因主要如下：
(1)MIS系统数据的迁移（从03版到06版，再从06版到09版MIS数据的两次迁移）。
(2）人工不合法的操作，主要涉及应用层和数据库层两个方面。其中应用层人工的不合法操作主要原因是因为软件本身存在的漏洞，数据库层主要原因是操作人员直接修改数据库中的数据。
(3）数据库设计的不合理。比如，应该有主外健约束的在现有表中没有，从而导致数据不一致的结果。图一是部分原始数据表的表间关系图，从中可以看出这几张表应该有主外健约束，却没有建立主外健关系。
(4）其他因素，比如计算机出现故障等。
2 数据清洗方法分析
目前国内外研究最早出现数据清洗的是美国。美国信息业和商业的发展，极大的刺激了对数据清洗技术的研究。国内对数据清洗技术的研究还处于初级阶段。直接针对数据清洗，特别是针对中文数据清洗的研究成果并不多。银行、保险、证券等对客户数据的准确性要求很高的行业，都在做各自的客户数据清洗工作，针对各自具体应用而开发的软件，而很少有理论性的成果见诸于报道。
在数据仓库系统中，数据清洗是ETL过程中的一个重要环节,主要任务是检测并删除/改正将装入数据仓库的错误数据。在数据抽取到中间数据库后，还需要一个再次清洗转换的环节以对转换后的数据再次清洗，然后装载到目标数据仓库中。
在本系统中，也是紧紧围绕ETL的思想，在数据抽取到省局前，对九个地市州的数据需要进行一次抽取转换；将抽取到九个地市州后的中间数据库中的数据也有再次清洗转换的过程，其流程如图二所示。
考虑到本系统开发的软件主要是对税收这一特定领域而作的数据质量分析与清理工作，本软件就必须要具有灵活、特定等特性。因此，我们采取的方案是：以自己编写软件为主，应用其他清洗工具（比如oracle warehouse builder）为辅的策略。
2.1 自己编写软件的方案分析
(1）前期通过数据库原始数据生成类图，采用的方案是具有完整版和精简版两种不同的类图版本。完整版便于从整体查看数据之间的关联关系；精简版可以更详细地查看单张数据表及其相关表信息。生成类图的目的是为了方便制订查错和改错的清洗规则。
(2）根据前期的清洗规则数据准备，我们需要对九个地市州原始数据库中另外建一个数据清洗的用户，用于存放数据清洗相关表及存储过程等信息。
(3）在调度查错及修改的存储过程前，我们还需要一些准备工作。准备工作分两个方面来考虑：(1)只运行一次（即只在系统上线前统一运行一次的工作）；(2)每次都需运行的工作（系统在每次调度前都需要执行的检查工作）。
(1)只运行一次的工作有以下几点：
a)历史数据备份与删除的步骤有如下几步：
i.先备份完整的历史数据。做修改之前，对super、kt2011两个用户做dmp备份，提供备份语句。
ii.备份历史数据(对要截取的数据表和代码表进行备份)。
iii.删除数据（删除数据表中已备份到备份表中原数据表中的数据）。
b)表结构的统一、定义主键、约束重命名。
c)用规范代码统一代码表，以后每次才是代码表内容一致性检查。
(2)每次都运行的工作有以下几点：
a)系统每次运行前需人工设置检查期数表的地区代码、检查期数、检查时间范围，是否允许修改数据等参数。检查期数表的功能实现需注意如下几点：
i.不同检查期数的时间范围不交叉，相邻两个检查期数时间范围不能漏选。
ii.同一期可以做无限次，但每次都是做原来没有做的部分或原来没有做成功的部分，每次都要全部检查。
b)表结构一致性检查，如果不一致，整个检查工作终止（系统每次运行前需用存储过程检查全省9地市州的数据库中的1300张表的主键、字段名、字段类型、外键、字段长度等是否与标准库一致）。
c)代码表内容一致性检查，如果不一致，整个检查工作终止。
d)新旧代码表对应关系中，09版代码值是否与MIS系统中一致，如果不一致，整个检查工作终止（用存储过程调用）。
e)每次都运行的工作可通过采用存储过程封装调度。
参照（1）中完成的类图，我们采用了规则表在数据清理用户下，将数据查错规则、数据备份规则及数据修改规则保存在一张表中，使用存储过程及函数按一定次序动态调用数据查错规则、数据备份规则及数据修改规则语句；在执行每个规则的过程中，执行结果（成功与否）将保存在同一张日志表中。
2.2 数据清洗中应用的清洗工具分析
数据清洗工具辅助完成了本系统的错误数据分析、错误数据统计等工作。在对数据清洗工具选择中，我们前期做了比较，结果见表一。
通过比较，我们发现Oracle的WareHouse Builder因为其支持异构数据库并且免费使用等特性，我们便考虑用它做我们的分析依据。它其中有一项非常强大的功能即为概要分析，能够全面的帮助我们分析出各种数据错误的类型、错误的种类等。
3 结束语
数据清洗在税务系统中的应用在国内外有很多参考，对原始数据的清洗工作需要长期、反复、渐进的进行，因此要求该系统要有一定的开放性和可维护性，以保证分析工作不断深入和顺利进行。对手工改动的数据要做到按单位、按地域、按年份进行评估考核。
参考文献
[1]国家税务总局.国家税务总局办公厅关于印发《微观税收分析基本方法》的通知[R].国家税务总局办公厅,2006年:国税[2006]26号.
[2]国家税务总局.国家税务总局关于印发《税收分析工作制度》的通知[R].国家税务总局办公厅,2007年:国税[2007]46号.
[3]税源监控管理及其数据应用分析编委会.税源监控管理及其数据应用分析(第1版)[M].北京:中国税务出版社,2005.
[4]郭志懋,周傲英.数据质量和数据清洗研究综述[J].软件学报,2002,13(11):2076-2084.
[5]陈传波,唐九飞.信息系统中的数据质量[J].湖北工学院学报,1998,13(3):36-41.
ETL系统设计 篇5
中国石油是我国重要的油气生产供应商, 生产经营过程中要消耗大量能源, 作为油气资源产能耗能大户, 一直以来高度关注自身节能节水管理整体有效把控。但管理层级深、各环节业务差异大、能耗算法纷繁复杂是大型石化企业管理中现实存在的具体情况。能源管理业务系统存在能耗数据没有有效标准、业务流程各行其道;上报地方政府、集团、国家口径不一;已建成信息系统存在缺乏数据共享集成机制、数据重复录入、人为误差偏大诸多弊端。打破信息孤岛, 有效挖掘不断集聚业务数据的价值, 支持企业决策是企业数据仓库系统需要解决的重要课题。为解决此问题, 节能节水管理系统中数据仓库子系统组建ETL, 对集聚的业务数据进行数据整合以应对大数据量条件下企业决策的需要。
数据仓库依赖于各个业务系统数据, 把源数据从分布式环境中加载到数据仓库中, 并把这些存储在异构数据库中的业务数据进行抽取转换, 提取相关数据通过同步或异步方式加载到分析层作为数据源, 这部分是ETL要完成工作, 一般也是整个数据仓库系统设计中最为重要部分之一。
1 ETL总体数据架构
1.1 数据整合架构原理
操作型数据库 (OLTP) 基于已知任务和负载需求, 为支撑业务逻辑开展而进行的部署, 例如使用主码索引和散列、检索特定记录等。而数据仓库执行的OLAP查询, 通常复杂且涉及大量数据汇总运算, 时常需要特殊的基于多维视图的数据组织、存取方法和实现方法。因此, 在OLTP数据库上执行OLAP查询会降低两种运算的性能[1]。
此外, 操作型数据库支持多事务并发处理, 需要并发控制和恢复机制 (如锁机制、日志生成等) , 以确保数据一致性和事务鲁棒性。而OLAP查询只需对汇总和聚集数据进行只读访问。若将并发控制及恢复机制作用于OLAP操作, 不仅不利于并行事务运行, 还将大大降低OLTP系统吞吐量。
最后, 数据仓库与操作数据库分离是由于这两种系统中数据结构、内容和用法都大不相同。决策支持需要历史数据及多个业务系统数据进行综合分析决策, 而操作数据库一般不维护历史数据, 业务数据分布于多个业务系统的相互独立的数据库中。在此种情况下, 每个操作数据库中的数据尽管很丰富, 但对于需要汇总级数据的决策者来说, 还是远非完整的。
1.2 数据整合架构设计
基于多数据源的数据仓库系统, 在源系统 (业务数据库) 和数据仓库之间增加一个中间层ODS (Operational Data Store, 操作型数据存储区) , 进行数据缓存。业务数据加载到ODS层后, ETL以ODS层数据作为数据源来进行的, 不影响前端业务系统的性能。数据在ODS汇总, 被重新统一设置主外键[12]。
另一方面, 从当前各类数据库管理系统供应商目前提供的功能来看, OLTP数据库与OLAP数据库分离是必要的。基于上述原则, 数据仓库系统设计实现如图1所示的ETL架构。
ETL过程 (IBM-DATASTAGE) 抽取初步业务数据 (其中包括历史遗留数据一次性注入和业务系统实时生成的数据) 到ETL整合中间层STG库, 其中中间层STG库元数据保留数据源系统所有表结构及数据。中间层STG库既是元数据的目标库, 也是集成层INT库的数据源。由于STG库中元数据尚未清洗、校验和整合, 原则上, 查询层业务报表只访问集成层INT库数据, 不直接访问中间层STG库中数据。
集成库INT把中间层STG的数据ETL后, 构建不同业务主题事实表及一套通用维度。构建事实表采用类似业务主题 (相似指标和维度组合) , 平衡空间、性能和业务三者的要求, 以3范式 (normalization) 和非标准化 (denormalization) 规则, 生成基于星型结构[1]的稀疏矩阵。集成库数据存储周期是所有的历史数据。集成库数据是数据整合系统的核心, 负责提供数据给分析展现层。
2 增量与全量设计
全量数据抽取源系统E7库所有数据到中间层全量数据库STG, 增量数据抽取源数据库E7库增量更新到中间层STG_DLTA库, 从全量STG库和增量STG_DLTA库抽取数据全量、增量更新集成层INT库, 以支撑分析层报表展现, 图2示出了ETL各数据库中数据流向。
在增量抽取中, 增量抽取机制既要按照一定频率捕获源业务系统中数据变化, 又不能对业务系统造成太大负载压力, 影响既有业务。较之于全量抽取, 增量抽取设计实现更加复杂。
本方案采用时间戳方式进行增量数据抽取, 抽取进程通过比对系统时间与源表时间戳字段值来决定抽取数据范围。业务系统中更新修改业务数据后, 数据仓库更新时间戳字段的值。
在源表上增加一个时间戳字段, 系统中更新修改数据时, 同时修改时间戳字段值。每次更新抽取时间戳位于以上次最近更新为基点时间, 到当前更新时点时间区间的所有数据。比较抽取源表的时间戳字段值与当前系统时间值来决定所要抽取数据记录。这一点实现需要在ETL抽取业务数据到数据仓库中时在目标表上添加更新时间字段作为时间戳。如图3所示, ETL进行增量抽取时, 只需抽取时间指针位于T1-T2之间的业务数据。
3维度整合
维度数据分简单维度和复杂维度两种方式。简单维度整合直接读取.csv维度文件, ETL读取.csv文件数据到中间层及集成层INT库, 图4示出了一种简单维度生成场景。
复杂维度通过关联各个相关维度表.csv文件, Copy组件数据分流及JOIN组件拼接生成目标维度, Transform组件对复杂维度按照关键字排序后, ETL到中间层及集成层INT库中, 图5示出了一种复杂维度生成场景。
4 源数据删数
业务端不但具有实时新生成的业务数据, 而且已经保存的数据也时常发生变化。业务系统端各类业务数据发生变化, ETL负责检测感知, 并实时更新数据仓库中已经更新的业务数据。而不一致数据等冗余将被删除以保持与业务系统源库数据的一致性。删数操作部署于ETL全量抽取, 及增量数据抽取之前进行。
5 元数据整合
基于前述数据整合架构, 对元数据进行整合。Oracle Stage组件读取源库数据, Join Stage组件进行数据关联, Copy Stage组件进行数据分流, Reject组件输出异常数据。
抽取加载到STG层的数据, 加工到中间表。给事实表提供数据源。不同类型业务数据划分成不同类别作业分别进行元数据整合。ETL作业开发工作量一般占整个系统研发工作量的60%以上, 因而ETL作业的开发周期常常是整个系统开发周期的瓶颈[2]。图6示出了, 中间层元数据加工逻辑中的一个作业。
图6 ETL作业从业务端数据源抽取一次加工数据进入中间数据层, 关联业务数据维度, 数据整合后, 最终分流成四类业务数据后, 写入集成层事实表。
6 ETL调度设计
把维度作业、事实表整合作业设计完成后, 需要按照一定的序列把各类作业分门别类串接起来形成不同类别的调度程序, 按照时间节点通过调度流程调度程序, 启动对不同维度数据及事实表数据的调度。
ETL调度满足以下条件: (1) 所有ETL操作一旦执行即不可终止; (2) 所有ETL操作可在任意处理机上执行[10]。在满足处理机资源约束及ETL作业序列基础上, 均匀分布ETL计算量于所有处理机上, 尽可能并行以缩短ETL总时间。采用基于同层划分屏蔽复杂度遗传算法建立ETL作业调度基础模型[9]。
全量数据初始化调度应用, 初始调度程序开始运行后, Gen Current Time组件生成当期系统时间以支持增量数据加载[8]。维度整合Stage调度底层所有维度整合作业, 运行所有简单纬度ETL应用, 加载维度数据到纬度表。四类事实表业务数据整合Stage, 分成四个批次, 全量抽取业务系统源数据到STG层, 提供给接下来的事实表作为数据源。Gen Prior Time Full组件在整个调度运行结束后, 标记本次运行的开始时间作为下次数据增量数据的开始范围。检测数据异常后进行异常处理, 打印日志。各个作业末端以Terminator Stage结尾, 支持程序中断报错, 提供错误日志数据输出。
增量数据调度, 共用全量调度所有作业。增量抽取数据为时间戳指针落在最近上次ETL抽数完毕时间, 到当前系统时间之间的业务数据。
7 实验及结果分析
ETL运行硬件环境为运行主机配置为内存128G/CPU2.8GHz (40核) , 业务数据库、源数据库、目标数据库均采用Oracle Database 11g企业级版, 样本数据表空间为亿条级数据记录大型表空间。选取如表1所示容量的业务数据。
选取ETL流程中复杂作业核心组件, 得到如表2所示的数据处理速率对比。
选取业务系统数据库运行峰底时段ETL总控调度程序进行一次全量样本数据抽取, 随后为保持中间层与业务系统数据一致性, 进行数据仓库中间层数据删除调度。以后数据仓库在业务系统数据库低峰时段运行增量数据抽取作业。表3列出了样例调度程序运转周期对比。
8 结束语
结合中国石油节能节水业务数据实际, 基于经典ETL架构设计及作业调度算法, 集成业务端多种数据库类型的业务数据, 并进行增量和全量数据抽取设计及流程控制设计, 给出了ETL数据整合系统数据抽取性能指标。宏观层面, ETL集成系统具有较强的数据抽取、转换及写入能力, ETL全量和增量数据抽取速率较高, 为上层报表展现及数据分析提供可靠数据源, 同时ETL架构设计为进一步应对业务数据大量集聚, 并进行有效数据挖掘提供了切实经验和铺垫。
摘要：近年来中国石油不断加大节能减排力度, 节能节水管理业务系统承载的数据上报任务变得越来越繁重, 然而业务管理需求复杂多变是普遍存在并始终贯穿于业务系统生命周期各阶段的问题。本文结合中国石油节能节水业务数据特点, 在对经典ETL数据集成方法深入分析、继承的基础之上, 给出一种基于多类型数据源ETL设计, 对地域分布广、数据结构松散、逻辑关联复杂的节能节水业务数据进行有效整合, 大容量样本数据测试表明, 该ETL设计具有较高的数据整合性能。
ETL系统设计 篇6
数据仓库的数据来源常包含着噪声数据、不完整数据、甚至是不一致的数据[1]。为了得到高质量的数据, 必须对抽取 (Extract) 出来的原始数据做一系列复杂转换 (Transform) 处理, 最后装载 (Load) 到数据仓库中。这种从原始数据到数据仓库之间, 对数据进行的操作称为ETL过程, 其工作量大约占系统的60%[2], 实现ETL过程的效率和质量很大程度上决定了数据仓库系统的构建效率和质量。目前研究ETL过程都是集中于个案的研究, 强调ETL系统的可扩展性和灵活性[3,4,5], 对于如何在类相似或相近的数据仓库项目中共享ETL过程的研究则很少, 很大程度上阻碍了数据仓库项目建设效率的进一步提高。如何在一类相似或相近的数据仓库项目中发现其共同特征、知识和需求, 使得ETL过程可以在这些数据仓库项目中被反复使用, 大幅度提高实现ETL过程的效率, 从而提高数据仓库构建的效率, 研究该问题具有一定理论意义及实用价值。
基于此, 这里研究了基于构件的思想, 设计并实现了可重用的ETL架构, 经北京银联、江苏银联、浙江银联等10家银联省级分公司的数据仓库项目实际应用, 表明该架构是有效的。
1 基于可复用构件思想的ETL架构设计
1.1 设计思想
基于构件技术的软件复用提倡以已有的工作为基础, 充分利用过去工作中积累的知识和经验, 将已经辨识的具有相对独立功能的构件应用于新系统的开发, 保证新系统开发的过程中, 能够将重点集中于辨识和实现应用系统特有的构成成分, 最终缩短系统开发周期, 提高系统的质量[6,7,8]。
基于构件技术的软件复用的核心思想包括如下几个方面:
(1) 构件化设计。通过系统地分析一类相似或相近的数据仓库项目, 识别出其共同特征和可变特征, 并对这些特征进行抽象, 形成领域分析模型, 并据此进一步识别出可复用的构件。
(2) 层次化设计。层次化设计可以提高系统的可扩展性和可维护性。通过层次化设计可以将所有识别的构件按一定的规则 (如抽象级别、处理对象和处理的功能) 分类管理, 然后以分层的形式来组织, 进而确定不同层构件之间的交互方式, 保证每个构件的变化只涉及它的邻近两层的相关构件, 实现系统一定程度上的开放性。
(3) 接口化设计。不同层次的构件之间需要沟通, 沟通需要规范, 通过规范的接口可实现构件之间沟通的规范化。接口只制定规范, 具体实现交由构件内部完成。接口化设计将构件的差异放到实现阶段, 而不是在设计阶段, 使得设计阶段可以致力于软件架构设计的完整性和复用性, 使得不同系统之间处理的差异通过替换构件而无需变动架构就可得到解决。
1.2 ETL架构模型设计
基于可复用构件思想的ELT架构 (如图1所示) 主要分成基础服务层、抽取层、集成转换层、特殊处理层四个层次, 每个层次的功能如下所述。
1.2.1 抽取层
抽取层构件位于ETL架构的最底层, 直接面对数据源, 完成数据抽取阶段的工作。鉴于数据仓库数据源差异性大的特点, 这个层的ETL构件在不同数据仓库间差异很大, 可重用程度总体上比较低。
1.2.2 集成转换层
集成转换层构件主要将抽取层抽取的数据转换成格式规范、含义统一、质量良好的数据, 并集成到数据仓库中。由于是在两个层接口构件之间, 所以集成转换层构件的输入和输出都要满足层间接口构件的约定, 在相似数据仓库项目之间的差异主要体现在业务处理规则上。集成转换层为每类数据对象提供一类ETL处理构件, 同层构件之间相对独立, 通过抽象各个相似数据仓库项目业务规则, 将其封装在构件内, 保证ETL架构在相似数据仓库之间移植时, 只要通过配置业务规则, ETL构件即可投入使用。
1.2.3 特殊处理层
为了保证后续功能开发者可以将注意力放在功能关注的指标上, 而不要关心指标的具体口径, 更不要担心指标口径变化和指标口径在相似数据仓库项目之间的差异对功能移植造成不利影响, 在集成转换层构件处理的基础上, 专门增加了特殊处理层, 负责将数据仓库中按流水交易形式组织的数据换算成按KPI组织的形式。
1.2.4 基础服务层
为了给ETL提供一个相对稳定和灵活的架构, 在元数据管理的构件识别的基础上引入了基础服务层, 扩展了传统意义上的元数据管理的功能, 包含元数据管理构件、层间接口构件、KPI自动测试构件[9]三大类:这些构件构成了ETL基础和骨架, 为系统的稳定性和适应性奠定了基础。
(1) 元数据管理构件。
元数据是关于数据的数据, 元数据管理构件主要完成ETL子系统中元数据管理模块的功能, 具体分成三小类, 分别是负责维护数据仓库架构的维护类构件、负责维护业务规则的维护类构件和调度类构件[10]。
(2) 层间接口构件。
为了在各个数据仓库项目之间平稳的移植ETL, 在此设计了层间接口构件。从抽象层面上为各数据仓库项目提供一个相同的ETL处理框架, 为ETL处理过程各层次的各种功能构件提供接口, 实现构件具体处理过程对架构的透明化, 为系统功能扩展留下了余地。
(3) KPI (关键绩效指标) 自动测试构件。
测试无疑是保证系统质量的一个重要方法, ETL也不例外, 但是, ETL过程测试和一般的软件测试在测试过程、测试方法、评价标准等方面都有比较大的不同, 它是一个非常繁琐、工作量巨大、有一定规律的过程。
从抽象层面上看, 一类相似或相近的数据仓库项目每个KPI (关键绩效指标) 的维度组合是相对固定的, 测试标准和过程是一致的, 所以, 在ETL架构中, 专门提供了KPI自动测试类构件, 为每类KPI提供一个自动测试构件, 其基本处理逻辑如图2所示。
该类构件能够快速发现ETL架构中集成层和转换层中相关构件数据处理过程中隐藏的问题, 从而降低ETL过程测试的难度和工作量, 大幅度提高ETL架构的效率和质量。
2 银联统计分析系统ETL构件识别与架构设计
为了说明基于可重用构建思想的ETL架构的有效性, 下面介绍该架构在多家银联统计分析系统中的实际应用。
2.1 银联统计分析系统的介绍
银联统计分析系统是建立在数据仓库基础上的, 为银联各分公司领导提供决策辅助信息的系统。其目的是为了更深入应用银联积累的大量跨行交易数据, 是为了促进分公司、银行、金融监管机构和行业客户对业务进行全面、及时、准确的分析和定位, 及时了解业务发展动态和预测, 及时解决业务发展中存在的问题。
银联在全国有37家省级分公司。各分公司所关心的数据内容, 关注的KPI体系, KPI的评价标准都是一致的。但是, 各分公司由于当地经济发展水平不同, 银行卡应用深度不同, 导致各分公司业务种类差异很大, 即使是同一种业务, 其成熟程度、规范程度差异也很大, 体现在数据上就是数据源的种类不一致, 即使是相同的业务数据源, 在数据结构、业务判断规则、数据表现形式方面也有很大差异性。
这种共性大差异性也大的多个数据仓库系统, 设计上选用可重用性构件的思想来指导ETL的架构设计, 实现上采用自己开发的拥有自动知识产权决策支持系统产品:数据挖掘商业应用平台 (Compass) 。该平台包括智能流程管理子系统、报表专家子系统、多维分析子系统、数据挖掘子系统四个部分。其中智能流程子系统是一个独立的ETL开发工具, 能够支持基于可复用构件思想ETL过程的实现。
2.2 银联统计分析系统ETL构件分层识别
在银联统计分析系统ETL设计阶段, 依据图1所示的ETL架构和设计思想来设计和组织ETL各处理阶段可重用构件以及构件之间的接口规则:
(1) 抽取层。
银联统计分析系统抽取层处理的数据主要三类:业务数据、维度数据、辅助数据。业务数据主要包括全流水、二次清分数据、公共支付、固网支付、网上支付等业务交易数据;维度数据主要包括商户信息、机构信息、终端信息、地区信息等;辅助数据主要是卡bin信息、发卡信息等。
银联统计分析系统这个层面的数据除了全流水数据外, 其他的内容在各个分公司表现形式、处理规则差异很大, 封装成构件的价值不大, 所以这个层面可以识别的构件只有全流水抽取。
(2) 集成转换层。
鉴于各分公司统计分析系统所关心的数据内容, 关注的KPI体系, 关注的维度数据 (商户、机构、终端) 信息相似度很高, 所以这个层面可以识别的构件比较多, 主要有两大类:流水数据集成转换构件;维度类数据集成转换构件, 具体包括商户、机构、终端、商户类别、地区信息的集成转换构件。
辅助数据因为类型多样, 差异比较大, 可重用价值不高, 所以不对其识别构件。
(3) 特殊处理层。
银联统计分析系统的特殊处理层的构件不再按照数据类别识别, 而是根据每个指标的使用频率、涉及数据记录数的多少识别三类构件:交易指标类构件、调账指标类构件、维度统计指标类构件, 分别负责交易类指标、调账类指标、商户和终端发展情况的统计。
(4) 基础服务层。
各分公司银联统计分析系统对元数据管理要求基本一致且没有特殊要求, 银联统计分析系统将其识别为元数据管理构件。
考虑到银联统计分析系统处理的数据对象基本一致, 差异主要体现在数据的表现形式和处理规则上, 加上ETL过程构件之间传递数据量很大, 这里选用数据池的形式而不采用函数调用的形式来定义构件接口。例如, 所有分公司对商户关注的信息都是一样的, 但是每个分公司提供的商户信息的表现形式却各不相同, 抽取层接口数据池通过约定抽取层商户信息抽取过程生成内容和格式, 为集成转换层商户信息集成转换构件提供一个稳定的数据源, 使其不必关心用户提供的数据源是什么形式。
考虑到银联统计分析系统关注的指标繁多, 一次性全部识别成控件难度和工作量都很大。所以, 首先识别并封装最重要的、最常用的交易类指标的自动测试构建;然后是调帐指标和维度统计指标的自动测试构件的识别和封装。
2.3 银联统计分析系统ETL架构设计
基于可复用构件思想银联统计分析系统ETL架构具体包含六个功能模块 (如图3所示) 包括:元数据抽取模块、可重用构件选择和导入模块、数据仓库架构自动维护模块、ETL过程定义模块、ETL调度模块、ETL构件生成模块。各个模块的具体功能分工如下:
(1) 元数据抽取模块。这个模块主要完成两项工作:抽取银联业务数据和维度数据元数据, 并在此基础上对系统进行更精确的定义, 例如银联各类数据源提供的时间周期、银联数据仓库数据保留的时间和备份频率等信息。
(2) 可重用构件的选择和导入模块。在银联统计分析系统的分析和设计阶段, 已经识别了抽取层、集成转换层等各层有重用价值的构件。为了管理和重用这些构件, 构件选择和导入模块的功能有两个:第一, 从银联构件库中抽取已经封装的全流水交易数据抽取构件、集成转换构件、KPI转换构件、维度类数据集成转换构件 (包括商户、机构、终端、商户类别、地区信息五类构件) 、KPI自动测试类构件, 将其导入到ETL过程库, 按照ETL架构 (见图1) 对导入的构件分层组织, 生成系统的ETL过程框架 (如图4 (a) 所示) , 图4 (a) 中的ETL过程链中三个JOB节点分别对应ETL架构 (见图1) 中的抽取层、数据集成转换层、特殊处理层, 图4 (b) ～ (d) 分别对应各导入构件 (一个JOB节点代表一个导入构件的处理过程) 在这三个层次中的组织形式。第二, 模块可根据新项目银联分公司特殊业务规则和指标口径配置每个构件的处理规则, 将配置信息加入元数据库中。
(3) 数据仓库架构自动维护模块。数据仓库架构自动维护模块主要功能是依据元数据库中的信息, 为银联统计分析系统完成数据仓库的创建和初始化工作, 完成事实表、维度表创建, 完成每个构件需要的配置表、中间表和临时表的建立等工作, 而这些原本需要用手工来实现和维护的。
(4) ETL过程定义模块。在初始框架的基础上, 可通过ETL定义模块可视化地定义构件库中没有可重用构件的ETL过程, 例如手续费和品牌费抽取、商户信息抽取 (如图5所示) 、终端信息过程等, 使整个银联统计分析系统的ETL趋于完善。
(5) ETL调度模块。ETL调度模块可以根据系统的调度设置, 执行ETL过程库中的ETL过程, 实现数据抽取、转换、加载、换算等工作。
(6) 可重用构件生成模块。对于新定义的ETL过程, 若可重用价值高, 可通过ETL构件生成模块从ETL过程库中抽取相应ETL处理过程包装成可重用构件。可重用构件一般包括以下内容:ETL处理过程、配置过程说明文档、相关表 (配置表、中间表和临时表) 的信息、初始化数据、特殊规则配置功能界面, 构件这些构成部分由ETL构件生成模块分别存入到构件库中相关表中。
3 结 语
这里介绍了一个基于可复用构件思想的ETL架构, 以北京银联、江苏银联、浙江银联等10家省级银联统计分析系统为例, 介绍了该架构各层构件的识别过程, 设计了ETL模块构成以及各模块主要功能。该架构已经在10家省级银联分公司的统计分析系统的ETL构建中应用, 实践表明该架构是有效的, 它能够在比较短的时间内完成统计分析系统的构建, 可有效缩短系统的开发周期, 大幅度降低各分公司的时间成本和资金成本, 对于推动数据仓库和商业智能在银联各个省级分公司的应用有显著意义和使用价值。
参考文献
[1]王鹏, 张焕水.数据仓库技术在数据服务平台中的应用[J].计算机与信息技术, 2005 (11) :26-28.
[2]刘绍清, 黄章树, 黄剑辉.数据挖掘商业应用平台的数据预处理管理[J].重庆工商大学学报:自然科学版, 2006 (5) :453-456.
[3]王磊, 李一凡.赵怀慈.银联数据仓库系统中ETL的设计和实现[J].微电子学与计算机, 2007 (5) :66-68.
[4]谷赫.电信业务数据仓库平台中接口的设计与ETL开发[J].吉林大学学报:信息科学版, 2008 (6) :652-656.
[5]陈弦, 陈松乔.基于数据仓库的通用ETL工具的设计与实现[J].计算机应用研究, 2004 (8) :214-216.
[6]宁伟, 蒋严冰.基于构件的可复用串行口通信架构的设计及实现[J].计算机应用与软件, 2003 (1) :3-4.
[7]程永.基于构件技术的电力GIS开发研究[J].软件导刊, 2008 (9) :110-112.
[8]任午令, 唐任仲, 郭尚鸿, 等.基于构件的企业应用集成技术[J].浙江大学学报:工学版, 2007 (8) :1 283-1 287.
[9]王冬, 吕慧娟.形式化方法自动生成测试用例的算法研究[J].科技信息, 2008 (23) :73-74.
ETL系统设计 篇7
ETL技术, 是英文Extract-Transform-Load的缩写, 通过采用清洗、转换、加载, 这3个步骤对海量数据进行抽取处理。设计的系统中针对这3个处理步骤, 在数据库中分别对应数据清理层 (ODS) 、数据转换层 (STG) 、数据存储层 (DW) 三层次的数据层进行处理[1]。
1 数据清理层 (Operational Data Store——ODS)
数据清理层的主要作用是对源数据进行清洗, 对应ETL技术的E-E X T R A C T部分。分为2个部分, 其一, O D S临时存储层 (ODS_TMP) , 其二, ODS错误数据存储层 (ODS_ERR) 。对于流入系统的数据首先加载入ODS_TMP, 加载过程中对出现误差或者错误的数据, 由系统预先判断剔除, 对系统不需要的数据也由系统根据预先设置的规则剔除。这些不符合条件的数据, 也即无需进一步向下处理的数据, 都将全部导入到ODS_ERR层。
ODS_TMP层在完成数据清洗后, 留下的将是有用的, 需要进一步处理的数据, 待进入下一个STG层进一步处理。这一层的设置可以减少转换的成本, 使后续操作只专注于抽取和加载。
ODS_ERR层在接受完成不符合条件的数据后, 完成本轮ETL操作使命, 相关数据将通过前台展现或文件导出的方式供监管部门系统管理人员查看, 以防止部分有效数据因各种原因进入ODS_ERR层后未被系统纳入分析。
ODS_TMP层的数据生命周期为1天, 该层数据在夜间批量完成后即进行清理, 不做保留, 采用随用随清理的策略。ODS_ERR层的数据生命周期为1个月, 即系统管理人员可以通过系统查询近一个月的错误数据存储, 超过1个月的数据永久清理, 不再保存。
2 数据转换层 (Data Transform Stage——STG)
数据清理层的主要作用是对进入ODS_TMP层的数据进行转换, 对应ETL技术的T-TRANSFORM部分。该部分根据预先设定的字典表的信息, 对不同渠道来源的数据进行统一化处理, 使数据拥有相同的标识符号;对于精度不同的数据统一化处理为一种精度标识;对于同一维度的数据进行分级汇总;对于所有需要对原始数据进行转换的情况都在本层进行处理[2]。
这一层的设置可以有效快速的将原始数据在纳入数据库最终保留之前进行转换完成, 加快转换效率。
STG层的数据生命周期为1天, 该层数据在夜间批量时实时完成数据转换功能, 完成后相关数据进入DW层后, 该层数据即清理, 不做保留, 采用随用随清理的策略。
3 数据存储层 (Data Warehouse——DW)
数据存储层的主要作用是对经过转换后的STG层的数据进行存储, 对应ETL技术的L-LOAD部分。本层是所有数据的最终存储部分, 所有的数据都将按时间, 地区, 餐饮企业类别, 油水分离器型号等等不同维度进行存储, 并且加上索引字段, 供后续使用的时候能快速的找到对应的数据。在明细数据的存储之外, 本层还担负数据整合的功能, 例如对于同比, 环比类的数据进行加工处理[3]。
这一层的设置是整个数据设计层次模型的核心部分, 积累下庞大的数据资产。
DW层的数据生命周期为5年, 该层数据做全量保留, 供系统做数据查询, 挖掘使用。对于超过5年的数据, 做离线磁带备份, 在有需要时候, 做磁带恢复使用。
4 结语
在餐饮业油水分离器监测系统中引入ETL技术, 采用数据清理层 (ODS) 、数据转换层 (STG) 、数据存储层 (DW) 三层次的数据结果模式, 将很好的处理大数量的油水分离数据, 并且可以通过高效的数据搜集处理分析, 有效辅助监管部门提高监管效率。对于油水分离器产生的庞大数据分门别类的存储起来也便于数据资产的积累, 为后续实现数字化监管打下基础。同时, 三层次的数据处理模式, 有效分隔了数据处理过程中的各个不同的功能模块, 使层与层之间相互隔离又相互作用, 提高数据处理的效率。
参考文献
[1]王珊.数据库系统概论[M].北京:高等教育出版社, 2006, 5.
[2]孙福生, 朱英存, 张俊强.环境监测[M].北京:化学工业出版社, 2007 (7) :309-312.