Contents

基础

基础

```{contents}
:local:
```

概念

事务

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。

事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所以操作。 事务的 ACID ：事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

  • 1 、原子性 ：事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

  • 2 、一致性 ：事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。

  • 3 、隔离性 ：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

  • 4 、持续性 ：也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

数据处理

大致可以分为两大类：

  • 操作型处理，叫联机事务处理 OLTP（On-Line Transaction Processing），也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，主要用于操作型处理。

  • 分析型处理，叫**联机分析处理 OLAP（On-Line Analytical Processing）**一般针对某些主题的历史数据进行分析，支持管理决策。

操作型处理

分析型处理

细节的

综合的或提炼的

实体-关系 (ER) 模型

星型或雪花模型

存取瞬间数据

存储历史数据，不包含最近的数据

可更新的

只读，只追加

一次操作一个单元

一次操作一个集合

性能要求高，响应时间短

性能要求宽松

面向事务

面向分析

一次操作数据量小

一次操作数据量大

支持日常操作

支持决策需求

数据量小

数据量大

客户订单、库存水平和银行账户等

客户收益分析、市场细分等

模式

三级模式：指的是外模式、模式和内模式

两级映射：指的是外模式-模式和模式-内模式。当数据库的外部模式或者模式需要改变时，只要对对应的映像做出相应的改变就可以保证数据和程序的逻辑独立性。

一、模式（Schema）

定义：也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。

理解： ① 一个数据库只有一个模式； ② 是数据库数据在逻辑级上的视图； ③ 数据库模式以某一种数据模型为基础； ④ 定义模式时不仅要定义数据的逻辑结构（如数据记录由哪些数据项构成，数据项的名字、类型、取值范围等），而且要定义与数据有关的安全性、完整性要求，定义这些数据之间的联系。

二、外模式（External Schema）

定义：也称子模式（Subschema）或用户模式，是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。

理解： ① 一个数据库可以有多个外模式； ② 外模式就是用户视图； ③ 外模式是保证数据安全性的一个有力措施。

三、内模式（Internal Schema）

定义：也称存储模式（Storage Schema），它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式（例如，记录的存储方式是顺序存储、按照B树结构存储还是按hash方法存储；索引按照什么方式组织；数据是否压缩存储，是否加密；数据的存储记录结构有何规定）。

理解： ① 一个数据库只有一个内模式； ② 一个表可能由多个文件组成，如：数据文件、索引文件。 它是数据库管理系统(DBMS)对数据库中数据进行有效组织和管理的方法 其目的有： ① 为了减少数据冗余，实现数据共享； ② 为了提高存取效率，改善性能。

优点是：

  • 提升了数据的独立化程度，将模式与内模式分开，保证了数据的物理独立性。讲外模式和模式分开，保证了数据的逻辑独立性。

  • 数据库系统把用户数据核物理数据完全分开，使用户摆脱了烦琐的物理存储细节，减少了应用程序维护的开销

  • 简化了用户接口。按照外模式编写应用程序或输入命令，而不需要了解数据库内部的存储结构，方便用户使用系统。

  • 有利于数据共享。在不同的外模式下可由多个用户共享系统中的数据，减少了数据冗余。

  • 有利于数据的安全保密。在外模式下根据要求进行操作，只能对限定的数据操作，保证了其他数据的安全。

数据库

数据库（Database）是按照一定格式和数据结构在计算机保存数据的软件，属于物理层。

最早期是广义上的数据库，这个阶段的数据库结构主要以层次或网状的为主，这是数据库的数据和程序间具备非常强的依赖性，应用有一定局限性。

我们现在所说的数据库一般指的是关系型数据库。关系数据库是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，具有结构化程度高，独立性强，冗余度低等优点。

  • 关系型数据库主要用于联机事务处理OLTP（On-Line Transaction Processing），主要用于进行基本的、日常的事务处理，例如银行交易等场景。

数据集市

数据集市是一种微型的数据仓库，它通常是有更少的数据，更少的主题区域，以及更少的历史数据，如果数据仓库是企业级的，那数据集市就是部门级的，一般数据集市只能为某个局部范围内的管理人员服务。

**跟数据仓库的区别：**数据集市是为了特定的应用目的或应用范围，而从数据仓库中独立出来的一部分数据，也可称为部门数据或主题数据（subjectarea）。

  • 在数据仓库的实施过程中往往可以从一个部门的数据集市着手，以后再用几个数据集市组成一个完整的数据仓库。

  • 需要注意的就是在实施不同的数据集市时，同一含义的字段定义一定要相容，这样在以后实施数据仓库时才不会造成大麻烦。

数据仓库

数据仓库（Data Warehouse, DW）定义：一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合，用于支持管理决策。

历史：数据仓库之父比尔·恩门于1990年提出数据仓库（Data Warehouse），数仓主要是为解决企业的数据集成与分析问题。数据仓库主要功能是将OLTP经年累月所累积的大量数据，通过数据仓库特有的数据储存架构进行OLAP (Online analytical processing)，最终帮助决策者能快速有效地从大量数据中，分析出有价值的信息，提供决策支持。自从数据仓库出现之后，信息产业就开始从以关系型数据库为基础的运营式系统慢慢向决策支持系统发展。

总结：数据仓库存在的意义在于对企业的所有数据进行汇总，为企业各个部门提供统一的， 规范的数据出口。

相比数据库的两个主要特点：

  • 数据仓库是面向主题集成的。数据仓库是为了支撑各种业务而建立的，数据来自于分散的操作型数据。因此需要将所需数据从多个异构的数据源中抽取出来，进行加工与集成，按照主题进行重组，最终进入数据仓库。

  • 数据仓库主要用于支撑企业决策分析，所涉及的数据操作主要是数据查询。因此数据仓库通过表结构优化、存储方式优化等方式提高查询速度、降低开销。

与数据库的对比：

维度

数据仓库

数据库

应用场景

OLAP——一般存储的是历史数据。面向主题的，用于分析和决策

OLTP——一般用来存储当前事务性数据，如交易数据。是面向事务处理的，数据是由日常的业务产生的，并且是频繁更新的

数据来源

多数据源

单数据源

数据标准化

非标准化Schema

高度标准化的静态 Schema

数据读取优势

针对读操作进行优化

针对写操作进行优化

范式

一般不符合三范式，有利于查询

一般符合三范式，有最大的精确度和最小的冗余度，有利于数据的插入

四大特点：

  • 数据仓库是面向主题的

    • 数据仓库中的数据是按照一定的主题域进行组织的，每一个主题对应一个宏观的分析领域。数据仓库排除对于决策无用的数据，提供特定主题的简明视图。

    • 举个例子：比如说一个公司会有很多的部门，不同的部门都会去数据仓库拿数据，做自己要做的报表，我们把这一个部门或是某一个业务，也就是独立从我们数据仓库中获取数据的单元，把它称作为主题，也可以理解为一个主题就是一个部门。这个部门作为一个主题会从数据仓库总去获取数据，用于完成需要的报表。

  • 数据仓库是集成的

    • 数据仓库中的数据不是一开始就是在里面的，而是从各个分散的数据库中抽取出来的。但是有一个问题，就是这些来自不同数据库的数据会有重复和不一样的地方，如字段的同名异议、异名同义、单位不统一，字长不统一等。所以在集成的过程中，还要对数据进行清洗、规划、去敏等操作。

    • 数据仓库是对企业内不同业务部门数据完整集合，而且还是处理过的数据。

  • 数据仓库的数据是稳定的

    • 数据仓库中的数据主要是为了给企业做决策时分析使用，涉及的主要是对数据的查询，一般情况下不会对数据进行修改，如果数据仓库中的历史数据超过存储期限，则会直接删除。

    • 因为数据仓库涉及的操作主要是查询，所以它的系统要比数据库简单很多，但是数据仓库涉及到查询的数据量一般都很大，所以在数据查询就有更高的要求。

    • 数仓里不存在数据的更新和删除（不是指数据到期的删除）操作。

  • 数据仓库中的数据是随时间变化而变化的

    • 数据仓库中的数据不可更新是针对应用来说的，也就是说，数据仓库的用户进行分析处理是不进行数据更新操作的。但并不是说，在从数据集成输入数据仓库开始到最后被删除的整个生存周期中，所有的数据仓库数据都是永远不变的。

    • 数据仓库的数据是随着时间变化而变化的主要表现如下：

      • 1）数据仓库随着时间变化不断增加新的数据内容。数据仓库系统必须不断捕捉OLTP数据库中变化的数据，追加到数据仓库当中去，也就是要不断的生成OLTP数据库的快照，经统一集成增加到数据仓库中去；但对于确实不在变化的数据库快照，如果捕捉到新的变化数据，则只生成一个新的数据库快照增加进去，而不会对原有的数据库快照进行修改。

      • 2）数据库随着时间变化不断删去旧的数据内容 。数据仓库内的数据也有存储期限，一旦过了这一期限，过期数据就要被删除。

      • 3）数据仓库中包含有大量的综合数据，这些综合数据中很多跟时间有关，如数据经常按照时间段进行综合，或隔一定的时间片进行抽样等等。这些数据要随着时间的变化不断地进行从新综合。因此数据仓库的数据特征都包含时间项，以标明数据的历史时期。

    • 数仓里会完整的记录某个对象在一段时期内的变化情况。

数据仓库分层

数据分层从关系型在线交易系统到面向主题的数据仓库系统，从范式建模到维度建模的必经之路。

  • 数据分层是一套让我们的数据体系更有序的行之有效的数据组织和管理方法。

  • 具体分多少层，是根据企业实际情况来决定的

数据分层的好处可以给我们带来如下的好处：

  • 隔离原始数据：不论是数据的异常还是数据敏感度，使真实数据与统计数据解耦开。

  • 数据结构化更清晰：每一个数据分层都有它的作用域和职责，在使用表的时候能更方便地定位和理解。

  • 数据血缘追踪：提供给外界使用的是一张业务表，但是这张业务表可能来源很多张表。如果有一张来源表出问题了，我们可以快速准确的定位到问题，并清楚每张表的作用范围。

  • 增强数据复用能力：减少重复开发，通过数据分层规范化，开发一些通用的中间层数据，能够减少重复计算，提高单张业务表的使用率，提升系统的执行效率。

  • 简化复杂的问题：把一个复杂的业务分成多个步骤实现，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。

  • 减少业务的影响：业务可能会经常变化，这样做就不必改一次业务就需要重新接入数据。

  • 减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算。

  • 统一数据口径：通过数据分层，提供统一的数据出口，统一对外输出的数据口径。

四层分层

  • ODS——原始数据层：存放原始数据

    • ODS层即操作数据存储，是最接近数据源中数据的一层，数据源中的数据，经过抽取、洗净、传输，也就说传说中的ETL之后，装入本层；一般来说ODS层的数据和源系统的数据是同构的，主要目的是简化后续数据加工处理的工作。从数据粒度上来说ODS层的数据粒度是最细的。ODS层的表通常包括两类，一个用于存储当前需要加载的数据，一个用于存储处理完后的历史数据。历史数据一般保存3-6个月后需要清除，以节省空间。但不同的项目要区别对待，如果源系统的数据量不大，可以保留更长的时间，甚至全量保存；数据在装入本层前需要做以下工作：去噪、去重、提脏、业务提取、单位统一、砍字段、业务判别。

  • DWD——数据明细层：对ODS层数据进行清洗、维度退化、脱敏等。

    • 该层一般保持和ODS层一样的数据粒度，并且提供一定的数据质量保证，在ODS的基础上对数据进行加工处理，提供更干净的数据。同时，为了提高数据明细层的易用性，该层会采用一些维度退化手法，当一个维度没有数据仓库需要的任何数据时，就可以退化维度，将维度退化至事实表中，减少事实表和维表的关联。例如：订单id,这种量级很大的维度，没必要用一张维度表来进行存储，而我们一般在进行数据分析时订单id又非常重要，所以我们将订单id冗余在事实表中，这种维度就是退化维度。

  • DWS——数据汇总层：对DWD层数据进行一个轻度的汇总。

    • DWS层为公共汇总层，会进行轻度汇总，粒度比明细数据稍粗，会针对度量值进行汇总，目的是避免重复计算。该层数据表会相对比较少，大多都是宽表(一张表会涵盖比较多的业务内容，表中的字段较多)。按照主题划分，如订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。

  • DM——数据集市层：为各种统计报表提供数据。

    • 存放的是轻度聚合的数据，也可以称为数据应用层，基于DWD、DWS上的基础数据，整合汇总成分析某一个主题域的报表数据。主要是提供给数据产品和数据分析使用的数据，通常根据业务需求，划分成流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。从数据粒度来说，这层的数据是汇总级的数据，也包括部分明细数据。从数据的时间跨度来说，通常是DW层的一部分，主要的目的是为了满足用户分析的需求，而从分析的角度来说，用户通常只需要分析近几年的即可。从数据的广度来说，仍然覆盖了所有业务数据。

三层分层

把四层中DWD、DWS合并成DW层

  • ODS——原始数据层：存放原始数据

  • DW——数据仓库层：数据清洗，初步汇总

    • 本层将从 ODS 层中获得的数据按照主题建立各种数据模型，每一个主题对应一个宏观的分析领域，数据仓库层排除对决策无用的数据，提供特定主题的简明视图。在DW层会保存BI系统中所有的历史数据，例如保存10年的数据。

  • DM——数据集市层：为各种统计报表提供数据。

五层分层

  • ODS——原始数据层：存放原始数据

  • DWD——数据明细层：对ODS层数据进行清洗、维度退化、脱敏等。

  • DWS——数据汇总层：对DWD层数据进行一个轻度的汇总。DWD和DWS又可以合并称为公共维度层，是可以共用的～

  • ADS——数据应用层：为各种统计报表提供数据——该层是基于DW层的数据，整合汇总成主题域的服务数据，用于提供后续的业务查询等。

  • DIM——维表层：基于维度建模理念思想，建立整个企业的一致性维度，包含两部分：

    • 高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。

    • 低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表。数据量可能是个位数或者几千几万。

五层分层+DIM分层

image-20221020214719868

  • DIM Dimension 通用维度层 该层主要存储简单、静态、代码类的维表，包括从OLTP层抽取转换维表、根据业务或分析需求沉淀一致性维度，归一化维度属性口径。可贯穿被使用于数仓任一层。

  • 数据运营层 (ODS)

    • ODS: Operation Data Store 数据准备区, 也称为贴源层。数据仓库源头系统的数据表通常会原封不动的存储一份, 这称为ODS层, 是后续数据仓库加工数据的来源。

    • ODS Operational Data Store 操作数据层 该层级主要功能是存储从源系统直接获得的数据（数据从数据结构、数据之间的逻辑关系上都与源系统基本保持一致）。实现某些业务系统字段的数据仓库技术处理、少量的基础的数据清洗（比如脏数据过滤、字符集转换、维值处理）、生成增量数据表。依据策略保留历史数据，不直接暴露给应用；外部数仓表同ODS。在这个层次中要描述清楚数据生成的流程图，便于用户了解数据生产的业务背景。需要构建角色、流程、数据的对应关系。该层原则为不做任何处理或尽可能少做处理，保留数据原貌，便于还原、追溯原始数据。

  • 数据仓库层 (DW) DW数据分层, 由下到上为:

    • DWD: data warehouse details 细节数据层，是业务层与数据仓库的隔离层。主要对ODS数据层做一些数据清洗和规范化的操作。

      • DWD Data Warehouse Detail 明细数据层 该层的主要功能是基于主题域的划分，面向业务主题、以数据为驱动设计模型，完成数据整合，提供统一的基础数据来源。在该层级保持原有粒度，维度退化，逻辑下沉并封装业务规则，或者轻度汇总，相同实体纵向整合。标准的明细层，屏蔽业务变化，完成数据的清洗、重定义、整合分类功能。

      • 数据清洗: 去除空值、脏数据、超过极限范围的

    • DWB: data warehouse base

      • 数据基础层, 存储的是客观数据, 一般用作中间层, 可以认为是大量指标的数据层。

      • DWM(也有叫DWI、DWA) Data Warehouse Model 汇总数据层(或轻度汇总层) 面向分析主题和业务过程的、统一的数据访问层，所有的基础数据、业务规则和业务实体的基础指标库以及多维模型都在这里统一计算口径、统一建模，大量基础指标库以及多维模型在该层实现。该层级以分析需求为驱动进行模型设计，实现跨业务主题域数据的关联计算或者轻度汇总计算，因此会有大数据量的多表关联汇总计算。封装业务规则（衍生计算），或者轻度汇总，加工原子指标和公共衍生指标，通用的汇总表，较强的共用性。所以很多字段不是来自业务系统，而是根据分析需求和业务场景计算出来得。抽象维度和指标，整合信息，口径收敛。

    • DWS: data warehouse service 数据服务层, / DM，data market

      • 基于DWB上的基础数据, 整合汇总成分析某一个主题域的服务数据层, 一般是宽表。用于提供后续的业务查询, OLAP分析, 数据分发等。 用户行为, 轻度聚合

      • DM Data Market 数据集市层 数据价值化沉淀，面向分析场景和数据产品需求，连接事实表和维度表形成冗余的多维的宽表。可以加工高度汇总层数据。该层次主要功能是加工多维度冗余的宽表（解决复杂的查询）、多角度分析的深度汇总表。针对具体分析项目、临时业务分析需求等，完成多样化数据模型设计，快速应对业务的变化。考虑快速响应和迭代，直接面向业务产品运营同学的需求，对于不确定的业务模型在该层次开发，比如：dau异动分析。

      • 又称数据集市或宽表。按照业务划分，如流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。

      • 主要对ODS/DWD层数据做一些轻度的汇总。

  • 数据服务层/应用层 (ADS) / APP曾

    • APP(也有叫DWA) Application 应用数据层 该层级的主要功能是提供差异化的数据服务、满足业务方的需求，支持数据集、数据产品、数据服务。

    • 这一层是提供为数据产品使用的结果数据

    • ADS: applicationData Service应用数据服务, 该层主要是提供数据产品和数据分析使用的数据, 一般会存储在ES、mysq|等系统中供线上系统使用。

    • 我们通过说的报表数据, 或者说那种大宽表, 一般就放在这里

例子

阿里：

image-20221026023018788

  • ODS 层：订单粒度的变更过程, 一笔订单有多条记录。

  • DWD 层：订单粒度的支付记录, 一笔订单只有一条记录。

  • DWS 层: 卖家的实时成交金额,一个卖家只有一条记录, 并且 指标在实时刷新。

  • ADS 层: 外卖地区的实时成交金额, 只有外卖业务使用。

  • DIM 层: 订单商品类目和行业的对应关系维表。

数据仓库建模

数据模型是抽象描述现实世界的一种工具和方法，是通过抽象的实体及实体之间联系的形式，来表示现实世界中事务的相互关系的一种映射。数据模型就是数据组织和存储方法，它强调从业务、数据存取和使用角度合理存储数据。

数据仓库建模的目标是通过建模的方法更好的组织、存储数据，以便在性能、成本、效率和数据质量之间找到最佳平衡点。

当有了适合业务和基础数据存储环境的模型（良好的数据模型），那么大数据就能获得以下好处：

  • 性能：快速的查询我们所需要的数据，减少数据的I/O吞吐。

  • 成本：极大的减少不必要的数据冗余，实现计算结果复用，降低数据的存储和计算成本。

  • 效率：极大的改善用户使用数据的体验，提高使用数据的效率。

  • 质量：改善数据统计口径的不一致性，减少数据计算错误的可能性。

基本原则

  • 1.高内聚和低耦合：

    • 将业务相近或者相关、粒度相同的数据设计为一个逻辑或者物理模型

    • 将高概率同时访问的数据放一起，将低概率同时访问的数据分开存储。

  • 2.核心模型与扩展模型分离

    • 建立核心模型与扩展模型体系，核心模型包括的字段支持常用的核心业务，扩展模型包括的字段支持个性化或少量应用的需要，不能让扩展模型的字段过度侵入核心模型，以免破坏核心模型的架构简洁性与可维护性。

  • 3.公共处理逻辑下沉及单一

    • 越是底层公用的处理逻辑越应该在数据调度依赖的底层进行封装与实现，不要让公用的处理逻辑暴露给应用层实现，不要让公共逻辑多处同时存在。

  • 4.成本与性能平衡

    • 适当的数据冗余可换取查询和刷新性能，不宜过度冗余与数据复制。

  • 5.数据可回滚

    • 处理逻辑不变，在不同时间多次运行数据结果确定不变。

  • 6.一致性

    • 具有相同含义的字段在不同表中的命名必须相同,必须使用规范定义中的名称。

  • 7.命名清晰、可理解

    • 表命名需清晰、一致，表名需易于消费者理解和使用。

ER模型

  • ER模型用实体关系模型描述企业业务，在范式理论上满足3NF。数仓中的3NF是站在企业角度面向主题的抽象，而不是针对某个具体业务流程的实体对象关系的抽象。

  • 采用ER模型建设数据仓库模型的出发点是整合数据，将各个系统中的数据按照主题进行相似性整合，并进行一致性处理。

  • ER模型特点：

    • 需要全方位了解企业业务数据

    • 设计思路自上而下，适合上游基础数据存储，同一份数据只存储一份，没有数据冗余，方便解耦，易维护

    • 缺点：开发周期一般比较长，维护成本高。对建模人员要求教高

  • 场景：关系模型主要应用于OLTP系统中，为了保证数据的一致性以及避免冗余，所以大部分业务系统的表都是遵循第三范式的。

设计范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式时符合某一种设计要求的总结。

  • 第一范式：确保每列保持原子性，即要求数据库表中的所有字段列都是不可分解的原子值。而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。

    • 不符合一范式的表格设计如下：

      ID

      商品

      商家ID

      用户ID

      001

      5台电脑

      XXX旗舰店

      00001

      很明显上表所示的表格设计是不符合第一范式的，商品列中的数据不是原子数据项，是可以进行分割的，因此对表格进行修改，让表格符合第一范式的要求，修改结果如下表所示：

      ID

      商品

      数量

      商家ID

      用户ID

      001

      电脑

      5

      XXX旗舰店

      00001

  • 第二范式：1NF+确保表中的每列都和主键相关。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。如果存在某些属性只依赖混合主键中的部分属性，那么不符合二范式。

    • 作用：减少了数据库的冗余

    • 例如在员工表中的身份证号码即可实现每个一员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分，如果在员工关系中，没有对其身份证号进行存储，而姓名可能会在数据库运行的某个时间重复，无法区分出实体时，设计辟如ID等不重复的编号以实现区分，被添加的编号或ID选作主键。

      • （该主键的添加是在ER（Entity Relationship Diagram，实体-联系图)设计时添加，不是建库时随意添加）。简而言之，第二范式就是在第一范式的基础上**属性完全依赖于主键。**

    • 不符合二范式的表格设计如下：

      学生ID

      姓名

      所属系

      系主任

      所修课程

      分数

      20170901176

      王小强

      计算机系

      马小腾

      000001

      95

      20170901176

      王小强

      计算机系

      马小腾

      000002

      99

      上述表格中是混合主键（学生 ID + 所修课程），但是所属系和系主任这两个属性只依赖于混合主键中的学生 ID 这一个属性，因此，不符合第二范式。

      如果有一天学生的所属系要调整，那么所属系和系主任这两列都需要修改，如果这个学生修了多门课程，那么表中的多行数据都要修改，这是非常麻烦的，不符合第二范式。

      为了消除这种部分依赖，只有一个办法，就是将大数据表拆分成两个或者更多个更小的数据表。符合二范式的表格设计如下：

      学生ID

      所修课程

      分数

      20170901176

      000001

      95

      20170901176

      000002

      99

      学生ID

      所属系

      主任

      20170901176

      计算机系

      马小腾

      20170901176

      计算机系

      马小腾

      学生ID

      姓名

      20170901176

      王小强

  • 第三范式：2NF+确保每列都和主键列直接相关，而不是间接相关。：3NF 在 2NF 的基础之上，消除了非主属性对于主键（复合主键）的传递依赖。

    • 例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。

    • 不符合三范式的表格设计如下：

      订单ID

      商品ID

      商品颜色

      商品尺寸

      商家ID

      用户ID

      001

      0001

      深空灰

      300x270x40

      XXX旗舰店

      00001

      很明显，上表中，商品颜色依赖于商品 ID，商品 ID 依赖于订单 ID，那么非主属性商品颜色就传递依赖于订单 ID，因此不符合三范式，解决方案是将大数据表拆分成两个或者更多个更小的数据表。（但符合2NF，因为商品尺寸这一列也是和主键订单ID相关的！）

      符合三范式的表格设计如下：

      订单ID

      商品ID

      商家ID

      用户ID

      001

      0001

      XXX旗舰店

      00001

      商品ID

      商家颜色

      商家尺寸

      0001

      深灰色

      300x270x40

  • BCNF：no redundancy with respect to FDs

    • image-20221026220001844

      image-20221026220315356

      • 反例：

        image-20221026220025291

        • 首先 sid决定了名字，但这张表sid不是主键，而是一张（sid, hobby）的表

        • 虽然（sid, hobby）的主键可以确定name，但name其实被sid决定就可以了，所以出现了冗余

    • 意味着在关系模式中每一个决定因素都包含候选键，也就是说，只要属性或属性组A能够决定任何一个属性B，则A的子集中必须有候选键。

    • BCNF范式排除了任何属性(不光是非主属性，2NF和3NF所限制的都是非主属性)对候选键的传递依赖与部分依赖。

    • 3NF和BCNF是在函数依赖的条件下对模式分解所能达到的最大程度。一个模式中的关系模式如果都属于BCNF，那么在函数依赖范围内，它已经实现了彻底的分离，已消除了插入和删除的异常。3NF的“不彻底”性表现在可能存在主属性对键的部分依赖和传递依赖。

    • Decompose的方式：

      image-20221026220510988

      • 注意：3NF不会有decomposition problems，但是BCNF在分解的时候可能会损失信息！

范式化设计⇒时间换空间：

  • 优点：范式化的表减少了数据冗余，数据表更新操作快、占用存储空间小。

  • 缺点：

    • 查询时需要对多个表进行Join关联拼接，查询性能降低

    • 索引优化会更难进行

反范式化设计⇒空间换时间：

反范式化的过程就是通过增加数据表中的冗余字段来提高数据库的读（查询）性能，但冗余数据会牺牲数据一致性。

  • 优点：

    • 可以减少表关联

    • 可能更好的进行索引优化

  • 缺点：

    • 存在大量冗余数据

    • 数据维护成本更高（删除异常，插入异常，更新异常）

维度建模

关系模型虽然冗余少，但是在大规模数据，跨表分析统计查询过程中，会造成多表关联，这会大大降低执行效率。所以一般都会采用维度模型建模，把相关各种表整理成两种：事实表和维度表两种。

维度模型如图所示，主要应用于OLAP系统中，通常以某一个事实表为中心进行表的组织，主要面向业务，特征是可能存在数据的冗余，但是能方便的得到数据。

image-20221026020254146

维度建模按照事实表和维度表来构建数仓。

  • 维度建模从分析决策的需求出发构建模型，为分析需求服务。重点关注用户如何快速的完成数据分析，可以直观的反应业务模型中的业务问题，需要大量的数据预处理、数据冗余，有较好的大规模复杂查询的响应性能。

基本概念

  • 事实表：在ER模型中抽象出了实体、关系、属性三种类型，每个操作型事件都会产生一个事实表，其中涉及到多个实体，比如：购物下单事件中涉及的主体包括客户、商品、商家，产生可度量值包括商品数量、金额、件数等。发生在现实世界中的操作性事件，其产生的可度量数值，存储在事实表中。从最细粒度级别来看，事实表的一行对应一个度量事件。事实表表示对分析主题的度量。’

    • 事实表中包含了与各个维度表相关联的外键，可与维度表关联。事实表的度量通常是数值类型，且记录数不断增加，表数据量迅速增长。

    • 事实表的设计是以能够正确记录历史信息为准则

  • 维度表：维度就是看待事物的角度，维表一般为单一主键，在ER模型中，实体会带有自己描述性的属性，这些属性就称为维度。比如：商品的产生、颜色、单价等。

    • 每个维度表都包含单独的主键列。维度表行的描述环境应该与事实表行完全对应。维度表通常比较宽，是扁平型的非规范表，包含大量的低粒度的文本属性。

  • 维度表的设计是以能够以合适的角度来聚合主题内容为准则

ER和维度模型的对比

  • 维度建模相对能快速上手，快速交付，运营人员只需要根据我们的数据字典，查询自己所属的业务模块对应的业务过程，一个简单的select语句就可以搞定所有事情，但缺点是冗余会较多，不过在当今时代，存储存储可以忽略不计。维度模型是面向业务过程，由于所有主题数据和业务过程数据都已经提前处理好了，在进行查询数据的时候，相对ER模型来讲，就不需要那么多的关联查询，大大提高了执行效率和减低算力、人力成本，但是数据冗余可能会较多，但是将多个源的数据以文本的形式存储在分布式系统中，存储成本（列式存储）并不需要那么多成本。但是从存储、计算、成本、交付、易用性等多方面考虑取平衡值，还是维度模型更实用。

  • ER模型是完全遵守3NF，当查询数据时，需要进行多表关联查询，这时候随着数据量几百亿几千亿的情况下，会大大的降低执行效率并且算力越来越多，最终导致计算成本都要不断的累加，在存储方法由于规范性好，数据冗余比较小。

星形模型

一个星型模式中可以有一个或多个事实表，每个事实表引用任意数量的维度表。

星座模型以事实表为中心，所有的维度直接连接在事实表上。由一个事实表和一组维度表组成。也是星型模型的扩展。区别是星座模型中存在多张事实表，不同事实表之间共享维表信息，常用于数据关系更复杂的场景。

imgimage-20221026020415552

  • 由一个事实表和一组维表组成，且所有维表直接与事实表相连。其特点是不存在渐变维度，有一定数据冗余，效率相对较高。在维表设计方面通常采用降维的方式，通过数据冗余来简化模型，以提高模型易用性和分析效率，标准的星型模型只有一层维度

雪花模型

对星形模型的扩展。雪花模型的维度表可以拥有更细的维度，比星形更规范一点。维护成本较高，且查询是要关联多层维表，性能较低。

img

  • 由一个事实表和多个维表组成，且有一个或多个维表通过其他维表与事实表相连。其特点是数据冗余较少，但由于表连接的增加，导致效率相对较低。其设计通常遵循3NF关系模式，但是往往无法严格遵守，因为需付出的性能成本较高

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

  • 当所有的维度表完成规范化后，就形成了以事实表为中心的雪花型结构，即雪花模式。

星座模型

基于多张事实表，多张事实表共享维度信息

在这里插入图片描述

  • 通常基于多个事实表，且多个事实表之间共享一些维度表，往往应用于数据关系比星型模型和雪花模型更复杂的场合

  • 数据仓库大多数时候是比较适合使用星型模型构建底层数据Hive表，通过大量的冗余来提升查询效率

事实表设计原则

原则一：尽可能包含所有与业务过程相关的事实

  • 分析哪些事实与业务过程有关是设计中非常重要的关注点。

  • 在事实表中应尽量包含所有与业务过程相关的事实，即使存在冗余，不过事实通常为数字型，存储开销不会太大。

原则二：只选择与业务过程相关的事实

  • 选择事实时，应只选择与业务过程有关的事实。比如在订单的下单这个业务过程中，事实表中不应该存在支付金额这个表示支付业务过程的事实。

原则三：分解不可加性事实为可加的事实

  • 比如订单的优惠率，应分解为订单原价金额与订单优化金额两个事实存储在事实表中。

原则四：在选择维度和事实之前必须先声明粒度

  • 粒度用于确定事实表中一行所表示业务的细节层次，决定了维度的扩展性。

  • 每个维度和事实必须与所定义的粒度保持一致。

  • 事实表设计过程中，粒度定义得越细越好，建议从最低级别的原子粒度开始。

  • 原子粒度提供了最大限度的灵活性，可以支持无法预期的各种细节层次的用户需求。

原则五：在同一个事实表中不能有多种不同粒度的事实

下图为机票支付成功事务事实表：

图片

  • 对于上图事务事实表，粒度为“票一级”（实际业务中，一个订单可以同时支付多张票）

  • 票支付金额和票折扣金额两个事实的粒度与表定义的粒度一致，都为“票级”，支持按表的任意维度汇总。

  • 订单支付金额和订单票数粒度为“订单级”，与事实表粒度不一致，且不能进行汇总。

  • 若在汇总计算时对总订单金额和总票数两个度量进行汇总计算，则会造成重复计算的问题。

原则六：事实的单位要保持一致

  • 比如原订单金额、订单优惠金额、订单运费金额这三个事实，应采用一致的计量单位，统一为元或分，以方便使用。

原则七：对事实的null值要处理

  • 在数据库中，null值对常用数字型字段的SQL过滤条件都不生效，比如大于、小于、等于、大于或等于、小于或等于，建议用0代替null。

原则八：使用退化维度提高事实表的易用性

  • 在Kimball的维度建模中，通常按照星型模型的方式来设计，通过事实表的外键关联专门的维表的方式来获取维度，谨慎使用退化维度，这一点，与大数据领域的事实表设计不一样。

  • 在大数据领域的事实表设计中，大量采用退化维度的方式，在事实表中存储各种类型的常用维度信息。这样做的设计目的如下：

    • 减少下游用户使用时关联多个表的操作，直接通过退化维度实现对事实表的过滤查询、控制聚合层次、排序数据以及定义主从关系等。

    • 通过增加冗余存储的方式减少计算开销，提高使用效率。

Data Vault模型

DataVault由Hub（关键核心业务实体）、Link（关系）、Satellite（实体属性） 三部分组成 ，是Dan Linstedt发起创建的一种模型方法论，它是在ER关系模型上的衍生，同时设计的出发点也是为了实现数据的整合，并非为数据决策分析直接使用。

Anchor模型

Anchor模型是对Data Vault模型做了进一步规范化处理，它是一个高度可扩展的模型，所有的扩展只是添加而不是修改，因此它将模型规范到6NF，基本变成了K-V结构模型。企业很少使用。

数据湖

数据仓库：主要存储的是以关系型数据库组织起来的结构化数据。数据通过转换、整合以及清理，并导入到目标表中。在数仓中，数据存储的结构与其定义的schema是强匹配的。

数据湖：集中存储各类结构化和非结构化数据的大型数据仓库

  • 可以存储来自多个数据源、多种数据类型的原始数据，存储任何类型的数据，包括像图片、文档这样的非结构化数据，数据无需经过结构化处理，就可以进行存取、处理、分析和传输。

  • 数据湖能帮助企业快速完成异构数据源的联邦分析、挖掘和探索数据价值。

  • 数据湖通常更大，其存储成本也更为廉价。存储其中的数据不需要满足特定的schema，数据湖也不会尝试去将特定的schema施行其上。相反的是，数据的拥有者通常会在读取数据的时候解析schema（schema-on-read），当处理相应的数据时，将转换施加其上。

数据湖的本质，是由“数据存储架构+数据处理工具”组成的解决方案。

  • 数据存储架构：要有足够的扩展性和可靠性，可以存储海量的任意类型的数据，包括结构化、半结构化和非结构化数据。

  • 数据处理工具，则分为两大类：

    • 第一类工具，聚焦如何把数据“搬到”湖里。包括定义数据源、制定数据同步策略、移动数据、编制数据目录等。

    • 第二类工具，关注如何对湖中的数据进行分析、挖掘、利用。数据湖需要具备完善的数据管理能力、多样化的数据分析能力、全面的数据生命周期管理能力、安全的数据获取和数据发布能力。如果没有这些数据治理工具，元数据缺失，湖里的数据质量就没法保障，最终会由数据湖变质为数据沼泽。

数据仓库和数据湖的不同类比于仓库和湖泊：仓库存储着来自特定来源的货物；而湖泊的水来自河流、溪流和其他来源，并且是原始数据。

  • 与数据仓库的对比

维度

数据湖

数据仓库

应用场景

可以探索性分析所有类型的数据，包括机器学习、数据发现、特征分析、预测等

通过历史的结构化数据进行数据分析

使用成本

起步成本低，后期成本较高

起步成本高，后期成本较低

数据质量

包含大量原始数据，使用前需要清洗和标准化处理

质量高，可作为事实依据

适用对象

数据科学家、数据开发人员为主

业务分析师为主

底层数据库

索引

索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构

你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引

  • 索引加快数据库的检索速度

  • 索引降低了插入、删除、修改等维护任务的速度

  • 唯一索引可以确保每一行数据的唯一性

  • 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

  • 索引需要占物理和数据空间

通常，通过索引查询数据比全表扫描要快，但是是有成本的

  • 使用索引查询不一定能提高查询性能：索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

  • 索引范围查询(INDEX RANGE SCAN)适用于两种情况:

    • 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%

    • 基于非唯一性索引的检索

事务

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

三段锁协议

共享锁（share lock）：共享 (S) 用于只读操作，如 SELECT 语句。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

排他锁（exclusive lock）：用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。

如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

**作用：**利用三段锁，可以避免以下问题

  • 丢失修改：两个事务T1和T2读入同一数据并修改，T2提交的结果破坏了T1提交的结果，导致T1的修改被丢失

  • 读脏数据：事务T1对数据D进行修改，事务T2读取到了事务T1修改的数据，接着事务T1发生异常进行回滚，事务T2读取到的就叫做“脏数据”

  • 不可重复读：不可重复读是指事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果

数据倾斜

定义：Hadoop能够进行对海量数据进行批处理的核心，在于它的分布式思想，也就是多台服务器（节点）组成集群，进行分布式的数据处理。但当大量的数据集中到了一台或者几台机器上计算，这些数据的计算速度远远低于平均计算速度，导致整个计算过程过慢，这种情况就是发生了数据倾斜。

**特点：**数据倾斜在 MapReduce 计算框架中经常发生。通俗理解，该现象指的是在整个计算过程中，大量相同的key被分配到了同一个任务上，造成“一个人累死、其他人闲死”的状况，这违背了分布式计算的初衷，使得整体的执行效率十分低下。数据倾斜后直观表现是任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大，单一reduce的记录数与平均记录数差异过大，最长时长远大于平均时长。

产生机制：无论是MR还是Spark任务进行计算的时候，都会触发Shuffle动作。一些典型的操作如distinct、reduceByKey、groupByKey、join、repartition等都会触发shuffle：一旦触发，Spark就会将相同的key及其value拉到一个节点上。如果有某个key及其对应的数据太多的话，那就会发生明显的单点问题——单个节点处理数据量爆增的情况，比如

  • Map端倾斜：

    • map读数据时，由于读入数据的文件大小分布不均匀，会导致有些map instance读取并且处理的数据特别多，而有些很少，造成map端长尾

      • 上游表文件的大小特别不均匀，并且小文件特别多

      • Map端做聚合时，某些instance读取文件的某个值特别多而引起长尾（主要是count distinct）

  • Join倾斜：Join 操作需要参与map和reduce的整个阶段。

    • 小表join大表，但某个key过于集中：分发到某一个或几个Reduce/Stage上的数据远高于平均值

    • 大表与大表，但是分桶的判断字段0值或空值过多：空值由一个Reduce处理很慢

  • Reduce 端倾斜

    • 维度太小了，有些值处理太多+Map 端直接做聚合时出现 key 值分布不均匀, 造成 Reduce 端长尾。

解决/缓和的办法

  • 首先，是在SQL语句层面解决问题

    • 保证数据流是按合理的颗粒度进行的，要适当使用子查询，避免join的时候两边都有重复冗余之类的情况

    • 大量的null值或者一些无意义的数据参与到计算作业中，表中有大量的null值，如果表之间进行join操作，就会有shuffle产生，这样所有的null值都会被分配到一个reduce中，必然产生数据倾斜。

      • 如果不能处理掉的话，就给null值随机赋值，这样它们的hash结果就不一样

  • 第二，对库表进行partition

    • 比如现在是按省份进行汇总数据，如果只是简单的按省份去分（这并没有错），那么数据肯定会倾斜，因为各省的数据天然不一样。我们可以通过历史数据、抽样数据或者一些常识，对数据进行人工分区，让数据按照我们自定义的分区规则比较均匀的分配到不同的task中。

    • 常见的分区方式：

      • 随机分区：每个区域的数据基本均衡，简单易用，偶尔出现倾斜，但是特征同样也会随机打散。

      • 轮询分区：绝对不会倾斜，但是需要提前预知分成若干份，进行轮询。

      • hash散列：可以针对某个特征进行hash散列，保证相同特征的数据在一个区，但是极容易出现数据倾斜。

      • 范围分区：需要排序，临近的数据会被分在同一个区，可以控制分区数据均匀。

  • 第三，是尝试一些比较通用的参数设置，因为改参数成本比较低

    • 提高shuffle并行度 set spark.sql.shuffle.partitions= [num_tasks] （默认200），将原本被分配到同一个Task的不同Key分配到不同Task

    • 增加jvm（Java Virtual Machine：Java虚拟机）内存，这适用于变量值非常少的情况，这种情况下，往往只能通过硬件的手段来进行调优，增加jvm内存可以显著的提高运行效率；

  • 最后会读日志，然后具体问题具体分析：

  • Map的时候

    • 小文件很多

      • 比如当输入数据存在大块和小块的严重问题，例：一个大文件128M，还有1000个小文件，每 个1KB。

      • 解决方法：任务输入前做文件合并，将众多小文件合并成一个大文件，可以通过set hive.merge.mapredfiles=true来解决；

      • 然后也可以减少和增加map的数量

  • Group By的时候：可以map端聚合，启动负载均衡：使计算变成了两个mapreduce

    • 负载均衡参数

      set hive.map.aggr=true; --在map中会做部分聚集操作，效率更高但需要更多的内存
set hive.groupby.skewindata=true; --默认false，数据倾斜时负载均衡

      • 让MapReduce进程生成两个额外的 MR Job，这两个任务的主要操作如下：

        • 第一步：MR Job 中Map 输出的结果集合首先会随机分配到 Reduce 中，然后每个 Reduce 做局部聚合操作并输出结果，

          • 这样处理的原因是相同的Group By Key有可能被分发到不同的 Reduce Job中，从而达到负载均衡的目的。

        • 第二步：MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成聚合操作。

    • 手动操作数据打散：给key加上100以内的随机前缀，进行一次预聚合，然后对本次预聚合后的结果进行去掉随机前缀，进行一次数据的全局聚合。

      • 在第一个中在 shuffle 过程 partition 时，随机给 key 打10种不同的标记，使每个key都会随机均匀分布到各个reduce上计算，完成部分计算（但相同key没有分配到相同reduce上，所以需要第二次的mapreduce）

      • 第二次回归正常 shuffle，但数据分布不均匀的问题在第一次mapreduce已经有了很大的改善

      • 例1：

      -- 水果字段名为category
select count (substr(x.category,1,2)) 
from
(select concat(category,'_',cast(round(10*rand())+1 as string))
from table1
group by concat(category,'_',cast(round(10*rand())+1 as string))
) x --1阶段聚合 比如热带水果_1自己内部聚合
group by substr(x.category,1,2);   --2阶段聚合

      • 例22:

        • 在map阶段时给key加上一个随机数，有了随机数的key就不会被大量的分配到同一节点(小几率)，待到reduce后再把随机数去掉即可；

        • 将大表(A)中的id加上后缀(即“id_0”-“id_2”)，起到“打散”的作用。为了结果正确，小表B中的id需要将每条数据都“复制”多份。此时再执行join操作，将会产生三个task，每个task只需要关联一条数据即可，起到了分散的作用86559

        • SELECT id, value, concat(id, round(rand() * 10000)%3) as new_id

        • 经过处理之后再使用new_id来作为聚合条件

  • Join时候

    • 小表Join大表的时候可以把Reduce Join改成Map Join

      • mapjoin优化就是在Map阶段完成join工作（而不是像通常的join在Reduce阶段按照join的列值进行分发数据到每个Reduce上进行join工作），这样避免了Shuffle阶段，从而避免了数据倾斜。

      • 注意：这个操作会将所有的小表全量复制到每个map任务节点，然后再将小表缓存在每个map节点的内存里与大表进行join工作。小表的大小的不能太大，一般也就几百兆，表的JOIN操作是在Map端且在内存进行的，否则会出现OOM报错。

      • 开Map Join代码

      set hive.auto.convert.join = true; -- hive是否自动根据文件量大小，选择将common join转成map join (在Hive 0.11版本及之后，Hive默认启动该优化，也就是不在需要显示的使用 /*+ mapjoin(表明)*/标记)
set hive.mapjoin.smalltable.filesize =25000000; --大表小表判断的阈值，如果表的大小小于该值25Mb，则会被判定为小表。则会被加载到内存中运行，将commonjoin转化成mapjoin。一般这个值也就最多几百兆的样子。

    • 大表Join大表

      • 开启Skewed Join

        • 开启Skew Join之后，在运行时，会对数据进行扫描并检测哪个key会出现倾斜，对于会倾斜的key，用map join做处理，不倾斜的key正常处理

        set hive.optimize.skewjoin = true;
set hive.skewjoin.key = skew_key_threshold （default = 100000）;

        • 关于skew_key_threshold的设定：

        • hadoop 中默认是使用hive.exec.reducers.bytes.per.reducer = 1000000000，也就是每个节点的reduce 默认是处理1G大小的数据

        • 那么 skew_key_threshold = 1G/平均行长

          • 或者默认直接设成250000000 (差不多算平均行长4个字节)

      • 拆分大表：

        • 让倾斜部分单独处理（比如非常多的卖家、但很多卖家的订单很少），中间表分桶排序后join

    • 不同数据类型关联产生数据倾斜

      • 表a中需要join的字段key为int，表b中key字段既有string类型也有int类型。

      • 当按照key进行两个表的join操作时，默认的Hash操作会按int型的id来进行分配，这样所有的string类型都被分配成同一个id，结果就是所有的string类型的字段进入到一个reduce中，引发数据倾斜

    • 表中作为关联条件的字段值为0或空值的较多

      • 解决方式：给空值添加随机key值，将其分发到不同的reduce中处理。由于null值关联不上，所以对结果无影响。

    -- 方案一、给空值添加随机key值，将其分发到不同的reduce中处理。由于null值关联不上，所以对结果无影响。
SELECT * 
FROM log a left join users b 
on case when a.user_id is null then concat('hive',rand()) else a.user_id end = b.user_id;

-- 方案二：去重空值
SELECT a.*,b.name
FROM 
    (SELECT * FROM users WHERE LENGTH(user_id) > 1 OR user_id IS NOT NULL ) a
JOIN
    (SELECT * FROM log WHERE LENGTH(user_id) > 1 OR user_id IS NOT NULL) B
ON a.user_id; = b.user_id;

    • 表中作为关联条件的字段重复值过多

    SELECT a.*,b.name
FROM 
    (SELECT * FROM users WHERE LENGTH(user_id) > 1 OR user_id IS NOT NULL ) a
JOIN
-- 做去重操作
    (SELECT * FROM (SELECT *,row_number() over(partition by user_id order by create_time desc) rk FROM log WHERE LENGTH(user_id) > 1 OR user_id IS NOT NULL) temp where rk = 1) B
ON a.user_id; = b.user_id;

  • Reduce的时候

    • General：增加reduce的操作，让每个reducer处理的减少

      • 设置的reduce个数

      • set mapred.reduce.tasks=800;
-- 或者
set hive.exec.reducers.bytes.per.reducer

-- 这个设置了后hive会自动计算reduce的个数，因此两个参数一般不同时使用

    • 单个Count Distinct

      • -- 可能会造成数据倾斜的sql
select a,count(distinct b) from t group by a;
-- 先去重、然后分组统计，然后用sum来代替
select a,sum(1) from (select a, b from t group by a,b) group by a;

    • 多个Count Distinct

      • 此外，对同一个表 按照维度对不同的列进行 Count Distinct 操作, 造成 Map 端数据膨胀, 从而使得下游的 Join 和 Reduce 出现链路上的 长尾。所以如果需要分组统计很多个distinct，那就用UNIONALL的方式——先将值为空的数据占位处理，分sql统计数据，然后将两组结果union all进行汇总结算

    • 窗口函数中partition by造成的数据倾斜

最后，其实不用过度设计，对结果产出延迟在可接受范围，就不必过度重视。

previous

Q-BERT

next

SQL