基于Excel及数据转换服务的异构数据集成方法

开篇：润墨网以专业的文秘视角，为您筛选了一篇基于Excel及数据转换服务的异构数据集成方法范文，如需获取更多写作素材，在线客服老师一对一协助。欢迎您的阅读与分享！

摘要：针对异构数据库数据集成问题，在分析基于SOA架构三层设计模式的基础上，结合excel和数据转换服务技术，提出了比较适合于中小企业实现异构数据转换系统的体系结构。详细介绍了组件系统层异构数据源在集成子系统层整合数据、判断采用Excel还是DTS并向全局数据库系统层进行数据转换及集成的过程。

关键词：面向服务架构；Excel；数据转换服务；异构数据集成

中图分类号：TP311.12

文献标识码：A

随着信息技术的不断发展，企业在网络构建上也逐渐启用目前流行的高端或实用性较强的软硬件产品。不同产品不能很好融合，遗留系统更新所产生的数据安全性、完整性、可靠性也会存在很多问题。面向服务架构（ServiceOriented Architecture，SOA）作为此类问题的解决方案应运而生。本文根据SOA理念采用Excel和DTS（Data Transformation Services）两种互补的异构数据转换技术，提出了一种基于SOA架构的异构数据集成系统的体系结构。Excel[1]无需提取元数据且适合各种报表管理，但是数据以单元格为单位进行转换，代码量繁重；DTS技术[2,3]能完整地转换异构数据信息，且编码量小、易于维护，但该技术仅基于元数据的提取，对元数据难以挖掘的数据库信息不适用。两种技术相互结合，克服了各自技术的不足。

1SOA简介

SOA是将软件应用程序构建成为可重用的商业服务集合的新型体系结构模式。它的出现标志着设计、开发新应用程序并将其与原有业务应用程序集成的方式出现了根本性变化，它将企业应用程序的开发简化为轻松进行集成和重用的模块化业务服务。它利用一系列网络共享服务，使IT能更紧密地服务于业务流程。通过采用能隐藏潜在技术复杂性的标准界面，SOA能提高IT资产的重用率，从而加快了开发并更加可靠地交付新的增强后的业务服务[4]。因此，为企业应用提供各种灵活商业模块的SOA成为重量级厂商追逐的目标，包括SAP、IBM、BEA、Oracle、微软等在内的厂商都竞相为此推波助澜。

2异构数据集成系统体系结构

普通管理系统通常采用三层设计模式，即数据库开发层、业务流封装层和用户应用层。对于异构数据库集成系统的开发也可以采用三层设计模式，但是数据的流动特别是异构格式数据的流动就需要采用一种中间格式数据或一种提取元数据的方法作转化。因此这里基于SOA理念对异构数据库之间实现自动数据交换提出了一种解决方案，即在异构三层设计模式基础上，提出一种应用数据库系统集成的体系结构[5,6]，并实现以Excel为中间数据格式和DTS技术互补的转换应用。如图1所示。

图1中，组件系统Component System位于网络中的各个节点上。为了建立永久查询需即时更新整个多数据库系统的全局控制，在层次顶部仅设置一个全局数据库系统Global Database System，通过全局用户引用全局查询和事务处理来使用。全局查询根据访问数据的位置分割生成若干子查询，子查询不是直接发送给底层的组件系统，而是必须要经过一个集成子系统。每一个组件系统有它自己的集成子系统Integration Subsystem，其功能是转换子查询来响应组件系统的调用（如每一条SQL查询语句）。自治本地事务对于全局数据库系统和集成子系统都是可见的，可以直接调用组件系统接口。全局数据库系统和集成子系统执行异构三层事务的管理模块。

集成子系统与其组件系统定位在同一节点。在全局数据库系统和集成子系统之间的接口用分布式，全局数据库系统作为分布式系统被执行。Excel中间件提供对各种异构数据库数据的导入接口；组件系统提供数据源及数据导出接口。该体系结构数据流程如图2所示。

3基于Excel及DTS的异构数据集成

我们假设在全局数据库系统层的系统数据库使用Microsoft SQL Server，图1中各组件系统层的数据库与全局数据库系统层的数据库互异。由于数据库可能出现系统操作平台上的不同，故集成子系统通过执行Java这种具有开放特性、跨平台的语言所撰写的程序，便可以完成数据写入全局数据库系统前各项工作。

MS SQL Server的一个重要组件――DTS，使得管理员能够在服务器与数据库间转换信息。它整合了Microsoft Universal Data Access技术与ActiveX技术，其数据的导入、导出功能，既能应用于ODBC或OLE DB数据库（Oracle，Sybase SQL Server，Access以及FoxPro），也可以应用于存储类型（文本文件，Excel及Outlook）。由此可见，MS SQL Server的DTS功能很强大，但是也存在一些问题：

1） 对于适合数据导入导出的异构ODBC/OLE DB数据库不能保证其数据类型的完整性。例如将FoxPro中的数据导入到MS SQL Server中。逻辑上，FoxPro中的NUMERIC和DATE数据类型对应MS SQL Server中FLOAT和TIMESTAMP数据类型。但实际上，DTS只记录了数据值，而数据类型全部以VARCHAR类型导入进来，而且数据全部分配可变的、上限为255字节的存储空间来存储，无法为数据提供合法性检验，破坏数据的完整性。

2） 如果企业内部遗留系统存在多个异构数据库，且处于不同系统平台，那么DTS就需要大量手工操作，而且也会出现整合数据可靠性差的问题。

3） 对于ODBC/OLE DB数据库、文件存储类型以外的数据格式，DTS功能将不适用。

要解决上面提到的问题，决定在集成子系统层各类异构数据格式之间的转换采用Excel为其中间格式；组件系统层整合数据采用程序模拟DTS的解决方案实现向全局数据库系统层数据格式转换的策略。

3.1数据库接口实现

假设图1中的全局数据库系统下层分布两个异构组件系统CS1和CS2，其数据库分别使用MySQL和IBM DB2，并且在集成子系统层分别提供二者的数据库接口。我们采用JDBC底层应用程序编程接口（API），在不同数据库功能模块的层次上提供一个统一的用户界面。

CS1对应数据库接口主要代码如下：

程序前

System.setProperty("jdbc.drivers", " com.mysql.jdbc.Driver");

Class.forName("com.mysql.jdbc.Driver").new Instance();

DriverManager.registerDriver(new com.mysql.jdbc.Driver ());

程序后

CS2对应数据库接口主要代码如下：

程序前

System.setProperty("jdbc.drivers",

"com.presistentjava.JdbcDriver");

Class.forName("com.presistentjava.JdbcDriver").new Instance();

DriverManager.registerDriver

(new com.presistentjava.JdbcDriver());

程序后

连接数据库时，在DriverManger对象上调用getConnection()方法。

3.2Excel中间件实现

在数据交换过程中，目标数据往往是源数据中的部分数据，这些数据有可能是一张表中的几个字段，有可能是一张表中某些记录值，也有可能是多张表的复杂关联查询的结果。但无论如何，将采用Excel文件作为转换数据中间存储的媒介，把数据有序地存放在Excel文件中，使得数据双方都理解这一中间的数据媒介。Jakarta POI就是一种和Java技术相结合用来编辑Excel文件的开源工具。HSSF为POI提供了Excel格式文件的真正的Java执行。其步骤如下：

1）调用源数据库存储过程(或执行一条子查询SQL语句)，选择性地提取出关键数据。

2）将提取出的数据导入.xls文件。

Excel中的Excel文件、工作表、行、单元格对应POI中的workbook、sheet、row和cell。提取一个数据单元写入指定Excel文件特订单元格的主要代码如下：

程序前

ResultSet rs = stateMent.executeQuery();

int value1= rs.getInt(1);

POIFSFileSystem fs = new POIFSFileSystem

(new FileInputStream());

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(0); //工作表

HSSFRow row = sheet.getRow(4); //行

HSSFCell cell = row.getCell((short)2); //列

cell.setCellType(cell.CELL_TYPE_NUMERIC);

cell.setCellValue(value1);

…

FileOutputStream fos = new FileOutputStream ("");

wb.write(fos);

fos.flush();

fos.close();

程序后

3.3DTS的实现

除了用Excel中间件来完成集成子系统层的数据转换服务外，本方案还提供了通过DTS的方法实现图1中组件系统与全局数据库系统异构数据间的整合。

3.3.1ActiveX脚本设计

设计ActiveX脚本是为了指定数据库源文件的存储路径，供远程系统自动调用数据源。在设计中，我们选择了MySQL[7]和IBM DB2[8]进行了实验，其数据源调用步骤如下：

首先，在每一个组件系统的源数据库中分别设置实例数据库的路径，以文件夹形式保存。其次，在本地系统中再指定一个不同路径，用以保存实例数据库的副本，其作用是源数据库比对副本数据库，如果存在新增表文件，则开始进行数据转换服务。然后，检验是否有新增表文件存在（此过程在集成子系统层实现）。最后，将新增表文件单独保存在用来执行数据转换服务的新文件夹。

以MySQL为例，其数据转换服务过程设计如图3所示。

ActiveX脚本（JavaScript）主要代码如下：

程序前

Private Class FilesExtract

var dts, f1, strFullPath1, strFullPath2, strFullPath3,

groupFileName1[], groupFileName2[];

dts = new ActiveXObject("Scripting.FileSystemObject");

strFullPath1=dts.GetFolder

("C:\ProgramFiles\MySQL\MySQL Server 5.0\data");

strFullPath2=dts.GetFolder("D:\data_Compare");

strFullPath3=dts.GetFolder("D:\data2");

groupFileName1[]=dts.GetFolderFileName(strFullPath1);

groupFileName2[]=dts.GetFolderFileName(strFullPath2);

f1=dts.GetFile(strFullPath1);

For (var i=0; i

If not groupFileName1[i].exists(strFullPath2 &

groupFileName2[i]) then

f1.Copy(groupFileName1[i], strFullPath2);

f1.Copy(groupFileName1[i], strFullPath3);

Private function GetFolderFileName(FullPath)

var f, fc, s = "";

f = dts.GetFolder(FullPath);

fc = new Enumerator(f.files); //文件

For(; !fc.atEnd(); fc.moveNext())

s+=fc.item(); s+=",";

return(s);

程序后

3.3.2定位设计

设计定位程序为了让全局数据库系统了解数据转化服务的执行对象，远程系统数据库需要通过图3中的全局控制器得到本地数据库的新增表文件，并将其转换成系统数据库数据格式，导入系统数据库中。

Java中，对文件夹的属性、方法的定义由类File提供。在进行数据转换之前，先用File类得到新增表文件（由于SQL用到的表名无需后缀，为正确地转化数据，须执行去后缀处理)。主要代码如下：

程序前

String newFiles=new File("D:\data2");

String[] newFilesList=newFiles.list();

for(int i=0; i

String newTableName=newFilesList[i].getName();

String name=newTableName.subString

(1, newFilesList[i].LastIndexOf(".frm")); //去后缀处理

程序后

以上过程我们完成了数据库源文件的提取及系统远程调用的必要处理，接下来将对这些数据进行转换设计以完成DTS的功能实现。

3.3.3DTS设计

对于新增表文件要提取出其元数据，即要提取出“字段数”、“字段名”、“数据类型”、“数据长度”、“是否为空”等关键信息。一旦获得新增表文件的元数据后，就自动创建对应的系统数据库表。以MySQL向MS SQL Server导入元数据为例。

为了满足数据作转换后的完整性，首先要考虑两种数据库数据类型的对应关系。对于某些MySQL数据类型， MS SQL Server中对应不止一种的数据类型。这里只对MySQL中的DOUBLE、TIME、VARCHAR(m)这三种常用数据类型作描述。假设需要作数据转化的新增表文件中只存在以上这三种类型的数据。

主要代码如下：程序前

ResultSet rs = stateMent.executeQuery("select * from" + name);

ResultSetMetaData rsmd = rs.getMetaData();

int numberOfColumns = rsmd.getColumnCount();

boolean b = rsmd.isSearchable(1);

String createTableSQL="create table " + name + "(";

for(inti=1; i

intj=rsmd.isNullable(i);

if(j=0)String null="";

Else if(j>0)null="NOT NULL";

if(rsmd.getColumnTypeName(i)!=TIME)

if(rsmd.getColumnTypeName(i)!=DOUBLE)

createTableSQL=createTableSQL+ rsmd.getColumnLable(i)+

" "+rsmd.getColumnTypeName(i)+ "("+

rsmd.getColumnDisplaySize(i)+ ")"+null + ",";

//VARCHAR(m)类型数据转换为VARCHAR

else createTableSQL=createTableSQL+

remd.getColumnLable(i) + " " +"FLOAT"+ null + ",";

//DOUBLE类型数据转换为FLOAT

elsecreateTableSQL=createTableSQL +

rem- d.getColumnLable(i) + " " +" SMALLDATETIME"+ null + ",";

// TIME类型数据转换为SMALLDATETIME

stateMent.execute(createTableSQL);程序后

这样，就完成了异构数据库数据格式间的完整转化，并将其导入到系统数据库。在用户看来整个体系结构就如同一个黑匣子，数据访问具有较强的灵活性和可靠性。

4结语

该方法应用在某小型企业财务信息集成项目中，满足了企业的需求，得到了用户的肯定。底层各子系统异构数据的整合通过Excel及DTS实现；财务部在全局数据库系统层按业务流收集整合数据并导入Excel，实现跨应用的报表格式转换。执行界面如图4所示。

现今，越来越多的SOA开发商将异构数据库间的研究放在了一定高度，尽量减少了元数据间的差异；开发工具的研究也加入了几乎全部的SQL数据类型。这就对DTS提供了便利的开发环境。但并不是说Excel在数据转换方面的作用开始淡化，Excel是现今最强大的报表数据中间件。实践证明，本方法特别适合于网络架构层次不是很复杂的中小企业的异构数据集成。