数据库和数据仓库

南极找南

已于 2022-12-01 15:09:53 修改

阅读量536

点赞数 1

分类专栏：数据工厂文章标签：数据库数据仓库 java

于 2022-12-01 15:09:46 首次发布

本文链接：https://blog.csdn.net/weixin_43889788/article/details/128131869

版权

数据工厂专栏收录该内容

21 篇文章 2 订阅

订阅专栏

一、数据库三范式：

1NF：
确保每列保持原子性
每一列的值不能拆分。
2NF：
一张表只描述一个实体
表中的每一列都和主键相关。
3NF：
即确保每列都和主键直接相关，而不是间接相关。

二、数据模型

事实表：
事实数据 == 实际发生的数据
主要特点是含有大量数据，并且这些数据是可以汇总、被记录的。事实表中存储了能体现实际数据或详细数值，一般由维度编码和事实数据组成。

维度表：
用户来分析数据的窗口
维度表中包含事实数据表中事实记录的特性，有些特性提供描述性信息，有些特性指定如何汇总事实数据表数据，以便为分析者提供有用的信息，维度表包含帮助汇总数据的特性的层次结构。
表格里存放了具有独立属性和层次结构的数据，一般由为维度编码和对应的维度说明（标签）组成。

维度：
从广义上讲，维度是事物“有联系”的抽象概念的数量，“有联系”的抽象概念指的是由多个抽象概念联系而成的抽象概念，和任何一个组成它的抽象概念都有联系，组成它的抽象概念的个数就是它变化的维度。此概念的基础是一切事物都有相对联系。
看事实的角度

宽表：
字段比较多的数据库表
通常是指业务主题相关的指标、维度、属性关联在一起的一张数据库表。它不符合三范式的数据库设计规范，好处是查询性能的提高和便捷。

两个模式

星型模式
一种多维的数据关系，它是由一个事实表和一组维表组成。
每个维表都有一个维作为主键，所有这些维的主键组合成事实表的主键。
强调的是对维度进行预处理，将多个维度集合到一个事实表，形成一个宽表。

应用:
带来的好处是能够提升查询效率
因为生成的事实表已经经过预处理，主要的数据都在事实表里面，所以只要扫描事实表就能够进行大量的查询，而不必进行大量的join
维表数据一般比较少，在join可直接放入到内存进行join以提升效率，除此之外，星型模型的事实表可读性比较好，不用关联多个表就能获取大部分核心信息，设计维护性对比较简单。

雪花模式
当有一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，其图解就像多个雪花连接在一起
雪花模型是星型模型的扩展。
它对星型模型的维表进一步层次化，原有的各维度表可能被扩展为小的事实表 ，形成一些局部的层次区域，这些被分解的表都连接到主维度表而不是事实表。
雪花模型更符合数据库范式.

设计方式比较正规，数据冗余少
但在查询的时候可能需要join多张表从而导致查询效率下降，此外规范化操作在后期维护比较复杂。

星型模型和雪花模型的优劣对比：

属性	星型模型	雪花模型
数据总量	多	少
可读性	好	差
表的个数	少	多
查询速度	快	慢
冗余	高	滴
扩展	差	好

应用场景

星型模型的设计方式主要带来的好处是能够提升查询效率

因为生成的事实表已经经过预处理，主要的数据都在事实表里面，所以只要扫描事实表就能够进行大量的查询，而不必进行大量的join，其次维表数据一般比较少，在join可直接放入到内存进行join以提升效率，
星型模型的事实表可读性比较好，不用关联多个表就能获取大部分核心信息，设计维护性对比较简单。

雪花模型的设计方式是比较符合数据库范式的理念

设计方式比较正规，数据冗余少
在查询的时候可能需要join多张表从而导致查询效率下降，此外规范化操作在后期维护比较复杂。

总结

数据仓库大多数时候是比较适合使用星型模式构建底层数据表，在数据仓库中雪花模型的应用场景比较少

通过大量的冗余来提升查询效率，星型模式对OLAP的分析引擎支持比较友好，这一点在报表中比较能体现。
雪花模型在关系型数据库中如MySQL，Oracle中非常常见，尤其像电商的数据库表。

三、ETL（数据仓库技术）：

ETL，是英文Extract-Transform-Load的缩写
用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。
ETL一词较常用在数据仓库，但其对象并不限于数据仓库。

ETL工具：
RestCloud、Informatica、kettle、Xplenty、Talend、Stitch

四、数据仓库概念

概念

数据仓库通常是
数据仓库通常是一个面向主题的、集成的、随时间变化的、但信息本身相对稳定的数据集合，它用于对管理决策过程的支持。
数据仓库主要面向管理者，为管理者的决策提供支撑

面向主题的

主题不仅仅只有一个，但是都是重点关注的方向
所谓面向主题，是指数据仓库内的信息是按主题进行组织的，而不是像业务支撑系统那样是按照业务功能进行组织的。
（银行：面向贷款主题，面向存款主题）

集成的

是指数据仓库中的信息不是从各个业务系统中简单抽取出来的，而是经过一系列加工、整理和汇总的过程，因此数据仓库中的信息是关于整个企业的一致的全局信息。
（从不同的数据源采集数据到同一个数据源，此过程会有一些ETL操作）

随时间变化

是指数据仓库内的信息并不只是反映企业当前的状态，而是记录了从过去某一时点到当前各个阶段的信息。通过这些信息，可以对企业的发展历程和未来趋势做出定量分析和预测。
（T+1，当前时间获取前一天的数据；M+1，当前时间获取上个月的数据）

特点

效率足够高：
数据仓库的分析数据一般分为日、周、月、季、年等，可以看出，日为周期的数据要求的效率最高，要求24小时甚至12小时内，目前普遍的数据展现方式为T+1，即当日处理昨日的业务数据。

数据质量：
基于数据仓库的应用所面对的一般为企业决策层用户，所以对数据仓库提供的各种信息，肯定要准确的数据；但由于数据源有脏数据或者代码不严谨，所以数据仓库流程通常分为多个步骤，包括数据抽取，清洗，转换，装载，查询，展现等等；
其中数据清洗则主要对抽取过来各数据源的脏数据和不规范数据进行统一标准化。

把不需要的数据去掉，相关的数据精准简练，精简化

扩展性：
有的大型数据仓库系统架构设计复杂，是因为考虑到了未来3-5年的扩展性这样的话，未来不用花太多时间去重建数据仓库系统，就能很稳定运行。主要体现在数据建模的合理性，数据仓库方案中多出一些中间层，使海量数据流有足够的缓冲，不至于因为数据源的变动而导致用户应用功能的频繁变动。

脏数据

脏数据不可以删除

1. 数据超出值域范围
在源系统中的值超出实际的表示范围。
(考试成绩0-100，出现了101)
2. 源系统负值检查
如果在源系统的抽取过程中，如果出现了负值
必须做拒绝处理，给业务人员进行确认。
3. 重复的、冗余的、相互矛盾的数据
一个实体的属性被保存在多个表中
这些表中的信息有可能重复的、有冗余的，有的互相矛盾的数据
在编写ETL进行数据合并过程中需要适当的进行去重、合并，并解决数据冲突问题。
（去除重复，或者把不能删除的重复数据合并）
4. 数据类型不匹配
进行数据整合的时候必须把各个源系统多种的格式的数据转换为标准格式的数据。
在源系统中数据类型没有进行严格的划分
（金额用字符串表示，日期也用字符串表示，date类型后面出现数值范围等。）
5. 数据格式不一致
数据整合的过程中就需要将各个不同系统相同含义的数据按照标准代码进行转换。
源系统中的各个系统都是在不同时期，由不同的单位建设，数据格式也都由各个建设单位自己规定，造成的相同含义的数据的表示方法多种多样。
（金额的表示方法有xxx,xxx,xxx.00 、xxxxxx00、xxxxx 等
日期的表示方法就有xxxx年x月x日、20220506等）
6. 丢失数据
从源系统导出的文件中的记录数和源系统的记录数不匹配。
7. 违反唯一约束
为了提高整体的性能而取消了表中的唯一约束或者是处理历史数据的时候取消掉了唯一约束导致了有重复的数据。

怎么处理

1. 数据超出值域范围
将超出值域范围的数据也按照正常数一样导入到ODS数据库中
但是要在记录上增加一个错误类型的标志，由该标志说明该记录后续应该怎么处理。

2. 源系统负值检查
将出现负值的记录也导入到ODS数据库中
并且在记录后面增加一个错误类型的标志，由该标志说明该记录后续应该怎么处理。

3. 冗余的、相互矛盾的数据
重复的和冗余的数据在ETL处理的过程中直接去掉重复的数据。
相互矛盾的数据直接做拒绝处理，将两部分矛盾的数据生成文本文件输出。

4. 相似重复记录
在源系统中有些记录会有部分数据的重复，还有部分记录是在各自系统中有用的数据。在用ETL处理这类数据是时候需要将相似的记录都导入到ODS系统中，并生成一个标志位。由人工做合并、去重等操作。

5. 数据类型不匹配和数据格式不一致
在ETL的过程中，需要将源系统的数据按照标准代码中规定的数据类型和值域范围进行转换。

6. 丢失数据
在ETL过程中出现了从源系统抽取的条数和最终加工完成的数据条数不一致的情况，就删除加工完的那部分数据，重新从源系统中再加工数据。

7. 违反唯一约束和主键和外键缺失
违反唯一约束的需要将相同约束的数据做拒绝处理，并以文本文件的形式输出。
缺失主键的数据做拒绝处理，并以文本文件的形式输出。
在源系统中出现了外键关联的数据，但是在关联的表中却没有相应的记录，这种情况就将此类数据以文本文件的形式输出并反馈给相关的业务人员。

用途

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

五、数据仓库的架构

数据源
上游系统（数据源头）
↓
贴源层
导入层、缓冲层、ODS层
同步传输 : [ETL工具抽取数据、kettle、Informatica、datastage]
异步传输 : 数据库的导入导出 [Shell 等]

↓
基础层
按照一定的业务条线汇总加工贴源层数据 == 宽表
宽表，宽表加工
存储过程 [多表关联，函数，临时表分区表，拉链表]
↓
主题层
汇总层
按照客户的需求提炼加工主题数据、按照一定的维度汇总数据
（存储过程 [where，聚合函数]）
↓
应用层
报表
数据平台
为下游系统提供数据支撑服务
数据集市

ODS中的数据基本特征：
面向主题的
进入ODS的数据是来源于各个操作型数据库以及其他外部数据源
数据进入ODS前必须经过 ETL过程（抽取、清洗、转换、加载等）。
集成的
ODS的数据来源于各个操作型数据库
同时也会在数据清理加工后进行一定程度的综合
可更新的
可以联机修改。这一点区别于数据仓库
当前或接近当前的
“当前”是指数据在存取时刻是最新的
“接近当前”是指存取的数据是最近一段时间得到的。

数据集市也叫数据市场
数据集市就是满足特定的部门或者用户的需求，按照多维的方式进行存储，包括定义维度、需要计算的指标、维度的层次等，生成面向决策分析需求的数据立方体。

六、数据仓库的相关技术

数据处理

DataStage：
专门对多种操作数据源的数据抽取、转换和维护过程进行简化和自动化，并将其输入数据集市或数据仓库目标数据库的集成工具。
优点是对于多数据源中的数据处理和文本数据可以通过简单的配置轻松的实现；
缺点是对于需要复杂业务逻辑运算的数据处理比较繁琐，可读性较差。

Pro*C：

ORACLE支持的通过在过程化编程语言C/C++中嵌入SQL语句而开发出的应用程序。
优点是C语言的逻辑控制能力强；
缺点是可移植性差，有指针引起的内存泄露隐患。

存储过程：
在大型数据库系统中，一组为了完成特定功能的SQL
优点是存储过程是编译过的，执行快，写程序简单，升级、维护方便；
缺点是可移植性差，编程语言SQL功能较差，对文件的处理比较弱。

数据展现
报表工具

Cognos：IBM公司的BI产品，主要提供固定报表，灵活报表，仪表盘，多维分析等展现工具为企业提供提供完整的业务智能功能的解决方案。

RIDE：建行自主研发的报表集成开发环境的简称，它主要为了屏蔽各种BI工具的差异

数据库和数据仓库的区别

数据库	数据仓库
操作处理	信息处理
面向事务	面向分析
面向应用	面向主题星型、雪花模型
用户是DBA，开发	用户是经理、主管、分析
功能是日常操作	为了长期信息需求和决策支持
数据都是当前的	数据是历史的跨时间维护
访问读或者写	大部分都是读
高性能、高可用性	高灵活性
DB规模GB到TB	>=TB
数据进入	信息输出
主键索引操作	大量磁盘扫描