使用 WebSphere DataStage 和 WebSphere Federation Server 的一种灵活数据集成架构

T-ETL 架构简介

将 WebSphere® DataStage 与 WebSphere Federation Server 相结合,为移动和转换数据提供一种有效而灵活的架构。本文提出使用 WebSphere Federation Server 作为 DataStage 的 “数据预处理器”,并演示了这种组合减少作业运行时间(最高达 91%)和总体资源消耗的场景。本文并没有提倡将 Federation Server 与 DataStage 的组合用于所有数据整合场景,而是试图总结出能从这对组合获得最大收益的场景所具备的特征。

Simon Harris (simond_harris@uk.ibm.com), 联邦数据库性能工程师, IBM

Simon Harris 是硅谷实验室 WebSphere Federation Server 开发小组的一名性能工程师。从 1995 年联邦数据库技术刚刚在 IBM 起步开始,Simon 就一直从事这方面的工作,负责为欧洲、中东和非洲的很多客户提供售前和售后支持。



Susanne Englert (senglert@us.ibm.com), 高级软件工程师, IBM

Susanne Englert 是 Websphere Federation Server 性能小组成员之一,从 2001 年开始就从事与该产品相关的工作。她在数据库性能领域具有丰富的经验,尤其对查询优化、并行查询处理、联邦查询和客户用例很感兴趣。Susanne 毕业于波恩大学,曾担任 TPC Decision Support 基准开发小组的委员会主席。



2007 年 8 月 16 日

简介

在传统数据整合市场中,很多供应商要么将他们的产品定位为 Extract-Transform-Load (ETL) 工具,要么定位为 Extract-Load-Transform (ELT) 工具,甚至还将其产品定位为 Transform-Extract-Load (TEL) 工具。 很自然地,每个供应商都吹捧他们采用的方法功能多么强大,同时也强调竞争对手采用的方法固有的弱点。

那么,哪种方法是最好的?实际上,所有方法都有其优缺点,大多数公司很可能会发现需要将这些技术组合起来使用。因此,对于 ETL、ELT 和 TEL 这道字母汤而言,真正的关键在于灵活性,并且能够支持最适合所处理工作的技术。仅仅因为现有的工具不能充分支持一个过程,而将本来适合 ETL 架构的数据流建模到 ELT 架构中,这是导致灾难性后果的一个原因。

WebSphere DataStage 数据整合工具天生就具有很好的灵活性,可以为 ETL、ELT 和 TEL 拓扑提供固有支持。本文展示如何通过组合 WebSphere DataStage 和 WebSphere Federation Server,有效地支持 Transform-Extract-Transform-Load (T-ETL) 数据整合拓扑,以扩展上述字母汤。在 T-ETL 拓扑中,WebSphere DataStage 和 WebSphere Federation Server 相互补充,相对于单独使用 WebSphere DataStage,这种组合可以显著提高性能,节省 CPU 周期。在这个场景中,WebSphere Federation Server 可以在输入源附近预先进行处理,从而减少提供给提取阶段的数据,并减少 WebSphere DataStage 需要执行的转换。之所以能获得这样的优点,是因为 T-ETL 架构恰好能充分发挥这两种产品的长处;WebSphere Federation Server 的长处在于基于成本的优化器,以及异构环境中集合处理的效率,而 WebSphere DataStage 的优点在于强大的并行转换和数据流引擎。

在更详细地描述 T-ETL 架构之前,本文接下来的小节将简要地对 WebSphere Federation Server 作一个介绍。随后的用例场景小节详细描述能突出 T-ETL 的优点的 4 种不同用例。 接着,本文总结有望从这种架构中获益的 WebSphere DataStage 作业的一些特征。

WebSphere Federation Server

WebSphere Federation Server 支持业界新兴的 Enterprise Information Integration (EII) 概念。这种技术使应用程序能够访问和集成不同的数据和内容源,无论这些信息位于何处,它们看上去就像是一个资源,但同时又能保持源系统的自治和完整性。

联邦的底层原理是,对于用户而言,他们使用的所有数据看上去是在一个数据源中。通过呈现这个单独的源镜像,联邦技术使数据请求者不必直面与访问不同位置的数据相关的所有复杂性,包括连接、语义、格式和访问方法。中间件使用户或代表用户的应用程序可以透明地访问信息,而不必关心其物理实现。 因此,WebSphere Federation Server 非常适合作为常见分析和报告工具、开发环境门户和其它标准 IT 基础设施组件的幕后工具。

通过 WebSphere Federation Server,可以在一条 SQL 语句中将分布式请求发送到多个数据源。例如,可以在一条 SQL 语句中连接一个 DB2 表、一个 Oracle 表和一个 XML 标记文件中的数据。当应用程序向联邦系统提交一个查询时,联邦服务器识别相关数据源,并生成一个用于获得被请求数据的查询执行计划。查询执行计划通常将原始查询拆分成多个片段,这些片段表示委派到各个数据源的作业,同时还提供联邦服务器要执行的其它处理,包括进一步的过滤、聚合或合并数据。即使某些被请求的信息来自具有很少或不具有查询处理能力的数据源,例如简单的文本文件,联邦服务器将进一步处理从数据源收到的数据,这种能力使应用程序可以充分利用查询语言的威力。除了管理联邦以外,联邦服务器还是一个功能完整的关系数据库,具有存储和管理本地数据的能力。

总而言之,WebSphere Federation Server 的功能包括:

  • 整合来自本地表和远程数据源的数据,就好像这些数据是本地存储在联邦数据库中。
  • 更新关系数据源中的数据,就好像数据存储在联邦数据库中一样。
  • 将分布式请求发送到数据源进行处理,利用数据源的处理能力和特有的优化能力。
  • 在联邦服务器上处理一部分分布式请求,弥补 SQL 在数据源上的限制。

实现 EII 的联邦方法已经可以与更传统的数据整合方法相媲美。整合的数据存储通常用于提取、转换、装载(ETL)或复制数据,是当今信息集成的标准选择,已经成为高可用性的能够快速获取、集成访问相关信息的最佳方法。通过创建单个物理拷贝,企业可以满足性能或可用性需求,交付时间点一致的快照,并为语义一致性提供完善的转换。

联邦可以帮助 IT 部门快速地开发原型和改进转换,访问最新的数据,交付有附加价值的、内容丰富的信息,例如不能复制的文档和图像,并提供对不能整合的数据(例如由于兼容性原因)的访问,从而有效地对业务做出响应。在当今快节奏的环境中,通过将数据整合与联邦相结合,企业可以获得更大的灵活性,可以更快地作出反应。

使用 WebSphere DataStage 和 WebSphere Federation Server 的 T-ETL 架构

本文着重描述组合使用 WebSphere DataStage 和 WebSphere Federation Server 执行数据整合的优点。这种组合使 WebSphere Federation Server 成为 WebSphere DataStage 的 数据预处理器,在从一个或多个数据源提取数据之际,或在此之前,实际上就在执行初始的转换。在数据进入 WebSphere DataStage 之前,T-ETL 架构使用联邦来连接、聚合和过滤数据,并由 WebSphere DataStage 使用其并行引擎执行更复杂的转换和目标的维护。图 1 说明了这种架构:

图 1. 使用 WebSphere Federation Server 和 WebSphere DataStage 的 T-ETL 架构
使用 WebSphere Federation Server 和 WebSphere DataStage 的 T-ETL 架构

该架构充分利用这两种产品各自的优点,形成了一种灵活、高效的数据整合解决方案。WebSphere Federation Server 的优点在于连接能力和 SQL 处理能力,而 WebSphere DataStage 的优点在于并行数据流和强大的转换逻辑。WebSphere Federation Server 的基于成本的优化器还允许 T- ETL 架构动态地对数据量和数据模式的变化作出反应,而不需要修改作业。

T-ETL 不是一个新概念,在提取数据时,很多 ETL 作业可能已经采用了某种形式的转换 —— 例如过滤和聚合数据,或者执行位于同一个源数据库中的两个源表之间的连接。但是,源对象必须在同一个数据源的约束严重地限制了 T-ETL 解决方案的适用范围。WebSphere Federation Server 消除了这一限制,并将初始的转换阶段的范围扩展到 WebSphere Federation Server 所支持的异构数据源。例如,当源数据是一个 Oracle 表、一个 Teradata 表和一个平面文件时,WebSphere Federation Server 允许实现 T-ETL。除了扩展原有转换阶段的范围外,联邦还可以提高各阶段 的效率,因为其核心是在有效过滤和连接数据集方面有 30 多年投资的关系数据库引擎。

突出 T-ETL 优点的用例

以下四个用例场景是为突出使用 WebSphere DataStage 和 WebSphere Federation Server 整合数据的潜在优点而设计的。 在每个案例中,首先给出使用 WebSphere DataStage 作业的数据整合场景,进而展示如何将 WebSphere Federation Server 与 WebSphere DataStage 相结合,以减少执行时间和资源消耗。并且还展示如何修改原始的 WebSphere DataStage 作业,以利用 WebSphere Federation Server 的功能。本节的最后总结能从这种优化中受益的 WebSphere DataStage 作业应有的特点。

图 2 显示了用于测试这些用例场景的配置。

图 2. 用于用例场景的配置
用于用例场景的配置

取决于作业的配置,数据可能源自很多不同的 UNIX 系统。同样,目标也可能在一个或多个 UNIX 系统上。DB2 UDB API WebSphere DataStage stage 用于访问 DB2 源、目标和 WebSphere Federation Server。所有源和目标数据库都是非分区数据库。IBM Information Server(包括 WebSphere DataStage Enterprise Edition V8.0 和 WebSphere Federation Server V9.0)安装在一台双 CPU 的 Windows Server 2003 机器上。 在这四个用例场景中,每个作业都被设计为并行作业,以便充分利用 WebSphere DataStage 的并行处理能力。由于 WebSphere DataStage 服务器上有 2 个 CPU,因此使用的并行度为 2。

这些用例引用一家虚构的零件交付公司的一些表。 取决于具体的场景,这些表在物理上位于一个或多个源系统:

  • CUSTOMER 表,每个不同的客户键对应一行。每行包含客户的姓名、负债数、属于哪个市场等信息。
  • ORDERS 表,每个不同的订单键对应一行。每行包含下订单的客户键、订单的总价值、下订单的日期、描述订单优先次序的代码。通常,数据库中的有些客户有数个订单,而有些客户没有订单,或者很久都没有下订单。
  • LINEITEM 表,一个订单中的每样商品对应一行。每行包含它所属订单的订单键。通常,一个订单会包含数行内容。每行内容引用一个特定的零件键,并包括所定购的数量,零件的发货日期,以及使用的发货方式。
  • STOCK 表,将零件键与供应商键相链接,跟踪每家供应商手头上的零件数量。

案例 1:多个异构源

ProjectedBalance 作业根据 CUSTOMER 表中最近记录的客户负债数,以及最近 30 天客户下的订单的价值(记录在 ORDERS 表中)总和,计算客户的当前负债数。然后,该作业列出客户和计划负债数,按客户负债情况从多到少排序。如今,在很多企业中,客户信息与交易订单信息通常位于不同的数据库中。

考虑下面的 WebSphere DataStage 作业,该作业实现 ProjectedBalance 计算:

图 3. ProjectedBalance WebSphere DataStage 作业
ProjectedBalance DataStage 作业

图 3 中的 ProjectedBalance 作业从 ORDERS 表中获取上个月的订单,并为每个客户键计算订单总价值。然后,将该信息与 CUSTOMER 表连接,以提取客户姓名、地址和当前负债数。将客户的当前负债与客户在上个月下的订单的总价值相加,算出计划负债。最后,将这些记录(大约有 70,000 条)插入到 ProjectedBalance 表中,或者在将它们输出到平面文件之前对它们进行排序(以确保负债最多的客户排在报告的前面)。

图 3 中的 WebSphere DataStage 作业使用一个典型的 ETL 过程,以一种结构化的方式获取所需的结果。但是,在这个案例(以及其它类似的案例)中,通过将 WebSphere Federation Server 和 WebSphere DataStage 相结合,并对作业作细微的修改,就可以更高效地获得相同的结果。通过使用 WebSphere Federation Server 作为 WebSphere DataStage 的数据预处理器,将 WebSphere DataStage 作业的某些功能交给联邦数据库引擎处理,可以显著地减少作业的执行时间和占用的 CPU 时间。

考虑下面这个等效的 ProjectedBalance DataStage 作业:

图 4. 使用联邦的 ProjectedBalance WebSphere DataStage 作业
使用联邦的 ProjectedBalance DataStage 作业

图 4 中的作业可以与图 3 中原始的 ProjectedBalance 作业获得相同的结果,但是 CUSTOMER 与 ORDERS 之间的连接,以及总订单价值的聚合,已经被放入单个阶段(Join_CustOrds)中,并且下推到 WebSphere Federation Server 处理,而在那里处理起来更高效。作业中的其它阶段保持不变。

为了将连接下推到 WebSphere Federation Server,Orders、AggOrdersByCustomer、 Customer 和 Join_CustOrds 阶段被重新写成执行等效功能的 SQL。为便于编写和理解,可以使用 SQL Common Table Expressions (CTE) 来逐个处理各阶段并分解 SQL。例如,图 5 中显示的 SQL 被使用 CTE 分成 4 个便于管理的块 —— 每个块完全等价于 WebSphere DataStage 阶段中它所替代的块。实际上,Customer 和 Orders CTE 使用的 SQL 与原始作业的 CUSTOMER 和 ORDERS stage 中使用的 SQL 相同。 以这种方式使用 CTE 可以大大简化用 SQL 表达 WebSphere DataStage stage 的过程,因为它允许开发人员分别考虑每个阶段,逐个地进行转换。

图 5. ProjectedBalance 作业被下推到 WebSphere Federation Server 的 SQL
ProjectedBalance 作业被下推到 WebSphere Federation Server 的 SQL

当图 4 中的 ProjectedBalance 作业执行时,Join_CustOrds 阶段连接到联邦数据库,该阶段中的 SQL 被传递到 WebSphere Federation Server。WebSphere Federation Server 使用它的基于成本的优化器确定连接数据的最高效的方法。大部分节省下来的执行时间和 CPU 时间要归功于最佳执行计划的选择,以及功能完整的 DB2 关系数据库引擎对数据集的有效的处理。联邦数据库处理完 Join_CustOrds 阶段中的 SQL 之后,WebSphere DataStage 使用获得的数据,作业的处理继续进行。 第二个显著的性能优点是,WebSphere Federation Server 执行的初始连接 “减少” 了;也就是说,它输出的数据远远少于从源数据读取的数据。这意味着当与 WebSphere Federation Server 结合使用时,WebSphere DataStage 读取的数据(70,663 行)要少于在原先的实现中所读取的数据(240 万 + 77,636 行)。

昵称

由于使用 WebSphere Federation Server 连接 Oracle 和 DB2 数据,图 5 中的 SIMON.CUSTOMER 和 SIMON.ORDERS 实际上是引用联邦数据库中定义的昵称。而这些昵称又指向数据源中的表。

在数据到达 WebSphere DataStage 之前,使用 WebSphere Federation Server 对数据执行某种预处理,在这样做的过程中,就已经有效地采用了 T- ETL 方法;这说明了 WebSphere DataStage 和 WebSphere Federation Server 这对组合的灵活性。

图 3 中显示的原始 ProjectedBalance 作业的执行时间为 204 秒。而图 4 中显示的 T-ETL 作业的执行时间只有 127 秒 —— 总体执行时间缩短了 38% 之多。WebSphere DataStage 和 WebSphere Federation Server 机器使用的 CPU 资源同样有所减少,并且数据源上的 CPU 消耗并没有显著增加。因此,在这个案例中,当将 WebSphere DataStage 与 WebSphere Federation Server 结合使用时,与单独使用 WebSphere DataStage 相比,作业的执行时间和 CPU 消耗减少了 38%。

案例 2:一个典型的 ELT 场景

OrderPriority 作业生成由 'Building' 市场的客户所下的未完成订单的列表。该列表根据订单中未完成内容的未付款收益和下订单的日期排序。图 6 显示了 OrderPriority 作业的 WebSphere DataStage 实现。

图 6. WebSphere DataStage OrderPriority 作业
DataStage OrderPriority 作业

OrderPriority 作业首先提取 'Building' 市场客户的客户键,然后从 ORDERS 表中提取关于他们所下的订单的信息。然后,从 LINEITEM 表中提取关于订单中尚未发货的各行项目的信息。接着通过 LineitemRevenue stage 计算每个未发货的行项目的收益,将它们相加求和,确定每个订单的未付款收益。然后对订单进行排序,首先按未付款收益排序,然后按订单日期排序。最后,将这些数据(大约 45,000 条记录)插入到 ORDERPRIORITY 表中。

图 6 中显示的 OrderPriority 作业也可以使用 ELT 方法取得所需的结果。这种机制首先从源系统提取 DB2 LINEITEM 和 ORDERS 数据以及 Oracle CUSTOMER 数据,然后将它们装载到保存目标表的 SQL Server 数据库中。在将数据装载到 SQL Server 数据库之后,可以使用 SQL 执行转换和装载结果表。但是,在使用 ELT 方法时,该作业会将大大超过所需的数据装载到 SQL Server 数据库中 —— 而它实际上只需装载 WebSphere DataStage 作业从源提取的等量的数据。将数据装载到目标数据库之后,还会失去源数据库上可能提供的索引或其它优化。因此,这样的查询很可能会消耗超过实际需要的资源。

图 7 显示了使用 WebSphere Federation Server 实现 T-ETL 方法的 OrderPriority 作业:

图 7. 使用 WebSphere DataStage 和 WebSphere Federation Server 的 OrderPriority 作业
使用 WebSphere DataStage 和 WebSphere Federation Server 的 OrderPriority 作业

原先的 OrderPriority 作业中 CUSTOMER、ORDERS 和 LINEITEM 之间的三方连接现在被下推到 WebSphere Federation Server 中执行。同样,这里使用 CTE 将原始作业的 stage 转换成 SQL。图 8 中显示了 SQL:

图 8. OrderPriority 作业被下推到 WebSphere Federation Server 的 SQL
OrderPriority 作业被下推到 WebSphere Federation Server 的 SQL

用于 Customer、Orders 和 Lineitem CTE 的 SQL 与原始 OrderPriority 作业中的 SQL 相同。作业中的所有其它 stage 保持不变。

如果没有 WebSphere Federation Server,就无法在一条 SQL 语句中处理 CUSTOMER、ORDERS 和 LINEITEM 之间的三方连接,因为数据在两个异构的源中。大多数其它 ETL 产品都只能将连接下推到单个同构的数据源(或目标),正是由于这个原因,这些产品采用 ELT 策略 —— 在执行 SQL 处理之前,从多个源提取数据,然后将数据装载到一个目标。WebSphere DataStage 与 WebSphere Federation Server 的灵活组合使 ETL 开发人员可以选择最适当的方式来达到他们的数据整合目的 —— 而不必限于一种特定的 ETL 方法。

WebSphere Federation Server 用于连接 T-ETL OrderPriority 作业中的 Customer、Orders 和 Lineitem 表的连接顺序不同于 WebSphere DataStage 开发人员在原始作业中使用的顺序。WebSphere Federation Server 决定将 Orders 首先与 Lineitem 连接,然后将该连接下推到远程 DB2 服务器,在那里它可以更高效地执行。 在将 Orders 和 Lineitem 之间的连接下推到 DB2 服务器之后,从数据源提取的数据量就减少了。原始 OrderPriority 作业与 T-ETL 版本的 OrderPriority 作业在连接策略上的变化是 T-ETL 版本的作业更为有效的原因之一。

WebSphere Federation Server 基于成本的优化器

当该作业执行时,WebSphere Federation Server 根据已有的关于表的统计信息决定如何访问数据。 通过将选择最佳访问计划(以及连接策略)的责任交给 WebSphere Federated Server,WebSphere DataStage 开发人员在连接两个或多个数据集时,就不必自己确定最佳策略。

原始 OrderPriority WebSphere DataStage 作业执行时间为 70 分 45 秒(4,246 秒)。而使用 WebSphere Federation Server 作为数据预处理器 的 T-ETL 版本的作业则仅执行了 12 分 11 秒(731 秒),执行时间节省了大约 83%。

在提取数据并将数据发送到 WebSphere DataStage 时,让 WebSphere Federation Server 执行连接和转换通常可以使源数据库上提供的索引和其它优化得到充分的利用,而在原始方法中这是不可能的。 WebSphere Federation Server 使用它的基于成本的优化器决定执行多源查询的最有效的方式。这意味着下推相同数据源上的表之间的连接,在适用的情况下自动使用探测类型查找,在查询执行时,根据已有的索引和统计信息使用最有效的连接技术。如果 WebSphere Federation Server 执行的这些早期的连接和转换也减少了(即输出的数据少于读取的数据),那么就可以减少提供给 WebSphere DataStage 作进一步转换的数据。因此,当采用组合实现时,WebSphere DataStage 需要处理的数据大大减少了,总体性能随之上升。例如,在 OrderPriority 作业中,WebSphere DataStage 从 OrderDetails stage 读取 119,642 行,这正是 WebSphere Federation Server 执行的连接输出。而在不使用 WebSphere Federation Server 的原始 OrderPriority 实现中,WebSphere DataStage 需要从 DB2 和 Oracle 源中读取超过 5200 万行数据。

案例 3:源和目标是相同的数据库

ShipPriority 作业分析以某些发货方式发货的订单的优先顺序。该作业计算在最近 6 个月的时间里,一般或高优先级订单中通过邮寄或海运发货的行项目的数量,并将这些数量按订单优先级排序。该作业演示了源和目标是相同数据库、且作业中执行的转换非常简单这样一个很典型的场景。图 9 显示了用于 ShipPriority 作业的 WebSphere DataStage 作业。

图 9. ShipPriority WebSphere DataStage 作业
ShipPriority DataStage 作业

一开始,从 LINEITEM 表中提取规定时期内通过邮寄或海运发货的行项目所对应的订单号。然后在 ORDERS 表中查找这些订单键,确定行项目所属订单的优先级。该作业使用很小的 Lookup stage(GetOrderInfo)来探测 ORDERS。提取到与每个符合条件的行项目相关联的订单的优先级之后,计算行项目的数量,并按订单优先级和行项目的发货方式列出这些数字。最后,将这些数据插入到 ORDERS 和 LINEITEM 源表所在的同一个 Oracle 数据库中的 SHIP_PRIORITY 表中。该作业的输出是紧急或一般订单中通过邮寄或海运发货的行项目的数量,如下所示:

L_SHIPMODE URGENT_COUNT NORMAL_COUNT 
---------- ------------ ------------ 
MAIL               2079         3159 
SHIP               2150         3171

这种类型的作业有一个特点,即源和目标在相同的数据库中,且执行的转换并不复杂,这种作业非常适合重新将作业编写成一条可以完全在数据库引擎中执行的 SQL 语句。

图 10 中显示的 ShipPriority DataStage 作业演示了那样的重写,原始作业的所有逻辑都被整合到一个 DB2 stage (ShippingPriority)中,并以 SQL 表达。ShippingPriority stage 实际上连接到 WebSphere Federation Server,后者使用昵称来与 Oracle 会话。

图 10. 使用 WebSphere DataStage 和 WebSphere Federation Server 的 ShipPriority 作业
使用 WebSphere DataStage 和 WebSphere Federation Server 的 ShipPriority 作业

RowGenerator stage

之所以要包括图 10 中的 DummyRowGenerator stage,是因为一个 WebSphere DataStage 作业必须包含多于一个 stage 才能执行。这个例子中使用 RowGenerator stage 生成一个值(这个值后面将被丢弃)。

在这个例子中,WebSphere Federation Server 执行作业所需的所有处理。由于源和目标在相同的数据库中,所以这个例子并不是一定需要使用联邦。可以将 SQL 直接传递到 Oracle 数据库。但是,以这种方式继续使用 WebSphere Federation Server 可以使 WebSphere DataStage 开发人员获得一致性,并且容易修改源和目标,而不必改变 WebSphere DataStage 作业。在这个例子中,与直接和 Oracle 交互相比,WebSphere Federation Server 的开销更小,因为它只需提取 ShippingPriority stage 中的 INSERT..SELECT 语句,然后直接将其传递给 Oracle。

图 11 显示了 ShippingPriority stage 中使用的 SQL:

图 11. ShipPriority 作业被下推到 WebSphere Federation Server 的 SQL
ShipPriority 作业被下推到 WebSphere Federation Server 的 SQL

同样,SQL 被使用 CTE 拆分成易于理解的几个小块;每个小块代表原始的 WebSphere DataStage 作业的一个特定的 stage。以上显示的 LineItem 和 OrdersLookup CTE 使用原始 WebSphere DataStage 所使用的相同的 SQL 从源表提取信息。

当新版本的 ShipPriority 作业执行时,WebSphere DataStage 连接到联邦数据库,并发出 INSERT..SELECT 语句。如图 11 所示。 联邦数据库提取 SQL 语句,将它转换成有效的 Oracle 语法,并传递到 Oracle 数据库上进行处理。Oracle 数据库执行查询,并在返回前将行插入到 SHIP_PRIORITY 表中。

在将作业重写成 SQL 的过程中,就有效地将作业从典型的 ETL 作业转换成所有处理都在数据库引擎中进行的作业。虽然这是达到目标的一种有效的方式,但是这种方法只能用于源和目标在相同数据库中,且转换可以用 SQL 表达的那些作业。

在测试系统上,原始的 ShipPriority 作业执行了 68 秒。而重写的使用 WebSphere DataStage 和 WebSphere Federation Server 的作业则仅仅执行了 6 秒,总执行时间缩短了超过 91%。此外,对于重写后的作业,承载 WebSphere DataStage 和 WebSphere Federation Server 的机器上的 CPU 的消耗也大大降低,Oracle 数据源上的总体 CPU 消耗减少了大约 47%。Oracle 服务器上 CPU 消耗的减少是因为新的作业能够更好地利用 Oracle 数据库中的优化来执行查询。在其它情况中,将处理下推到数据库可能会导致数据库服务器上消耗更多的资源;如果资源有限,或者共享这台机器的其它工作负载的响应时间很关键,那么这样做并不理想。

将 SQL 直接下推到 Oracle

使用 WebSphere DataStage 将 INSERT..SELECT 直接下推到 Oracle 的一个类似的 ShipPriority 作业的执行时间也是 6 秒。这说明在这个案例中,WebSphere Federation Server 影响很小,其效果与直接在源数据库执行 INSERT..SELECT 相同。

当整个作业都可以下推并在一个数据库中执行的时候,继续使用 WebSphere DataStage 将使 ETL 开发人员可以使用相同工具解决所有数据整合需求。此外,WebSphere DataStage 作业的元数据存储在 IBM Information Server 元数据储存库中,因此可以共享该信息,还可以利用 IBM Information Server 套件中的其它功能。 由于发现、清洁、整合和数据访问过程都使用相同版本的元数据,因此可以立即估计对任何信息的更改,或任何处理对企业信息集成过程中任何其它组件的影响。

就像本文中描述的作业一样,作业几乎肯定依赖于其它作业,而不大可能独立地执行。WebSphere DataStage 的 作业定序器是一种图形化工具,程序员可以通过它来指定要运行的一系列作业,以及带循环和流控制的异常处理。根据序列中的作业成功还是失败,序列可以包含表示不同动作的控制信息。通过将 ShipPriority 作业设计在 WebSphere DataStage 中,就可以利用作业定序器 —— 例如,在成功完成一个一年两次的订单整合作业之后,就触发 ShipPriority 作业。

在这些案例中,通过继续使用 WebSphere Federation Server,ETL 开发人员可以获得单个的、一致的数据镜像 —— 无论数据位于何处(关系数据源还是非关系数据源),以及可用于访问任何数据的单个的 SQL 方言。因此,开发人员可以重用为将处理下推到 Oracle 数据库中而编写的相同 SQL,在 DB2 on Z/OS 数据库(例如)执行相同的过程。WebSphere Federation Server 使用昵称引用数据源对象,这也增加了 WebSphere DataStage 作业的灵活性。

考虑一家从 Oracle 迁移到 Microsoft SQL Server 的企业,其 IT 部门有一组 WebSphere DataStage 作业,这些作业将处理下推到一个 Oracle 数据库。为了迁移这些作业,ETL 开发人员必须逐一修改每个作业,用 SQL Server stage 替换作业中的 Oracle stage,然后将 Oracle SQL 转换成 Microsoft SQL Server 方言。然后,必须对新的作业进行测试,确保与原始作业一致。如果已经使用了 WebSphere Federation Server 部署原始作业,那么迁移时只需撤销 Oracle 昵称,并创建相应的 SQL Server 昵称 —— WebSphere DataStage 作业本身不必更改。由于作业本身不必更改,实际上减少了测试工作。

案例 4:数据集市填充场景

StockCheck 作业报告在下一季度公司的供应商很可能没有足够库存的零件。这种预测是根据之前三个月零件销售增长 5% 的假设以及关于每个供应商当前库存的信息作出的。对于存在上述有危险的零件的每个地区,该作业生成一个列表,其中包括那个地区提供这些零件的供应商的名称和预计短缺的数量。

图 12 显示了实现 StockCheck 的 WebSphere DataStage 作业。

图 12. StockCheck WebSphere DataStage 作业
StockCheck DataStage 作业

取决于供应商所在的地区,StockCheck 作业的输出被传递到 5 个不同的目标,或者,如果供应商所在地区不存在危险零件,则生成一个否认文件。每个目标代表一个数据集市,每个数据集市在物理上位于一个特定区域中。StockCheck 是一个相当典型的数据集市填充作业,它使用 ETL 逻辑将数据从一个数据仓库转移和转换到更小的一些数据集市。

该作业使用 LINEITEM (Oracle) 表计算上一季度销售的每种零件的数量。接着使用 STOCK (DB2) 表提取零件的供应商键和当前库存水平。然后,该作业计算出每种部件 5% 的销售增长,并将这个值与供应商手头上的当前库存进行比较。接下来,对于供应商很可能没有足够库存的那些零件,提取其供应商信息,例如姓名和地址。供应商信息是由一个外部实用程序通过 GetSuppliers stage 获得的。最后一步是按地区划分供应商,并将一条记录插入到适当的数据集市中 —— 以报告那个地区的库存情况,并采取适当的动作。

StockCheck 作业的复杂性意味着将它转换成 ELT 模式是件非常艰巨的作业。不管是使用外部应用程序获取供应商名称和地址的 GetSuppliers stage,还是根据供应商所在地区将记录分发到不同目标数据库的 SelectRegion Switch stage,都难于用 SQL 表达。

但是,将 StockCheck 转换成 T-ETL 模式则相当简单,只需转换作业中的一小部分,即 LINEITEM 与 STOCK 之间的连接。如图 13 所示。

图 13. 使用 WebSphere DataStage 和 WebSphere Federation Server 的 StockCheck 作业
使用 WebSphere DataStage 和 WebSphere Federation Server 的 StockCheck 作业

在上述作业中,LINEITEM 与 STOCK 之间的两方连接被整合到 PartsSoldLastPeriod stage 中,并且被使用 CTE 转换成 SQL,每个 CTE 表示原始 StockCheck 作业中的一个 stage。用于访问 LINEITEM 和 STOCK 表的 CTE 与原始 stage 中的 SQL 是完全匹配的。图 14 显示了完整的 SQL:

图 14. StockCheck 作业被下推到 WebSphere Federation Server 的 SQL
StockCheck 作业被下推到 WebSphere Federation Server 的 SQL

在这个 T-ETL 例子中,需要由 WebSphere Federation Server 来执行初始的转换,因为 LINEITEM 和 STOCK 表在不同的数据源上。将原始 WebSphere DataStage 作业中包含的所有功能都下推到目标或联邦层效果不佳,因为大部分处理被最适当地表达为 WebSphere DataStage 中的并行数据流(并得到最有效的处理)。执行那样复杂的一组转换的 SQL 十分笨拙,难于维护。这种解决方案的优点在于 WebSphere DataStage 的灵活性和并行处理引擎,以及联邦层对来自同构或异构源的数据进行有效的预处理能力 —— 提供一个简单的 SQL 接口。

原始 StockCheck WebSphere DataStage 作业执行时间为 682 秒,而 T-ETL 版本的作业的执行时间为 124 秒。消耗的时间大约缩短了 82%。

识别可从 T-ETL 中获益的作业

前面描述的 4 个用例都是能从采用 T-ETL 方法移动和转换数据中受益的典型数据整合场景。凡是具有能在数据流早期减少输入数据的作业(例如连接、合并、过滤和聚合)的数据整合场景,几乎都能从 T-ETL 处理中受益。尽可能使用 WebSphere Federation Server 来执行减少工作集的操作通常更为有效,因为这种处理是在关系数据库引擎中执行的,而且可以利用源数据库上的优化(例如索引和物化视图)。同样不容忽视的是,通过早期减少输入数据,可以减少 WebSphere DataStage 读取的数据量。因此,组合使用 WebSphere Federation Server 和 WebSphere DataStage 的作业的执行时间和资源消耗很可能都少于单独使用 WebSphere DataStage 的作业。

读取数据的约束

本文描述的 4 个作业都将大量时间花在从数据源读取数据上。

最能从 T-ETL 方法中受益的整合场景是那些从源系统获取数据所花的时间在整个作业的执行时间中占较大比例的场景。也就是说,作业受到读取数据到 WebSphere DataStage 中的约束。可以使用 WebSphere DataStage V8 的 Job Performance Analysis 特性来识别符合上述条件的候选作业。图 15 显示了 Job Performance Analysis 针对 ProjectedBalance 作业的示例输出,并清楚地表明,作业的大部分时间花在读 CUSTOMER 表上。

图 15. Job Performance Analysis 工具的示例输出
Job Performance Analysis 工具的示例输出

在数据流中,输入数据集显著减小的地方通常代表着联邦与 WebSphere DataStage 之间最有益的临界点。可以根据对数据的认识识别这种临界点,或者,如果作业已经存在,则可以使用 WebSphere DataStage 的性能统计信息来识别。图 16 显示了 StockCheck 例子中前几个阶段的性能统计信息:

图 16. StockCheck 作业的临界点
StockCheck 作业的临界点

“减” 操作符

本文中描述的 4 个作业都包含减少工作数据集且能用 SQL 表达的阶段(早期在作业中定义)。

对于这个特定的作业,读统计信息的行数清楚地表明临界点 就在 JoinOnPart 和 Calc5PctIncrease 两阶段之间。 这是因为,在这个地方,来自两个输入源的工作数据集从 11.2M 和 900K 记录剧减至 1M 记录。还可以将后续阶段包括在下推到 WebSphere Federation Server 的 SQL 中(在这个例子中,对于 Calc5PctIncrease 和 GetLowStock stage 都可以这样做)。如果将数据集缩减最多之处的临界点之前的其它阶段也包括进去,可能会进一步提高性能。但是,这里性能提高得不大显著,而且很大程度上要取决于可用的资源和 WebSphere DataStage 引擎中定义的并行度。

将数据流的这一部分放在 WebSphere Federation Server 中执行有两个好处:

  1. WebSphere DataStage 一开始要获取的数据集缩减了。由于需要处理的输入数据更少,WebSphere DataStage 执行的随后的处理得以加快。
  2. 由于 WebSphere Federation Server 可以利用很多优化技术,开始的 stage 中的工作在 WebSphere Federation Server 中执行通常比在 WebSphere DataStage 中执行得更快,从而导致整个作业的执行时间和资源消耗为之减少。

在每个案例中,将 ETL 作业转换成 T-ETL 作业的工作是通过将 WebSphere DataStage 作业的一部分重写成 SQL 来完成的。因此,被下推到 WebSphere Federation Server 的阶段必须能够用 SQL 语法表达。SQL 被包括在一个 DB2 stage 中,并传递给 WebSphere Federation Server 进行处理。使用 CTE 将原始作业的各个阶段重写成 SQL,这提供了一种便于理解的方法,这种方法只要求基本的 SQL 编程技能。

T-ETL 作业中的 SQL 引用联邦数据库中预定义的昵称,而不是实际的源表。当然,昵称又引用源表。昵称的创建非常简单,只需一步,不同的作业可以使用相同的昵称来引用同一个远程对象。

WebSphere Federation Server 使用它的基于成本的优化器,确定检索和处理数据的最佳路径。这样一来,WebSphere DataStage 开发人员在组合两个或多个数据集时,就不必自行确定最佳连接策略。如果选择了错误的连接技术或链接顺序,其对性能的影响是数量级的。当使用 WebSphere DataStage 连接和合并数据时,就要由开发人员根据对数据的认识来确定最佳策略。

但是,数据是动态的,一种策略可能在作业开发时适用,在作业部署时又变得无效。由于联邦优化器是在运行时根据表示输入表的昵称的当前统计信息来确定计划的,因此可以在作业执行时针对数据量和分布的变化作出反应,并选择最佳计划。WebSphere Federated Server 优化器选择好最佳访问策略之后,就按该策略执行。如果可能的话,一些操作会被下推到数据源远程地执行,以利用那里的一些优化功能(例如索引)。不能下推的操作则在联邦数据库中执行,由于联邦数据库是功能完整的关系数据库,因此可以非常有效地处理这些基于集合的操作。

自然,这个过程也适用于从单个数据源提取数据的作业。通过将某些 stage 下推到数据源,减少输入到 WebSphere DataStage 的数据量,可以提高总体效率。

结束语

作为数据整合工具,WebSphere DataStage 的优点在于它的灵活性 —— 它可以支持很多不同的整合场景和 ETL 风格,包括 ETL、ELT 和 TEL(在单个数据源上)。将 WebSphere DataStage 与 WebSphere Federation Server 相结合,为整合场景开辟了一个全新的整合领域,即 T- ETL,它可以用于同构或异构数据源上的数据整合。

在 T-ETL 场景中,将 WebSphere DataStage 与 WebSphere Federation Server 组合使用的优点是,这种解决方案可以充分发挥这两种产品的长处: WebSphere Federation Server 的长处在于基于集合的处理方面的效率和异构环境中基于成本的优化,而 WebSphere DataStage 的长处在于灵活而强大的并行转换引擎和数据流建模。使用 WebSphere Federation Server 以基于集合的方式对数据进行预处理,减少数据,然后将数据传送到 WebSphere DataStage 上,在高度可伸缩的、并行的引擎中作进一步的处理,两种产品相辅相成,大大提高了整个作业的效率。

本文找出最能从 T-ETL 策略中受益的数据整合场景,并通过实例进行验证。本文找出的上述数据整合场景的四大特征为:

  1. 当单独使用 WebSphere DataStage 来实现时,从数据源取数据集所花费的时间在作业的总体执行时间中占较大比例。
  2. 在 WebSphere DataStage 作业的早期阶段中,输入数据要经过一些操作的处理(过滤、聚合或连接),这些操作会缩小数据的规模。换句话说,某个早期阶段的输出数据流应该少于输入数据流。
  3. 在早期要读取来自一个或多个输入数据源的数据的 “减” 操作(过滤、聚合或连接)当中,至少有一个操作可以表达为 WebSphere Federation Server 昵称上的一个 SQL 查询。
  4. 源数据库是非分区的。如果一个源系统是非分区的,那么 WebSphere DataStage 首先会连续地读数据,然后将数据分区,以便利用并行。通过将作业中把数据读到 WebSphere DataStage 中的那部分替换为能减少输入数据集的基于集合的处理机制,可以提高作业的效率。

通过使用联邦优化器,T-ETL 解决方案还使 WebSphere DataStage 开发人员不必将注意力放在为连接和合并各个阶段选择最佳连接方法和正确的链接顺序上 —— 这种决定对作业运行时的影响是数量级的。联邦优化器还使 T-ETL 解决方案更具有可适应性,因为当作业中数据特征发生变化时,最佳连接策略和链接顺序也可能随之变化。而优化器会考虑这些变化,并且在作业执行时选择最佳访问计划。

本文演示了这样一点:通过将作业中的工作明确地划分到 WebSphere DataStage 和 WebSphere Federation Server 上,可以节省相当多的执行时间。表 1 总结了以上描述的 4 个用例场景。

表 1. 对 4 个用例场景的总结
作业名称最初执行时间(秒)使用 T-ETL 时的执行时间(秒)提高的百分比
ProjectedBalance20412738%
ShipPriority68691%
OrderPriority424673183%
StockCheck68212482%

在这些用例中,执行时间至少减少了 38%,这已经是一个很大的提高了,对于一个企业而言,这可以节省相当可观的成本。 当然,和所有性能研究相同,当采用 WebSphere Federation Server 与 WebSphere DataStage 相结合的 T-ETL 方法时,所节省的执行时间不是固定不变的。但是,如果使用以上定义的 4 个特征来识别符合条件的作业,那么可以更有把握地说,采用 T-ETL 方法的作业可以减少较多的执行时间。

参考资料

学习

获得产品和技术

  • 下载 IBM 产品评测版,获得来自 DB2®、Lotus®、Rational®、Tivoli® 和 WebSphere® 的应用程序开发工具和中间件产品。

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management, WebSphere
ArticleID=248963
ArticleTitle=使用 WebSphere DataStage 和 WebSphere Federation Server 的一种灵活数据集成架构
publish-date=08162007