数据仓库基础

1. *数仓中是如何划分主题的？

主题（Subject）是在较高层次上将企业信息系统中的数据进行综合，归类和分析利用的一个抽象概念，每一个主题基本对应一个宏观的分析领域。在逻辑意义上，它是对应企业中某一宏观分析领域所涉及的分析对象。例如“销售分析”就是一个分析领域，因此这个数据仓库应用的主题就是“销售分析”。
主题是根据分析的要求来确定的。这与按照数据处理或应用的要求来组织数据是不同的。如在生产企业中，同样是材料供应，在操作型数据库系统中，人们所关系的是怎样更方便和更快捷地进行材料供应的业务处理；而在进行分析处理时，人们就应该关心材料的不同采购渠道和材料供应是否即时，以及材料质量状况等。

技巧

考察数据仓库的相关知识：主题。目前大部分企业的数据仓库都是划分主题的。回答重点是主题是根据分析的要求来划分确定的。可以通过举例的方式来进行回答，讲一下自己项目中划分了哪些主题。

2. *数据如何分层？

一般情况下，将数据模型分为3层：

1.源数据层ODS

存放的是接入的原始数据。
经过ETL之后装入本层，大多是按照源头业务系统的分类方式而分类的，为了考虑后续可能追溯数据为题，因此对这一层不建议做过多的数据清洗工作，原封不动接入源数据即可，至于数据的去噪，去重，异常值处理等过程可以放在后面的DW层。

数据仓库层DW

重点设计的数据仓库中间层数据，在这里ODS层获得的数据按照主题建立各种数据模型，DW又细分：

  • 数据明细层：DWD（Data WareHouse Detail）
    该层一般保持和ODS层一样的数据颗粒，并且提供给一定的数据质量保证。同时为了提高数据明细层的易用性，该层会采用一些维度退化手法，将维度退化到事实表中，减少事实表和维度表的关联。另外，在该层也会做一部分的数据集合，将相同主题的数据汇集到一张表中，提高数据的可用性。
  • 数据中间层：DWM（Data WareHouse Middle）
    在DWD层的数据基础上，对数据做轻度的聚合操作，生成一系列的中间表提升公共指标的复用性，减少重复加工，直观来说，就是对通用的核心维度进行聚合操作，算出相应的统计指标。
  • 数据服务层：DWS（Data WareHouse Service）
    又称为数据集市或者宽表，按照业务划分，例如流量，订单，用户等，生成字段比较多的宽表，用于后续的业务查询，OLAP分析，数据分析等。
技巧

考察数据仓库的分层，结合项目来讲。需要讲清楚项目分了几层，然后每一层都有哪些数据。

3. *数仓和普通数据库区别是什么？

数据库与数据仓库的区别实际讲的是OLTP与OLAP的区别。
联机事务处理OLTP（on-line transaction processing）主要是执行基本的，日常的事务处理，比如数据库记录的增，删，改，查。比如在银行存取一笔款，就是一个事务交易。
OLTP的特点一般有：

  • 实时性要求高；
  • 数据量不是很大；
  • 交易一般是确定的，所以OLTP是对确定性的数据进行存取：（比如存取款都有一个特定的金额）；
  • 并发性要求高并且严格的要求事务的完整，安全性。（比如这种情况：有可能你和你的家人同时在不同的银行取同一个账号的款）。
    联机分析处理OLAP（on-line analytical processing）是数据仓库系统的主要应用，支持负载的分析操作，侧重决策支持，并且提供直观易懂的查询结果。典型的应用就是复杂的动态的报表系统。
    OLAP的特点一般有：
  • 实时性要求不是很高，很多应用的顶多是每天更新一下数据；
  • 数据量大，因为OLAP支持的是动态查询，所以用户也许要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大；
  • 因为重点在于决策支持，所以查询一般是动态的，也就是说允许用户随时提出查询的要求。所以在OLAP中通过一个重要概念“维”来搭建一个动态查询的平台（或技术），供用户自己去决定需要知道什么信息。
    简单来说：
  • OLTP即联机事务处理，就是我们经常说的关系数据库，以及记录即时的增，删，改查，就是我们经常应用的东西，这是数据库的基础；TPCC（Transaction　Processing　Performance　Council）属于此类。
  • OLAP即联机分析处理，是数据仓库的核心部分，所谓数据仓库是对于大量已经由OLTP形成的数据的一种分析型的数据库，用于处理商业智能，决策支持等重要的决策信息；数据仓库是在数据库应用到一定程序之后而对历史数据的加工与分析，读取较多，更新较少，TPCH属于此类，对于OLAP，列存储模式比通常的行存储模式可能更具优势。
  • OLAP不应该对OLTP产生任何影响，（理想情况下）OLTP应该完全感觉不到OLAP的存在。

OLTP

OLAP

用户

操作人员,低层管理人员

决策人员,高级管理人员

功能

日常操作处理

分析决策

DB设计

面向应用

面向主题

数据

当前的,最新的细节的,二维的分立的

历史的,聚集的,多维的集成的,统一的

存取

读/写数十条记录

读上百万条记录

工作单位

简单的事务

复杂的查询

用户数

上千个

上百万个

DB大小

100MB-GB

100GB-TB

时间要求

具有实时性

对时间的要求不严格

主要应用

数据库

数据仓库

技巧

此处可以讲一下OLTP和OLAP的区别.从应用,功能,时间要求,数据量等方面进行回答.

4.用过什么ETL工具?

ETL常用的三种工具–Datastage,Informatica,Kettle。

技巧

了解一种ETL工具即可。比如Kettle。

5. *星型模型和雪花模型的区别？

事实表：一般是用户行为产生的数据，数据量比较大。

维度表：一般是一些属性信息，用户信息表，产品信息表等。这些属性信息不经常变动。

数据仓库模型：星型模型和雪花模型

星型模型：当所有维表都直接连接到”事实表“上时，整个图解就像星星一样，故将该模型称为星型模型。特点：星型架构是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连接，不存在渐变维度，所以数据有一定的冗余，如果地域维度表中，存在国家A省B的城市C以及国家A省B的城市D两条记录，那么国家A和省B的信息分别存储了两次，即存在冗余。

图 : 销售数据仓库中的星型模型

数据仓库主题域创建实例 数据仓库主题如何划分_数据


雪花模型：当有一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起，故称雪花模型。雪花模型是对星型模型的扩展。它对星型模型的维表进一步层次化，原有的各维表可能被扩展为小的事实表，形成一些局部的“层次”区域，这些被分解的表都连接到主维度表而不是事实表。如图，将地域维度表又分解为国家，省份，城市等维表。它的优点是：通过最大限度地减少数据存储量以及联合较小地维表来改善查询性能。雪花型结构去除了数据冗余。

数据仓库主题域创建实例 数据仓库主题如何划分_数据仓库_02


星型模型因为数据地冗余，所以很多统计查询不需要做外部的连接，因此一般情况下效率比雪花模型要高。星型模型不用考虑很多正规化的因素，设计与实现都比较简单。雪花模型由于去除了冗余，有些统计就需要通过表的连接才能产生，所以效率不一定有星型模型高。正规化也是一种比较复杂的过程，相应的数据库结构设计，数据的ETL，以及后期的维护都要复杂一些。因此在冗余可以接受的前提下，实际运用中星型模型使用更多，也更有效率。

技巧

考察数据仓库的模型。以及相关的一些概念。需要能够回答出星型模型和雪花模型的区别。以及在项目中的使用情况。一般星型模型运用的比较多，效率更高。

用户留存和拉链表

1. *用户留存怎么做？

1.留存率计算方法

假如今天新增了100名用户，第二天登录了50名，则次日留存率为50/100=50%，第三天登录了30名，则第二日留存率为30/100=30%，以此类推。

2.用SQL的计算思路
  1. 用SQL调取出user_id和用户login_time的表，获得新增用户登录时间表。
  2. 根据user_id和login_time，增加一列first_day，此列存着每个用户最早登录时间。
  3. 有了最早登录时间和所有的登录时间，再增加一列by_day，这一列是用login_time-first_day，得到0，1，2，3，4，5…，这就得到了某一天登录离第一次登录有多长时间。
  4. 然后从表中提取数据，找到first_day对应的with_first列中0有多少个，1有多少个，一直到7以上。

    根据此表，就很容易计算出每天引流的留存率。
技巧

需要能够回答出留存率是如何计算的，不过一般不会问相关概念，而是直接给一个需求，让你进行写SQL计算。所以应该平时多练习SQL语句。

2.拉链表怎么创建

拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。

下面就是一张拉链表，存储的是用户的最基本信息以及每条记录的生命周期。我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。

数据仓库主题域创建实例 数据仓库主题如何划分_数据_03


说明：

  • t_start_date表示该条记录的生命周期开始时间，t_end_date表示该条记录的生命周期结束时间。
  • t_end_date = ‘9999-12-31’ 表示该条记录目前处于有效状态。
  • 如果查询当前所有有效的记录，则 select * from user where t_end_date = ‘9999-12-31’。
  • 如果查询 2017-01-01 的历史快照，则 select * from user where t_start_date <= ‘2017-01-01’ and end_date >=‘2017-01-01’。
1.拉链表的使用场景

在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计：

  • 有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用了ORC压缩，单张表的存储也会超多100G，在HDFS使用双备份或者三备份的话就更大一些。
  • 表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等。
  • 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。
  • 表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。
    对于这种表的设计？下面有几种方案可选：
    方案一：每天只留最新的一份，比如我们每天用datax抽取最新的一份全量数据到Hive中。
    方案二：每天保留一份全量的切片数据。
    方案三：使用拉链表。
2.为什么使用拉链表？

方案一：每天只留最新的一份
这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽取一份最新的。
优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。
缺点同样明显，没有历史数据，先翻翻旧账只能通过其他方式，比如从流水表里面抽。
方案二：每天保留一份全量的切片数据
每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。
缺点就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费。
当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无知的，数据的生命周期不是我们能完全左右的。
方案三：拉链表
拉链表在使用上基本兼顾了我们的需求。
首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至万分之一。
其实它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史数据。所以我们还是很有必要来使用拉链表的。

技巧

考察数据仓库建设中，拉链表的使用。用于历史数据变更这种场景。需要能够回答出拉链表的应用情况，拉链表的说明，拉链表如何创建。