ETL数据抽取专项方案简介.docx

目录ETL介绍4ETL抽取方案41.数据抽取42.数据转换和加工73.数据装载74.时间戳方法75.全量删除插入方法86.全量比对方法8ETL介绍数据集成是把不一样起源、格式和特点数据在逻辑上或物理上有机地集中，从而提供全方面数据共享，是企业商务智能、数据仓库系统关键组成部分。ETL 是企业数据集成关键处理方案。ETL 中三个字母分别代表是Extract、Transform、Load，即抽取、转换、加载。（1）数据抽取：从源数据源系统抽取目标数据源系统需要数据；（2）数据转换：将从源数据源获取数据根据业务需求，转换成目标数据源要求形式，并对错误、不一致数据进行清洗和加工；（3）数据加载：将转换后数据装载到目标数据源。ETL 作为构建数据仓库一个步骤，负责将分布、异构数据源中数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最终加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘基础。现在越来越多地将ETL 应用于通常信息系统数据迁移、交换和同时。一个简单ETL步骤图1所表示。数据源文件其它 抽取临时数据 转换 加载目标数据库 图1ETL抽取方案ETL 过程中关键步骤就是数据抽取、数据转换和加工、数据装载。为了实现这些功效，ETL 工具会进行部分功效上扩充，比如工作流、调度引擎、规则引擎、脚本支持、统计信息等。1. 数据抽取数据抽取是从数据源中抽取数据过程。实际应用中，数据源较多采取是关系数据库。从数据库中抽取数据通常有以下多个方法：1) 全量抽取全量抽取类似于数据迁移或数据复制，它将数据源中表或视图数据原封不动从数据库中抽取出来，并转换成自己ETL 工具能够识别格式。全量抽取比较简单。2) 增量抽取增量抽取只抽取自上次抽取以来数据库中要抽取表中新增或修改数据。在ETL 使用过程中，增量抽取较全量抽取应用更广。怎样捕捉改变数据是增量抽取关键。对捕捉方法通常有两点要求：正确性，能够将业务系统中改变数据按一定频率正确地捕捉到；性能，不能对业务系统造成太大压力，影响现有业务。现在增量数据抽取中常见捕捉改变数据方法有：a) 触发器方法（又称快照式）在要抽取表上建立需要触发器，通常要建立插入、修改、删除三个触发器，每当源表中数据发生改变，就被对应触发器将改变数据写入一个临时表，抽取线程从临时表中抽取数据，临时表中抽取过数据被标识或删除。优点：数据抽取性能高，ETL 加载规则简单，速度快，不需要修改业务系统表结构，能够实现数据递增加载。缺点：要求业务表建立触发器，对业务系统有一定影响，轻易对源数据库组成威胁。b) 时间戳方法它是一个基于快照比较改变数据捕捉方法，在源表上增加一个时间戳字段，系统中更新修改表数据时候，同时修改时间戳字段值。当进行数据抽取时，经过比较上次抽取时间和时间戳字段值来决定抽取哪些数据。有数据库时间戳支持自动更新，即表其它字段数据发生改变时，自动更新时间戳字段值。有数据库不支持时间戳自动更新，这就要求业务系统在更新业务数据时，手工更新时间戳字段。优点：同触发器方法一样，时间戳方法性能也比很好，ETL 系统设计清楚，源数据抽取相对清楚简单，能够实现数据递增加载。缺点：时间戳维护需要由业务系统完成，对业务系统也有很大倾入性（加入额外时间戳字段），尤其是对不支持时间戳自动更新数据库，还要求业务系统进行额外更新时间戳操作；另外，无法捕捉对时间戳以前数据delete和update 操作，在数据正确性上受到了一定限制。c) 全表删除插入方法每次ETL 操作均删除目标表数据，由ETL 全新加载数据。优点：ETL 加载规则简单，速度快。缺点：对于维表加外键不适应，当业务系统产生删除数据操作时，综合数据库将不会统计到所删除历史数据，不能够实现数据递增加载；同时对于目标表所建立关联关系，需要重新进行创建。d) 全表比对方法全表比正确方法是ETL 工具事先为要抽取表建立一个结构类似临时表，该临时表统计源表主键和依据全部字段数据计算出来，每次进行数据抽取时，对源表和临时表进行比对，如有不一样，进行Update 操作，如目标表没有存在该主键值，表示该统计还没有，即进行Insert 操作。优点：对已经有系统表结构不产生影响，不需要修改业务操作程序，全部抽取规则由ETL完成，管理维护统一，能够实现数据递增加载，没有风险。缺点：ETL 比对较复杂，设计较为复杂，速度较慢。和触发器和时间戳方法中主动通知不一样，全表比对方法是被动进行全表数据比对，性能较差。当表中没有主键或唯一列且含有反复统计时，全表比对方法正确性较差。e) 日志表方法在业务系统中添加系统日志表，当业务数据发生改变时，更新维护日志表内容，看成ETL 加载时，经过读日志表数据决定加载那些数据及怎样加载。优点：不需要修改业务系统表结构，源数据抽取清楚，速度较快。能够实现数据递增加载。缺点：日志表维护需要由业务系统完成，需要对业务系统业务操作程序作修改，统计日志信息。日志表维护较为麻烦，对原有系统有较大影响。工作量较大，改动较大，有一定风险。f) Oracle 改变数据捕捉（CDC 方法）经过分析数据库本身日志来判定改变数据。Oracle 改变数据捕捉（CDC，Changed Data Capture）技术是这方面代表。CDC 特征是在Oracle9i 数据库中引入。CDC 能够帮助你识别从上次抽取以后发生改变数据。利用CDC，在对源表进行insert、update 或 delete 等操作同时就能够提取数据，而且改变数据被保留在数据库改变表中。这么就能够捕捉发生改变数据，然后利用数据库视图以一个可控方法提供给目标系统。CDC 体系结构基于公布/订阅模型。公布者捕捉改变数据并提供给订阅者。订阅者使用从公布者那里取得改变数据。通常，CDC 系统拥有一个公布者和多个订阅者。公布者首先需要识别捕捉改变数据所需源表。然后，它捕捉改变数据并将其保留在尤其创建改变表中。它还使订阅者能够控制对改变数据访问。订阅者需要清楚自己感爱好是哪些改变数据。一个订阅者可能不会对公布者公布全部数据全部感爱好。订阅者需要创建一个订阅者视图来访问经公布者授权能够访问改变数据。CDC 分为同时模式和异步模式，同时模式实时捕捉改变数据并存放到改变表中，公布者和订阅全部在同一数据库中；异步模式则是基于Oracle 流复制技术。优点：提供了易于使用API 来设置CDC 环境，缩短ETL 时间。不需要修改业务系统表结构，能够实现数据递增加载。缺点：业务系统数据库版本和产品不统一，难以统一实现，实现过程相对复杂，而且需深入研究方能实现。或经过第三方工具实现，价格昂贵。2. 数据转换和加工从数据源中抽取数据不一定完全满足目标库要求，比如数据格式不一致、数据输入错误、数据不完整等等，所以有必需对抽取出数据进行数据转换和加工。数据转换和加工能够在 ETL 引擎中进行，也能够在数据抽取过程中利用关系数据库特征同时进行。a) ETL引擎中数据转换和加工ETL 引擎中通常以组件化方法实现数据转换。常见数据转换组件有字段映射、数据过滤、数据清洗、数据替换、数据计算、数据验证、数据加解密、数据合并、数据拆分等。这些组件如同一条流水线上一道道工序，它们是可插拔，且能够任意组装，各组件之间经过数据总线共享数据。同时ETL 工具还提供了脚本支持，使得用户能够以一个编程方法定制数据转换和加工行为。相比在数据库中加工，性能较高，但不轻易进行修改和清楚识别。b) 在数据库中进行数据加工关系数据库本身已经提供了强大SQL、函数来支持数据加工，如在SQL 查询语句中添加where 条件进行过滤，查询中重命名字段名和目标表进行映射，substr 函数，case条件判定等等。相比在 ETL 引擎中进行数据转换和加工，直接在SQL 语句中进行转换和加工愈加简单清楚；但依靠SQL语句，有些数据加工经过SQL语句可能无法实现，对于SQL 语句无法处理能够交由ETL 引擎处理。3. 数据装载将转换和加工后数据装载到目标库中通常是ETL 过程最终步骤。装载数据最好方法取决于所实施操作类型和需要装入多少数据。当目标库是关系数据库时，通常来说有两种装载方法：（1） 直接 SQL 语句进行insert、update、delete 操作。（2） 采取批量装载方法，sqlldr等。大多数情况下使用第一个方法，因为它们进行了日志统计而且是可恢复。不过，批量装载操作易于使用，而且在装入大量数据时效率较高。使用哪种数据装载方法取决于业务系统需要。