搜索
关于ETL相关问题的解决办法◇ETL定义◇模式及比较◇ETL过程◇问题分析◇现状分析目录ETL定义ETL定义涉及以下内容：–ETL定义–ETL前提–ETL原则ETL定义定义：–数据的抽取(Extract)、转换(Transform)、装载(Load)的过程目标：–数据优化。以最小代价(包括对日常操作的影响和对技能的要求)将针对日常业务操作的数据转化为针对数据仓库而存储的决策支持型数据ETL的前提–确定ETL范围•通过对目标表信息的收集，确定ETL的范围–选择ETL工具•考虑资金•运行的平台、对源和目标的支持程度、可编程的灵活性、对源数据变化的监测、数据处理时间的控制、管理和调度功能、对异常情况的处理–确定解决方案•抽取分析、变化数据的捕获、目标表的刷新策略、数据的转换及数据验证ETL过程中应尽量遵循以下原则：–应尽量利用数据中转区对运营数据进行预处理。保证数据的安全性、集成与加载的高效性。–ETL的过程应是主动“拉取”，而不是从内部“推送”，其可控性将大为增强。–流程化的配置管理和标准协议–数据质量的保证•正确性、一致性、完整性、有效性、可获取性◇ETL定义◇模式及比较◇ETL过程◇问题分析◇现状分析目录ETL模式及比较两种模式–异构–同构模式比较的维度：–特点–环境ETL模式-同构SynchronousarchitectureETLSourceDTMReaderTargetSourceDataCenterSourceDataCenterTargetDataCenterTargetDataCenterETLETL模式-异构AsynchronousarchitectureETLSourceDTMReaderTargetFTPFTPETLETLFlatFlatFileFileFlatFlatFileFileETLETLSourceDataCenterSourceDataCenterTargetDataCenterTargetDataCenter两种模式的比较-特点异构(Asynchronous)同构(Synchronous)比同构模式提供了更好的数据处理性能，需要更少的处理时间，因为通过网络传输文件的速度比直接通过数据库存取数据要快很多。要避免性能瓶颈问题，解决办法是缩小每次抽取的时间粒度，例如将抽取周期定为每日抽取，这样可以保证每次抽取的增量数据数目是很少量的。在数据抽取过程中，应尽量避免本次抽取定义的时间区间内的源数据在抽取过程中同时产生变动的情况。即抽取的理想状况是抽取的同时源数据系统的数据是静止的，没有增、删、改的情况伴随发生。对于ODS系统来说，数据不会频繁地发生变动；而对于OLTP系统来说，应该选择源数据变化较少的时段完成抽取工作。与异构方式类似，应避免抽取时间区间和源数据系统的生产时段相重合。如果源数据系统时刻都有新数据插入，一种解决办法是设置一个时间区间，定义每次抽取的开始和结束时间值：本次抽取的开始时间为上次抽取的结束时间，本次抽取的结束时间为机器系统时间（Sysdate）或者是目前数据库系统中已有记录的最大时间戳值。实际上就是定义某个时间区间内的源数据的快照(Snapshot)，这样就可以避免重复装载的情况发生。除此之外，还应该充分考虑源和目标两套数据库系统的Down机的时间因素。需要两套ETL包，一个用来抽取，一个用来装载，两个包都需要由专门的系统管理人员监视是否装载过程会发生错误。只需要一个ETL软件包。系统管理人员也只需要监视一套系统。源和目标之间没有直接的联系。只要中间过渡的文本文件结构不发生变化，源和目标的结构即使改变而不会对ETL流程产生很大的影响。源和目标的关系是被绑定在具体的映射中的。当源或者目标的结构发生变化，相对应的映射也要做修改。异构(Asynchronous)同构(Synchronous)当ETL错误发生时，可以采用简单的处理办法修复数据：当抽取失败时，修正问题并重新从源中抽取；当装载过程发生问题，回滚(Rollback)，返回上一次装载的状态并再次运行装载的程序。必要时甚至可以将数据仓库系统恢复到某一个时点的状态并批量地装载文本文件。与异构模式类似，当ETL错误发生也可以采用相应手段来修复数据。例如，倘若源数据库中保留了所有历史记录，ETL程序可以根据设定的时间区间修复上一次装载失败的数据。前提是必须先删除上一次装载失败从而在目标库中产生的垃圾数据，回滚(Rollback)，返回到上一次加载数据前的状态。可以根据目标表的主键来确定装载过程中插入或更新记录的策略，如果源记录的主键是新的，那么就插入该记录；如果源记录的主键在目标数据库中已经存在，那么就用源记录更新目标记录。这样就可以避免多次重复装载的情况发生。同时，适当调大装载的时间区段也不会发生数据重复加载的情况。需要有专门的核查(Audit)程序来监控数据传输或者装载过程是否有失败或者记录缺失的情况发生。直接拷贝(directcopy),可以避免传送过程中的许多错误。可以在源和目标库中运行Sum和Count等聚合函数来对数据质量进行校验。能够简化查错troubleshooting的过程，由于在中间步骤生成文本文件，实际上是所要抽取的源数据的快照(Snapshot)。问题发生时，可以很容易地确定是哪一个环节发生了问题，源，目标还是网络传输？与异步模式相比，查错过程比较复杂。源数据的不断变化导致问题难以定位。很难判断问题究竟是由于抽取程序还是由于源数据的变化引起的(上一次抽取的数据是什么?)。异构(Asynchronous)同构(Synchronous)源和目标的数据接口分离，只需要定义好中间的文本文件数据接口，就可以同步完成独立的源和目标的开发工作。当各自模块完成后再将其装配，提高开发效率。开发人员不仅需要熟悉源的结构，对目标的结构也要有所了解。要将数据导出成字节流并写入导文本文件中。如果源包含图形数据，要将其导出成文本，实现起来有一定的难度。从源到目标的直接映射，不需要从ASCII或者Unicode作为中间过渡。维护过程中要求不能误删文件或其中的某些记录，以免破坏文本文件之间的关联关系。主表和子表之间有主外键相关联，数据库中的各表记录能够保持相互间的匹配关系。数据转换过程包括两个步骤：1、将数据库中的表导出成中间过渡的文本；2、装载文件。导出的处理过程比较灵活，可以从源表导出，也可以从相关的视图导出，也可将源表内容先导出到临时表再导出到文本文件。处理导出过程的存储过程可以在源上，也可以在目标上。数据转换过程只有一个步骤，一次性地完成导出和装载的工作。简化了设计和测试的过程，但是另一方面也降低了灵活性。要求具备两套安全控制机制，对于源数据库有读权限，对于目标数据库有写权限。同时还需要有能够在源和目标服务器上有写文件的权限（用于存放中间文本文件和上传文件到目标服务器）。与异构模式类似，也需要对于源数据库有读权限，对于目标数据库有写权限。但是抽取过程可以不需要源和目标服务器上操作系统级的文件管理权限。两种模式的比较-环境条件异构(Asynchronous)同构(Synchronous)数据传输(Datatransfer)大数据量小数据量网络连接(Networkconnectivity)广域网局域网或者同一数据中心源和目标在物理架构上是否属于不同的分布式环境是不是抽取数据的复杂度(Complexityofdata)源中只包含了文本或数值类型的字段源数据库中包含了图形类字段◇ETL定义◇模式及比较◇ETL过程◇问题分析◇现状分析目录ETL过程ETL过程：–数据抽取–数据清洗–数据转换–数据加载ETL的问题ETL过程-0层DFD业务数据P1数据抽取未经清洗加工的数据P2数据清洗清洗后的有效数据文件数据P3数据转换与目标匹配的数据P4数据加载数据仓库文件字段映射字段映射数据过滤业务清洗规则加载转换规则转换规则加载装载策略批量加载RejectP0ETL过程1层-数据抽取业务数据P1.2增量抽取P1.3全量抽取P1.1字段关联文件数据业务字段业务字段原始中转数据字段关联后的数据全量数据增量数据待清洗数据元数据源/规则抽取方式/频率抽取方式/频率P1数据抽取1层-数据清洗待清洗数据P2.1数据替换无效数据P2.2数据补缺缺失数据P2.3数据规范化干净的中转数据干净的业务数据干净的业务数据干净的业务数据干净数据清洗后的有效数据格式转换元数据清洗规则清洗规则清洗规则P2数据清洗1层-数据转换清洗后的有效数据P3.1数据/表拆分拆分P3.2关联查询多表关联P3.3数据计算计算与目标匹配的明细数据数据匹配匹配数据匹配仓库需求的明细中转数据元数据关联规则拆分规则计算规则P3数据转换1层-数据加载与目标匹配的明细数据P4.1增量加载增量数据P4.3数据比对维度数据P4.2全量加载全量数据仓库细节事实数据仓库细节维度数据统一数据统一数据插入/更新文件数据RejectRejectReject元数据加载方式/目标加载方式/目标加载方式/目标P4数据加载ETL过程-数据抽取–数据来源•文件系统，业务系统–抽取方式•根据具体业务进行全量或增量抽取–抽取效率•将数据按一定的规则拆分成几部分进行并行处理–抽取策略•根据具体业务制定抽取的时间、频度，以及抽取的流程ETL过程-数据清洗清洗规则：–数据补缺•对空数据、缺失数据进行数据补缺操作，无法处理的作标记–数据替换•对无效数据进行数据的替换–格式规范化•将源数据抽取的数据格式转换成为便于进入仓库处理的目标数据格式–主外键约束•通过建立主外键约束，对非法数据进行替换或导出到错误文件重新处理转换规则–数据合并•多用表关联实现，大小表关联用lookup，大大表相交用join（每个字段加索引，保证关联查询的效率）–数据拆分•按一定规则进行数据拆分–行列互换–排序/修改序号–去除重复记录–数据验证：lookup，sum，count实现方式–在ETL引擎中进行（SQL无法实现的）–在数据库中进行（SQL可以实现）ETL过程-数据加载实现方式优点缺点时戳方式在业务表中统一添加字段作为时戳，当OLTP系统更新修改业务数据时，同时修改时戳字段值源数据抽取相对简单清楚，速度快，适合数据的增量加载需要修改业务表中的数据结构，业务数据变动时工作量比较大，相对风险较大日志表方式在OLTP系统中添加日志表，业务数据发生变化时，更新维护日志表内容不需要修改业务表中的数据结构。源数据抽取简单清楚，速度快，适合数据的增量加载业务系统中更新记录日志操作麻烦全表对比方式抽取所有源数据，在更新目标表之前先根据主键和字段进行数据比对，有更新的进行update或insert对系统表结构没有任何影响，管理维护统一，可以实现数据的增量加载数据比对复杂，设计比较复杂，执行速度慢全表删除插入方式删除目标表数据，将源数据全部插入ETL规则简单，速度快对维表加代理健不适应，OLTP系统有删除数据时，不能在数据仓库体现被删数据，不能实现增量加载◇ETL定义◇模式及比较◇ETL过程◇问题分析◇现状分析目录ETL执行时的异常处理数据异常–将错误信息单独输出，继续执行ETL，错误数据修改后再单独加载–中断ETL，修改后重新执行ETL原则：最大限度接收数据环境异常–对于网络中断等外部原因造成的异常，设定尝试次数或尝试时间，超数或超时后，由外部人员手工干预其他异常–例如源数据结构改变、接口改变等异常状况，应进行同步后，再装载数据ETL设计规范DI开发规范–ETL开发首要确定的是流程的执行顺序及条件；其次是具体表映射关系的定义，在数据库性能允许的情况下，应该尽可能使用sql语句进行处理–对于具体映射和流程的命名，应该以维护方便为前提：•映射：以目标表名命名•流程：以流程要实现的功能命名–不允许使用临时的SQL语句操纵数据库，必须编写好的SQL脚本或存储过程–限定手工干预只能运行某个流程，不允许运行单个过程–每一项手工操作必须留下记录设计规范–SQL语句应书写规范，关键字全部大写，同时应增加注释•例如：/*表名：BSL_EMP_STATUS作者：CASEY日期：2007-12-17描