ETL工具算法构建企业级数据仓库五步法.docx

ETL工具算法构建企业级数据仓库五步法01 什么是 ETL ? ETL 是数据抽取（Extract ）、转换（Transform ）、 加载（Load ）的简写，它是将OLTP系统中的数据经过抽取，并将不 同数据源的数据进行转换、整合，得出一致性的数据，然后加载到数据 仓库中。简而言之ETL是完成从OLTP系统到OLAP系统的过程。02数据仓库架构数据仓库是基于OLTP系统的数据源，为了便于 多维分析和多角度展现将其数据按特定的模式进行存储而建立的关系 型数据库，它不同于多维数据库，数据仓库中的数据是细节的，集成的， 数据仓库是面向主题的，是以OLAP系统为分析目的。数据仓库包括星 型架构与雪花型架构，其中星型架构中间为事实表，四周为维度表，类 似星星；雪花型架构中间为事实表，两边的维度表可以再有其关联子表, 而在星型中只允许一张表作为维度表与事实表关联，雪花型一维度可以 有多张表，而星型不可以。考虑到效率时，星型聚合快，效率高，不过 雪花型结构明确，便于与OLTP系统交互。在实际工程中，综合运用星 型架构与雪花型架构。03 ETL构建企业级数据仓库五步法（一）确定主题即确定数 据分析或前端展现的某一方面的分析主题，例如我们分析某年某月某一 地区的啤酒销售情况，就是一个主题。主题要表达某一方面的各分析角 历史数据全部删除，然后重新全量加载。处理复杂度：历史拉链，Upsert,Append，全删全加。加载性能:全删全加，Append , Upsert , 历史拉链。近源模型层主要算法：APPEND算法，常规拉链算法,全量 带删除拉链算法。整合模型层算法：APPEND算法，MERGE算法,常 规拉链算法，基于增量数据的删除拉链算法，基于全量数据的删除拉链 算法，经济型常规拉链算法，经济型基于增量数据的删除拉链算法，经 济型基于全量数据的删除拉链算法,PK_NOT_IN_APPEND算法,源日 期字段自拉链算法。技术缓冲到近源模型层的数据流算法APPEND算法此算法通常用于流水事件表，适合这类算法的源表 在源系统中不会更新和删除，而只会发生一笔添加一笔，所以只需每天 将交易日期为当日最新数据取过来直接附加到目标表即可，此类表在近 源模型层的字段与技术缓冲层、源系统表基本上完全一致，不会额外增 加物理化处理字段，使用时也与源系统表的查询方式相同。技术缓冲到 近源模型层的数据流算法-常规拉链算法此算法通常用于无删除操 作的常规状态表，适合这类算法的源表在源系统中会新增、修改，但不 删除，所以需每天获取当日末最新数据（增量或全增量均可），先找出真 正的增量数据（新增和修改），用它们将目标表中属性发生修改的开链数 据（有效数据）进行关链操作（即END_DT关闭到当前业务日期），然后再 将最新的增量数据作为开链数据插入到目标表即可。此类表再近源模型 层比技术缓冲层、源系统的相应表额外增加两个物理化处理字段 START_DT（开始日期）和END_DT（结束日期），使用时需要先选定视觉日期，通过START_DT和END_DT去卡视觉日期，即START_DT，视觉日期：技术缓冲到近源模型层的数据流算法-全量带删除拉链算法此算法通常用于有删除操作的常规状态类 表，并且要求全量的数据文件，用以比照出删除增量;适合这类算法的 源表在源系统中会新增，修改，删除，每天将当日末最新全量数据取过 来外，分别找出真正的增量数据（新增，修改）和删除增量数据，用它们 将目标表中属性发生修改的开链数据（有效数据）进行关链操作（即 END_DT关闭到当前业务日期），然后再将最新增量数据中真正的增量 及删除数据作为开链数据插入到目标表即可，注意删除记录的删除标志 DELND会设置为D。此类表在近源模型层比技术缓冲层，源系统 的相应表额外增加三个物理化处理字段START_DT（开始日期）,ENT_DT（结束日期），DELND（删除标准）。使用方式分两类：一时一般 查询使用，此时需要先选定视角日期，通过START_DT和END_DT去 卡视角日期，即START_DT视角日期， 同时加上条件DELND 另一种是下载或获取当日增量数据，此时就是需要START_DT视角日期一个 条件即可，不需要加DELND D的条件。近源模型层到整合模型 层的数据流算法一一APPEND算法此算法通常用于流水事件表，适合这 类算法的源表在源系统中不会更新和删除，而只会发生一笔添加一笔, 所以只需每天将交易日期为当日的最新数据取过来直接附加到目标表 即可。通常建一张名为VT_NEW_编号的临时表，用于将各组当日最新 数据转换加到VT_NEW_编号后，再一次附加到最终目标表。近源模型 层到整合模型层的数据流算法一一MERGE INTO算法此算法通常用于无删除操作的常规状态表，一般是无需保存历史而只保存当前最新状态的表，适合这类算法的源表在源系统中会新增，修改，但不删除，所以 需获取当日末最新数据（增量或全量均可），用于MERGEIN或UPSERT 目标表。为了效率及识别真正增量的要求，通常先识别出真正的增量数 据（新增及修改数据），然后再用这些真正的增量数据向目标表进行MERGE INTO操作。通常建两张临时表,一个名为VT_NEW_编号,用 于将各组当日最新数据转换加到VT_NEW_编号。另一张名为VT_INC_ 编号,将VT_NEW_编号与目标表中昨日的数据进行比照后找出真正的 增量数据（新增和修改）放入VTNC_编号，然后再用VTNC_编号对最 终目标表进行MERGE INTO或UPSERT。近源模型层到整合模型层的数据流算法一一常规拉链算法此算法通常用于无删除操作的常规状态表，适合这类算法的源表在源系统中会新增、修改，但不删除，所以需每天 获取当日末最新数据（增量或全增量均可），先找出真正的增量数据（新增 和修改），用它们将目标表中属性发生修改的开链数据（有效数据）进行关 链操作（即END_DT关闭到当前业务日期），然后再将最新增量数据作为 开链数据插入到目标表即可。通常建两张临时表，一个名为VT_NEW_ 编号，用于将各组当日最新数据转换加到VT_NEW_编号。另一张名为VTNC_编号，将VT_NEW_编号与目标表中昨日的数据进行比照后找出真正的增量数据（新增和修改）放入VTNC_编号，然后再将最终目标表的开链数据中的PK出现在VTNT_编号中进行关链处理，然后将VTNC_编号中的所有数据作为开链数据插入最终目标表即可。近源模型层到整合模型层的数据流算法一基于增量数据删除拉链算法此算法通常用于有删除操作的常规状态表，并且要求删除数据是以DELND=，D，删除增量的形式提供;适合这类算法的源表再源系统中会 新增、修改、删除，除每天获取当日末最新数据（增量或全量均可）外， 还要获取当日删除的数据，根据找出的真正增量数据（新增和修改）以及 删除增量数据，用它们将目标表中属性发生修改的开链数据（有效数据） 进行关链操作（即END_DT关闭到当前业务时间），然后再将增量（不含 删除数据）作为开链数据插入到目标表中即可。通常建三张临时表，一 个名为VT_NEW_编号，用于将各组当日最新数据（不含删除数据）转换 加载到VT_NEW_编号。第二张表名为VTNC_编号，用VT_NEW一编 号与目标表中的昨日的数据进行比照后找出真正的增量数据放入 VTNC_编号。第三张表名为VT_DEL_编号，将删除增量数据转换加载 到VT_DEL_编号。最后再将最终目标表的开链数据中PK出现在 VTNC_编号或VT_DEL_编号中的进行关链处理，最后将VTNC_编号 中的所有数据作为开链数据插入最终目标表即可。近源模型层到整合模 型层的数据流算法一基于全量数据删除拉链算法此算法通常用于有删 除操作的常规状态表，并且要求提供全量数据，用以比照出删除增量。 适合这类算法的源表在源系统中会新增、修改、每天将当日末的最新全 量数据取过来外，分别找出真正的增量数据（新增、修改）和删除增量数 据，用它们将目标表中属性发生修改的开链数据（有效记录）进行关链操 作（即END_DT关闭到当前业务时间），然后再将最新数据中真正的增量 数据（不含删除数据）作为开链数据插入到目标表即可。通常建两张临时 表，一个名为VT_NEW_编号，用于将各组当日最新全量数据转换到VT_NEW_编号。另一张表名为VTNC_编号，将VT_NEW_编号与目标 表中昨日的数据进行比照后找出真正的增量数据（新增、修改）和删除增 量数据放入VTNC_编号，注意将其中的删除增量数据的END_DT置 以最小日期（借用）;最后再将最终目标表的开链数据中PK出现再 VTNC_编号或VT_DEL_编号中的进行关链处理，然后将VTNC_编号 中所有的END_DT不等于最小日期数据（非删除数据）作为开链数据插入 最终目标表即可。近源模型层到整合模型层的数据流算法一经济型常规 拉链算法此算法基本等同与常规拉算法，只是在最后一步只将属性非空 即非0的记录才作为开链数据插入目标表。近源模型层到整合模型层的 数据流算法一经济型基于增量数据删除拉链算法此算法基本等同于基 于增量数据删除拉链算法，只是在最后一步只将属性非空及非0的记录 才作为开链数据插入目标表。近源模型层到整合模型层的数据流算法一 经济型基于全量数据删除拉链算法此算法基本等同于基于全量数据删 除拉链算法，只是在最后一步只将属性非空及非0的记录才作为开链数 据插入目标表。近源模型层到整合模型层的数据流算法-PK_NOT_IN_APPEND算法此算法是对每一组只将PK在当前VT_NEW_编号表中未出现的数据再插入VT_NEW_编号表，最后再将PK未出现在目标表中的数据插入目标表，以保证只进那些PK未进过的数据。近源模型层到整合模型层的数据流算法一以源日期字段自拉链算 法此算法是源表中有日期字段标识当前记录的生效日期，本算法通过对同主键记录按这个生效日期排序后，一次首尾相连行形成一条自然拉链 的算法。-END -度（维度）和统计数值型数据（量度），确定主题时要综合考虑，一个 主题在数据仓库中即为一个数据集市，数据集市表达了某一方面的信息， 多个数据集市构成了数据仓库。（二）确定量度在确定了主题以后，考 虑要分析的技术指标，诸如年销售额此类，一般为数值型数据，或者将 该数据汇总，或者将该数据取次数，独立次数或取最大最小值等，这样 的数据称之为量度。量度是要统计的指标，必须事先选择恰当，基于不 同的量度可以进行复杂关键性能指标（KPI ）等的计算。（三）确定事 实数据粒度在确定了量度之后要考虑到该量度的汇总情况和不同维度 下量度的聚合情况，考虑到量度的聚合程度不同，将采用最小粒度原 那么，即将量度的粒度设置到最小。例如将按照时间对销售额进行汇总， 目前的数据最小记录到天，即数据库中记录了每天的交易额，那么不能 在ETL时将数据进行按月或年汇总，需要保持到天，以便于后续对天进 行分析。而且不必担忧数据量和数据没有提前汇总带来的问题，因为在 后续的建立CUBE时已经将数据提前汇总了。（四）确定维度维度是要 分析的各个角度，例如希望按照时间，或者按照地区，或者按照产品进 行分析，那么这里的时间、地区、产品就是相应的维度，基于不同的维 度可以看到各量度的汇总情况，我们可以基于所有的维度进行交叉分析。 这里首先要确定维度的层次（Hierarchy ）和级别（Level ），维度的层 次是指该维度的所有级别，包括各级别的属性；维度的级别是指该维度 下的成员。例如当建立地区维度时我们将地区维度作为一个级别，层次 为省、市、县三层，考虑到维度表要包含尽量多的信息，所以建立维度 时要符合矮胖原那么，即维度表要尽量宽，尽量包含所有的描述性信 息，而不是统计性的数据信息。还有一种常见的情况，就是父子型维度， 该维度一般用于非叶子节点含有成员等情况，例如公司员工的维度，在 统计员工的工资时，部门主管的工资不能等于下属成员工资的简单相加， 必须对该主管的工资单独统计，然后该主管部门的工资等于下属员工工 资加部门主管的工资，那么在建立员工维度时，需要将员工维度建立成 父子型维度，这样在统计时，主管的工资会自动加上，防止了都是叶子 节点才有数据的情况。另外，在建立维度表时要充分使用代理键，代理 键是数值型的ID号码，好处是代理键唯一标识了每一维度成员信息， 便于区分，更重要的是在聚合时由于数值型匹配，JOIN效率高，便于 聚合，而且代理键对缓慢变化维度有更重要的意义，它起到了标识历史 数据与新数据的作用，在原数据主键相同的情况下，代理键起到了对新 数据与历史数据非常重要的标识作用。有时也会遇到维度缓慢变化的情 况，比方增加了新的产品，或者产品的ID号码修改了，或者产品增加 了一个新的属性，此时某一维度的成员会随着新的数据的加入而增加新 的维度成员，这样要考虑到缓慢变化维度的处理，对于缓慢变化维度， 有三种情况：1、缓慢变化维度第一种类型：历史数据需要修改。这样 新来的数据要改写历史数据，这时要使用UPDATE ,例如产品的ID号 码为123 ,后来发现ID号码错误了 ,需要改写成456 ,那么在修改好 的新数据插入时，维度表中原来的ID号码会相应改为456 ,这样在维 度加载时要使用第一种类型，做法是完全更改。2、缓慢变化维度第二 种类型：历史数据保存，新增数据也要保存。这时要将原数据更新，将 新数据插入，需要使用UPDATE / INSERT ,比方某一员工2005年在A 部门，2006年时他调到了 B部门。那么在统计2005年的数据时就应 该将该员工定位到A部门；而在统计2006年数据时就应该定位到B 部门，然后再有新的数据插入时，将按照新部门（B部门）进行处理, 这样做法是将该维度成员列表加入标识列，将历史的数据标识为过 期，将目前的数据标识为“当前的。另一种方法是将该维度打上时 间戳，即将历史数据生效的时间段作为它的一个属性，在与原始表匹配 生成事实表时将按照时间段进行关联，这样的好处是该维度成员生效 时间明确。3、缓慢变化维度第三种类型：新增数据维度成员改变了属 性。例如某一维度成员新加入了一列，该列在历史数据中不能基于它浏 览，而在目前数据和将来数据中可以按照它浏览，那么此时需要改变维 度表属性，即加入新的列，那么我们将使用存储过程或程序生成新的维 度属性，在后续的数据中将基于新的属性进行查看。（五）创立事实表 在确定好事实数据和维度后，接下来考虑加载事实表。在公司的大量数 据堆积如山时，我们想看看里面究竟是什么，结果发现里面是一笔笔生 产记录，一笔笔交易记录那么这些记录是将要建立的事实表的原始 数据，即关于某一主题的事实记录表。做法是将原始表与维度表进行关 联，生成事实表。注意在关联时有为空的数据时（数据源脏），需要使 用外连接，连接后将各维度的代理键取出放于事实表中，事实表除了各 维度代理键外，还有各量度数据，这将来自原始表，事实表中将存在维 度代理键和各量度，而不应该存在描述性信息，即符合“瘦高原那么”， 即要求事实表数据条数尽量多（粒度最小），而描述性信息尽量少。如 果考虑到扩展，可以将事实表加一唯一标识列，以为了以后扩展将该事 实作为雪花型维度，不过不需要时一般建议不用这样做。事实数据表是 数据仓库的核心，需要精心维护，在JOIN后将得到事实数据表，一般 记录条数都比拟大，需要为其设置复合主键和索引，以为了数据的完整 性和基于数据仓库的查询性能优化，事实数据表与维度表一起放于数据 仓库中，如果前端需要连接数据仓库进行查询，还需要建立一些相关的 中间汇总表或物化视图，以方便查询。04 ETL中高级技巧的运用（一）准备区的运用在构建数据仓库 时，如果数据源位于一服务器上，数据仓库在另一服务器端，考虑到数 据源Server端访问频繁，并且数据量大，需要不断更新，所以可以建 立准备区数据库。先将数据抽取到准备区中，然后基于准备区中的数 据进行处理，这样处理的好处是防止了在原OLTP系统中中频繁访问, 进行数据运算或排序等操作。例如我们可以按照天将数据抽取到准备区 中，基于数据准备区，进行数据的转换、整合，将不同数据源的数据进 行一致性处理。数据准备区中将存在原始抽取表，一些转换中间表和临 时表以及ETL日志表等。（二）时间戳的运用时间维度对于某一事实主 题来说十分重要，因为不同的时间有不同的统计数据信息，那么按照时 间记录的信息将发挥很重要的作用。在ETL中，时间戳有其特殊的作用, 在上面提到的缓慢变化维度中，可以使用时间戳标识维度成员；在记录 数据库和数据仓库的操作时，也将使用时间戳标识信息。例如在进行数 据抽取时按照时间戳对OLTP系统中的数据进行抽取比方在午夜0 : 00取前一天的数据，按照OLTP系统中的时间戳取GETDATE到GETDATE减一天，这样得到前一天数据。（三）日志表的运用在对数据进行处理时，难免会发生数据处理错误，产生出错信息，那么如何获 得出错信息并及时修正呢？方法是使用一张或多张Log日志表，将出错 信息记录下来，在日志表中将记录每次抽取的条数，处理成功的条数, 处理失败的条数，处理失败的数据，处理时间等等，这样当数据发生错 误时，很容易发现问题所在，然后对出错的数据进行修正或重新处理。（四）使用调度在对数据仓库进行增量更新时必须使用调度，即对事实 数据表进行增量更新处理，在使用调度前要考虑到事实数据量，需要多 长时间更新一次，比方希望按天进行查看，那么最好按天进行抽取，如 果数据量不大，可以按照月或半年对数据进行更新，如果有缓慢变化维 度情况，调度时需要考虑到维度表更新情况，在更新事实数据表之前要 先更新维度表。调度是数据仓库的关键环节，要考虑缜密，在ETL的流 程搭建好后，要定期对其运行，所以调度是执行ETL流程的关键步骤, 每一次调度除了写入Log日志表的数据处理信息外，还要使用发送 Email或报警信息等，这样也方便的技术人员对ETL流程的把握，增强 了平安性和数据处理的准确性。ETL构建数据仓库需要简单的五步，掌 握了这五步的方法将构建一个强大的数据仓库，不过每一步都有很深的 需要研究与挖掘，尤其在实际工程中，要综合考虑，例如如果数据源的 脏数据很多，在搭建数据仓库之前首先要进行数据清洗，以剔除掉不需 要的信息和脏数据。总之，ETL是数据仓库的核心，掌握了 ETL构建数 据仓库的五步法，就掌握了搭建数据仓库的根本方法。不过，不能盲目 教条，基于不同的工程，需要进行具体分析，如父子型维度和缓慢变化 维度的运用等。在数据仓库构建中，ETL关系到整个工程的数据质量， 所以马虎不得，必须将其摆到重要位置，将ETL这一大厦根基筑牢。05 ETL与SQL的区别及联系如果ETL和SQL来说，肯定是 SQL效率高的多。但是双方各有优势，先说ETL , ETL主要面向的是建 立数据仓库来使用的。ETL更偏向数据清洗，多数据源数据整合，获取 增量，转换加载到数据仓库所使用的工具。比方有两个数据源，一个是 数据库的表，另外一个是Excel数据，需要合并这两个数据，通常这种 东西在SQL语句中比拟难实现。但是ETL却有很多现成的组件和驱动， 几个组件就搞定了。还有比方跨服务器，并且服务器之间不能建立连接 的数据源，比方公司系统分为一期和二期，存放的数据库是不同的，数 据结构也不相同，数据库之间也不能建立连接，这种情况下，ETL就显 得尤为重要和突出。通过固定的抽取，转换，加载到数据仓库中，即可 很容易实现。那么SQL呢？ SQL事实上只是固定的脚本语言，但是执 行效率高，速度快。不过灵活性不高，很难跨服务器整合数据。所以 SQL更适合在固定数据库中执行大范围的查询和数据更改，由于脚本语 言可以随便编写，所以在固定数据库中能够实现的功能就相当强大，不 像ETL中功能只能受组件限制，组件有什么功能，才能实现什么功能。 所以具体在什么时候使用ETL和SQL就很明显了，当需要多数据源整 合建立数据仓库，并进行数据分析的时候，使用ETLO如果是固定单一 数据库的数据层次处理，就使用SQL。当然,ETL也是离不开SQL的。06 ETL算法和工具简介常用的ETL工具主要有三大主流工具， 分别是 Ascential 公司的 Datastage、Informatica 公司的 Powercenter.NCR Teradata公司的ETL Automationo还有其他开源工具，如 PDI(Kettle)等。ETL是DW系统的基础：DW系统以事实发生数据为基础，自产数据较少。一个企业往往包含多个业务系统，均可能成为DW数据源。业务系统数据质量良莠不齐，必须学会去伪存真。业务系统数据纷繁复杂，要整合进数据模型。源数据之间关系也纷繁复杂，源数据在加工进DW系统时，有些 必须遵照一定的先后次序关系。源数据的分类：流水事件表：此类源表用于记录交易等动作的发生，在源系统中 会新增、大局部不会修改和删除，少量表存在删除情况。如定期存款登 记簿。常规状态表：此类源表用于记录数据信息的状态。在源系统中会 新增、修改，也存在删除的情况。如客户信息表。代码参数表：此类源表用于记录源系统中使用到的数据代码和参 数。数据文件的类型：数据文件大多数以1天为固定的周期从源系统加载到数据仓库。数据文件包含增量，全量以及待删除的增量。增量数据文件：数据文件的内容为数据表的增量信息，包含表内新增及修改的记录。全量数据文件：数据文件的内容为数据表的全量信息，包含表内的所有数据。带删除的增量：数据文件的内容为数据表的增量信息，包含表内 新增、修改及删除的记录，通常删除的记录以字段DELND = D标识 该记录。ETL标准算法可划分为：历史拉链算法、追加算法（事件表）、Upsert算 法（主表）及全删全加算法（参数表）。ETL标准算法选择：历史拉链：根据业务分析要求，对数据变化都要记录，需要基于 日期的连续历史轨迹；追加（事件表）：根据业务分析要求，对数据变化都要记录，不需 要基于日期的连续历史轨迹；Upsert（主表）：根据业务分析要求，对数据变化不需要都要记录， 当前数据对历史数据有影响；全删全加算法（参数表）：根据业务分析要求，对数据变化不需要 都要记录，当前数据对历史数据无影响；历史拉链法：所谓拉链，就是记录历史，记录一个事务从开始，一直到 当前状态的所有变化信息（参数新增开始结束日期）。追加算法：一般用于事件表，事件之间相对独立，不存在对历史信息进 行更新。Upsert算法：时update和insert组合体，一般用于对历史 信息变化不需要进行跟踪保存、只需其最新状态且数据量有一定规模的 表，如客户资料表。全删全加算法：一般用于数据量不大的参数表，把