DB2 Warehouse V10.5 构建列存储数据仓库最佳实践-数据仓库-火龙果软件工程

DB2 Warehouse V10.5 构建列存储数据仓库最佳实践

作者黄巍，火龙果软件发布于 2014-07-21

3463 次浏览

摘要: DB2 V10.5 正是在这一背景下应运而生，这也使得构建基于列存储的数据仓库成为可能。这篇文章将介绍使用 DB2 Warehouse V10.5 时，构建列存储数据仓库的一系列最佳实践，并且穿插阐述了数据仓库数据流设计时的一些细 ...

概述

在 DB2 V10.5 之前，DB2 在物理实现上一直是基于行存储，但在典型的数据仓库应用中，由于读操作的频率（查询、连接、聚集等）会远大于写操作（增删改），这种存储实现方式并不能给数据仓库、实时分析带来显著的优势。

近些年来列存储数据库以其读磁盘效率，存储空间的可压缩比率，排序 / 索引效率，以及由此带来的技术、管理和应用优势，成为了数据仓库应用的一大热点。

DB2 V10.5 正是在这一背景下应运而生，这也使得构建基于列存储的数据仓库成为可能。这篇文章将介绍使用 DB2 Warehouse V10.5 时，构建列存储数据仓库的一系列最佳实践，并且穿插阐述了数据仓库数据流设计时的一些细节处理。

准备活动

为了演示后续的用户场景，首先我们在 DB2 V10.5 上创建名为 BLUTEST 的数据库，并分别建立两个行存储，两个列存储的表：

清单 1. preparation ddl

CREATE DB BLUTEST; 
 CONNECT TO BLUTEST; 
 CREATE TABLE CDE.AROMA_CLASS (CLASSKEY INTEGER, CLASS_TYPE, VARCHAR(12), CLASS_DESC 
 VARCHAR(60)) ORGANIZE BY COLUMN; 
 CREATE TABLE CDE.AROMA_CLASS1 (CLASSKEY INTEGER, CLASS_TYPE VARCHAR(12), CLASS_DESC 
 VARCHAR(60)) ORGANIZE BY COLUMN; 
 CREATE TABLE ROW.AROMA_CLASS (CLASSKEY INTEGER, CLASS_TYPE VARCHAR(12), CLASS_DESC 
 VARCHAR(60)); 
 CREATE TABLE ROW.AROMA_CLASS1 (CLASSKEY INTEGER, CLASS_TYPE VARCHAR(12), CLASS_DESC 
 VARCHAR(60));

建完之后，为了在 Design Studio 中使用这个数据库，需要将其反向工程为本地的数据模型文件（.dbm 文件）。具体步骤此处省略。

场景一：将文件内容装载至列存储表

在很多数据仓库的应用场景下，中间数据（例如从 OLTP 数据中转换并导出）会以文件形式保存，然后再将其直接导入数据仓库表中。此场景将介绍如何将文件内容装载至列存储表。

首先创建一个数据流，并拖拽“文件源”操作符至数据流编辑器中，其中抽取方法有三种选项：

使用 DB2 LOAD 实用程序从文件抽取数据

使用 DB2 IMPORT 实用程序从文件抽取数据

使用 DB2 INGEST 实用程序从文件抽取数据

这三种方式均能正确将文件内容装载至列存储表，这里我们采取默认的 DB2 LOAD 方式。

接着指定该文件在客户机的位置。（注意：若选择“数据库服务器”选项，则意味着文件需存在于此数据流的 SQL 执行数据库上）

图 1. 文件源操作符

点击“下一步”，此处需要指定文件各字段的列名与数据类型——既可手工输入，也可通过“生成文件格式”等其他方式自动生成。接着点击“完成”，这样对一个源文件的定义就完成了。

接下来在选择目标表的操作符时，有两个选择：

1.成批装入目标（推荐）

2.表目标

其具体区别在于运用场景不同：

成批装入目标会直接使用 DB2 的 Load 命令直接导入数据，这是速度上是最优的。而表目标适合的场景更灵活，会根据目标表的类型生成不同的 SQL。例如若目标表不在 SQL 执行数据库上，那最终的执行方式会是 JDBC；若会需要也会生成一个不记日志的临时表作为中转需要。而对于将文件内容装载至列存储表这类场景，我们推荐使用文件源 -> 成批装入目标的操作符组合。

场景二：将任意数据流的阶段性输出导向列存储表

上一场景中，文件其实代表了“暂存”的数据，即我们手工地把这些数据以文件的格式保存到了本地。而在典型的 ETL（数据的抽取，转换，装载）流程中，这一过程是不需要人工干预，而且需要比持久化文件更好的解决方案。

一个典型的数据仓库 ETL 流需要将业务表中（通常在 OLTP 数据库中）的数据与维度表进行键查询，然后装载至目标的事实表中，如下图所示：业务表 1 为行存储的业务表 ROW.AROMA_CLASS，表源 2 为列存储的维度表 CDE.AROMA_CLASS。

图 2. 典型 ETL 流示例

此处，表目标也可以替换成成批装入目标操作符。下面我们简单对比一下二者的执行 SQL 代码 >

1、成批装入目标

执行方式：DB2 的 SYSPROC.ADMIN_CMD 存储过程

清单 2. 成批装入目标生成代码

LOAD FROM (SELECT Q200.CLASSKEY AS CLASSKEY, Q200.CLASS_TYPE AS CLASS_TYPE, 
  Q200.CLASS_DESC AS CLASS_DESC 
  FROM ROW.AROMA_CLASS Q200, CDE.AROMA_CLASS Q362 
  WHERE (Q200.CLASSKEY = Q362.CLASSKEY) 
 )  OF CURSOR METHOD P(1, 2, 3) MESSAGES ON SERVER INSERT INTO 
 "CDE"."AROMA_CLASS1"("CLASSKEY", "CLASS_TYPE", "CLASS_DESC") NONRECOVERABLE

2、表目标

执行方式：JDBC

清单 3. 表目标生成代码

 INSERT INTO CDE.AROMA_CLASS1 (CLASSKEY, CLASS_TYPE, CLASS_DESC) 
  SELECT Q200.CLASSKEY AS CLASSKEY, Q200.CLASS_TYPE AS CLASS_TYPE, 
         Q200.CLASS_DESC AS CLASS_DESC 
    FROM ROW.AROMA_CLASS Q200, CDE.AROMA_CLASS Q340 
    WHERE (Q200.CLASSKEY = Q340.CLASSKEY)

可以看出二者的执行 SQL 代码基本相似，区别在于：

1.成批装入目标是将其包在一个匿名的 CURSOR 中，并通过 DB2 的 ADMIN_CMD 存储过程来执行

2.而表目标是通过 JDBC 直接执行。我们知道 Load 方式并不会产生日志，而对于表目标在默认情况下（行存储表）也可以手工关掉日志：表目标 > 高级选项 > NOT LOGGED INITIALLY。但对于列存储表来说，此选项并不可用，故对于用户禁止写日志且运行更快速的需求，我们推荐使用成批装入目标；而若用户需要更加灵活地处理目标表（例如对目标表进行预处理或推迟处理，记录日志，以及需要在目标表接其他 ETL 动作），则表目标是更好的选择。

场景三：使用列存储选项创建合适的表

在场景二中，事实表 1 是已经创建好的列存储表，而在 DB2 Warehouse V10.5 所支持的数据仓库设计场景中，其实这个表是可以随着开始的进程动态创建的，而所用的功能就是“创建合适的表”。

假设我们暂时没有这个基于列存储的事实表，只有业务表和维度表两个源表，然后经过了键查询，并剔除掉无用列，修改列类型，或重命名一些列后，得到这个事实表需要的列名，列类型。那么很然想到的下一步动作应该就是执行相应的 db2 create 语句，即在数据库上手工创建这个表。然而使用创建合适的表可以很方便在同时在 db2 和数据模型中创建这个即时需要的表，具体步骤是在键查询操作符的相匹配端口上右键 > 创建合适的表 ... > 填写合适的表名 > 选择数据库连接，然后会看下如下界面：

图 3. 创建合适的表

在 Columns 页，创建此表的所有列信息是从键查询操作符的“相匹配”端口传递过来的，其他页的信息采用了默认值，均可手工修改。对于列存储来说，最重要的是信息是表组织项，需要在此选择“列”值。

接着点击“完成”即可同时在数据库和数据模型中创建这个即时需要的表。

这种场景同时可以应用于所有有输出端口的操作符上，如表源，文件源，渐变维等等。

场景四：优化行存储表及列存储表间的数据抽取，转换，装载

DB2 Warehouse V10.5 作为数据仓库的端到端完整解决方案，其对于列存储表的最大优势在于其延续了高质量的代码生成技术——用户不必考虑行存储表与列存储表在之间在数据抽取，转换，装载时的各种考量，可能的性能消耗，这些都由内部基于查询图模型（Query Graph Model）的代码生成技术来完成。对于用户来说，只需要考虑端到端的表类型选择，中间的过程对于其是透明的。

场景二的优化后查询图模型如下图所示：