数据仓库存储的数据库 数据仓库可实现的功能

一 数据仓库的定义和作用

1.1 数据仓库的定义

1. 数据仓库是有bill inmon提出的概念
2. 数据仓库是一种面向主题的，集成的，相对稳定(不可变更)的，反映历史变化的数据集合。
3. 数据仓库是用于支持领导管理决策或者是信息的全局共享
4. 可以为数据分析，数据挖掘，机器学习提供数据支持

1.2 数据仓库的作用

1. 整合公司所有业务数据，建立统一的数据中心
2. 产生业务报表，用于作出决策
3. 为网站运营提供运营上的数据支持
4. 可以作为各个业务的数据源，形成业务数据互相反馈的良性循环
5. 分析用户行为数据，通过数据挖掘来降低投入成本，提高投入效果
6. 开发数据产品，直接或间接地为公司盈利

1.3 数据仓库与数据库的区别

1. 着重点不同：
   - 数据库着重于数据的业务处理（数据的增删改）、也就是数据的OLTP处理
   - 数据仓库着重于数据的分析，通常都是面向某一个行业，领域（查询），也就是数据的OLAP处理
2. 存储结构不同：
   - 数据库是面向行式存储。
   - 数据仓库是面向列式存储，利于查询和分析. 数据仓库也可以称之为"分析型数据库"
3. 使用的用户不同
   - 数据库主要是业务人员，人数相对大。会经常进行读和写操作。每次读和写的数据量都相对来说少。
   - 数据仓库主要是管理人员，人数相对少。会经常进行读操作，每次读取的数据量巨大
4. 使用的工具不同
   - 数据库主要用的是oracle、mysql、sqlserver等传统关系型数据库
   - 数据仓库主要用的是hive、mr、spark、flink
5. 数据的存储位置不同
   - 数据库的数据存储到本地文件系统，比如windows、linux、mac
   - 数据仓库的数据存储到分布式文件系统，比如hdfs，hbase
6. 响应时间不同
   - 数据库的反映时间是非常短的，毫秒级别
   - 数据仓库的反映时间较长，秒级别，分钟级别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ihamnRK3-1615727213477)(%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E7%9A%84%E6%A6%82%E5%BF%B5%E8%AE%B2%E8%A7%A3.assets/1575872977647.png)]

1.4 OLTP和OLAP的区别

1.  OLTP的全称是OLTP Online Transaction Processing，叫联机事务处理
	主要是针对于事务性非常高的系统，也就是高可用的在线系统，以小的事务以及小的查询为主，以传统的关系型数据库为主要应用，主要是基本的、日常的事务处理，主要为业务数据，例如银行交易
	OLTP对应的系统最容易出现瓶颈的地方就是CPU与磁盘的子系统，所以比较在意Cache技 术与B-tree索引技术
	OLTP对应的系统是一个数据块变化非常频繁，SQL语句提交非常频繁的系统
2. OLAP的全称是OLAP Online Analytical Processing，叫联机分析处理
	主要针对于DSS决策支持系统，也就是数据仓库，重点主要是面向分析，会产生大量的查询，一般很少涉及增删改
	OLAP不会将反映时间作为考核标准，而是磁盘的吞吐量，如能达到多少MB/s的流量。
	OLAP常关心的是分区技术、并行技术。分区技术可以避免全表扫描，也方便管理数据，并行技术可以提高查询性能

二 数据仓库的特征

2.1 面向主题

1. 主题（subject）,指的是特定的数据分析领域和目标
2. 主题是根据需求来确定的。
   比如在材料供应在，数据仓库要做的是分析不同的采购渠道的区别，材料供应是否及时，以及材料的质量问题
                  而数据库不会关系这些，只是做记录。
3. 数据仓库中的主题有时会因用户主观要求的变化而变化的，比如是决策人员的主管意识。

面向主题划分如下：

数据仓库面向在数据模型中已经定义好的公司的主要主题领域。典型的主题领域包括顾客、产品、订单和财务 或是其他某项事务或活动。 

基本主题： 
教育机构：学生、讲师、班主任、课程等 
电商行业：运营、流量、价值、商品、市场、风控、销售等 
传统行业：供应商、商品、客户、仓库等

主题域

1. 一些紧密关联的主题，构成一个主题域。
2. 主题域的划分由最终用户和数据仓库的分析人员来共同完成
3. 一个主题域会涉及到多个实体表、维度表、关系表
4. 划分规则如下：
    a:每个主题域包含一个主要业务概念; 
    b:每个主题域包含一个主要交易业务概念,用一个或几个核心实体来表述。 
    c:主题域与主题域之间的核心实体不能重叠,核心实体间的关系实体则可以出现在两个主题域内; 
    d:每个主题域中包含几个关键的核心实体,且这几个核心实体间具有直接的关联关系。
5. 数据仓库可以包含多个主题域

2.2 数据是集成的

1. 分析一个事物时，数据应该是全面的，也就是说要将和这个事物有关的所有数据都集成到数据仓库中，分析才会有意义
   比如分析王者荣耀玩家的信息，不应该只对王者荣耀客户端产生的数据，还需要其他相关产品产生的数据，比如微信上的王者荣耀小程序，腾讯视频的相关链接，手机应用商店的下载量等，
2. 	集成的方法：
    统一：消除不一致的现象
    综合：对原有数据进行综合和计算
3. 集成需要考虑的问题：
    数据格式： json格式和业务表的格式不同，在数据仓库重要统一
    计量单位： 一个是毫秒，一个是秒，也要统一
    数据代码和名称含义混乱    两个表的数据内容相同，但是字段名不同。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0lUZdQjs-1615727213479)(%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E7%9A%84%E6%A6%82%E5%BF%B5%E8%AE%B2%E8%A7%A3.assets/2975846-fc2d3eebe87a68ab.png)]

2.3 相对稳定（非易失的）

-1. 数据仓库的数据是经过抽取而形成的分析型数据，不具有原始性，主要供企业决策分析之用
-2. 数据仓库执行的主要是‘查询’操作，一般情况下不执行‘更新’操作，只是在初始时导入数据。
    相对而言，数据库要经常频繁的“更新”数据
-3. 数据仓库要具有稳定的环境。

2.4 反映历史变化（拉链）

1. 数据仓库中，时间维是数据仓库中很重要的一个维度
2. 数据仓库中的数据的时间跨度会很大，可能会是几年到几十年，称为历史数据
3. 数据仓库中的数据不应该是一成不变的，而是应该保留最近一段时间内的数据，超出这个时间段的数据要删除掉
4. 数据变化方式: 
    –不断增加新的数据内容
    –不断删去旧的数据内容
    –更新与时间有关的综合数据
5. 举例说明：
	比如银行使用的是7年和13个月的时间段。
	7年：指的是保留一般客户类、账户类等信息的时间期限
	13个月：指的保留用户的流水信息

三 数据仓库的分层

3.1 分层的原因

主要原因是在管理数据的时候，能对数据有一个更加清晰的掌控。详细来讲，主要有下面几个原因

1. 数据结构清晰
每一个数据分层都有它的作用域，这样我们在使用表的时候能更方便地定位和理解。
2. 数据血缘追踪
简单来说，我们最终给业务呈现的是一个能直接使用业务表，但是它的来源有很多，如果有一张来
源表出问题了，我们希望能够快速准确地定位到问题，并清楚它的危害范围。
3. 减少重复开发
规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算。
4. 把复杂问题简单化
将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且
便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤
开始修复。
5. 屏蔽原始数据的异常
屏蔽业务的影响，不必改一次业务就需要重新接入数据

3.2 分层的价值

1. 高效的数据组织形式【易维护】
面向主题的特性决定了数据仓库拥有业务数据库所无法拥有的高效的数据组织形式，更加完
整的数据体系，清晰的数据分类和分层机制。因为所有数据在进入数据仓库之前都经过清洗
和过滤，使原始数据不再杂乱无章，基于优化查询的组织形式，有效提高数据获取、统计和
分析的效率。
2. 时间价值【高性能】
数据仓库的构建将大大缩短获取信息的时间，数据仓库作为数据的集合，所有的信息都可以
从数据仓库直接获取，数据仓库的最大优势在于一旦底层从各类数据源到数据仓库的ETL流程
构建成型，那么每天就会有来自各方面的信息通过自动任务调度的形式流入数据仓库，从而
使一切基于这些底层信息的数据获取的效率达到迅速提升。
从应用来看，使用数据仓库可以大大提高数据的查询效率，尤其对于海量数据的关联查询和
复杂查询，所以数据仓库有利于实现复杂的统计需求，提高数据统计的效率。
3. 集成价值【简单化】
数据仓库是所有数据的集合，包括日志信息、数据库数据、文本数据、外部数据等都集成在
数据仓库中，对于应用来说，实现各种不同数据的关联并使多维分析更加方便，为从多角度
多层次地数据分析和决策制定提供的可能。
4. 历史数据【历史性】
记录历史是数据仓库的特性之一，数据仓库能够还原历史时间点上的产品状态、用户状态、
用户行为等，以便于能更好的回溯历史，分析历史，跟踪用户的历史行为，更好地比较历史
和总结历史，同时根据历史预测未来。

3.3 如何分层

数仓的常见分层一般为3层，分别为：数据操作层、数据仓库层和数据集市层。当然根据研发人员经验或者业务，可以分为更多不同的层，只要能达到流程清晰、方便查数即可。

3.3.1）ods层

Operate data store，操作数据存储，是最接近数据源中数据的一层，数据源中的数据，经过抽取、洗净、传输，也就说传说中的ETL之后，装入本层。本层的数据，总体上大多是按照源头业务系统的分类方式而分类的。

例如这一层可能包含的数据表可为：人口表（包含每个人的身份证号、姓名、性别、年龄、住址等）、机场登机记录（包含乘机人身份证号、航班号、乘机日期、起飞城市等）、银联的刷卡信息表（包含银行卡号、刷卡地点、刷卡时间、刷卡金额等）、银行账户表（包含银行卡号、持卡人身份证号等）等等一系列原始的业务数据。这里我们可以看到，这一层面的数据还具有鲜明的业务数据库的特征，甚至还具有一定的关系数据库中的 数据范式的组织形式。

但是，这一层面的数据却不完全等同于原始数据。在源数据装入这一层时，根据业务不同，可能会进行诸如去噪（例如去掉明显偏离正常水平的银行刷卡信息）、去重（例如银行账户信息、公安局人口信息中均含有人的姓名，但是只保留一份即可）、提脏（例如有的人的银行卡被盗刷，在十分钟内同时有两笔分别在中国和日本的刷卡信息，这便是脏数据）、业务提取、单位统一、砍字段（例如用于支撑前端系统工作，但是在数据挖掘中不需要的字段）、业务判别等多项工作。

ODS层数据的来源方式：

1. 业务库
	经常会使用sqoop来抽取，比如我们每天定时抽取一次。在实时方面，可以考虑用canal监听mysql的binlog，实时接入即可。

2. 埋点日志
	线上系统会打入各种日志，这些日志一般以文件的形式保存，我们可以选择用flume定时抽取，也可以用用spark streaming或者storm来实时接入，当然，kafka也会是一个关键的角色。

3. 其它数据源
	不同的业务其它数据源不一样,比如第三方数据。

2）dwd层

这一层主要解决一些数据质量问题和数据的完整度问题。比如用户的资料信息来自于很多不同表，而且经常出现延迟丢数据等问题，为了方便各个使用方更好的使用数据，我们可以在这一层做一个屏蔽。

3）dws层

轻度汇总层，从ODS层中对用户的行为做一个初步的汇总，抽象出来一些通用的维度：时间、ip、id，并根据这些维度做一些统计值，比如用户每个时间段在不同登录ip购买的商品数等。这里做一层轻度的汇总会让计算更加的高效，在此基础上如果计算仅7天、30天、90天的行为的话会快很多。我们希望80%的业务都能通过我们的DWS层计算，而不是ODS。

注意：dws与dwd通常是一对一的关系。

4）dm层

数据集市，以某个业务应用为出发点而建设的局部dw，dw只关心自己需要的数据，不会全盘考虑企业整体的数据架构和应用。每个应用有自己的dm

1.dm结构清晰、针对性强、扩展性好，因为dm仅仅是单对某一个领域而建立，容易维护和修改。
2.dm建设任务繁重，公司有众多业务，每一个业务如果单独建立一个数据仓库，工作量大。
3.dm的建立消耗更多存储空间，单独一个dm可能数据量不大，但是企业所有领域都建立dm数据量就会增加很多倍。

5）dim层（维度层）

DIM：这一层比较单纯，举个例子就明白，比如国家代码和国家名、地理位置、中文名、国旗图片等信息就存在DIM层中。

四 开发规范

4.1 数据库命名

指的是数据仓库在存储数据时，所对应的数据库名，如果是hive，则是database，如果是hbase，则是namespace.

数据库命名规则：数仓层_业务方式，如 ods_db_order

数仓各层对应数据库

ods/sda层 -> sda/ods_业务（原始数据） 

dw层 -> dw_业务 （主题库） 

dim层 -> dim_业务 （维表库） 

dm层 -> dm_业务（集市库） 

middle层 -> mid_业务（中间库）

临时数据 -> temp（临时库）

4.2 表命名

(1) 数据库表命名规则：

原始层表：数仓层_来源类型_业务 如 ods_01_xxx 

其他表：数仓层_业务 如 dw_xxx 

如果业务名称较长可以简写 如 ods_01_xxx_xx_xx/ods_01_xx

(2) 数据来源代码(sda层)

01 -> hdfs数据 
02 -> mysql数据 
03 -> redis数据 
04 -> mongodb数据 
05 -> tidb数据

如

ods_release.ods_01_release 投放数据 

ods_release.ods_02_user 注册用户表(业务表：存于MYSQL) 

dw_release.dw_customer 目标客户主题表 

dm_release.dm_customer_stat 目标客户统计表

五 名词解释

5.1 维度

维度：就是观察指标，度量值的一个分析角度。 
比如地域维度，就可以从省、市的角度来观察数据，人数数量，GDP, 汇入量，流失量
比如时间维度，就可以从年，季度，月，日，周等角度来统计数据，

city    channel    os      dev       number
1       2          2       4        7899
1       1          1`      3        10000


city：
1      北京
2      广州
3      郑州

channel:
1     淘宝
2	  京东
3     苏宁

os:
1     苹果
2     安卓

dev：
4     手机
3     平板
2     pc端
1     ..

2）维度基数

指的是某一个维度有多少不同的值。比如省份这个维度，有34个。维度基数就是34.

5.2 指标

指标用于衡量事物发展程度的单位或方法，它还有个IT上常用的名字，也就是度量。。例如，维度“城市”可以关联指标“人口”，其值为具体城市的 居民总数

指标： 人数数量，流失量，汇入量， PV(page view, 统计的是一个网站被访问的次数)，UV(统计的是一个网站被多少人访问)，GDP、收入、用户数、利润率、留存率、覆盖率等。很多公司都有自己的KPI指标体系，就是通过几个关键指标来衡量公司业务运营情况的好坏。

案例说明

1、企业运营好坏的指标：

1.月常规收入 MRR
    2.总客户获取成本 tCAC
    3.常规性毛利 RGB
    4.毛利回收期 GMPP
    5.预计生命周期 eLT
    6.生命周期价值 LTV
    7.总客户获取成本回报率 rCAC

2、一个网站的指标

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8rEME4mZ-1615727213480)(%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E7%9A%84%E6%A6%82%E5%BF%B5%E8%AE%B2%E8%A7%A3.assets/website-operation-big.jpg)]

维度与指标

虽然维度和指标可以独立使用，但常见的还是相互结合使用。维度和指标的值以及这些值之间的关系，使您的数据具有了意义。为了挖掘尽可能多的深层次信息，维度通常与一个或多个指标关联在一起。 例如，维度“城市”可以与指标“人口”和“面积”相关联。有了这些数据，系统还可以创建“人口密度”等比值指标，带来有关这些城市的更详细的深入信息

5.3 度量

度量就是被聚合的统计值，也是聚合运算的结果，一般是连续的值。

比如，count(1), sum(salary),max(salary).....

5.4 事实表

1. 所有的表都可称之为事实表
2. 但是，一般情况下，通过维度表，及其基本信息表关联的结果存储的表称之为事实表。

5.5 ETL

1）概念

数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL（抽取Extra, 转化 Transfer, 装载Load）的过程，ETL是数据仓库的流水线

2）抽取（extra）

1. 抽取方式：
	-  拉取（poll）,指的是数仓主动拉取源数据， 这是最常用的方式
	-  推送（push）,指的是源数据方主动把数据存储到数仓里，这种方式不可取，原因是源数据还要单独开发一个推送机制。会影响性能
2. 抽取类型：
	- 全量导入: 数据总量小。
	- 增量导入: 数据总量大，导入上一次导入后的发生变化的数据。

3）转化（transfer）

数据在采集或进行外部接口调用过程中可能会产生不符合要求的“脏数据”
数据转换是将数据进行重构以及标准化，消除数据的不一致，处理缺失数据，转换最主要的任务就是
数据清洗。数据清洗是对数据进行重新审查和校验的过程，目的在于删除重复信息，纠正存在的错误，
并提供数据一致性。

4）装在（load）

数据仓库搭建完成后，有一些预装载的操作先进行，比如时间维度以及其他不从源数据获取的一些数据
进行预装载，接着就是初始装载和定期装载。

六 维度模型

在多维分析的商业只能解决方案中，根据事实表和维度表的关系，又可将常见的模型分为星型模型和雪花型模型。在设计逻辑型数据的模型的时候，就应考虑数据是按照星型模型还是雪花型模型进行组织。

当所有维表都直接连接到“ 事实表”上时，整个图解就像星星一样，故将该模型称为星型模型，如图 1 。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5BXUxMZA-1615727213484)(%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E7%9A%84%E6%A6%82%E5%BF%B5%E8%AE%B2%E8%A7%A3.assets/v2-3a95b294ca7168b03094cd1ba5903850_r.jpg)]

当有一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起，故称雪花模型。雪花模型是对星型模型的扩展。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K1PuUOM9-1615727213485)(%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E7%9A%84%E6%A6%82%E5%BF%B5%E8%AE%B2%E8%A7%A3.assets/v2-0f52d33bbe11d5d606b9a2f8455aec7a_r.jpg)]

两个模型的比较：

星型模型：数据冗余，但是查询性能高
雪花型模型：数据不冗余，查询性能低

七 离线数仓和实时数仓的比较

数据仓库的发展史：

最开始只有离线数仓，后来附加了实时需求，但是实时需求还不是数据仓库的核心。再后来，实时需求变的更多，因此实时需求变成了数据仓库的核心业务。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LccpjDFd-1615727213486)(%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E7%9A%84%E6%A6%82%E5%BF%B5%E8%AE%B2%E8%A7%A3.assets/20200825103049500.png)]

八、案例演示：销售案例

8.1 了解数据源

目前只有mysql的业务数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0pyNZou3-1615727213487)(%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E7%9A%84%E6%A6%82%E5%BF%B5%E8%AE%B2%E8%A7%A3.assets/image-20201104110858236.png)]

8.2 分析业务需求

指标	指标说明	单位	维度	备注
订单数量	每个省份每个城市的订单数量	个	省份，城市
订单总额	每个省份每个城市的订单总额	元	省份，城市	精确小数点后两位
订单数量	每个省份每个城市对应每个产品类型订单数量	个	省份，城市，产品类型
订单总额	每个省份每个城市对应每个产品类型订单总额	元	省份，城市，产品类型	精确小数点后两位

8.3 构建数仓

1）将数据源导入到hdfs,并预加载dim层的数据

create database dim_1;

根据模型不同设计不同的维度表，
如果是星型模型，地域维度表就可以是一张表。
如果是雪花型模型，地域唯独表可以设计成两张表

2）创建数仓的ods层，创建库，以及表

create database ods_order;
create table ods_order.ods_customer(
customer_number	 int,
customer_name	varchar,
customer_street_address  varcahr,
customer_zip_code   int,
customer_city   varchar,
customer_province  varchar
)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as  textfile;


create table ods_order.ods_product(
product_code	 int,
product_name	varchar,
product_category varchar
)
row format delimited
fields teminated by ','
lines terminated by '\n'
stored as  textfile;


create table ods_order.ods_sales_order(
order_number int,
customer_number int,
product_code int,
order_date date,
entry_date date,
order_amount double
)
row format delimited
fields teminated by ','
lines terminated by '\n'
stored as  textfile;


别忘记加载数据

3）创建数据仓库的dwd层，库和表

create database dwd_order;

create table dwd_order.dwd_order_detail(
order_number int,
customer_number int,
product_code int,
order_date date,
entry_date date,
order_amount double,
customer_name	varchar,
customer_street_address  varcahr,
customer_zip_code   int,
customer_city   varchar,
customer_province  varchar,
product_name	varchar,
product_category varchar
)
row format delimited
fields teminated by ','
lines terminated by '\n'
stored as  parquet;

4）创建数据仓库的dws层，库和表

在这要创建相应的库名，以及微聚合的数据表，比如存储每个省份每个城市每天每小时每种商品类型的总额，总数

create table tablename(
id  int
order_sum   double
order_number  int
province  varchar
city	varchar
day	    date
hour    int
type    varchar
)

5）创建数据仓库的dm层，库和表

create database dm_order;

create table dm_order.dm_order_p_c_number
as 
select province,city,sum(order_number)
group by province,city


create table dm_order.dm_order_p_c_sum
as 
select province,city,sum(order_sum)
group by province,city


create table dm_order.dm_order_p_c_number
as 
select province,city,type,sum(order_number)
group by province,city,type


create table dm_order.dm_order_p_c_sum
as 
select province,city,type,sum(order_sum)
group by province,city,type

varchar
city varchar
day date
hour int
type varchar
)

5）创建数据仓库的dm层，库和表

create database dm_order;

create table dm_order.dm_order_p_c_number
as
select province,city,sum(order_number)
group by province,city

create table dm_order.dm_order_p_c_sum
as
select province,city,sum(order_sum)
group by province,city

create table dm_order.dm_order_p_c_number
as
select province,city,type,sum(order_number)
group by province,city,type

create table dm_order.dm_order_p_c_sum
as
select province,city,type,sum(order_sum)
group by province,city,type