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

第三讲数据抽取、转换和装载数据抽取、转换和装载►目标：广泛了解ETL的功能检查数据抽取功能（挑战、技术、评估和应用）数据转换功能的任务和类型理解数据整合和合并的意义认识数据装载功能的重要性，了解将数据应用到数据仓库的主要方法理解为何说ETL非常重要、耗时和艰巨的任务►ContentsETL概览数据抽取数据转换数据装载ETL总结ETL概览►Contents最重要和最具有挑战性耗时而且费劲ETL的需求和步骤关键因素1.最重要和最具有挑战性►ETL功能具有挑战性，是因为源系统的性质源系统彼此悬殊，种类多样，通常需要应付多个平台上的不同操作系统很多源数据都是陈旧的应用系统采用的是过时的技术旧系统中的数据质量各不相同，需要花很多时间进行处理历史数据通常不会被保存在操作型系统中，但对于DW至关重要。源系统之间普遍缺乏一致性。在不同的源系统中，相同的数据可能会用不同的形式来代表，且缺乏解决方法，导致不一致问题更加严重源系统的结构随着时间会发生变化，因为新的商业条件不断出现。ETL功能也必须相应的调整大多数源系统的数据格式和类型对用户没有实际的含义，而且很多展现方式模糊而晦涩的。2.耗时而且费劲50％～70％的时间花费在ETL上►抽取：what?where?when?►转换：How？定义结构、缺失值补充、格式转换►装载：LongTime（测试）抽取和应用变化着的数据有困难事实表的ETL维度表的ETL为所有的数据装载编写规程组织数据缓存区域和检测工具为聚集表制定计划决定数据转换和清洗规则建立全面的数据抽取规则准备从源到目标数据元素的的数据映像关系决定所有的数据源，包括内部和外部决定数据仓库中需要的所有的目标数据ETL处理过程的主要步骤3.ETL的需求和步骤ETL过程涉及的各个方面1.将几个数据源结构组合成数据仓库目标数据库中的行2.将一个源数据结构分成若干个结构放入目标数据库中的若干行3.从源系统数据字典和目录中读取数据4.从多种文件结构中读取数据，包括平面文件、索引文件、旧系统数据库5.装载大量原子事实表的细节6.为大量聚集表或事实表做聚集7.将数据从源系统平台上的一种格式转换成目标平台上的另一个格式8.将晦涩的数值改变成对用户有意义的值4.关键因素►数据抽取、转换复杂性源于源系统巨大的差异性►硬件平台OSDBMSProtocol►遗留的旧系统►与数据装载相关最初装载和定期装载本身都需要大量时间（必须找和合适的时间）增量转载中的特殊问题►如何捕获源系统的变化，且不影响源系统工作►不影响DW用户使用情况下装载►ContentsETL概览数据抽取数据转换数据装载ETL总结二、数据抽取►两个因素增加了抽取工作的复杂度（相比操作型系统的ET）1.更多不同的系统中抽取2.增量装载►数据抽取的一些要点：数据源确认：确认数据的源系统和结构抽取方法：针对每个数据源，定义抽取过程是人工抽取还是基于工具的抽取（工具自己编写的还是购买的）抽取频率：对于每个数据源，确定数据抽取的频率，每天、每星期、每季度等等时间窗口：对于每个数据源，表示出抽取过程进行的时间窗口工作顺序：决定抽取任务中某项工作是否必须等到前面工作成功完成，才能开始异常处理：决定如何处理无法完成抽取的输入记录数据抽取：1.数据源确认关键因素:是否可以提供数据仓库需要的值？大量的时间和复杂彻底的分析例子：利用订单数据提供战略信息►订单：订单数量、折扣、佣金、希望运输时间、实际运输时间、不同处理阶段时间等►维度表：产品、订单部署、运输渠道、客户数据源确认过程数据源目标订单处理客户产品运输合同出货跟踪存货管理产品数据客户运输渠道数据部署数据时间数据订单量度1.列出对事实表进行分析所需要的每一个数据项或事实2.从所有维度中列出每一个维度属性3.对于每个目标数据项，找出源系统和源数据项4.如果一个数据元素有多个来源，选择最好的来源5.确认一个目标字段的多个源字段，建立合并规则6.确认多个目标字段的一个源字段，建立分离规则7.确认默认值8.检查缺失值的源数据数据抽取:2.数据抽取技术►源系统中的数据是依赖时间变化的►在数据仓库中不能忽略历史信息如何从源系统中捕获历史数据？——数据如何存储在源系统中的。►源系统中的数据如何保存？当前值►存储的值是代表当前属性的值，可能会改变，但何时改变则无法预知。为保存历史变化的DW在这类系统中抽取数据很棘手。周期性的状态►例如随时间变化的政策（利率等）►变化的数据存储在源系统中,为DW而进行的抽取相对容易例子数据抽取►从源操作系统中捕获数据1.静态数据►一般用于在数据仓库初始装载的时候进行►是相关数据源在某个时刻的快照►静态数据的抽取包括当前值的抽取周期性数据的抽取：每个时间点的值的获取2.修正数据2.修正数据：追加的数据捕获►是最后一次捕获数据后的修正►追加的数据捕获可能是立刻进行的或者延缓进行的立即型数据捕获:数据抽取发生在源系统中发生交易的时候，数据抽取是即时的或者实时的。►三种不同的方法延缓型数据捕获：非即时的或实时的数据抽取►两种不同的方法源操作型系统数据库管理系统源数据库源数据选择1：通过交易日志捕获交易日志文件从源系统中抽取文件输出触发程序文件选择3：在源应用程序中捕获选择2：在数据库触发器中捕获立即型数据抽取：可选方法触发程序数据抽取►立即型通过交易日志捕获数据：日志本来就是DBMS所应该存储的为应付突发情况的备份。►没有额外开销。需要保证日志刷新之前，已抽取了所有记录。►缺点：如果源数据不是基于DB的则无法进行此方式的数据捕获►使用数据库复制技术从数据库触发器中捕获数据►缺点1：只能捕获基于DB的数据►缺点2：建立和维护触发器以及触发器的执行增加了开销从源应用程序中捕获数据►优点：适用于所有的系统（基于DB的或者文件系统的）►缺点：1程序的开销2可能会降低应用程序的性能数据抽取►延缓型基于日期和时间标记的捕获►通过日期比较来选择应该抽取的数据：前提是源系统中有时间戳►记录删除了如何抽取？删除先做标记（逻辑删除），待抽取后物理删除。但增加了开销。通过文件的比较来捕获►保存副本，然后比较昨天的副本和今天的副本以决定抽取那些数据。►缺点：如果数据文件很大，则比较费时间►优点：对于没有交易日志或者时间标记的而言，唯一可行的方法数据抽取►技术的评估静态数据捕获：初次数据装载时使用修正数据捕获通过交易日志捕获：DB►通过数据库触发器捕获：DB、书写触发器►在源系统中捕获：程序书写开销►基于日期和时间标记的捕获：有时间戳、删除的问题（逻辑删除、物理删除）通过文件的比较来捕获：比较会很费劲、但实用►主要考察：灵活性？对源系统性能的影响对已有应用程序的影响与旧系统的兼容性能用在文件系统中吗？需要内部开发还是可用第三方软件►ContentsETL概览数据抽取数据转换数据装载ETL总结数据转换►根据标准对数据进行转换，因为他们来自不同的系统，必须保证所有数据放在一起后，数据的组合不能违反任何商业规则。►Contents基本任务主要转换类型数据整合和合并维度属性的转换如何实施转换数据转换1。基本任务选择：从源系统得到的整个记录或部分记录。通常构成抽取功能本身的一部分。分离或合并：包括数据处理类型。转化：多种对单独字段的基本转化：标准化和可理解化汇总：最细事务粒度上的前期汇总丰富：从多个源字段构成一个目标字段时，创建一个更好的数据视图。数据转换2。主要转化类型1.格式修正：数据类型与字段长度2.字段的解码：使得晦涩的值变得用户易于理解和有意义3.计算值和导出值4.单个字段的分离：姓和名；邮编和地址5.信息合并：从不同源系统中得到某个新的实体的过程。6.特征集合转化：编码的转化：ASCII码、BCD码、Unicode、Big5、GB2312等等7.度量单位的转化：8.日期、时间格式的转化：9.汇总：10.键重构：数据转换3.数据整合ETL功能的真正挑战：从多个不同的分离的源系统中将所有数据有机的组织在一起供决策分析使用。数据整合就是将所有相关的数据组合成一致的数据结构，准备装载到DW中。►实体识别的困难订单系统、客服系统、市场系统客户信息如何匹配这三个不同系统中同一个客户的问题：算法或者手工识别►多数据源多个数据源可以得到同一个数据字段，但值不一样？如何取舍？数据转换4.维度属性的转换三种慢速变化的维度属性的修改方法►第一类►第二类►第三类数据转换5.如何实施转换数据转换的复杂性决定了手工操作的困难性。►使用转换工具昂贵高效率高准确性元数据的产生即使是最先进的工具也可能需要手工的配合►使用手工技术编写程序适用于小的DW增加了后期的维护成本不能产生元数据，即使是产生元数据也因为后期的维护产生额外的工作维护负担。►ContentsETL概览数据抽取数据转换数据装载ETL总结数据装载►数据装载易出现问题且是很困难需要大量的时间，而且时间不好估计装载的过程可能是不顺利的：计划制定（维度表与事实表的不匹配）数据准备区和数据仓库数据库分别处在何处？装载牵涉到维度表、事实表装载需要专门的程序数据装载►几个名词初始装载：第一次对所有数据仓库的表进行迁移增量装载：根据变化需要定期对DW中的表进行更新完全刷新：完全删除DW中的一个表或多个表，然后重新装载新的数据Chapter12数据抽取、转换和装载►ContentsETL概览数据抽取数据转换数据装载ETL总结ETL总结►数据仓库的ETL包括了很广泛的概念而且是一个不断重复的周期性的过程►数据仓库的ETL需要工具的支持商用的ETL工具支持ETL的各个过程；大多数ETL工具采用常见的语言编写，因而我们容易对此进行必要的二次开发可选择的ETL工具非常广泛商用ETL工具的最大好处就是可以产生维护所产生的驱动DW建设的元数据►结论：ETL虽然耗时、复杂费劲，但我们不得不面对此痛苦，因为ETL对于DW的建设是至关重要的，决策的信息依赖与ETL的数据。小结►数据仓库中的ETL功能是最重要。最具挑战性和耗时、费劲的。源系统繁多、数据量大、抽取转换装载难►数据抽取技术静态修正:立即型、延缓型►数据转换包括了数据转化、清洗、拆分、合并►数据装载包括了初始、定期增量、刷新装载►完成ETL功能的工具很多，工具需要手工编程的补充完善。