第三讲 数据抽取、转换和装载

源系统的结构随着时间会发生变化，因为新的商业条件不断出现。ETL 功能也必须相应的调整
大多数源系统的数据格式和类型对用户没有实际的含义，而且很多展 现方式模糊而晦涩的。
2.耗时而且费劲
50％～70％的时间花费在ETL上
what? where? when? ►转换：How？定义结构、缺失值补充、格式转换 ►装载：Long Time（测试）
►
数据抽取：1.数据源确认
关键因素 :是否可以提供数据仓库需要的值？ 大量的时间和复杂彻底的分析 例子：利用订单数据提供战略信息
►订单：订单数量、折扣、佣金、希望运输时间、实际
运输时间、不同处理阶段时间等 ►维度表：产品、订单部署、运输渠道、客户
数据源 数据源确认过程
订单处理
1. 列出对事实表进行分析所需 要的每一个数据项或事实
确认默认值 检查缺失值的源数据
时间数据
订单量度
数据抽取:2.数据抽取技术
► ►
源系统中的数据是依赖时间变化的 在数据仓库中不能忽略历史信息 如何从源系统中捕获历史数据？—— 数据如何存储
在源系统中的。
►
源系统中的数据如何保存？ 当前值
►
存储的值是代表当前属性的值，可能会改变，但何时改变则无法预 知。为保存历史变化的DW在这类系统中抽取数据很棘手。

ETL概览 数据抽取 数据转换 数据装载 ETL总结
二、数据抽取
►
两个因素增加了抽取工作的复杂度（相比操作型系统的ET）
1. 更多不同的系统中抽取 2. 增量装载 数据抽取的一些要点：
数据源确认：确认数据的源系统和结构
抽取方法：针对每个数据源，定义抽取过程是人工抽取还是基于工 具的抽取（工具自己编写的还是购买的） 抽取频率：对于每个数据源，确定数据抽取的频率，每天、每星期、 每季度等等 时间窗口：对于每个数据源，表示出抽取过程进行的时间窗口 工作顺序：决定抽取任务中某项工作是否必须等到前面工作成功完 成，才能开始 异常处理：决定如何处理无法完成抽取的输入记录
系统中有时间戳 ►记录删除了如何抽取？删除先做标记（逻辑删 除），待抽取后物理删除。但增加了开销。
通过文件的比较来捕获
►保存副本，然后比较昨天的副本和今天的副本以
决定抽取那些数据。 ►缺点：如果数据文件很大，则比较费时间 ►优点：对于没有交易日志或者时间标记的而言， 唯一可行的方法
数据抽取
► 技术的评估
目标
产品数据
2.
客户 3.
从所有维度中列出每一个维 度属性
对于每个目标数据项，找出 源系统和源数据项
客户
运输渠道数据
产品
4.
5.
如果一个数据元素有多个来 源，选择最好的来源
确认一个目标字段的多个源 字段，建立合并规则 部署数据
运输合同
6.
出货跟踪 7. 8. 存货管理
确认多个目标字段的一个源 字段，建立分离规则
基本任务 主要转换类型 数据整合和合并 维度属性的转换 如何实施转换
数据转换
1。基本任务
选择：从源系统得到的整个记录或部分记录。通 常构成抽取功能本身的一部分。 分离或合并：包括数据处理类型。 转化：多种对单独字段的基本转化：标准化和可 理解化 汇总：最细事务粒度上的前期汇总 丰富：从多个源字段构成一个目标字段时，创建 一个更好的数据视图。
ETL总结
►
►
数据仓库的ETL包括了很广泛的概念
而且是一个不断重复的周期性的过程
数据仓库的ETL需要工具的支持
商用的ETL工具支持ETL的各个过程；大多数ETL工具采用 常见的语言编写，因而我们容易对此进行必要的二次开发 可选择的ETL工具非常广泛 商用ETL工具的最大好处就是可以产生维护所产生的驱动 DW建设的元数据
► Contents

ETL概览 数据抽取 数据转换 数据装载 ETL总结
ETL概览
► Contents

最重要和最具有挑战性 耗时而且费劲 ETL的需求和步骤 关键因素
1.最重要和最具有挑战性
►
ETL功能具有挑战性，是因为源系统的性质
源系统彼此悬殊，种类多样，通常需要应付多个平台上的不同操作系 统 很多源数据都是陈旧的应用系统采用的是过时的技术 旧系统中的数据质量各不相同，需要花很多时间进行处理 历史数据通常不会被保存在操作型系统中，但对于DW至关重要。 源系统之间普遍缺乏一致性。在不同的源系统中，相同的数据可能会 用不同的形式来代表，且缺乏解决方法，导致不一致问题更加严重
►多数据源
多个数据源可以得到同一个数据字段，但值不一样？如何取舍？
数据转换
4.维度属性的转换
三种慢速变化的维度属性的修改方法
►第一类 ►第二类 ►第三类
数据转换
5.如何实施转换
数据转换的复杂性决定了手工操作的困难性。
► 使用转换工具 昂贵 高效率 高准确性 元数据的产生 即使是最先进的工具也可能需要手工的配合 ► 使用手工技术 编写程序 适用于小的DW 增加了后期的维护成本 不能产生元数据，即使是产生元数据也因为后期的维护产生 额外的工作维护负担。
► Contents

ETL概览 数据抽取 数据转换 数据装载 ETL总结
数据装载
► 数据装载易出现问题且是很困难
需要大量的时间，而且时间不好估计 装载的过程可能是不顺利的：计划制定（维 度表与事实表的不匹配） 数据准备区和数据仓库数据库分别处在何处？ 装载牵涉到维度表、事实表 装载需要专门的程序
数据转换
2。主要转化类型
1. 格式修正：数据类型与字段长度 2. 字段的解码：使得晦涩的值变得用户易于理解和有意义 3. 计算值和导出值
4. 单个字段的分离：姓和名；邮编和地址
5. 信息合并：从不同源系统中得到某个新的实体的过程。 6. 特征集合转化：编码的转化：ASCII码、BCD码、Unicode、 Big5、GB2312等等 7. 度量单位的转化： 8. 日期、时间格式的转化：
4.关键因素
► 数据抽取、转换复杂性源于源系统巨大的
差异性
OS DBMS Protocol ►遗留的旧系统
►硬件平台
► 与数据装载相关
最初装载和定期装载本身都需要大量时间 （必须找和合适的时间） 增量转载中的特殊问题
►如何捕获源系统的变化，且不影响源系统工作
►不影响DW用户使用情况下装载
► Contents
2.修正数据
ቤተ መጻሕፍቲ ባይዱ 2.修正数据：追加的数据捕获
►是最后一次捕获数据后的修正 ►追加的数据捕获可能是立刻进行的或者延缓进行的
立即型数据捕获: 数据抽取发生在源系统中发生交易的时候， 数据抽取是即时的或者实时的。 ► 三种不同的方法 延缓型数据捕获：非即时的或实时的数据抽取 ► 两种不同的方法
源数据库 源数据 源操作型系统 触发程序 数据库管理系统 选择1： 通过交易 日志捕获 选择2： 从源系统中 抽取文件 选择3： 输出触发 程序文件 交易日志 文件
从数据库触发器中捕获数据
►
►
缺点1：只能捕获基于DB的数据
缺点2：建立和维护触发器以及触发器的执行增加了开销 优点：适用于所有的系统（基于DB的或者文件系统的） 缺点： 1 程序的开销 2可能会降低应用程序的性能
从源应用程序中捕获数据
► ►
数据抽取
► 延缓型
基于日期和时间标记的捕获
►通过日期比较来选择应该抽取的数据：前提是源
►
结论：ETL虽然耗时、复杂费劲，但我们不得不面对此痛 苦，因为ETL对于DW的建设是至关重要的，决策的信息 依赖与ETL的数据。
小结
► 数据仓库中的ETL功能是最重要。最具挑战性
和耗时、费劲的。
源系统繁多、数据量大、抽取转换装载难
► 数据抽取技术
静态 修正:立即型、延缓型
► 数据转换包括了数据转化、清洗、拆分、合并
► 数据装载包括了初始、定期增量、刷新装载 ► 完成ETL功能的工具很多，工具需要手工编程
的补充完善。
ETL过程涉及的各个方面
1.
2. 3. 4. 5. 6. 7. 8.
将几个数据源结构组合成数据仓库目标数据库中的行 将一个源数据结构分成若干个结构放入目标数据库中的若干 行 从源系统数据字典和目录中读取数据 从多种文件结构中读取数据，包括平面文件、索引文件、旧 系统数据库 装载大量原子事实表的细节 为大量聚集表或事实表做聚集 将数据从源系统平台上的一种格式转换成目标平台上的另一 个格式 将晦涩的数值改变成对用户有意义的值
►抽取：
抽取和应用变化着的数据有困难
3.ETL的需求和步骤
事实表的ETL
维度表的ETL
为所有的数据装载编写规程
组织数据缓存区域和检测工具 为聚集表制定计划
决定数据转换和清洗规则 建立全面的数据抽取规则 准备从源到目标数据元素的的数据映像关系 决定所有的数据源，包括内部和外部 决定数据仓库中需要的所有的目标数据 ETL处理过程的主要步骤
周期性的状态
例如随时间变化的政策（利率等） ► 变化的数据存储在源系统中,为DW而进行的抽取相对容易
►
例子
数据抽取
► 从源操作系统中捕获数据
1.静态数据
►一般用于在数据仓库初始装载的时候进行 ►是相关数据源在某个时刻的快照 ►静态数据的抽取包括
当前值的抽取 周期性数据的抽取：每个时间点的值的获取
静态数据捕获：初次数据装载时使用 修正数据捕获
通过交易日志捕获：DB ►通过数据库触发器捕获：DB、书写触发器 ►在源系统中捕获：程序书写开销 ►基于日期和时间标记的捕获：有时间戳、删除的问题
（逻辑删除、物理删除） 通过文件的比较来捕获：比较会很费劲、但实用
► 主要考察：

灵活性？ 对源系统性能的影响 对已有应用程序的影响 与旧系统的兼容性 能用在文件系统中吗？ 需要内部开发还是可用第三方软件
► Contents

ETL概览 数据抽取 数据转换 数据装载 ETL总结
数据转换
► 根据标准对数据进行转换，因为他们来自不
同的系统，必须保证所有数据放在一起后， 数据的组合不能违反任何商业规则。 ► Contents
在数据库 触发器中 捕获
在源应用程序 中捕获
立即型数据抽取：可选方法
数据抽取
►
立即型
通过交易日志捕获数据：日志本来就是DBMS所应该存储的为应付突发情 况的备份。
► ► ►
没有额外开销。需要保证日志刷新之前，已抽取了所有记录。 缺点：如果源数据不是基于DB的则无法进行此方式的数据捕获 使用数据库复制技术
9. 汇总：
10.键重构：
数据转换
3.数据整合
ETL功能的真正挑战：从多个不同的分离的源系统中将 所有数据有机的组织在一起供决策分析使用。 数据整合就是将所有相关的数据组合成一致的数据结构， 准备装载到DW中。 ►实体识别的困难
订单系统、客服系统、市场系统 客户信息 如何匹配这三个不同系统中同一个客户的问题：算法或者手工识别
第三讲
数据抽取、转换和装载
数据抽取、转换和装载
► 目标：
广泛了解ETL的功能 检查数据抽取功能（挑战、技术、评估和应用） 数据转换功能的任务和类型 理解数据整合和合并的意义 认识数据装载功能的重要性，了解将数据应用到 数据仓库的主要方法 理解为何说ETL非常重要、耗时和艰巨的任务
数据装载
► 几个名词
初始装载：第一次对所有数据仓库的表进行迁移 增量装载：根据变化需要定期对DW中的表进行 更新 完全刷新：完全删除DW中的一个表或多个表， 然后重新装载新的数据
Chapter 12 数据抽取、转换和装载
► Contents

ETL概览 数据抽取 数据转换 数据装载 ETL总结