骆洪青 ( hq_l@tom.com), 软件事业部总监, 北京银信长远科技有限公司
2008 年 8 月 29 日 本文主要介绍了如何使用DWE 提供的SQL Warehouse工具中的数据流和控制流填充数据仓库和数据集市的事实表和维表。DWE这一功能为数据库内的ETL设计提供了一种简单、实用的方法。
简介
DB2 Data Warehouse Edition (DWE) 产品为随需应变（ On Demand ）商务智能业务提供了综合的基础平台，它将 IBM 一系列的商务智能产品无缝地结合在一起，使得用户可以方便地搭建下一代数据仓库。Design Studio 是 DWE 产品的 IDE （ Integrated Development Environment ）界面，它包含了 SQL Warehoure 工具集插件。
SQL Warehoure 工具集解决了 DB2 数据仓库环境下的数据集成问题，用户可以构建代表数据流动的高层逻辑模型，并集成到执行计划中。 SQL Warehoure 工具提供了元数据系统和 IDE 环境创建、编辑、管理这些流，并可以把这些流生成用于执行的优化代码。当流的开发完成后，用户可以把产生的代码以及相关制品打包成一个数据仓库应用部署到不同的运行环境下。
数据流是指用于替换硬编码的 SQL 语句一套图形化 SQL 产生工具，这些工具方便了数据仓库的维护和管理。这些流产生的 SQL 语句是以 DB2 特定 SQL 语法作为基础的，主要针对 DB2 数据库，同时也支持 JDBC 连接的数据库。控制流是为一个或多个数据流提供了一个运行容器的模型，可以被打包成数据仓库应用部署到 DWE 运行环境中，而数据流以及 SQL Warehouse 提供的另一种流――挖掘流本身是不能直接被部署的。
基于 SQW 的开发过程
SQL Warehouse Tool（SQW）主要由开发环境和运行环境两大部分组成，包括 DWE Design Studio、DB2 SQL 执行引擎、WAS DIS 执行器和执行计划（EPG）等组件。
图 1. SQW 组件
一个典型的基于 SQW 的开发过程如下：
-
安装、建立设计和运行环境
-
设计和验证数据流
-
测试数据流
-
设计和验证控制流
-
测试控制流
-
为部署准备控制流应用程序
-
从管理控制台部署应用
-
根据源数据库数据变化情况进行，定期运行应用。
数据仓库应用部署与普通的 J2EE 应用在 WebSphere 上部署基本类似，因此本文重点关注数据流和控制流的设计。
在准备数据仓库运行环境时首先需要创建一个数据设计项目。数据设计项目包含目标或者源数据库的物理数据模型，当然这些物理数据模型可以通过从现有数据库反向工程获得或者通过 DDL 脚本获得。数据设计项目被链接到数据仓库项目中，为数据仓库项目提供源表和目标表，因此数据设计项目是数据仓库项目的必要条件。
数据库资源管理器为数据设计项目提供数据库连通性的相关信息，如目标或源数据库的主机地址、用户名、密码等。所有的数据设计项目可以共享数据库资源管理器下已经建立连接的数据库。因此准备环境时第一步就是建立一个数据库连接。DWE Design Studio 提供了 JDBC 方式连接到各个数据库。在创建数据设计项目时选择合适的数据库连接，并对数据库进行反向工程即可得到数据库的物理模式信息。在建立好数据设计项目后，我们就可以创建数据仓库项目了。数据仓库项目是一个容器，我们可以在其中定义挖掘流、数据流、控制流、子流、应用概要文件、资源概要文件等，本文重点关注数据流和控制流设计。
图 2. 数据库连接、数据设计项目、数据仓库项目相互关系
数据流设计
数据流是一个代表了数据移动和变换过程的模型，数据从源文件或者源表流过一系列变化步骤，被填充到目标文件或者表中。
图 3. 一个数据流例子
图 3 为一个数据流的例子，从 DB2 数据登台表中选择数据，消除重复并进行排序后被插入到另一张 DB2 数据表中，丢弃的重复记录被放置到一个平面文件中。
数据流由多个运算符组成，如上图即有 5 个运算符，运算符分为源、目标和变化三类。源运算符和目标运算符通常是指一个表或者一个平面文件，表可以为 DB2 数据表也可以为其他支持 JDBC 连接的关系数据库表。变换运算符是将源运算符代表的数据按照一定的变化规则移动到目标运算符代表的数据中。变化运算符可以是基本的 SQL 语句，如 Sort、Group By 等，也可以是复杂的 OLAP 旋转或者是渐变维更新等。
SQW 中源运算符包括表源运算符（ Table Source ）、导入文件运算符（ File Import ），目标运算符包括目标表运算符（ Table Target ）、文件导出运算符（ File Export ）、成批装入目标运算符（ Bulk Load Target ）等。表类运算符可以是 DB2 的本地表，也可以是通过 JDBC 程序连接获得的表。文件运算符主要用来与操作系统平面文件交换数据。数据流变化运算符主要包括选择列表（ Select list ）、相异（ Distinct ）、Where 条件（ Where condition ）、表连接（ Table join ）、分组（ Group by ）、排序（ Order by ）、并集（ Union ）等。这些变换运算符主要是对源表中的数据做各种基于 SQL 的变化。其他运算符如查找键（ Key lookup ）、替换事实键（ Fact key replace ）、旋转（ Pivot ）、分割器（ Splitter ）、数据站（ Data station ）等则较为复杂，主要用于对表进行非常规的变换。
每个运算符都需要有数据作为输入，有数据作为输出，这是通过运算符的端口实现的。数据从一个运算符的输入端口流入，并再从其输出端口流出。当一个运算符的输出端口被连接到另一个运算符的输入端口时，就形成了数据流。有些运算符支持变化的端口，用户可以自己定义需要的输入端口或输出端口。连接两个运算符的输入 / 输出端口的线被称为连接器。
下面我们以一个零售店例子演示数据流的创建过程。在例子数据仓库中，我们需要从两个平面文件中将交易数据导入到表交易项（ ITM_TXN ），并且要求导入过程中消除重复的行。同时，事实表中的所有数据外键都必须在产品表（ PD ）和交易订单（ MKT_BSKT_TXN ）中。数据物理模型如图 4 所示。
图 4. 零售店物理数据模型
我们把这个数据变化分为两个部分，第一部分以数据站运算符结尾，该运算符表示数据流中的登台点。在该位置，将并集运算符的结果存储到 DWESAMP 数据库的持久表中。这个登台表代表数据流中的可靠恢复点。第二部分通过另一系列的变换来从数据站移动数据，并最终装入 ITM_TXN 表中。
图 5. 零售店数据流（部分一）
图 5 中，数据从两个导入文件运算符中输入，即从操作系统平面文件中获取数据源。两个相异运算符用于消除平面文件中的重复行，被废弃的行输出到导出文件运算符中，这类似于 SQL 语句的 Distinct 操作。在相异运算符中，我们指定按照 MKT_BSKT_TXN_ID、PD_ID、ITM_TXN_TMS 列消除重复数据。在导入文件运算符的文件名处，我们指定为固定值，而在导出文件运算符的文件名处我们使用了变量。通过为这些资源设置变量，可以将某些属性的定义工作推迟到应用程序生命周期的以后阶段进行。例如，我们可能不知道在运行时要使用的数据库模式，也可能想构建在不同环境中运行的更为灵活的数据流等。
两个相异运算符的正常结果被作为一个并集操作输入。并集操作支持 SQL 语句中的 UNION、INTERSECTION、DIFFERENCE、UNION_ALL、INTERSECTION_ALL、DIFFERENCE_ALL 等集合操作。在本例中，我们采用的是 UNION 操作，即对两个相异运算符的结果取并集同时消除重复行。
并集的结果被作为数据站运算符的输入。数据站运算符代表着数据流中的一个登台点，数据登台点为跟踪、调试或者恢复保存中间数据，在作为检查点或者数据恢复时特别有用。数据站支持的数据保存类型永久表、临时表、视图和文件。数据站可以设置“通过”特性，这样我们在设计阶段用数据站检查数据流的正确性，而在生产阶段为减轻压力，直接让数据流过数据站而不保存中间结果。
图 6. 零售店数据流（部分二）
数据流的第二部分是通过查找键运算符对第一部分数据站运算符的结果进行过滤。查找键运算符使用多个查找表的键值去与一个源表的键值进行匹配，类似与 SQL 语句中的 IN 操作，这里使用的键值条件为：
清单 1. 键值条件
DATA_016.PD_ID = LOOKUP_016.PD_ID
DATA_016.MKT_BSKT_TXN_ID = LOOKUP1_016.MKT_BSKT_TXN_ID
|
其中， DATA_016 查找键运算符数据端口的虚拟表名，LOOKUP_016 是查找键运算符查询端口的虚拟表名，LOOKUP1_016 LOOKUP_016 是查找键运算符查询端口 1 的虚拟表名。本例中对不满足要求的行输出到文件中，匹配的行输出到成批导入目标运算符中。成批导入运算符接受外部输入，采用类似 DB2 Load 命令将输入导入到目标表中。在查找键运算符中，我们通过输出选择列表控制流入到目标表中的数据列如下：
图 7. 选择目标表数据列
控制流设计
控制流是顺序化一个或多个数据流并使得他们与其他数据活动集成在一起的容器。可以部署的 J2EE 数据仓库应用也依赖于控制流进行构建，不能直接在数据流上进行构建。控制流更关注于数据流动过程中的异常处理。控制流的运算符代表了组成大型商业活动的个体活动，与数据流类似，控制流运算符也存在着输入、输出、和连接。控制流开始于启动运算符，结束于停止运算符。一个控制流只有一个启动运算符，可以有多个停止运算符。控制流运算符一般只一个输入端口，表示该活动的开始，有三个输出端口，分别表示该活动成功结束、失败结束、无条件结束，上面两种结束情景都会触发无条件结束。
启动运算符没有输入，有三个输出，包括启动进程输出、失败时处理进程输出、清除进程输出。当控制流中的任何一个活动失败，都会触发失败时处理进程；当主分支处理结束或者错误分支处理结束时，都会触发清除进程。停止进程是可选的，只有一个输入。
图 8. 启动与停止运算符
控制流可以包括 SQW 流运算符如数据流和挖掘流，命令运算符、控制运算符、电子邮件运算符以及 DataStage 运算符。命令运算符可以是 OS 脚本、DB2 脚本、FTP 命令或者是刻执行文件，由应用程序负责调用。控制运算符可以迭代器、跳出、继续以及文件等待等，主要用于流内部的流程控制。DataStage 运算符包含 DataStage 并行任务运算符和任务序列运算符。
下面我们将上面设计的数据流 test 包装到控制流 test 中，并且允许循环运行。
图 9. test 控制流
如上图所示，控制流中有一个迭代器运算符用于循环控制数据流 _08 的多次运行。数据流 _08 指向刚刚设计的数据流 test 。迭代器的循环条件采用变量固定次数形式，当循环结束结束条件到达后迭代器 04 将控制权交给结束迭代器运算符。迭代器运算符还支持从文件中输入多行以分割符判断循环是否结束，也支持对某个目录中每个文件进行循环一次，这样在 ETL 抽取时就非常方便对抽取次数进行控制。我们在迭代器循环条件输入文件中填入需要处理的文件名称即可。
构建、部署应用
对于要在 WebSphere 环境中运行和管理的所有仓库构建任务来说，都需要进行部署准备。部署准备过程需要使用数据仓库应用程序，后者基于数据仓库项目并包含一个或多个控制流。在选择所需的控制流之后，先生成代码，然后将结果打包成 zip 文件，于是就可以将该 zip 文件部署到 WebSphere Application Server 了。数据仓库应用的部署与普通 WebSphere 应用部署类似，都需要先创建应用需要的资源和变量，本文不再赘述。
结束语
IBM DWE 的 SQW 组件为在数据库环境内进行 ETL 提供了丰富的操作符。使用这些操作符，开发人员可以避免进行 SQ L 的硬编码，大大提高了开发的效率。最终的 ETL 过程以数据流、控制流的形式体现，部署在 WebSphere 环境下，并可以按需调度运行。
参考资料
关于作者  | | | 骆洪青，北京银信长远科技有限公司软件事业部总监，主要从事数据库仓库，ETL 等相关技术。 |
对本文的评价
| 
