您可以捐助,支持我们的公益事业。

1元 10元 50元





认证码:  验证码,看不清楚?请点击刷新验证码 必填



  求知 文章 文库 Lib 视频 Code iProcess 课程 角色 咨询 工具 火云堂 讲座吧   成长之路  
会员   
 
   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
 
     
   
 订阅
  捐助
DB2 Warehouse V10.5 构建列存储数据仓库最佳实践
 
作者 黄 巍,火龙果软件    发布于 2014-07-21
1206 次浏览     评价:      
 

摘要: 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)的代码生成技术来完成。对于用户来说,只需要考虑端到端的表类型选择,中间的过程对于其是透明的。

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

图 4. 优化后的查询图模型

场景五:各操作符对于列存储表的细节调整及限制

由于 DB2 Warehouse 产品是建立在 DB2 之上的,所以 DB2 V10.5 中列存储表的一些限制也直接或间接地反映到了 DB2 Warehouse 上。下面将以操作符为序,介绍其对于列存储表的细节调整及限制:

1、成批装入目标

准备和清除页中,原先默认的 SET INTEGRITY 语句会被设置为空。

图 5. 成批装入目标

高级选项 > 恢复选项,「使用保存的输入数据副本来恢复到目录中」选项对于列存储表不可用,故会收到如下错误:

图 6. COPY YES 出错信息

2、表目标

高级选项页中,REORG 和 NOT LOGGED INITIALLY 选项将不可用。

图 7. 表目标操作符高级选项

3、数据站

若站类型为常规表,INDEX 和 NOT LOGGED INITIALLY 选项将不可用。

图 8. 数据站选项

4、SQL 合并,渐变维

这两个操作符因为生成的 SQL 代码中会使用 MERGE 语法,而其暂时在 DB2 V10.5 的列存储表中不支持,故这两个操作符暂时不能作用于列存储表。

5、重组(控制流中)

重组操作符若作用于列存储表,会出现如下错误:

图 9. 重组操作符出错信息

   
1206 次浏览  评价: 差  订阅 捐助
相关文章

我们该如何设计数据库
数据库设计经验谈
数据库设计过程
数据库编程总结
 
相关文档

数据库性能调优技巧
数据库性能调整
数据库性能优化讲座
数据库系统性能调优系列
相关课程

高性能数据库设计与优化
高级数据库架构师
数据仓库和数据挖掘技术
Hadoop原理、部署与性能调优
 

MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...   
 
 
 
 
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号