数据抽取及交换工具的设计与实现

王德胜++李弘++田佳烨

摘要：本文研究了ETL相关技术，并针对出版行业的现状和应用需求，提出了面向出版行业的数据抽取及交换工具设计方案和实现方法，通过.NET开发工具和C#开发语言开发了一套可灵活定义的工具，在多家出版单位进行了应用，取得了良好的效果。

关键词：数据抽取；数据交换；数字出版；商业智能

中图分类号：TP311.13

文献标识码：A

DOI： 10.3969/j.issn.1003-6970.2015.08.008

0 引言

出版商业智能系统是面向出版单位的综合数据分析系统，该系统基于出版企业各业务系统的数据进行综合分析，另一方面，出版企业各业务系统之间也需要进行数据交换。由于出版企业采用不同公司提供的业务系统，各业务系统采用不同的数据库，应用较广泛的有SQL Server、Oracle，少量有采用MySQL等其它类型数据库，另外还有许多的外部数据以Excel、TXT、XML等文件方式存储，本文针对出版行业的现状和应用需求，研究了通用的商业智能系统ETL工具的数据抽取、转换和加载相关技术，提出了面向出版行业的数据抽取及交换工具设计方案和实现方法，通过.NET开发工具和C#开发语言开发了一套工具。

1 ETL技术综述

ETL即数据抽取、转换清洗、加载的过程，能够按照统一的规则集成并提高数据的价值，是数据仓库获取高质量数据的关键环节，是从数据源获取数据并对数据进行清洗转换最终加载到数据仓库的过程，是数据由数据源系统向目标数据源加载的主要方法。ETL工具从本质上而言是一种数据转换工具，一般ETL工具还会设计任务管理和调度引擎。

1.1 数据抽取

数据抽取是按业务需求从源数据从数据源系统抽取数据仓库系统所需的数据[错误！未定义书签。]，ETL处理的数据源除了关系数据库外，还可能是文档，例如TXT、Excel、XML等文件数据，这要求抽取工具采用统一的数据接口，既满足从数据库抽取数据的需求，也可以满足从文文本件抽取的需求。对于不同数据源、不同数据量的源数据以及不同性能要求的业务系统，采用的实现方式不同。一般的抽取方式包括全量抽取、增量抽取、全表比对、日志比较等，为保证抽取效率，减少对生产运营的影响，可针对不同的情况选用不同的抽取方式。

1.2 数据清洗转换

从数据源中抽取的数据不一定完全满足目的库的要求，例如数据格式的不一致、数据输入错误、数据不完整等等，数据转换就是完成对抽取的源数据根据数据仓库系统或目标数据库的要求，进行数据的转换、清洗、拆分、汇总等，保证来自不同系统、不同格式的数据和信息具有一致性和完整性，并按要求装入目标数据库。一般数据转换可通过源数据与目标数据的映射关系来完成，其转换规则可定义在映射表中，转换工具根据定义的规则进行转换。转换工具可直接采用SQL语句的方式和转换组件的方式进行转换。比较而言，直接在SQL语句中进行转换和加工更加简单清晰，性能更高，但一般要求操作的数据是关系型数据库。对于SQL语句无法处理的可以采用组件的方式处理。

1.3 数据加载

将清洗转换后的数据加载到目的库中通常是ETL过程的最后步骤，当目的库是关系数据库时，直接通过SQL语句进行insert、update、delete操作。目的数据源是文件格式如XML、Excel、TXT等文件时，可通过转换工具根据映射表的设置转换成要求的文件。

2 系统设计与实现

2.1 设计思想

ETL设计与开发，需深入到业务系统内部去获取所需数据，需定义源数据、目标数据，数据抽取、转换和装载策略等。参考ETL设计开发的基本要求，本文对系统进行了整体设计，下文介绍主要设计思想。

数据交换的过程如图1所示，系统设计了三种数据交换方式：

为提高数据交换的性能，对于源数据和目标数据都是关系型数据库的情况，可以直接运用SQL语句的方式进行数据交换。

一般数据可通过系统的转换工具进行数据转换后直接写入目标数据源。

复杂转换需要经过中间数据表暂存数据，通过对中间表的清洗转换处理后再加载到目标数据源。

系统采用灵活定义的方式解决各种情况下的数据交换需求，包括各种类型数据库连接的定义、源数据与目标数据的映射关系定义、数据清洗转换规则定义，系统提供可视操作界面并将定义的结果保存到数据库表中，整个程序将依据各种定义进行分别处理，完成数据抽取、转换及加载的各项功能。

为了更方便用户操作各项数据交换任务，系统提供了作业调度管理的功能，系统按照调度计划、作业组、作业的层次关系进行管理，以作业为最小的操作单元，作业组可包含作业和其他作业组，一项调度计划可包含多项作业组和作业，对作业、作业组和调度计划系统提供界面由用户根据实际应用需要定义。系统将针对调度计划采用队列扫描的方式执行每一项作业。

为适应不同类型的数据库，系统按照分层设计的理念对数据库的访问处理封装为一层，在程序处理时将根据不同的数据库做出不同的处理。根据出版行业实际情况目前系统提供了对SQL Server、My SQL、Oracle的支持，支持Excel、TXT和XML文件。该设计提供了良好的可扩展性，对该层代码进行扩充即可适应更多类型的数据库。

系统选用.NET集成开发工具，C#开发语言进行开发实现。系统采用三层架构，包括UI层、业务逻辑层和数据层，此种设计保证系统具备良好的可扩展性。

2.2 数据库连接

用户对数据库连接属性定义，可定义各种数据源的连接属性，支持SQL Server、My SQL、Oracle等关系型数据库的连接，定义的项目包括标识、服务器地址、数据库名称、用户名、密码等。系统将根据用户的定义信息动态创建数据源连接对象，该连接对象既可以作为源数据库也可以作为目标数据库。

2.3 数据映射关系

数据映射关系定义了抽取和转换的规则，即数据源表中的数据字段与目标库表中的对应关系定义，映射关系采用主子表结构进行定义，主表中需要从已定义的数据库连接中选择目标表数据源连接和源数据表连接、是否根据源数据表结构创建目标表，子表定义相关表字段的对应关系及转换规则。

系统提供可视化界面方便用户建立源数据到目标数据的抽取映射关系。为方便用户选择，可直接通过源数据中提取源数据表和目标数据中提取对应库表中的表及字段供用户选择，用户直接选择对应关系，定义转换规则，也可以直接输入表中的字段名建立对应关系，定义完成后的映射关系和转换规则保存到映射关系到表中。系统将根据定义的映射关系完成抽取、转换和加载。

2.4 复杂的清洗转换处理

对于复杂的数据转换，需要由源数据抽取到中间库表，此时可将中间表看作目标数据源，经过专门的清洗转换处理，形成与目标数据库一致的数据，再定义一个由中间库表到目标数据的映射关系，即将中间数据库看作源数据，将规范的数据由中间表加载到目标数据的表中。

为完成专门的清洗转换处理，用户可通过系统提供的可视化工具进行清洗转换定义，支持SQL语句更新的方式进行数据清洗和转换，用户可定义更新的表、更新的字段、更新的条件，提供了对统计、计算、类型转换、字符串拆分处理等基本处理功能，针对出版行业的需求提供了标准书号与简书号转换等特色的转换功能，系统将根据用户定义的清洗转换规则进行转换处理。系统同时支持用户直接写SQL语句进行清洗转换，更复杂的处理通过二次开发的方式解决。

2.5作业管理

作业管理是整个数据抽取与交换软件的核心，作业是指一个完整的操作过程，作业管理包括作业定义和执行，系统可分三类作业，对于一项作业，首选作业类型，系统将根据不同的作业类型进行分别处理。

直接执行SQL语句。

直接执行SQL语，只能针对于关系型数据库，作业定义时，首先选择一个数据源，定义SQL语句，用户可以直接输入SQL语句，也可以通过系统提供的SQL编辑工具进行添加，一个任务可以添加多个SQL语句。

通过映射关系定义由一个数据库抽取数据到另外一个数据库的库表中。

任务定义时，首先需要选择已定义好的映射关系表，同时需定义目标数据获取时所使用的条件，设置对比标识列（一般为表的唯一标识列，可以是复合字段），同时可设置操作是完全添加、增量更新、是否反向删除等抽取方式。如果输出为文件类型，需定义输出位置，文件命名规则，EXCEL、XML样式等信息。

通过转换清洗规则在中间库进行数据转换处理。

系统根据用户定义的清洗规则，自动转换为可执行的SQL语句执行该项作业。

系统支持作业组，可将相关或功能相近多个作业定义到一个集合中。可以选择一批作业或作业组组成一个新的集合，其中每个子项可以有任意下属子项，子项可以为作业也可以为作业组，但在作业组包含其他作业组时应慎重使用避免死循环。系统将保存作业组标识，作业组描述信息，关联的作业或作业组及执行顺序。

2.6 调度计划

调度计划是实现作业或作业组自动化运行的一种方式，设置什么时间点来处理什么信息的一种定义。一个调度计划包括一组作业或作业组，系统将以调度计划为单位进行调度管理。可设置一项调度计划是否启用，是自动执行还是手动执行，对于自动执行的调度计划，提供可视化界面设置调度计划的运行开始时间、运行周期，来实现作业或作业组自动化运行的定义，参见图2。

如果配置为自动运行，系统在程序启动时会自动加载设置为启用的调度计划，根据计划运行频率算出每个作业下次运行时间，自动启动运行线程，系统将不断扫描是否有需要自动运行的任务，如果运行时间相等则将调度编号推送到运行队列表中，由另一线程扫描运行。系统根据调度计划定义信息.调度中包含的作业组，作业定义信息，作业定义包含的抽取规则、映射关系等按照作业的顺序自动运行，运行结果将写入日志文件。程序逻辑如图3所示：

程序启动，主线程自动扫描调度定义，每间隔一段时间扫描当前时间等于下次运行时间的调度，将时间相等的调度放人到调度作业队列中。

作业执行线程每间隔一秒扫描调度作业队列，如果有执行的调度，将调度编号传人到调度执行类中。

调度执行类加载调度信息，加载调度下属所包含的作业或作业组，如果为作业组则循环加载作业组下属子项，如是作业则执行该项作业。

作业执行：加载作业信息，判断作业类型分别执行。

①如果执行SQL，先加载目标数据源连接，顺次执行所包含的SQL脚本。同时记录脚本执行状态。

②通过映射关系定义执行数据交换，先加载作业所包含的源数据源定义连接，根据源数据定义及作业中加载获取源数据，根据目标数据定义及作业中定义的目标数据条件加载目标数据，根据条件比对、更新、插入的列生成数据表。根据定义，如果目标库为关系型数据对内存表执行添加、更新、或反向删除操作，如果目标库为文件类型，则据规则输出TXT、EXCEL、XML。

③根据用户定义的转换清洗规则，自动转换为SQL语句，在中间库进行数据转换处理。

记录日志：将作业的运行时间，各个环节状态记录到日志表中，如果执行出错，系统将错误记录到日志中，同时事务回滚。

3 结束语

针对不同数据库类型的数据交换的应用需求，本文设计的系统通过可视化界面为用户提供灵活的定制功能，包括数据库连接的定义、数据转换所需要的源数据库和目标数据库的连接选取、源数据表列与目标数据表列的映射和转换规则，对复杂的清洗转换处理采用在中间库表中由用户进行自由定义规则的处理方法，通过灵活定义的方式保证系统具有较强的适应性。同时用户可通过对调度计划、作业组、作业灵活定义的方式实现各种需求的作业和调度管理功能。该系统可满足各种不同的数据抽取、转换、加载的应用需求。

系统在电子工业出版社、浙江出版联合集团有限公司及其下属出版社等单位进行了应用，主要用途包括在不同的业务系统间自动交换数据，与企业外部系统交换数据，作为出版商业智能软件的数据抽取工具将各业务系统的数据集中到数据仓库中。随着数字出版业务的发展，海量的、多媒体化的数字内容加工、处理、转换、存储、分发、传播和不间断的网络服务不断增多，同时对全球化的、超大规模的用户数据进行分析、挖掘和推荐等智能性处理的需求也伴随着商业模式的创新而日益膨胀，本文设计的系统作为基础的数据交换工具为这类应用提供支撑，并且在我们承担的国家科技支撑计划课题“动态数字出版技术应用与运营示范”（2012BAH88F04）也得到了良好应用。