ETL流程数据流图及ETL过程解决方案课件

ETL定义目录目录ETL定义ETL定义涉及以下内容： ETL定义 ETL前提 ETL原则ETL定义定义： 数据的抽取(Extract)、转换(Transform)、装载(Load)的过程目标： 数据优化。以最小代价(包括对日常操作的影响和对技能的要求) 将针对日常业务操作的数据转化为针对数据仓库而存储的决策支持型数据 ETL的前提 确定ETL范围 通过对目标表信息的收集，确定ETL的范围 选择ETL工具 考虑资金 运行的平台、对源和目标的支持程度、可编程的灵活性、对源数据变化的监测、数据处理时间的控制、管理和调度功能、对异常情况的处理 确定解决方案 抽取分析、变化数据的捕获、目标表的刷新策略、数据的转换及数据验证ETL过程中应尽量遵循以下原则： 应尽量利用数据中转区对运营数据进行预处理。保证数据的安全性、集成与加载的高效性。 ETL的过程应是主动“拉取”，而不是从内部“推送”，其可控性将大为增强。 流程化的配置管理和标准协议 数据质量的保证 正确性、一致性、完整性、有效性、可获取性ETL定义模式及比较目录目录ETL模式及比较两种模式 异构 同构模式比较的维度： 特点 环境ETL模式-同构ETL模式-异构两种模式的比较-特点异构异构(Asynchronous )同构同构(Synchronous )比同构模式提供了更好的数据处理性能，需要更少的处理时间，因为通过网络传输文件的速度比直接通过数据库存取数据要快很多。要避免性能瓶颈问题，解决办法是缩小每次抽取的时间粒度，例如将抽取周期定为每日抽取，这样可以保证每次抽取的增量数据数目是很少量的。在数据抽取过程中，应尽量避免本次抽取定义的时间区间内的源数据在抽取过程中同时产生变动的情况。即抽取的理想状况是抽取的同时源数据系统的数据是静止的，没有增、删、改的情况伴随发生。对于ODS系统来说，数据不会频繁地发生变动；而对于OLTP系统来说，应该选择源数据变化较少的时段完成抽取工作。与异构方式类似，应避免抽取时间区间和源数据系统的生产时段相重合。如果源数据系统时刻都有新数据插入，一种解决办法是设置一个时间区间，定义每次抽取的开始和结束时间值：本次抽取的开始时间为上次抽取的结束时间，本次抽取的结束时间为机器系统时间（Sysdate）或者是目前数据库系统中已有记录的最大时间戳值。实际上就是定义某个时间区间内的源数据的快照(Snapshot)，这样就可以避免重复装载的情况发生。除此之外，还应该充分考虑源和目标两套数据库系统的Down机的时间因素。需要两套ETL包，一个用来抽取，一个用来装载，两个包都需要由专门的系统管理人员监视是否装载过程会发生错误。只需要一个ETL软件包。系统管理人员也只需要监视一套系统。源和目标之间没有直接的联系。只要中间过渡的文本文件结构不发生变化，源和目标的结构即使改变而不会对ETL流程产生很大的影响。源和目标的关系是被绑定在具体的映射中的。当源或者目标的结构发生变化，相对应的映射也要做修改。异构异构(Asynchronous )同构同构(Synchronous )当ETL错误发生时，可以采用简单的处理办法修复数据：当抽取失败时，修正问题并重新从源中抽取；当装载过程发生问题，回滚(Roll back)，返回上一次装载的状态并再次运行装载的程序。必要时甚至可以将数据仓库系统恢复到某一个时点的状态并批量地装载文本文件。与异构模式类似，当ETL错误发生也可以采用相应手段来修复数据。例如，倘若源数据库中保留了所有历史记录，ETL程序可以根据设定的时间区间修复上一次装载失败的数据。前提是必须先删除上一次装载失败从而在目标库中产生的垃圾数据，回滚(Roll back)，返回到上一次加载数据前的状态。可以根据目标表的主键来确定装载过程中插入或更新记录的策略，如果源记录的主键是新的，那么就插入该记录；如果源记录的主键在目标数据库中已经存在，那么就用源记录更新目标记录。这样就可以避免多次重复装载的情况发生。同时，适当调大装载的时间区段也不会发生数据重复加载的情况。需要有专门的核查(Audit)程序来监控数据传输或者装载过程是否有失败或者记录缺失的情况发生。直接拷贝(direct copy), 可以避免传送过程中的许多错误。可以在源和目标库中运行Sum 和Count等聚合函数来对数据质量进行校验。能够简化查错troubleshooting的过程，由于在中间步骤生成文本文件，实际上是所要抽取的源数据的快照(Snapshot)。问题发生时，可以很容易地确定是哪一个环节发生了问题，源，目标还是网络传输？与异步模式相比，查错过程比较复杂。源数据的不断变化导致问题难以定位。很难判断问题究竟是由于抽取程序还是由于源数据的变化引起的(上一次抽取的数据是什么?)。异构异构(Asynchronous )同构同构(Synchronous )源和目标的数据接口分离，只需要定义好中间的文本文件数据接口，就可以同步完成独立的源和目标的开发工作。当各自模块完成后再将其装配，提高开发效率。开发人员不仅需要熟悉源的结构，对目标的结构也要有所了解。要将数据导出成字节流并写入导文本文件中。如果源包含图形数据，要将其导出成文本，实现起来有一定的难度。从源到目标的直接映射，不需要从ASCII或者Unicode作为中间过渡。维护过程中要求不能误删文件或其中的某些记录，以免破坏文本文件之间的关联关系。主表和子表之间有主外键相关联，数据库中的各表记录能够保持相互间的匹配关系。数据转换过程包括两个步骤：1、将数据库中的表导出成中间过渡的文本；2、装载文件。导出的处理过程比较灵活，可以从源表导出，也可以从相关的视图导出，也可将源表内容先导出到临时表再导出到文本文件。处理导出过程的存储过程可以在源上，也可以在目标上。数据转换过程只有一个步骤，一次性地完成导出和装载的工作。简化了设计和测试的过程，但是另一方面也降低了灵活性。要求具备两套安全控制机制，对于源数据库有读权限，对于目标数据库有写权限。同时还需要有能够在源和目标服务器上有写文件的权限（用于存放中间文本文件和上传文件到目标服务器）。 与异构模式类似，也需要对于源数据库有读权限，对于目标数据库有写权限。但是抽取过程可以不需要源和目标服务器上操作系统级的文件管理权限。两种模式的比较-环境条件条件异构异构(Asynchronous) 同构同构(Synchronous)数据传输(Data transfer)大数据量小数据量网络连接(Network connectivity)广域网局域网或者同一数据中心源和目标在物理架构上是否属于不同的分布式环境是不是抽取数据的复杂度(Complexity of data)源中只包含了文本或数值类型的字段源数据库中包含了图形类字段ETL定义ETL过程目录目录ETL过程ETL过程： 数据抽取 数据清洗 数据转换 数据加载ETL的问题ETL过程-0层DFD1层-数据抽取1层-数据清洗1层-数据转换1层-数据加载ETL过程-数据抽取 数据来源 文件系统，业务系统 抽取方式 根据具体业务进行全量或增量抽取 抽取效率 将数据按一定的规则拆分成几部分进行并行处理 抽取策略 根据具体业务制定抽取的时间、频度，以及抽取的流程ETL过程-数据清洗清洗规则： 数据补缺 对空数据、缺失数据进行数据补缺操作，无法处理的作标记 数据替换 对无效数据进行数据的替换 格式规范化 将源数据抽取的数据格式转换成为便于进入仓库处理的目标数据格式 主外键约束 通过建立主外键约束，对非法数据进行替换或导出到错误文件重新处理转换规则 数据合并 多用表关联实现，大小表关联用lookup，大大表相交用join（每个字段加索引，保证关联查询的效率） 数据拆分 按一定规则进行数据拆分 行列互换 排序/修改序号 去除重复记录 数据验证：lookup，sum，count实现方式 在ETL引擎中进行（SQL无法实现的） 在数据库中进行（SQL可以实现）ETL过程-数据加载实现方式优点缺点时戳方式在业务表中统一添加字段作为时戳，当OLTP系统更新修改业务数据时，同时修改时戳字段值源数据抽取相对简单清楚，速度快，适合数据的增量加载需要修改业务表中的数据结构，业务数据变动时工作量比较大，相对风险较大日志表方式在OLTP系统中添加日志表，业务数据发生变化时，更新维护日志表内容不需要修改业务表中的数据结构。源数据抽取简单清楚，速度快，适合数据的增量加载业务系统中更新记录日志操作麻烦全表对比方式抽取所有源数据，在更新目标表之前先根据主键和字段进行数据比对，有更新的进行update或insert对系统表结构没有任何影响，管理维护统一，可以实现数据的增量加载数据比对复杂，设计比较复杂，执行速度慢全表删除插入方式删除目标表数据，将源数据全部插入ETL规则简单，速度快对维表加代理健不适应，OLTP系统有删除数据时，不能在数据仓库体现被删数据，不能实现增量加载ETL定义问题分析目录目录ETL执行时的异常处理数据异常 将错误信息单独输出，继续执行ETL，错误数据修改后再单独加载 中断ETL，修改后重新执行ETL原则：最大限度接收数据环境异常 对于网络中断等外部原因造成的异常，设定尝试次数或尝试时间，超数或超时后，由外部人员手工干预其他异常 例如源数据结构改变、接口改变等异常状况，应进行同步后，再装载数据ETL设计规范DI开发规范 ETL开发首要确定的是流程的执行顺序及条件；其次是具体表映射关系的定义，在数据库性能允许的情况下，应该尽可能使用sql语句进行处理 对于具体映射和流程的命名，应该以维护方便为前提： 映射：以目标表名命名 流程：以流程要实现的功能命名 不允许使用临时的SQL语句操纵数据库，必须编写好的SQL脚本或存储过程 限定手工干预只能运行某个流程，不允许运行单个过程 每一项手工操作必须留下记录设计规范 SQL语句应书写规范，关键字全部大写，同时应增加注释 例如： /*表名：BSL_EMP_STATUS 作者：CASEY 日期：2007-12-17 描述：人员状态中间表*/ 对于自定义列，列名应采用标准后缀 数字类型，*_NO 字符类型，*_CODE常见问题的分析 字符集问题 缓慢变化维处理 增量、实时同步的处理 错误数据的检测 变化数据的捕获 抽取异常中止的处理一、字符集问题字符集定义 字符集是字符(包含字母，数字，符号和非打印字符等)以及所指定的内码所组成的特定的集合。是基于某种操作系统平台和某种语言集支持的。语言集的集合被称为语言组，它可能包含一种或多种语言。C/S字符集转换 直接转换 对于同一语言组的不同字符集之间，可以直接进行字符的转换，不会产生乱码 通过Unicode转换 Unicode支持超过650种语言的国际字符集 Unicode系统缺省字符集utf-8不同语言组的字符集进行直接转换的时候会出现乱码！异构库字符集转换 假设案例： 源数据库为Oracle 10g，字符集zhs 16gbk 目标数据库Sybase IQ，字符集为x， locales.dat文件对应操作系统下字符集设置为y x=cp936，y=cp936 结果：直接转换出现乱码。因为sybase目前支持的中文字符集有cp936、eucgb、gb18030、UTF-8 方案：利用unicode进行转换 ODBC 利用字符集设置为UTF-8的中间库二、缓慢变化维处理缓慢变化维定义 在现实世界中，维度的属性并不是静态的，会随着时间的流失发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维。处理方式 不保留历史数据 保留历史数据 起始-结束日期字段标识 真/假状态字段标识 版本号字段标识 代理键字段标识 自增序列 构造算法 保留且分析历史信息 添加新的维度列（数据增多，维度列增多）三、增量、实时同步的处理 整表匹配 同一个库中进行 写触发器 客户是否允许创建触发器 是否影响数据库性能 读数据库日志 Oracle：设定物化视图日志四、断点续传 利用源表的索引机制，抽取时按”数据块”顺序抽取 采取DBLink的机制，结合oracle自身机制优化效率 生成本地文件块，FTP传输减少对带宽影响。若中断，流程控制自动回滚加载当前数据块 ETL工具大都支持异常中止后读取断点重新加载的处理 支持对变化数据的捕获 与目标数据库松耦合存储过程与ETL存储过程：数据库内部利用索引的查找、排序、优化，不涉及输出、转换等操作ETL：异构数据源或减少数据库负荷、sql嵌套的情况 如果仓库中同时需要明细和汇总数据，在仓库汇总；如果仓库不需要明细数据，ETL服务器上先排序然后汇总，减少业务系统负荷。 数据库不适合做大数据量汇总的话，排序后按排序键汇总，并行处理加动态分区（ETL厂商高性能汇总的机理）ETL定义 现状分析目录目录ETL工具厂商目前ETL工具来源： 数据库厂商自带的ETL工具，如OWB等； 第三方工具提供商，如Informatic等； 开源ETL工具，如kettle；ETL和数据集成工具：ETL和数据集成的工作量占BI项目的40%，但是ETL工具约占BI市场的9%，其中很多应用是采用手工编码方式，ETL工具仍有待普及 ?谢 谢 !