超全面试总结——数据仓库

  • 如何理解数仓
  • 为什么要数据仓库建模
  • 为什么要设计数据分层
  • 通用的数据分层设计
  • 分层的原则是什么？
  • 数据集市和数据仓库的区别
  • 数据库和数据仓库有什么区别？
  • 维度建模三种模式 星形、雪花、星座
  • 事实表
  • 事实表设计方法
  • 维度表
  • 什么是缓慢变化的维度
  • 维度表设计方法
  • 如何维度建模
  • 最常见的三种数据仓库建模体系
  • 什么是ETL？
  • 常用的ETL工具
  • 联机分析处理 OLAP
  • OLAP和OLTP的区别
  • 元数据（Metadata）
  • ER建模


如何理解数仓

  • 数据仓库就是整合多个数据源的历史数据进行细粒度的、多维度的分析，帮助高层管理者或者业务分析员做出决策。
  • 数据仓库是一个面向主题的、集成的、随时间变化的、但信息本身相对稳定的数据集合，用于对管理决策过程的支持。

为什么要数据仓库建模

  • 性能：良好的模型能帮我们快速查询需要的数据，减少数据的IO吞吐
  • 成本：减少数据冗余、计算结果复用、从而降低存储和计算成本
  • 效率：改善用户使用数据的体验，提高使用数据的效率
  • 改善统计口径的不一致性，减少数据计算错误的可能性

为什么要设计数据分层

  • 需要一套行之有效的数据组织和管理方法来让我们的数据体系更有序
  • 复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层解决特定的问题，
  • 清晰数据结构：每一个数据分层都有它的作用域和职责，在使用表的时候能够更方便地定位和理解
  • 数据血缘追踪：当数据出现问题之后，快速准确地定位到问题，并清楚它的危害范围
  • 减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算，用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量冗余的数据。
  • 统一数据口径：通过数据分层，提供统一的数据出口，统一对外输出的数据口径

通用的数据分层设计

  • 数据运营层 ODS、数据仓库层 DW(DWD、DWM、DWS)、数据应用层 ADS、维表层
  • 数据运营层 ODS （Operational Data Store） 面向主题的
  • 接入的是原始数据，一般不做处理
  • 参考Sqoop导入的数据类型进行建模，Canal 监听 Mysql 的 Binlog，埋点日志，Flume，Kafka等
  • 在ODS层中，由于各端的开发团队不同PC端、H访问、小程序等或者各种其它问题，用户的访问日志被分成了好几张表上报到了我们的ODS层。
  • ODS层数据还起到一个数据备份作用,如果是比较特殊行业,在ODS层的数据会保留一年甚至多年.不过普通公司一般就保存3–6个月,看数据量和存储压力以及存储预算决定.
  • 数据仓库层 DW （ Data Warehouse）
  • 存放我们要重点设计的数据仓库中间层数据
  • 数据明细层 DWD（ Data Warehouse Detail）
  • 对ODS层数据做一定的清洗和主题汇总
  • DWD层做了一张用户访问行为日志表，在这里，我们将PC网页、H5、小程序和原生APP访问日志汇聚到一张表里面，统一字段名，对部分枚举类型的值进行翻译，剔除异常数据，字段命名规范化、时间字段的统一，保证质量。
  • 提高易用性，采用维度退化手法（把几个维度放在一起），将商品一二三级分类表，sku商品表，spu商品表，商品品牌表合并汇总为一张维度表！
  • 数据中间层 DWM (Data WareHouse Middle）
  • 对明细数据按照常用维度做初步的汇总
  • 选取业务关注的核心维度来做聚合操作，比如只保留人、商品、设备和页面区域维度，计算出常用的统计指标：次数、时长。用户访问天表，用户登陆行为天表，订单行为天表
  • 对数据做轻度的聚合操作，生成中间表，提升公共指标的复用性，减少重复加工
  • 数据服务层 DWS （Data WareHouse Servce）
  • 又称数据集市或宽表
  • 将一个人在整个网站中的行为数据放到一张表中，构建商品、商铺、地址主题宽表，更多用户相关的指标：登陆次数、访问次数、添加购物车次数、购买商品数、购买不同商品数、购买金额，时间维度数据：天、月级的数据。
  • 按照业务划分，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。
  • 在实际计算中，如果直接从DWD或者ODS计算出宽表的统计指标，会存在计算量太大并且维度太少的问题，因此一般的做法是，在DWM层先计算出多个小的中间表，然后再拼接成一张DWS的宽表。
  • dws和dwd是并行的
  • 数据应用层 APP （Application）
  • 面向业务定制的应用数据
  • 主要是提供给数据产品和数据分析使用的数据
  • 报表数据存放在这

数据仓库 面试 数据仓库面试总结_数据库

  • 维表层 （Dimension）
  • 高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。
  • 低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表。数据量可能是个位数或者几千几万。

分层的原则是什么？

  • 从对应用的支持来讲，我们希望越靠上层次，越对应用友好。比如APP层，基本是完全为应用来设计的，很易懂，DWS层的话，相对来讲就会有一点点理解成本，然后DWM和DWD层就比较难理解了，因为它的维度可能会比较多，而且一个需求可能要多张表经过很复杂的计算才能完成。
  • 从能力范围来讲，我们希望80%需求由20%的表来支持。直接点讲，就是大部分（80%以上）的需求，都用DWS的表来支持就行， DWS支持不了的，就用DWM和DWD的表来支持，这些都支持不了的极少一部分数据需要从原始日志中捞取。结合第一点来讲的话就是：80%的需求，我们都希望以对应用很友好的方式来支持，而不是直接暴露给应用方原始日志。
  • 从数据聚合程度来讲，我们希望，越上层数据的聚合程度越高，看上面的例子即可，ODS和DWD的数据基本是原始日志的粒度，不做任何聚合操作，DWM做了轻度的聚合操作只保留了通用的维度，DWS做了更高的聚合操作，可能只保留一到两个能表征当前描述主体的维度。从这个角度来看，我们又可以理解为我们是按照数据的聚合程度来划分数据层次的。

数据集市和数据仓库的区别

  • 数据仓库是企业级的，能为整个企业各个部门的运行提供决策支持手段；
  • 数据集市则是一种微型的数据仓库,它通常有更少的数据,更少的主题区域,以及更少的历史数据,因此是部门级的，

数据库和数据仓库有什么区别？

  • 面向主题特性，操作型数据库是为了支撑各种业务而建立，需要严格满足完整性等约束和范式设计要求，而分析型数据库则是为了对从各种繁杂业务中抽象出来的分析主题(如用户、成本、商品等)进行分析而建立，可以不满足第一范式
  • 集成性，数据仓库会将不同源数据库中的数据汇总到一起，比数据库更加庞大；
  • 范围性，数据仓库内的数据是面向公司全局的，数据库可能面向部门的
  • 历史性，数据仓库的时间跨度比较长，数据库可能保存几个月，数据仓库可能几年

维度建模三种模式 星形、雪花、星座

  • 星形模式
  • 由一个事实表和一组维表构成，以事实表为核心，维表围绕核心呈星状分布
  • 维表只和事实表关联，维表之间没有关联
  • 每个维表的主键为单列，且该主键放在事实表中，作为两边连接的外码
  • 雪花模式
  • 每个维表可继续向外连接多个子维表
  • 雪花模型相当于将星形模式的大维表拆分成小维表，满足规范化设计，再实际中较少
  • 星座模式
  • 多对多
  • 维度空间内的事实表可能不止一个，一个维表可能被多个事实表用到
  • 好处：能够共享维度 和 设置细节/聚集事实表
  • 共享维度：公司希望用分析销售主题的方法分析劣质产品，不需要重新建模，只需要加入一个新的劣质产品事实表
  • 细节事实表：每条记录表示单一事实，通常设置TID属性，查询灵活但速度慢
  • 聚集事实表：每条记录聚合多条事实，无TID属性，速度快但查询功能受到一定限制
  • 常见做法同时设置这两种事实表
  • 常考虑两个属性：
  • 事物标识码（TID），各种订单号、事物编号，不放入维度表是因为数量级太大每次查询都会耗很多资源来join，将某些逻辑意义上的维度放到事实表里的做法称为退化维度
  • 事务时间：数量级大，分布式数据仓库工具会对数据进行分区，默认分区字段为日期
  • 三种模式对比
  • 雪花模式是将星形模式的维表进一步划分，使维表满足规范化设计
  • 星座模式允许星形模式中出现多个事实表

事实表

  • 发生在现实世界中的操作型事件，其所产生的可度量数值，存储在事实表中。从最低的粒度级别来看，事实表行对应一个度量事件， 反之亦然。
  • 表示对分析主题的度量。比如上面那个例子中，200元就是事实信息。事实表包含了与各维度表相关联的外键，并通过JOIN方式与维度表关联。事实表的度量通常是数值类型，且记录数会不断增加，表规模迅速增长。
  • 事实表三种类型
  • 事务事实表：类似于mysql binlog日志，每一次相关的 change 都记录下来，生成一行新的数据
  • 周期快照事实表：只看某个业务过程，比如订单收货，数据按订单收货时间来切分，周期可以为每天、每月等。
  • 积累快照事实表：要看整个生命周期的多个业务过程，比如：创建订单 → 买家付款 → 卖家发货 → 买家确认收货。粒度是一个订单一行数据，创建订单时间，付款时间，发货时间，收货时间，分别作为一个字段，便于计算不同业务过程的时间间隔
  • 事实表设计8大原则
  1. 尽可能包含所有与业务过程相关的事实
  2. 只选择与业务过程相关的事实，订单的下单这个业务过程，事实表中不应该存在支付金额这个表示支付业务过程的事实；
  3. 分解不可加性事实为可加的组件，订单的优惠率，应分解为订单原价金额与订单优惠金额两个事实存储在事实表中；
  4. 在选择维度和事实之前必须先声明粒度，计事实表时，粒度定义越细越好，一般从最低级别的原子粒度开始；
  5. 在同一个事实表中不能有多种不同粒度的事实，粒度为票一级时，票支付金额和折扣金额这两个事实的粒度为 “票级”，与定义的粒度一致，订单支付金额和票数，属于订单级细粒度，不能放一起，一个订单可以支付多张票
  6. 事实的单位要保持一致
  7. 对事实的null值要处理 ，用0代替null
  8. 使用退化维度提高事实表的易用性，谨慎使用退化维表，目的主要是为了减少下游用户使用时关联多个表的操作，直接通过退化维度。实现对事实表的过滤查询、控制聚合层次、排序数据以及定义主从关系等。

事实表设计方法

  • 选择业务过程及确定事实表类型、声明粒度、确定维度、确定事实、冗余维度
  • 思路：详细分析需求，对业务的整个生命周期进行分析，明确关键的业务步骤，从而选择与需求有关的业务过程；业务过程通常使用行为动词表示业务执行的活动；
  • 该订单流转的业务过程有 4 个：创建订单 → 买家付款 → 卖家发货 → 买家确认收货；
  • 选择与维度建模有关的业务过程：是选择 “买家付款” 这个业务过程，还是选择 “创建订单” 和 “买家付款” 这两个业务过程
  • 根据所选的业务过程确定事实表类型；如，选择 “买家付款” 这个业务过程，则事实表类型应为只包含买家付款这一个业务过程的 “单事务事实表”；，选择了所有 4 个业务过程，并且需要分享各业务过程的时间间隔，则事实表类型应为包含了所有 4 个业务过程的 “累积快照事实表”
  • 声明粒度
  • 意味着精确定义事实表的每一行所表示的业务含义；明确的粒度能够确保对实表中行的意思的理解不会产生混淆，保证所有的事实按照同样的细节层次记录；
  • 尽量选择最细级别的原子粒度，以确保事实表的应用具有最大的灵活性；
  • 确定维度
  • 完成了粒度声明，就意味着确定了主键，对应的维度组合以及相关的维度字段也可以确定了；
  • 选择维度的原则：应该选择能够描述清楚业务过程所处的环境的维度信息；如，淘宝订单 “付款事务事实表” 中，粒度为 “子订单”，相关的维度有买家、卖家、商品、收货人信息、业务类型、订单时间等；
  • 确定事实
  • 选择与业务过程有关的所有事实，且事实的粒度要与所声明的事实表的粒度一致；
  • 冗余维度

维度表

  • 每个维度表都包含单一的主键列。维度表的主键可以作为与之关联的任何事实表的外键
  • 表示对分析主题所属类型的描述。比如"昨天早上张三在京东花费200元购买了一个皮包"。那么以购买为主题进行分析，可从这段信息中提取三个维度：时间维度(昨天早上)，地点维度(京东), 商品维度(皮包)。通常来说维度表信息比较固定，且数据量小
  • 维度表设计原则
  1. 维度属性尽量丰富，为数据使用打下基础
  2. 给出详实的、富有意义的文字描述
  3. 区分数值型属性和事实。数值型宇段是作为事实还是维度属性，可以参考字段的一般用途。 如果通常用于查询约束条件或分组统计，则是作为维度属性;如果通常用于参与度量的计算， 则是作为事实。 如果数值型字段是离散值，则作为维度属性存在的可能性较大;如果数值型字段是连续值 ，则作为度量存在的可能性较大，但并不绝对，需要 同时参考宇段的具体用途。
  4. 沉淀出通用的维度属性，为建立一致性维度做好铺垫，把那些比较复杂的逻辑处理的字段另外再放一个
  5. 退化维度，这种维度指的是直接把一些简单的维度放在事实表中。
  6. 缓慢变化维，一般使用代理健作为维度表的主健

什么是缓慢变化的维度

  • 当业务数据库中的一些数据发送了变化，如顾客的联系方式发生改变，应该如何把这些变化也反映到数据仓库中，一些基本信息的更改可能会引起数据归纳和分析出现的问题。在数据仓库中，其数据主要的特征一是静态历史数据，二是少改变不删除，三是定期增长，其作用主要用来数据分析。
  • 缓慢渐变类型一 ：不记录历史数据，将旧的业务数据覆盖重写
  • 缓慢渐变类型二 ：保存多条记录，直接新添记录，新增加一个 Key（代理键），配合时间戳 告诉数据仓库 哪个是最新在用的 ，通常是DW表的主键，用来连接业务数据库 和 数据仓库的，
  • 代理键的好处：
  • 解决这种缓慢渐变维度，维护历史信息记录
  • business key可能较长**，代理键可以设置为整形，效率高节省体积**
  • 业务数据库来自不同的系统，可能出现相同的business key，用代理键可以处理/
  • 缓慢渐变类型三：添加历史列，用不同的字段保存变化痕迹.它只能保存两次变化记录.适用于变化不超过两次的维度

维度表设计方法

  • 选择维度或新建维度。作为维度建模的核心，在企业级数 据仓库中必须保证维度的唯一性。以淘宝商品维度为例，有且只允许有 一个维度定义。
  • 确定主维表。此处的主维表一般是 ODS 表，直接与业务 系统同步。以淘宝商品维度为例， s_auction_auctions 是与前台商品中心 系统同步的商品表，此表即是主维表。
  • 确定相关维表。数据仓库是业务源系统的数据整合，不同业务系统或者同 一业务系统中的表之间存在 关联性。根据对业务的梳 理，确定哪些表和主维表存在关联关系，并选择其中的某些表用于生成维度属性。
  • 确定维度属性 。
  • 从主维表中选择维度属性或生成新的维度属性
  • 从相关维表中选择维度属性或生成新的维度属性。

如何维度建模



地区 - 商店 - 交易记录 - 顾客 - 产品 - 种类 - 卖主

  • 哪些维度对主题分析（这里是销售额）有用
  • 产品product、顾客customer、商店store、日期date 对销售额分析有帮助
  • 如何使用现有数据生成维表
  • 产品维度 可由产品关系、供应商关系和种类关系得到
  • 顾客维度 可由顾客关系得到
  • 商店维度 可由商店维度和地理维度得到
  • 日期维度 可由交易记录的日期列得到
  • 用什么指标来度量主题
  • 本例的主题是销售，销售和销售额最能反映销售情况
  • 如何用现有数据生成事实表
  • 销售和销售额信息可以通过交易记录得到
  • 维表不满足3NF，事实表1NF都不满足，各维表的主键由xxID 变成 xxKey，Key这样的字段被称为代理码(surrogate key)，它是一个通过自动分配整数生成的主码，没有任何其他意义。使用它主要是为了能够处理"缓慢变化的维度"

最常见的三种数据仓库建模体系

  • 规范化数据仓库：规范化设计的分析型数据库，首先对ETL得到的数据进行ER建模，关系建模，得到一个规范化的数据库模式。各部门开发人员大都从这些数据集市提数，通常来说不允许直接访问中心数据库。
  • 维度建模数据仓库： 使用交错维度进行建模的数据仓库，创建一个大星座模型表示所有分析型数据
  • 独立数据集市：公司的各个组织自己创建并完成ETL，自己维护自己的数据集市，信息分散，效率低
  • 三种对比
  • 规范化数据仓库：需要全局进行规范化建模，前期花费时间大，投入使用慢，后期容易维护
  • 维度建模数据仓库：敏捷性强，适用于业务变化频繁的情况，开发要求没那么高

什么是ETL？

  • ETL，是英文Extract-Transform-Load的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程，是数据仓库的生命线。
  • 抽取（Extract）主要是针对各个业务系统及不同服务器的分散数据，充分理解数据定义后，规划需要的数据源及数据定义，制定可操作的数据源，制定增量抽取和缓慢渐变的规则。
  • 转换（transform）主要是针对数据仓库建立的模型，通过一系列的转换来实现将数据从业务模型到分析模型，通过ETL工具可视化拖拽操作可以直接使用标准的内置代码片段功能、自定义脚本、函数、存储过程以及其他的扩展方式，实现了各种复杂的转换，并且支持自动分析日志，清楚的监控数据转换的状态并优化分析模型。
  • 装载（Load）主要是将经过转换的数据装载到数据仓库里面，可以通过直连数据库的方式来进行数据装载，可以充分体现高效性。在应用的时候可以随时调整数据抽取工作的运行方式，可以灵活的集成到其他管理系统中
  • 在传统数仓中，数据量小，计算逻辑相对简单，我们可以直接用ETL工具实现数据转换（T），转换之后再加载到目标库。但在大数据场景下，数据量越大越大，计算逻辑愈发复杂，数据清洗需放在运算能力更强的分布式计算引擎中完成，ETL也就变成了ELT（Extract-Load-Transform）。即：Extract-Transform-Load >> Extract-Load-Transform
  • ETL加载策略：增量、全量、流式
  • 增量：比较推荐的是全外连接full outer join + 数据全覆盖 insert overwrite
  • 全量：每天一个全量表，也可hive分区，一个天一个全量。
  • 流式（推荐）：使用kafka，消费mysql binlog日志到目标库，源表和目标库是1：1的镜像

常用的ETL工具

  • sqoop
  • 是Apache开源的一款在Hadoop和关系数据库服务器之间传输数据的工具。
  • 可以将一个关系型数据库（MySQL ,Oracle等）中的数据导入到Hadoop的HDFS中，也可以将HDFS的数据导出到关系型数据库中。
  • sqoop命令的本质是转化为MapReduce程序。
  • sqoop分为导入（import）和导出（export），策略分为table和query
  • 模式分为增量和全量。
  • Kettle
  • 一款国外免费开源的、可视化的、功能强大的ETL工具，纯java编写，可以在Windows、Linux、Unix上运行，数据抽取高效稳定。
  • canal
  • canal是阿里巴巴旗下的一款开源项目，纯Java开发。基于数据库增量日志解析，提供增量数据实时订阅和消费，目前主要支持了MySQL，也支持mariaDB。

联机分析处理 OLAP

  • OLAP需要以大量历史数据为基础，再配合上时间点的差异，对多维度及汇整型的信息进行复杂的分析。
  • Online Analytical Process，以多维度的方式分析数据，而且能够弹性地提供**上卷（Roll-up）、下钻（Drill-down）和透视分析（Pivot）**等操作，它是呈现集成性决策信息的方法，多用于决策支持系统、商务智能或数据仓库
  • OLAP分类
  • MOLAP，基于多维数组的存储模型，也是OLAP最初的形态，特点是对数据进行预计算，以空间换效率，明细和聚合数据都保存在cube中。但生成cube需要大量时间和空间。
  • ROLAP，基于关系模型进行存储数据，不需要预计算，按需即时查询。明细和汇总数据都保存在关系型数据库事实表中。其特点是与事务实体对应，关系清晰；但一般需要较为复杂的数据准备。在响应前端需求时，一般较快，但取决于计算引擎能力。
  • HOLAP，混合模型，细节数据以ROLAP存放，聚合数据以MOLAP存放。这种方式相对灵活，且更加高效。可按企业业务场景和数据粒度进行取舍，没有最好，只有最适合。
  • OLAP基本操作
  • 钻取：维的层次变化，从粗粒度到细粒度，汇总数据下钻到明细数据。如通过季度销售数据钻取每个月的销售数据
  • 上卷：钻取的逆，向上钻取。从细粒度到粗粒度，细粒度数据到不同维层级的汇总。eg. 通过每个月的销售数据汇总季度、年销售数据
  • 切块：维区间数据（剩余维三个）。eg. 第一季度到第二季度销售数据
  • 切片：特定维数据（剩余维两个）。eg. 只选电子产品销售数据
  • 旋转：维位置互换（数据行列互换），通过旋转可以得到不同视角的数据
  • OLAP选型： kylin 和 druid

OLAP和OLTP的区别

  • 联机交易处理（OLTP），联机交易处理，更侧重于基本的、日常的事务处理，包括数据的增删改查。

OLTP

OLAP

对象

业务开发人员

分析决策人员

功能

日常事务处理

面向分析决策

模型

关系模型

多维模型

数据量

几条或几十条记录

＞百万于万条记录

操作类型

增、删、查、改(CRUD)

查询为主

总体概括

联机事务处理

在线分析处理

元数据（Metadata）

  • 一个管理元数据信息的系统，能够提供方便的元数据的操作和查询操作
  • metadata 即元数据。包含 database、tabel、column names、partitions 信息、bucketing 信息等的元数据信息。  元数据默认是存储在 Derby 中，建议存储在关系型数据库中。

ER建模

  • 实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。
  • **实体：矩形 | 属性：椭圆 | 联系：菱形 4种基数约束 **
  • 基数约束
  • 一个实体A对应多个实体B
  • 一个实体A对应0个或多个实体B
  • 一个实体A对应一个实体B
  • 一个实体A对应0个或1个实体B
  • 复合属性 圆括号 地址属性 包括省份 城市 街道
  • 多值属性 双层椭圆 属性有多值，一个职工可能有多个电话号码
  • 派生属性 虚线椭圆 从其他属性或者其他数据(如当前日期)派生出来
  • 可选属性 属性名后面添加(0)标识分属性可能有也可能没有取值，比如说职工奖金。
  • 基数约束上可以加最大最小基数、联系的角色