万字长文带你了解ETL和数据建模~!

2022年05月15日 阅读数:2
这篇文章主要向大家介绍万字长文带你了解ETL和数据建模~!,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

做者 |胡保强php

原文|https://zhuanlan.zhihu.com/p/59184600前端

什么是ETL

ETL是数据抽取(Extract)、转换(Transform)、加载(Load )的简写,它是将OLTP系统中的数据通过抽取,并将不一样数据源的数据进行转换、整合,得出一致性的数据,而后加载到数据仓库中。简而言之ETL是完成从 OLTP系统到OLAP系统的过程算法

数据仓库的架构

数据仓库(Data Warehouse \ DW)是基于OLTP系统的数据源,为了便于多维分析和 多角度展示将其数据按特定的模式进行存储而创建的关系型数据库,它不一样于多维数据库,数据仓库中的数据是细节的,集成的,数据仓库是面向主题的,是以 OLAP系统为分析目的。它包括星型架构与雪花型架构,其中星型架构中间为事实表,四周为维度表, 相似星星;雪花型架构中间为事实表,两边的维度表能够再有其关联子表,而在星型中只容许一张表做为维度表与事实表关联,雪花型一维度能够有多张表,而星型 不能够。考虑到效率时,星型聚合快,效率高,不过雪花型结构明确,便于与OLTP系统交互。在实际项目中,咱们将综合运用星型架构与雪花型架构。数据库

ETL构建企业级数据仓库五步法的流程

1.肯定主题

即 肯定数据分析或前端展示的某一方面的分析主题,例如咱们分析某年某月某一地区的啤酒销售状况,就是一个主题。主题要体现某一方面的各分析角度(维度)和统 计数值型数据(量度),肯定主题时要综合考虑,一个主题在数据仓库中即为一个数据集市,数据集市体现了某一方面的信息,多个数据集市构成了数据仓库。安全

2.肯定量度

在 肯定了主题之后,咱们将考虑要分析的技术指标,诸如年销售额此类,通常为数值型数据,或者将该数据汇总,或者将该数据取次数,独立次数或取最大最小值 等,这样的数据称之为量度。量度是要统计的指标,必须事先选择恰当,基于不一样的量度能够进行复杂关键性能指标(KPI)等的计算。性能优化

3.肯定事实数据粒度

在 肯定了量度以后咱们要考虑到该量度的汇总状况和不一样维度下量度的聚合状况,考虑到量度的聚合程度不一样,咱们将采用“最小粒度原则”,即将量度的粒度设置 到最小,例如咱们将按照时间对销售额进行汇总,目前的数据最小记录到天,即数据库中记录了天天的交易额,那么咱们不能在ETL时将数据进行按月或年汇总, 须要保持到天,以便于后续对天进行分析。并且咱们没必要担忧数据量和数据没有提早汇总带来的问题,由于在后续的创建CUBE时已经将数据提早汇总了。服务器

4.肯定维度

维 度是要分析的各个角度,例如咱们但愿按照时间,或者按照地区,或者按照产品进行分析,那么这里的时间、地区、产品就是相应的维度,基于不一样的维度咱们可 以看到各量度的汇总状况,咱们能够基于全部的维度进行交叉分析。这里咱们首先要肯定维度的层次(Hierarchy)和级别(Level)(图 四:pic4.jpg),维度的层次是指该维度的全部级别,包括各级别的属性;维度的级别是指该维度下的成员,例如当创建地区维度时咱们将地区维度做为一 个级别,层次为省、市、县三层,考虑到维度表要包含尽可能多的信息,因此创建维度时要符合“矮胖原则”,即维度表要尽可能宽,尽可能包含全部的描述性信息,而不 是统计性的数据信息。数据结构

还有一种常见的状况,就是父子型维度,该维度通常用于非叶子节点含有成员等状况,例如公司员工 的维度,在统计员工的工资时,部 门主管的工资不能等于下属成员工资的简单相加,必须对该主管的工资单独统计,而后该主管部门的工资等于下属员工工资加部门主管的工资,那么在创建员工维度 时,咱们须要将员工维度创建成父子型维度,这样在统计时,主管的工资会自动加上,避免了都是叶子节点才有数据的状况。架构

另外,在创建维度表时要充 分使用代理键,代理键是数值型的ID号码,好处是代理键惟一标识了每一维度成员信息,便于区分,更重要的是在聚合时因为数值型匹 配,JOIN效率高,便于聚合,并且代理键对缓慢变化维度有更重要的意义,它起到了标识历史数据与新数据的做用,在原数据主键相同的状况下,代理键起到了 对新数据与历史数据很是重要的标识做用。工具

有时咱们也会遇到维度缓慢变化的状况,好比增长了新的产品,或者产品的ID号码修改了,或者产品增长了一个新的属性,此时某一维度的成员会随着新的数据的加入而增长新的维度成员,这样咱们要考虑到缓慢变化维度的处理,对于缓慢变化维度,有三种状况:

  1. 缓慢变化维度第一种类型:历史数据须要修改。这样新来的数据要改写历史数据,这时咱们要使用UPDATE,例如产品的ID号码为123,后来发现ID 号码错误了,须要改写成456,那么在修改好的新数据插入时,维度表中原来的ID号码会相应改成456,这样在维度加载时要使用第一种类型,作法是彻底更 改。

  2. 缓慢变化维度第二种类型:历史数据保留,新增数据也要保留。这时要将原数据更新,将新数据插入,须要使用UPDATE / INSERT,好比某一员工2005年在A部门,2006年时他调到了B部门。那么在统计2005年的数据时就应该将该员工定位到A部门;而在统计 2006年数据时就应该定位到B部门,而后再有新的数据插入时,将按照新部门(B部门)进行处理,这样咱们的作法是将该维度成员列表加入标识列,将历史的 数据标识为“过时”,将目前的数据标识为“当前的”。另外一种方法是将该维度打上时间戳,即将历史数据生效的时间段做为它的一个属性,在与原始表匹配生成事 实表时将按照时间段进行关联,这样的好处是该维度成员生效时间明确。

  3. 缓慢变化维度第三种类型:新增数据维度成员改变了属性。例如某一维度成 员新加入了一列,该列在历史数据中不能基于它浏览,而在目前数据和未来数据中可 以按照它浏览,那么此时咱们须要改变维度表属性,即加入新的列,那么咱们将使用存储过程或程序生成新的维度属性,在后续的数据中将基于新的属性进行查看。

5.建立事实表

在肯定好事实数据和维度后,咱们将考虑加载事实表。

在公司的大量数据堆积如山时,咱们想看看里面到底是什么,结果发现里面是一笔笔生产记录,一笔笔交易记录… 那么这些记录是咱们将要创建的事实表的原始数据,即关于某一主题的事实记录表。

我 们的作法是将原始表与维度表进行关联,生成事实表(图六:pic6.jpg)。注意在关联时有为空的数据时(数据源脏),须要使用外链接,链接后咱们将 各维度的代理键取出放于事实表中,事实表除了各维度代理键外,还有各量度数据,这未来自原始表,事实表中将存在维度代理键和各量度,而不该该存在描述性信 息,即符合“瘦高原则”,即要求事实表数据条数尽可能多(粒度最小),而描述性信息尽可能少。

若是考虑到扩展,能够将事实表加一惟一标识列,觉得了之后扩展将该事实做为雪花型维度,不过不须要时通常建议不用这样作。

事 实数据表是数据仓库的核心,须要精心维护,在JOIN后将获得事实数据表,通常记录条数都比较大,咱们须要为其设置复合主键和索引,觉得了数据的完整性和 基于数据仓库的查询性能优化,事实数据表与维度表一块儿放于数据仓库中,若是前端须要链接数据仓库进行查询,咱们还须要创建一些相关的中间汇总表或物化视图,以方便查询。

ETL中高级技巧的运用

1.准备区的运用

在构建数据仓库时,若是数据源位于一服务器上,数据仓库在另外一 服务器端,考虑到数据源Server端访问频繁,而且数据量大,须要不断更新,因此能够创建准备区数据库(图七:pic7.jpg)。先将数据抽取到准备 区中,而后基于准备区中的数据进行处理,这样处理的好处是防止了在原OLTP系统中中频繁访问,进行数据运算或排序等操做。例如咱们能够按照天将数据抽取 到准备区中,基于数据准备区,咱们将进行数据的转换,整合,将不一样数据源的数据进行一致性处理。数据准备区中将存在原始抽取表,一些转换中间表和临时表以 及ETL日志表等。

2.时间戳的运用

时间维度对于某一事实主题来讲十分重要,由于不一样的时间有不一样的统计数据信息,那么按照时间记录 的信息将发挥很重要的做用。在ETL中,时间戳有其特殊的 做用,在上面提到的缓慢变化维度中,咱们可使用时间戳标识维度成员;在记录数据库和数据仓库的操做时,咱们也将使用时间戳标识信息,例如在进行数据抽取 时,咱们将按照时间戳对OLTP系统中的数据进行抽取,好比在午夜0:00取前一天的数据,咱们将按照OLTP系统中的时间戳取GETDATE到 GETDATE减一天,这样获得前一天数据。

3.日志表的运用

在对数据进行处理时,不免会发生数据处理错误,产生出错信息,那么咱们 如何得到出错信息并及时修正呢? 方法是咱们使用一张或多张Log日志表,将出错信息记录下来,在日志表中咱们将记录每次抽取的条数,处理成功的条数,处理失败的条数,处理失败的数据,处 理时间等等,这样当数据发生错误时,咱们很容易发现问题所在,而后对出错的数据进行修正或从新处理。

4.使用调度

在对数据仓库进行 增量更新时必须使用调度(图八:pic8.jpg),即对事实数据表进行增量更新处理,在使用调度前要考虑到事实数据量,须要多长时间更 新一次,好比但愿按天进行查看,那么咱们最好按天进行抽取,若是数据量不大,能够按照月或半年对数据进行更新,若是有缓慢变化维度状况,调度时须要考虑到 维度表更新状况,在更新事实数据表以前要先更新维度表。

调度是数据仓库的关键环节,要考虑缜密,在ETL的流程搭建好后,要按期对其运行,因此 调度是执行ETL流程的关键步骤,每一次调度除了写入Log日志表 的数据处理信息外,还要使用发送Email或报警信息等,这样也方便的技术人员对ETL流程的把握,加强了安全性和数据处理的准确性。

ETL构建数据仓库须要简单的五步,掌握了这五步的方法咱们将构建一个强大的数据仓库,不过每一步都有很深的须要研究与挖掘,尤为在实际项目中,咱们要综合考虑,例如若是数据源的脏数据不少,在搭建数据仓库以前咱们首先要进行数据清洗,以剔除掉不须要的信息和脏数据。

总之,ETL是数据仓库的核心,掌握了ETL构建数据仓库的五步法,就掌握了搭建数据仓库的根本方法。不过,咱们不能教条,基于不一样的项目,咱们还将要进行 具体分析,如父子型维度和缓慢变化维度的运用等。在数据仓库构建中,ETL关系到整个项目的数据质量,因此马虎不得,必须将其摆到重要位置,将ETL这一 大厦根基筑牢。

ETL和SQL的区别与联系

若是ETL和SQL来讲,确定是SQL效率高的多。可是双方各有优点,先说ETL,ETL主要面向的是创建数据仓库来使用的。ETL更偏向数据清洗,多数据源数据整合,获取增量,转换加载到数据仓库所使用的工具。好比我有两个数据源,一个是数据库的表,另一个是excel数据,而我须要合并这两个数据,一般这种东西在SQL语句中比较难实现。可是ETL却有不少现成的组件和驱动,几个组件就搞定了。还有好比跨服务器,而且服务器之间不能创建链接的数据源,好比咱们公司系统分为一期和二期,存放的数据库是不一样的,数据结构也不相同,数据库之间也不能创建链接,这种状况下,ETL就显得尤其重要和突出。经过固定的抽取,转换,加载到数据仓库中,便可很容易实现。

那么SQL呢?SQL事实上只是固定的脚本语言,可是执行效率高,速度快。不过灵活性不高,很难跨服务器整合数据。因此SQL更适合在固定数据库中执行大范围的查询和数据更改,因为脚本语言能够随便编写,因此在固定数据库中可以实现的功能就至关强大,不像ETL中功能只能受组件限制,组件有什么功能,才能实现什么功能。

因此具体咱们在何时使用ETL和SQL就很明显了,当咱们须要多数据源整合创建数据仓库,并进行数据分析的时候,咱们使用ETL。若是是固定单一数据库的数据层次处理,咱们就使用SQL。固然,ETL也是离不开SQL的。

ETL算法和工具简介:

1.经常使用的ETL工具

主要有三大主流工具,分别是Ascential公司的Datastage、Informatica公司的Powercenter、NCR Teradata公司的ETL Automation.还有其余开源工具,如PDI(Kettle)等。

2.ETL是DW系统的基础

DW系统以事实发生数据为基础,自产数据较少。

一个企业每每包含多个业务系统,都可能成为DW数据源。

业务系统数据质量参差不齐,必须学会去伪存真。

业务系统数据纷繁复杂,要整合进数据模型。

源数据之间关系也纷繁复杂,源数据在加工进DW系统时,有些必须遵守必定的前后次序关系;

3.源数据的分类

流水事件表:此类源表用于记录交易等动做的发生,在源系统中会新增、大部分不会修改和删除,少许表存在删除状况。如按期存款登记簿;

常规状态表:此类源表用于记录数据信息的状态。在源系统中会新增、修改,也存在删除的状况。如客户信息表;

代码参数表:此类源表用于记录源系统中使用到的数据代码和参数;

4.数据文件的类型

数据文件大多数以1天为固定的周期从源系统加载到数据仓库。数据文件包含增量,全量以及待删除的增量。

增量数据文件:数据文件的内容为数据表的增量信息,包含表内新增及修改的记录。

全量数据文件:数据文件的内容为数据表的全量信息,包含表内的全部数据。

带删除的增量:数据文件的内容为数据表的增量信息,包含表内新增、修改及删除的记录,一般删除的记录以字段DEL_IND='D'标识该记录。

5.ETL标准算法

可划分为:历史拉链算法、追加算法(事件表)、Upsert算法(主表)及全删全加算法(参数表);

6.ETL标准算法选择

历史拉链:根据业务分析要求,对数据变化都要记录,须要基于日期的连续历史轨迹;

追加(事件表):根据业务分析要求,对数据变化都要记录,不须要基于日期的连续历史轨迹;

Upsert(主表):根据业务分析要求,对数据变化不须要都要记录,当前数据对历史数据有影响;

全删全加算法(参数表):根据业务分析要求,对数据变化不须要都要记录,当前数据对历史数据无影响;

7.历史拉链法

所谓拉链,就是记录历史,记录一个事务从开始,一直到当前状态的全部变化信息(参数新增开始结束日期);

8.追加算法

通常用于事件表,事件之间相对独立,不存在对历史信息进行更新;

9.Upsert算法

是update和insert组合体,通常用于对历史信息变化不须要进行跟踪保留、只需其最新状态且数据量有必定规模的表,如客户资料表;

10.全删全加算法

通常用于数据量不大的参数表,把历史数据所有删除,而后从新全量加载;

11.处理复杂度

历史拉链,Upsert,Append,全删全加;加载性能:全删全加,Append,Upsert,历史拉链;

12.近源模型层主要算法

APPEND算法,常规拉链算法,全量带删除拉链算法;

13.整合模型层算法

APPEND算法,MERGE算法,常规拉链算法,基于增量数据的删除拉链算法,基于全量数据的删除拉链算法,经济型常规拉链算法,经济型基于增量数据的删除拉链算法,经济型基于全量数据的删除拉链算法,PK_NOT_IN_APPEND算法,源日期字段自拉链算法;

14.技术缓冲到近源模型层的数据流算法-APPEND算法

此算法一般用于流水事件表,适合这类算法的源表在源系统中不会更新和删除,而只会发生一笔添加一笔,因此只需天天将交易日期为当日最新数据取过来直接附加到目标表便可,此类表在近源模型层的字段与技术缓冲层、源系统表基本上彻底一致,不会额外增长物理化处理字段,使用时也与源系统表的查询方式相同;

15.技术缓冲到近源模型层的数据流算法-常规拉链算法

此算法一般用于无删除操做的常规状态表,适合这类算法的源表在源系统中会新增、修改,但不删除,因此需天天获取当日末最新数据(增量或全增量都可),先找出真正的增量数据(新增和修改),用它们将目标表中属性发生修改的开链数据(有效数据)进行关链操做(即END_DT关闭到当前业务日期),而后再将最新的增量数据做为开链数据插入到目标表便可。

此类表再近源模型层比技术缓冲层、源系统的相应表额外增长两个物理化处理字段START_DT(开始日期)和END_DT(结束日期),使用时须要先选定视觉日期,经过START_DT和END_DT去卡视觉日期,即START_DT<='视觉日期'AND END_DT>'视觉日期';

16.技术缓冲到近源模型层的数据流算法-全量带删除拉链算法

此算法一般用于有删除操做的常规状态类表,而且要求全量的数据文件,用以对比出删除增量;适合这类算法的源表在源系统中会新增,修改,删除,天天将当日末最新全量数据取过来外,分别找出真正的增量数据(新增,修改)和删除增量数据,用它们将目标表中属性发生修改的开链数据(有效数据)进行关链操做(即END_DT关闭到当前业务日期),而后再将最新增量数据中真正的增量及删除数据做为开链数据插入到目标表便可,注意删除记录的删除标志DEL_IND会设置为‘D’;

此类表在近源模型层比技术缓冲层,源系统的相应表额外增长三个物理化处理字段START_DT(开始日期),ENT_DT(结束日期),DEL_IND(删除标准)。使用方式分两类:一时通常查询使用,此时须要先选定视角日期,经过START_DT和END_DT去卡视角日期,即START_DT<='视角日期' AND END_DT>‘视角日期’,同时加上条件DEL_IND <> 'D';另外一种是下载或获取当日增量数据,此时就是须要START_DT<='视角日期' AND END_DT>'视角日期' 一个条件便可,不须要加DEL_IND <> 'D'的条件。

17.近源模型层到整合模型层的数据流算法-APPEND算法

此算法一般用于流水事件表,适合这类算法的源表在源系统中不会更新和删除,而只会发生一笔添加一笔,因此只需天天将交易日期为当日的最新数据取过来直接附加到目标表便可;

一般建一张名为VT_NEW_编号的临时表,用于将各组当日最新数据转换加到VT_NEW_编号后,再一次附加到最终目标表;

18.近源模型层到整合模型层的数据流算法-MERGE INTO算法

此算法一般用于无删除操做的常规状态表,通常是无需保留历史而只保留当前最新状态的表,适合这类算法的源表在源系统中会新增,修改,但不删除,因此需获取当日末最新数据(增量或全量都可),用于MERGE IN或UPSERT目标表;为了效率及识别真正增量的要求,一般先识别出真正的增量数据(新增及修改数据),而后再用这些真正的增量数据向目标表进行MERGE INTO操做;

一般建两张临时表,一个名为VT_NEW_编号,用于将各组当日最新数据转换加到VT_NEW_编号;另外一张名为VT_INC_编号,将VT_NEW_编号与目标表中昨日的数据进行对比后找出真正的增量数据(新增和修改)放入VT_INC_编号,而后再用VT_INC_编号对最终目标表进行MERGE INTO或UPSERT。

19.近源模型层到整合模型层的数据流算法-常规拉链算法

此算法一般用于无删除操做的常规状态表,适合这类算法的源表在源系统中会新增、修改,但不删除,因此需天天获取当日末最新数据(增量或全增量都可),先找出真正的增量数据(新增和修改),用它们将目标表中属性发生修改的开链数据(有效数据)进行关链操做(即END_DT关闭到当前业务日期),而后再将最新增量数据做为开链数据插入到目标表便可;

一般建两张临时表,一个名为VT_NEW_编号,用于将各组当日最新数据转换加到VT_NEW_编号;另外一张名为VT_INC_编号,将VT_NEW_编号与目标表中昨日的数据进行对比后找出真正的增量数据(新增和修改)放入VT_INC_编号,而后再将最终目标表的开链数据中的PK出如今VT_INT_编号中进行关链处理,而后将VT_INC_编号中的全部数据做为开链数据插入最终目标表便可。

20.近源模型层到整合模型层的数据流算法-基于增量数据删除拉链算法

此算法一般用于有删除操做的常规状态表,而且要求删除数据是以DEL_IND='D'删除增量的形式提供;适合这类算法的源表再源系统中会新增、修改、删除,除天天获取当日末最新数据(增量或全量都可)外,还要获取当日删除的数据,根据找出的真正增量数据(新增和修改)以及删除增量数据,用它们将目标表中属性发生修改的开链数据(有效数据)进行关链操做(即END_DT关闭到当前业务时间),而后再将增量(不含删除数据)做为开链数据插入到目标表中便可;

一般建三张临时表,一个名为VT_NEW_编号,用于将各组当日最新数据 (不含删除数据)转换加载到VT_NEW_编号;第二张表名为VT_INC_编号,用VT_NEW_编号与目标表中的昨日的数据进行对比后找出真正的增量数据放入VT_INC_编号;第三张表名为VT_DEL_编号,将删除增量数据转换加载到VT_DEL_编号;最后再将最终目标表的开链数据中PK出如今VT_INC_编号或VT_DEL_编号中的进行关链处理,最后将VT_INC_编号中的全部数据做为开链数据插入最终目标表便可;

21.近源模型层到整合模型层的数据流算法-基于全量数据删除拉链算法

此算法一般用于有删除操做的常规状态表,而且要求提供全量数据,用以对比出删除增量;适合这类算法的源表在源系统中会新增、修改、天天将当日末的最新全量数据取过来外,分别找出真正的增量数据(新增、修改)和删除增量数据,用它们将目标表中属性发生修改的开链数据(有效记录)进行关链操做(即END_DT关闭到当前业务时间),而后再将最新数据中真正的增量数据(不含删除数据)做为开链数据插入到目标表便可;

一般建两张临时表,一个名为VT_NEW_编号,用于将各组当日最新全量数据转换到VT_NEW_编号;另外一张表名为VT_INC_编号,将VT_NEW_编号与目标表中昨日的数据进行对比后找出真正的增量数据(新增、修改)和删除增量数据放入VT_INC_编号,注意将其中的删除增量数据的END_DT置以最小日期(借用);最后再将最终目标表的开链数据中PK出现再VT_INC_编号或VT_DEL_编号中的进行关链处理,而后将VT_INC_编号中全部的END_DT不等于最小日期数据(非删除数据)做为开链数据插入最终目标表便可;

22.近源模型层到整合模型层的数据流算法-经济型常规拉链算法

此算法基本等同与常规拉算法,只是在最后一步只将属性非空即非0的记录才做为开链数据插入目标表;

23.近源模型层到整合模型层的数据流算法-经济型基于增量数据删除拉链算法

此算法基本等同于基于增量数据删除拉链算法,只是在最后一步只将属性非空及非0的记录才做为开链数据插入目标表;

24.近源模型层到整合模型层的数据流算法-经济型基于全量数据删除拉链算法

此算法基本等同于基于全量数据删除拉链算法,只是在最后一步只将属性非空及非0的记录才做为开链数据插入目标表;

25.近源模型层到整合模型层的数据流算法-PK_NOT_IN_APPEND算法

此算法是对每一组只将PK在当前VT_NEW_编号表中未出现的数据再插入VT_NEW_编号表,最后再将PK未出如今目标表中的数据插入目标表,以保证只进那些PK未进过的数据;

26.近源模型层到整合模型层的数据流算法-以源日期字段自拉链算法

此算法是源表中有日期字段标识当前记录的生效日期,本算法经过对同主键记录按这个生效日期排序后,一次首尾相连行造成一条天然拉链的算法

end






Flink 从入门到精通 系列文章
基于 Apache Flink 的实时监控告警系统关于数据中台的深度思考与总结(干干货)日志收集Agent,阴暗潮湿的地底世界

公众号(zhisheng)里回复 面经、ClickHouse、ES、Flink、 Spring、Java、Kafka、监控 等关键字能够查看更多关键字对应的文章。

点个赞+在看,少个 bug ????