Kyle's Notebook

数据仓库设计基础

Word count: 3.3kReading time: 11 min
2020/05/28

数据仓库设计基础

与业务系统库对比

基于业务系统库(OLTP)完成数据分析需求:

  • 安全性:统计分析应与业务分离。

  • 变更频繁:重写分析系统、重新测试。

  • 难以建立和维护源于多个业务系统的报表。

  • 不同的数据源数据格式不统一。

  • 业务数据库为事务处理性能优化,不适合分析查询。

  • 统计分析影响业务系统性能。

使用数据仓库(OLAP):

  • 面向主题,即业务类型:如机器,用户,歌曲,营收。

  • 集成:从多数据源抽取数据后按一致、无歧义的格式写入数据仓库。

  • 随时间变化:存放历史数据,保留快照。

  • 非易失:写入数据仓库后不再修改删除。

  • 粒度:数据装载的时间周期(日/月/周,增量/全量/汇总等),粒度越小数据量越大,保留信息越多。

传统数据仓库

  • 失信的 SLA:随着数据规模变大,处理时间越来越长。

    难堪重负:ETL 处理与用户查询竞争资源,体验变差。

  • 查询成本高昂:为了节约成本把数据归档,却难以查询时间窗口以外的数据。

  • 缺乏灵活性:每当需求变更,数据库模式改动大;而且结构化数据与其他数据源难以关联。

Hadoop 的优势

  • 减少对 EDW 资源占用。

  • 提供数据访问接口。

  • 在线归档数据。

  • 灵活的模式演进,半结构化、非结构化数据处理。

  • 可通过 Oozie 等工作流管理工具协调调度。

Sqoop(操作型数据源系统)、Flume/Kafka(日志等)-> HDFS -> Hive/Impala/Spark/HBase <-> Sqoop(EDW)<-> 分析工具

相关术语

模型

  • 维度模型:事实表 + 维度表(星型模型)。

  • 规范化模型:遵循关系型数据库三大范式。

粒度:事实表粒度(一条记录表示的内容)应尽可能细小。

可加性:字段是否可以直接相加(指标),是否可计数或求平均值(维度)。

事实表:一或多个外键(参考维表,如订单表的城市 id),主键为外键的组合(标识唯一一条记录)。

聚合事实表:为提高查询效率而构建的聚合(如每天订单平均金额)。

维表:各种查询约束(如省份城市表)以及报表标签源头。

日历维度:附加在大多数事实表和一些维表上。

缓慢变化的维度:把已存在的旧值修改为新的值(机器基本信息表),可使用新值添加一条新记录,并将其标记为当前值,将旧记录标记为废弃(机器基本信息历史表)。

关系数据模型

关系数据模型具备以下优势:

  • 非冗余性

  • 稳定性

  • 一致性

  • 灵活性

数据结构

  • 关系(表,实体)

  • 属性(列,字段)

  • 属性域(约束)

  • 元组(行,记录)

  • 键(超键,候选键,主键,外键)

主键选取原则

  • 尽可能小、数字类型(如 AUTO_INCREMENT)。

  • 不可修改(避免外键引用失效)。

  • 没有业务含义(避免随业务改变而修改)。

  • 可多列组成,但尽可能少。

完整性约束

  • 实体完整性:主键不为空。

  • 参照完整性:外键与主表某记录的候选键值相同,或外键全为空(即无关联)。

业务规则:包括属性域和完整性规则(check)。

关系数据库语言(SQL)

  • DDL(数据定义语言):create,alter,drop,truncate,comment,rename

  • DML(数据操纵语言):select,insert,update,delete,merge,call,explain,lock

  • DCL(数据控制语言):grant,revoke

  • TCL(事务控制语言):commit,rollback,savepoint,set transaction

规范化

减少数据冗余度、提高更新效率,但由于联表过多降低查询性能。

1NF

原子性列,即属性不可再分。

id name mobile dept_no dept_name province city zip
1 张三 13800138000
13800138001
d1 部门1 广东 广州 110
2 李四 13800138002 d2 部门2 北京 北京 111

mobile 列可存放多个号码,拆分:

id name mobile dept_no dept_name province city zip
1 张三 13800138000 d1 部门1 广东 广州 110
1 张三 13800138001 d1 部门1 广东 广州 110
2 李四 13800138002 d2 部门2 北京 北京 111

2NF

在满足 1NF 条件下消除部分依赖,即每个非主属性完全函数依赖于键码。

上表中候选键 {id, name, dept_no},dept_name 依赖 dept_no、name 依赖 id。拆分为如下三个表:

部门表:

dept_no dept_name
d1 部门 1
d2 部门 2

员工 - 部门表:

id dept_no
1 d1
2 d2

员工表:

id mobile province city zip
1 13800138000 广东 广州 110
2 13800138002 北京 北京 111

3NF

在满足 2NF 条件下消除传递依赖,即非主属性不传递函数依赖于键码。

上面的员工表中 province、city 依赖 zip,而 zip 依赖 id 即为传递依赖,应把表再拆分成员工表和地区表。

员工表:

id mobile zip
1 13800138000 110
2 13800138002 111

地区表:

zip province city
110 广东 广州
111 北京 北京

维度数据模型

维度数据模型重点关注事实表、维度表(规范化级别低于关系模型)和数据粒度三个概念构建,具备以下优势:

  • 易理解

  • 高性能

  • 可扩展

其中高性能源于非规范化,比如订单明细表和订单表,可从订单明细状态中提取出订单状态维度表、再把订单表和订单明细表整合到一起。

构建流程

  • 选择业务流程(UML)

  • 声明粒度(订单/消费流水)

  • 确认维度(日期:年/月/日)

  • 确认事实(金额/数量)

维度规范化

把一个维度映射成多个维度表,即维度 - 子维度”连接。比如地址表拆分为“省份表 - 城市表 - 区域表”。

但以下情况需要避免规范化处理:

  • 增加表数量会使结构复杂。

  • 多表连接使查询复杂。

  • 不适合使用位图索引。

  • 明显降低查询性能。

星型模型

以事实表为中心,多个维度表与事实表相连。其中事实表包括:

  • 事务事实表(如销售行为)

  • 快照事实表(如用户月底余额)

  • 累积事实表(如月总营收)

其具备以下特点:

  • 简化查询:简化业务报表逻辑,提升查询性能。

  • 快速聚合,便于向 OLAP 提供数据。

  • 不能保证数据完整性,应通过批处理和实时处理抵消。

  • 分析需求不灵活。

雪花模型

对星型模型维度表作规范化处理,比如把机器的门店维度表拆分成门店表和门店地址表。

能比星型模型节省存储空间,但同时会降低查询性能。

也可结合使用星型模型与雪花模型(底层使用雪花模型,配置视图模拟星型模型)。

Data Vault 模型

Data Vault 模型具备以下特点:

  • 基于时间存储。

  • 依赖越少越好。

  • 与源系统越独立越好。

  • 适应源系统的数据变化、支持扩展。

  • ETL 作业可反复执行。

  • 数据可追踪。

由于存放原始数据(所有时间所有数据),不遵照任何业务规则,不同数据源的数据冲突时可保留多个版本数据。

其中数据解析延迟到数据集市阶段:存放单一主题(财务、销售部门等)、数据仓库/事务系统的少量数据源的粗粒度数据(汇总数据),且只保留一段时间范围内(如数月)。

通常使用星型模型、雪花模型,存放 ETL 的输出数据。

中心表、链接表与附属表

由中心表(HUB)、链接表(Link)、附属表(Satelite)组成,综合了 3NF 和星型模型的优势。

  • 中心表(如用户、机器、订单表等):主键(系统生成的代理键)、业务主键(如用户 id,机器 id,可能用于多个系统,但只保留一份)、装载时间、数据源。

  • 链接表(中心表之间的链接关系,如订单-用户关联表):主键、外键、装载时间、数据源。

  • 附属表(中心表和链接表的附属信息,如订单详情表):主键、外键、装载时间、失效时间、数据源、属性 xxx。

构建流程

  • 设计中心表

  • 设计链接表

  • 设计附属表

  • 设计必要的 PIT 表

数据仓库架构

数据仓库的实施流程:

  • 定义范围

  • 确定需求

  • 逻辑设计

  • 物理设计

  • 装载数据

  • 访问数据

  • 管理维护

可根据业务形态考虑技术选型。

数据建模与存储

选择存储引擎::取决于数据访问方式。

  • HDFS:从 OLTP 系统提取数据,批量处理(整个时间周期数据、批处理效率更高)后加载到数据仓库,或者全表扫描。

  • HBase:随机访问,即席查询。

反范式设计:为分析提速,也避免维护多个数据集的繁琐。缺点:带来数据冗余,数据采集流水线变得繁琐,数据更新困难。尤其是 Hadoop 不适合进行多次关联操作,尤其是较大的事实表关联较小的维表:比如处理时订单表应直接写入机器信息而非 id,避免查询时机器信息表再与订单表关联。

数据更新:HDFS 不支持原地更新,且加上处理逻辑原地更新后也无法追踪更新的流水记录。

事实表:根据更新时间取日增量每天同步追加。比如订单收益取生成时间,订单退款取更新时间同步到事实表,即可计算净收益并追踪变化,如有需要还可以另外维护订单全量表记录订单最新状态信息。

历史表:根据更新时间取日全量每天同步。比如完整的用户表,其最新的一份可作为状态表。

存储格式及压缩算法

  • Avro:行式存储,支持模式演进,不需要根据 OLTP 修改 ETL 操作(适合追加)。

  • Parquet:列式存储,性能强大(涉及多个维表的事实表较宽,查询和聚合操作只涉及小部分列),压缩支持好。

  • 不推荐 CSV(出于性能、压缩和模式管理考虑,CSV 访问时还需要数据解析)

压缩算法推荐使用 Snappy。

分区方式:取决于数据量和访问方式,目的是跳过不必要的数据。按时间周期分区,分区平均大小为 HDFS 数据块的若干倍(典型值为每天数据 1GB 则按天分区,较多则按小时,较少则按周或月)。

数据采集

OLTP导出:目标是数据几乎不变的表(一次性导入),数据频繁更新的小表(日全量),数据频繁更新、无法每天全量提取的大表(日增量,周/月全量)。可使用 Sqoop,Informatica,Pentaho,Kettle。

数据整合:Sqoop 导出时执行关联。可仅插入、仅更新和混合,但只支持简单的条件,而且关联操作会给 OLTP 带来压力;同时支持元数据服务,可记录上次执行状态,下次执行时可获取更新的记录。

建议导出到 HDFS 后再用 Spark、Hive 等工具关联。

数据处理与访问

分区

  • 对已存在表分区(从旧表读取数据,并将数据加载到新创建的、带分区的表的正确分区中)。

  • 将数据加载到表的多个分区中。

  • 将导入某个目录的数据作为新的分区添加到表中。

合并、更新:除了记录事实表,还要维护已有信息表(缓慢变化的维表等),可以全量导入,也可以从已有信息读出、追加修改后再重新写入,并更新元数据(如果有的话)。

提高更新效率:只针对分区修改、添加 Hadoop 节点、改用 HBase(如果更新频繁的话,但也会渐慢查询速度)。

数据聚合

在关系型数据库中执行聚合操作非常耗资源,比如求和、计数、平均等。而 Hadoop 天然高度并行,因此支持且更擅长聚合操作。

还能使用 Hive、Spark、Impala 等工具,把聚合后的数据集通过 Sqoop 导入关系型数据库,可再接入 BI 工具继续处理。

数据导出

  • 将数据保留在 Hadoop 中,使用 Impala(更低延迟)、Hive 和集成的 BI 工具查询。

  • 与关系型数据库集成:Informatica、Pentaho,Sqoop + Oracle 连接器 。

流程调度

  • Oozie(开源),Autosys、Activebatch 以及 UC4。

  • Oozie 工作流的关键几点(中间两步都可以由 Spark 完成):

    • 一个用于获取数据的 Sqoop 操作。

    • 一个用于执行数据集关联、分区、合并的 Hive 操作。

    • 多个用于完成聚合的 Hive 或 MapReduce 操作。

    • 一个用于将结果导出至数据仓库的 Sqoop 操作。

  • 多组步骤时采用 fork-and-join 模式。

CATALOG
  1. 1. 数据仓库设计基础
    1. 1.1. 与业务系统库对比
      1. 1.1.1. 传统数据仓库
      2. 1.1.2. Hadoop 的优势
    2. 1.2. 相关术语
    3. 1.3. 关系数据模型
      1. 1.3.1. 数据结构
      2. 1.3.2. 完整性约束
      3. 1.3.3. 关系数据库语言(SQL)
      4. 1.3.4. 规范化
        1. 1.3.4.1. 1NF
        2. 1.3.4.2. 2NF
        3. 1.3.4.3. 3NF
    4. 1.4. 维度数据模型
      1. 1.4.1. 构建流程
      2. 1.4.2. 维度规范化
      3. 1.4.3. 星型模型
      4. 1.4.4. 雪花模型
    5. 1.5. Data Vault 模型
      1. 1.5.1. 中心表、链接表与附属表
      2. 1.5.2. 构建流程
    6. 1.6. 数据仓库架构
      1. 1.6.1. 数据建模与存储
      2. 1.6.2. 数据采集
      3. 1.6.3. 数据处理与访问
      4. 1.6.4. 数据聚合
      5. 1.6.5. 数据导出
      6. 1.6.6. 流程调度