使用 DWE-SQW 实现 ETL 过程 1

核心提示：摘要本文介绍了 IBM DWEv9.1.1 产品包中的一个新成员：SQW(SQL Warehousing Tool)， SQW 是一种 ETL(Extract Transform and Load) 工具，使用 DWE-SQW 实现 ETL 过程 1，它可以实现多种数据源到 DB2 数据仓库的抽取、转换和加载，并且实现

摘要

本文介绍了 IBM DWEv9.1.1 产品包中的一个新成员：SQW(SQL Warehousing Tool)。 SQW 是一种 ETL(Extract Transform and Load) 工具，它可以实现多种数据源到 DB2 数据仓库的抽取、转换和加载，并且实现这一过程的可视化、自动化和可控化。

从需求讲起

一个跨国零售店，主要经营衣服、鞋、饰品、电器、食品，有着多年的经营历史。为了增加率润，公司需要分析多年的历史销售数据，得到客户的消费趋势并且找到公司现存的销售问题。为了达到这个目标，销售经理需要分析销售状态并且找到可以改进的问题，数据架构师则需要建立一个数据仓库来向销售经理提供相关的数据。

图 1. 零售公司的数据仓库需求

数据架构师首先根据公司现存的交易型数据库，设计出一个供公司进行销售分析的分析型数据库。

图 2. 数据仓库模型

下面他的问题来了，怎样将多年的历史数据导入这个数据仓库数据库里呢？公司的数据库系统运营多年，产生了多个历史备份，并且其间系统经过了多次升级，数据库结构也已经大相径庭，因此怎样将这些零散的、结构不一致的数据导入到一个数据库仓库变成了一个非常复杂的问题。

SQW 的功能和相关术语

SQW 可以实现多数据源到 DB2 数据源的数据导入，可以将多个异构数据源的数据经过加工整理导入到 DB2 数据仓库里 , 并且可以按时按期循环执行。下面我们将介绍如何使用 SQW 在可视化环境中进行数据的转移、整理和加载的。

SQW 分为设计和生产两个环境，也可以描述为客户端和服务器。其中 DWE Design Studio 是内置于 Eclipse 的客户端，用来完成各种数据流和控制流的设计调试。而 DWE Admin Console 则是部署在 WebSphere 之上的一个服务器，用来管理、运行和监控 ETL 进程。

为了便于描述，这里有一些术语名词需要解释：

ETL 是 Data Extraction, Transformation and Loading 的首字母缩写，是数据仓库、数据挖掘以及商业智能等技术的基石 , 其主要用来实现异构多数据源的数据集成。

工作空间 (workspace)：一个本地的目录，用来存放工程文件。DWE Design Studio 运行时，必须指定一个 workspace。同一个 workspace 同时只能由一个用户打开。

数据仓库工程 (Data Warehouse Project)：为了完成某项任务而设计的 model 和 flow 的集合。一个 Workspace 中可以包含多个 Project.

SQL 执行数据库 (SQL Execution Database)：用于执行 SQL 的中间数据库，可以是源数据库，或者目标数据库，也可以是一个单独的数据库，物理上必须是 DB2。

数据流 (dataflow)：通过对源文件或者源表的一系列转换步骤，从而加载或者更新数据到目标文件或者目标表中。任何 dataflow 都需要在一个 SEDB 上执行。

数据子流 (subflow)：dataflow 的一个子集，用来定义一些通用的数据转换操作，源和目标可以在 dataflow 中指定，并引用 subflow。

控制流 (controlflow)：用于序列化一个或多个 dataflow，并集成了如 FTP，Command，Email 等多种功能的流程，也可以定义分支 (branch) 和循环 (iterator)。

数据仓库应用 (application)：设计完成 controlflow 之后，为了部署到 DWE Admin Console 上而需要对 code 打包，application 正是打包之后生成的 zip 文件，一个 application 可以包含多个 control flow。

进程 (process)：部署到 DWE Admin Console 里的 application 的下一个级别，它对应于 design studio 的一个 control flow。

实例 (instance)：process 的每一个具体执行。

数据仓库 application 的产生和运行

数据仓库 application 从产生到运行到卸载有一个生命周期，要经过下述的步骤：

安装并建立设计和运行的环境：可以通过 DWE 安装介质来安装 SQW，其中 DB2 和 WAS 是必须的。

在 Design Studio 中设计、验证、运行和调试数据流。

产生可部署的应用包，做部署到 DWE Admin Console 的预备工作。

在 DWE Admin Console 中部署 Design Studio 生成的应用包。

在 DWE Admin Console 中运行、定时、管理和监控进程，以完成 ETL 过程。

如果应用包不再需要运行，可以卸载。

下图描述了 application 从产生到运行的整个过程。

图 3. application 从产生到运行的整个过程

从上图可以清晰的看出完成一个 application 经历了设计、部署预备、部署、运行和管理五个阶段。

在 DWE Design Studio 中建立 SQW 的设计环境

DWE Design Studio 是以 eclipse 为基础平台的开发的一套数据仓库设计工具。上面集成众多插件以支持数据仓库的设计开发。DWE Design Studio 通过 xmi 作为原数据模型，保存并解析用户的设计图形。其基本结构如下所示：

图 4：DWE Design Studio 基本结构图

Design Studio 同时也是一个集成的开发环境和元数据系统，它承担了下述的一些功能：

导入、建立物理模型。

建立、执行、调试数据流。

建立、执行、调试控制流。

打包控制流到可部署文件。

用户要在 Design Studio 中完成一个设计，需要遵循如下所示的基本工作流程：

图 5. 在 Design Studio 中的设计流程

1、　导入数据模型

数据模型代表某种具体的数据库结构，由表、视图、函数等数据库对象组成，是数据库的映射。数据模型是 SQW 的可操作对象。通过数据模型，SQW 可以获知预操作数据库的基本结构，并且根据数据模型，生成相应的 SQL 语句。用户可以修改数据模型以适应自己的需要，SQW 可以根据修改后的数据模型同步数据库结构。下图就是导入后的数据模型

图 6. 导入后的数据模型

2、　建立、执行、调试数据流

用户可以使用 DWE Design Studio，通过 GUI 的方式快速的设计出合适的数据流图，然后可以在 Design Studio 中执行修改数据流图，验证数据的逻辑走向是否符合用户的期望。同时，DWE Design Studio 为用户提供了生成代码的功能，生成后的代码是一种类 Java 的代码，由 SQL 语句、TRY/CATCH/FINALLY 语句、属性定义语句组成。SQW 执行解释器所能控制的最小控制单元叫 SQL 单元，每一个 SQL 单元也是由一组 SQL 语句、TRY/CATCH/FINALLY 语句、属性定义语句组成。SQW 执行解释器将这些 SQL 单元解释成 node，并且赋予唯一的名称。因此如果数据流在 DWE Design Studio 里执行错误或者返回了不正确的结果，用户就可以以 node 为最小单元来对该数据流进行调试，发现数据流问题所在。

清单 1. 生成的数据流代码

……
EPGTXN ( ) : type TXN : node /graph12
(db connection = [SALES])
{
( ):CODE_UNIT, node /graph12/node9;
CODE_UNIT:JDBC
( )
{
　　CREATE TABLE SQWTMP_1_INPUT2_017(
　　　 COL_1 DATE,
　　　 COL_2 VARCHAR(15),
　　　 COL_3 VARCHAR(15),
　　　 COL_4 INTEGER)
}
( ):CODE_UNIT, node /graph12/node12;
……

3、建立、执行、调试控制流

如前面的定义：数据流通过对源文件或者源表的一系列转换步骤，从而加载或者更新数据到目标文件或者目标表中。控制流则是用于序列化一个或多个 dataflow，并集成了如 FTP、Command、Email、循环控制、分支控制等多种功能的流程。与数据流不同的是，控制流所生成的代码并不包含 SQL 语句，但是控制流同样是由 node 组成，用户同样可以以 node 为最小单元来对控制流进行调试，发现控制流问题所在。

清单 2. 控制流生成的代码

……
CODE_UNIT:SHELL
(OperatorTag = /flow:06/op:02
OperatorLabel = DB2_Shell_02
Activity: = DB2_Shell_02
@RESOURCE =
@FILE = ${VMODVALVAR01/var_design}${VMODVALVAR01/var_deployprep}
　　　　　　　 ${VMODVALVAR01/var_deployment}${VMODVALVAR01/var_runtime}
　　　　　　　 ${VMODVALVAR01/var_execute}
@ARGUMENTS =
commandType = DB2Shell
logLevel = 0
traceLevel = 0
……

4、　打包控制流到可部署文件

控制流调试无误后，用户将需要使用 Design Studio 提供的“数据仓库应用打包向导”将该控制流打包成一个 zip 文件。至此，用户在 Design Studio 里的设计工作就全部完成。

在 DWE Admin Console 中建立 SQW 的生产环境

如果说刚才介绍的 DWE Design Studio 属于设计环境的话，那么现在要介绍的 DWE Admin Console 则属于 SQW 的生产环境。DWE Admin Console 本身属于 WebSphere 上的一个应用，通过与 WebSphere 和 DB2 的集成，DWE Admin Console 为用户提供了更好的安全性、稳定性和更快的执行力度。DWE Admin Console 可以部署在 Window、Linux、AIX、Solaris 等各种平台，并且为用户提供了功能强大的管理和统计功能。

图 7. DWE Admin Console 结构图

上图展示了 DWE Admin Console 与 WAS、DB2 的关系。其中用户请求通过浏览器将一个 HTTP 请求发送给 WAS 的 Http Server，经过 Http Server 解析后将强求转发给 DWE Admin Console, 之后 DWE Admin Console 调用 WAS 的 Scheduler、MBean、JNDI、Security 等服务，最后与 DB2 完成用户数据的查询或者持久化操作。

大家需要注意的是，DWE Admin Console 里有一些术语和 Design Studio 里是对应的，如果不仔细观察的话，容易产生混乱。对应关系如下表所示：

表 1. 对应关系表

DWE Design Studio	DWE Admin Console
Data Warehouse Applications	Deployed Applications
Control Flows	Processes
Control Flow Operators	Activities

其中应用的打包过程是在 DWE Design Studio 中完成，在 DWE Admin Console 里完成的运行、监控、日志操作都是针对 Processes 的。Activities 在 DWE Admin Console 是不能单独运行的，用户对 Activities 唯一能做的事情就是监控这些 Operators 的执行结果。

DWE Admin Console 是为用户提供了基于角色管理 (ACL) 的认证方式。用户可以属于以下三种角色：administrators、managers、operators。

administrators：创建数据源、系统资源和部署应用。

managers：运行监控 Processes，创建 Profiles，对 Processes 定时。

operators：运行监控 Processes，其权限低于 managers。

用户访问 DWE Admin Console 的 URL 地址是：http://ip:9080/dweadm，其界面如下所示：

图 8. DWE Admin Console 的界面结构

DWE Admin Console 非常简单易用，用户在 DWE Admin Console 里搭建生产环境的流程如下图所示：

图 9. DWE Admin Console 使用流程

首先用户登陆之后，需要建立相应的数据源和系统资源（系统资源分为 FTP Server 和 DataStage Server 两种），之后部署在 Design Studio 中打包的 zip 文件，在部署过程中需要绑定相应的数据源和系统资源，需要指定变量值。在部署好后，在 DWE Admin Console 会生成一个应用，用户可以运行这个应用里包含的 Processes，可以对这个应用所包含的 Processes 定时。如果执行失败，用户不但可以查看日志，而且还可以指定日志级别，对某个 Process 进行 trace。系统会记录用户所有的部署 / 卸载纪录，同时，系统还会记录下用户所有的执行记录和执行的详细信息，这些信息对于用户分析执行结果、执行效率也都非常有用。