转载

使用 BLU Acceleration 提高 ETL 性能

简介

ETL（提取、转换和加载）流程是商业智能报告系统的数据集成部分，负责加载数据并保持数据在 DW 表中最新。客户需要当前数据，这样客户才能创建/访问报告或指示板，获得基础业务数据的详细视图。典型的 ETL 流程将会按特定的间隔时间（每隔 30 分钟）加载增量数据，需要频繁地更新/删除 DW 表中的数据。在对按列组织的表（BLU 表）使用 DB2 BLU Acceleration 特性时，我们注意到，插入记录比更新记录更有效。

在对按列组织的表执行 ETL 流程时，很难确定每个记录的列的列表，因而很难只更新发生改变的列。很多次，最终我们只能更新表中所有的列，这会导致性能降低，因为我们正在阅读更多的页面，而每一列又都存储在不同的页面中。我们的目标是更改 Update / Delete 操作，以便只处理必要的列，从而充分利用 BLU Acceleration 的最大优势。

下面的例子将解释我们如何在按列组织的表中将 Update / Delete 转换成 Insert single-column 更新。 F_DOCUMENT 是一个按列组织的表，没有主列。它总共有 20 列，其中的 5 个是关键列，这意味着剩下的 15 个列可在运行 ETL 的过程中发生更改。

CREATE TABLE TEST.F_DOCUMENT (  DOCUMENT_ID BIGINT NOT NULL,   DIRECTION_ID BIGINT NOT NULL,   COMPANY_ID BIGINT NOT NULL,   DOCUMENT_TYPE_ID BIGINT NOT NULL,   INTERCHANGE_STATUS_ID BIGINT NOT NULL,   TP_IDENTIFIER_ID BIGINT NOT NULL,   RECEIVER_IDENTIFIER_ID BIGINT NOT NULL,   APPLICATION_ID BIGINT NOT NULL,   APPLICATION_ACCOUNT_ID BIGINT NOT NULL,   CONTROL_NUMBER_ID BIGINT NOT NULL,   RETENTION_PERIOD INTEGER NOT NULL,  PROCESS_DATE_ID BIGINT NOT NULL,   PROCESS_TIME_ID BIGINT NOT NULL,   AVAILABLE_DATE_ID BIGINT NOT NULL,   AVAILABLE_TIME_ID BIGINT NOT NULL,   ACCEPTED_DATE_ID BIGINT NOT NULL,   ACCEPTED_TIME_ID BIGINT NOT NULL,   DOCUMENT_CHARS BIGINT,   DOCUMENT_BYTES BIGINT,   DOCUMENT_SEGMENTS BIGINT ) ORGANIZE BY COLUMN IN TEST_DATA;

在 ETL 流程必须更新所有记录时，它会在常规运行中执行下面的语句，尝试更新所有列，包括未发生改变的列。这导致需要阅读更多的页面，最终花费更多的时间来完成执行。

UPDATE TEST.F_DOCUMENT   SET DOCUMENT_CHARS=<SOURCE>.DOCUMENT_CHARS,  DOCUMENT_BYTES=<SOURCE>.DOCUMENT_BYTES,  DOCUMENT_SEGMENTS=<SOURCE>.DOCUMENT_SEGMENTS,  ACCEPTED_TIME_ID=<SOURCE>.ACCEPTED_TIME_ID,  AVAILABLE_TIME_ID=<SOURCE>.AVAILABLE_TIME_ID,  PROCESS_TIME_ID=<SOURCE>.PROCESS_TIME_ID,  ACCEPTED_DATE_ID=<SOURCE>.ACCEPTED_DATE_ID,  AVAILABLE_DATE_ID=<SOURCE>.AVAILABLE_DATE_ID  ,APPLICATION_ID=<SOURCE>.APPLICATION_ID,  RECEIVER_PROTOCOL_ID=<SOURCE>.RECEIVER_PROTOCOL_ID,  PROCESS_DATE_ID=<SOURCE>.PROCESS_DATE_ID,  RETENTION_PERIOD=<SOURCE>.RETENTION_PERIOD,  APPLICATION_ACCOUNT_ID=<SOURCE>.APPLICATION_ACCOUNT_ID,  CONTROL_NUMBER_ID=<SOURCE>.CONTROL_NUMBER_ID,  RECEIVER_IDENTIFIER_ID=<SOURCE>.RECEIVER_IDENTIFIER_ID,  TP_IDENTIFIER_ID=<SOURCE>.TP_IDENTIFIER_ID  WHERE  DOCUMENT_ID=<SOURCE>.DOCUMENT_ID  AND DIRECTION_ID=<SOURCE>.DIRECTION_ID  AND COMPANY_ID=<SOURCE>.COMPANY_ID  AND DOCUMENT_TYPE_ID=<SOURCE>.DOCUMENT_TYPE_ID  AND INTERCHANGE_STATUS_ID=<SOURCE>.INTERCHANGE_STATUS_ID;

回页首

将一个多列更新转换成一个单列更新和插入的步骤

将一个额外的列 ACTIVE SMALLINT 添加到表 F_DOCUMENT 中。
对于该表的每一个 Insert 操作，确保 ACTIVE 列被设置为 1。
对于该表中的每一个 Update / Delete 操作，确保 ACTIVE 类被设置为 0。

CREATE TABLE TEST.F_DOCUMENT (  DOCUMENT_ID BIGINT NOT NULL,   DIRECTION_ID BIGINT NOT NULL,   COMPANY_ID BIGINT NOT NULL,   DOCUMENT_TYPE_ID BIGINT NOT NULL,   INTERCHANGE_STATUS_ID BIGINT NOT NULL,   TP_IDENTIFIER_ID BIGINT NOT NULL,   RECEIVER_IDENTIFIER_ID BIGINT NOT NULL,   APPLICATION_ID BIGINT NOT NULL,   APPLICATION_ACCOUNT_ID BIGINT NOT NULL,   CONTROL_NUMBER_ID BIGINT NOT NULL,   RETENTION_PERIOD INTEGER NOT NULL,  PROCESS_DATE_ID BIGINT NOT NULL,   PROCESS_TIME_ID BIGINT NOT NULL,   AVAILABLE_DATE_ID BIGINT NOT NULL,   AVAILABLE_TIME_ID BIGINT NOT NULL,   ACCEPTED_DATE_ID BIGINT NOT NULL,   ACCEPTED_TIME_ID BIGINT NOT NULL,   DOCUMENT_CHARS BIGINT,   DOCUMENT_BYTES BIGINT,   DOCUMENT_SEGMENTS BIGINT,  ACTIVE SMALLINT ) ORGANIZE BY COLUMN IN TEST_DATA;

在向该表添加了 ACTIVE 列之后，ETL 流程必须对每个更新执行两个语句。第一个语句会是一个 update 语句，用于将 ACTIVE 列设置为 0，这会导致性能提升，因为该流程读取了更少的页面。第二个语句将会插入一个新记录，用于将 ACTIVE 列设置为 1。

UPDATE TEST.F_DOCUMENT  SET ACTIVE = 0 WHERE  DOCUMENT_ID=<SOURCE>.DOCUMENT_ID  AND DIRECTION_ID=<SOURCE>.DIRECTION_ID  AND COMPANY_ID=<SOURCE>.COMPANY_ID  AND DOCUMENT_TYPE_ID=<SOURCE>.DOCUMENT_TYPE_ID  AND INTERCHANGE_STATUS_ID=<SOURCE>.INTERCHANGE_STATUS_ID;

INSERT INTO TEST.F_DOCUMENT  (DOCUMENT_ID,  DIRECTION_ID,  COMPANY_ID,  DOCUMENT_TYPE_ID,  INTERCHANGE_STATUS_ID,  TP_IDENTIFIER_ID,  RECEIVER_IDENTIFIER_ID,  APPLICATION_ID,  APPLICATION_ACCOUNT_ID,  CONTROL_NUMBER_ID,  RETENTION_PERIOD,  PROCESS_DATE_ID,  PROCESS_TIME_ID,  AVAILABLE_DATE_ID,  AVAILABLE_TIME_ID,  ACCEPTED_DATE_ID,  ACCEPTED_TIME_ID,  DOCUMENT_CHARS,  DOCUMENT_BYTES,  DOCUMENT_SEGMENTS,ACTIVE) VALUES  (<SOURCE>.DOCUMENT_ID,  <SOURCE>.DIRECTION_ID,  <SOURCE>.COMPANY_ID,  <SOURCE>.DOCUMENT_TYPE_ID,  <SOURCE>.INTERCHANGE_STATUS_ID,  <SOURCE>.TP_IDENTIFIER_ID,  <SOURCE>.RECEIVER_IDENTIFIER_ID,  <SOURCE>.APPLICATION_ID,  <SOURCE>.APPLICATION_ACCOUNT_ID,  <SOURCE>.CONTROL_NUMBER_ID,  <SOURCE>.RETENTION_PERIOD,  <SOURCE>.PROCESS_DATE_ID,  <SOURCE>.PROCESS_TIME_ID,  <SOURCE>.AVAILABLE_DATE_ID,  <SOURCE>.AVAILABLE_TIME_ID,  <SOURCE>.ACCEPTED_DATE_ID,  <SOURCE>.ACCEPTED_TIME_ID,  <SOURCE>.DOCUMENT_CHARS,  <SOURCE>.DOCUMENT_BYTES,  <SOURCE>.DOCUMENT_SEGMENTS,1);

同样的，您可以将您的 ETL 流程更改为对每个删除操作执行单列更新。

UPDATE TEST.F_DOCUMENT   SET ACTIVE = 0 WHERE  DOCUMENT_ID=<SOURCE>.DOCUMENT_ID  AND DIRECTION_ID=<SOURCE>.DIRECTION_ID  AND COMPANY_ID=<SOURCE>.COMPANY_ID  AND DOCUMENT_TYPE_ID=<SOURCE>.DOCUMENT_TYPE_ID  AND INTERCHANGE_STATUS_ID=<SOURCE>.INTERCHANGE_STATUS_ID;

确保您的 F_DOCUMENT 的所有 SELECT 查询都在 WHERE 子句中提供了 ACTIVE='1' 设置。之后，作为维护活动的一部分，您可以删除 ACTIVE='0' 的所有记录。

SELECT     DOCUMENT_ID,     DIRECTION_ID,     COMPANY_ID,     DOCUMENT_TYPE_ID,     DOCUMENT_CHARS,     DOCUMENT_BYTES,     DOCUMENT_SEGMENTS,    FROM     TEST.F_DOCUMENT    WHERE     DOCUMENT_ID=<SOURCE>.DOCUMENT_ID     AND DIRECTION_ID=<SOURCE>.DIRECTION_ID     AND COMPANY_ID=<SOURCE>.COMPANY_ID     AND DOCUMENT_TYPE_ID=<SOURCE>.DOCUMENT_TYPE_ID     AND INTERCHANGE_STATUS_ID=<SOURCE>.INTERCHANGE_STATUS_ID                AND ACTIVE = 1;

回页首

需要牢记的事项

以下这些技术会增加表中记录的数量，因为我们在 ACTIVE 列被设置为 1 时为每个更新插入了新记录，并更改了 ACTIVE='0' 的现有记录。我们无需要担心额外的记录，因为 BLU Acceleration 为我们提供了更好的存储管理。
您应该每周/每月进行一次维护活动，删除 ACTIVE='0' 的记录，以便我们可以回收存储空间和提高性能。
重要提示：所有 SELECT 语句都应该总是在 WHERE 子句中提供 "ACTIVE=1" 设置。

回页首