1、第三章基本技术方法3.1ETL入门3.2技术路线3.3ETL工具3.4ETL子系统习题3.1ETL入门第三章 基本技术方法1 ETL，全称为Extraction-Transformation-Loading，中文名为数据抽取、转换和加载。2 ETL的主要功能是将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。3 ETL也是一个长期的过程，只有不断地发现问题并解决问题，才能使ETL运行效率更高，为项目后期开发提供准确的数据。企业每年产生海量的数据，这些数据，有的对企业非常重要，有的对企业
2、没有用处。如何在海量数据中抽取出有用的数据？可以通过ETL来进行实现。3.1.1 ETL解决方案“一切围绕需求”最终用户提交界面数据集成业务需求数据评估大ETL系统的最终步骤是将数据提交给最终用户，提交过程占据十分重要的位置，并对构成最终用户应用的数据结构和内容进行严格把关，确保其简单快捷。在数据进入数据仓库之前，需要将全部数据无缝集成到一起。数据集成可采用规模化的表格来实现，也就是在分离的数据库中建立公共维度实体，从而快速构建报表。业务需求是数据仓库最终用户的信息需求，它直接决定了数据源的选择。在ETL设计时，需要考虑原始数据是否能解决用户的业务需求，同时，业务需求和数据源的内容是不断变化的
3、，需要对ETL不断进行检验和讨论。数据评估是使用分析方法来检查数据，充分了解数据的内容、质量。设计好的数据评估方法能够处理海量数据。3.1ETL入门第三章 基本技术方法“一切围绕需求”3.1ETL入门1.业务需求 业务需求是数据仓库最终用户的信息需求，它直接决定了数据源的选择。在许多情况下，最初对于数据源的调查不一定完全反映数据的复杂性和局限性，所以在ETL设计时，需要考虑原始数据是否能解决用户的业务需求，同时，业务需求和数据源的内容是不断变化的，需要对ETL不断进行检验和讨论。对数据仓库典型的需求包括：数据源的归档备份以及随后的数据存储。任何造成数据修改的交易记录的完整性证明。对分配和调整的
4、规则进行完备的文档记录。数据备份的安全性证明，不论是在线还是离线进行。第三章 基本技术方法“一切围绕需求”3.1ETL入门2数据评估 数据评估是使用分析方法来检查数据，充分了解数据的内容、质量。设计好的数据评估方法能够处理海量数据。例如，企业的订单系统，能够很好地满足生产部门的需求。但是对于数据仓库来说，因为数据仓库使用的字段并不是以订单系统中的字段为中心，因此订单系统中的信息对于数据仓库的分析来讲是远远不够的。对于“脏”数据源需要进行操作处理，主要包括以下几个方面：完全清除某些输入字段；补入一些丢失的数据；自动替换掉某些错误数据值；在记录级别上进行人工干预；对数据进行完全规范化的表述。第三章
5、 基本技术方法“一切围绕需求”3.1ETL入门3数据集成 在数据进入数据仓库之前，需要将全部数据无缝集成到一起。数据集成可采用规模化的表格来实现，也就是在分离的数据库中建立公共维度实体，从而快速构建报表。在ELT系统中，数据集成是数据流程中一个独立的步骤，叫作规格化步骤。4.最终用户提交界面 ETL系统的最终步骤是将数据提交给最终用户，提交过程占据十分重要的位置，并对构成最终用户应用的数据结构和内容进行严格把关，确保其简单快捷。将使用复杂、查询缓慢的数据直接交给最终用户是不负责的，经常犯的一个错误就是将完全规范化的数据模型直接交给用户，就不再过问。第三章 基本技术方法ETL基本构成数据抽取数据
6、转换数据加载3.1ETL入门3.1.2 ETL基本构成第三章 基本技术方法“一切围绕需求”3.1ETL入门1数据抽取 所谓数据抽取，就是从源端数据系统中抽取目标数据系统需要的数据。进行数据抽取的原则：一是要求准确性，即能够将数据源中的数据准确抽取到；二是不对源端数据系统的性能、响应时间等造成影响。数据抽取可分为全量抽取和增量抽取两种方式。（1）全量抽取 全量抽取好比数据的迁移和复制，它是将源端数据表中的数据一次性全部从数据库中抽取出来，再进行下一步操作。（2）增量抽取增量抽取主要是在第一次全量抽取完毕后，需要对源端数据中新增或修改的数据进行抽取。增量抽取的关键是抽取自上次以来，数据表中已经变化
7、的数据。第三章 基本技术方法“一切围绕需求”3.1ETL入门1数据抽取（2）增量抽取 例如，在新生入学时，所有学生的信息采集整理属于全量抽取；在后期，如果有个别学生或部分学生需要休学，对这部分学生的操作即属于增量抽取。增量抽取一般有4种抽取模式 触发器模式，这是普遍采用一种抽取模式。一般是建立3个触发器，即插入、修改、删除，并且要求用户拥有操作权限。当触发器获得新增数据后，程序会自动从临时表中读取数据。这种模式性能高、规则简单、效率高，且不需要修改业务系统表结构，可实现数据的递增加载。第三章 基本技术方法“一切围绕需求”of403.1ETL入门1数据抽取（2）增量抽取 时间戳方式，即在源数据表
8、中增加一个时间戳字段。当系统修改源端数据表中的数据时，同时修改时间戳的值。在进行数据抽取时，通过比较系统时间和时间戳的值来决定需要抽取哪些数据。全表对比方式，即每次从源端数据表中读取所有数据，然后逐条比较数据，将修改过的数据过滤出来。此种方式主要采用MD5校验码。全表对比方式不会对源端表结构产生影响。日志对比方式，即通过分析数据库的日志来抽取相应的数据。这种方式主要是在Oracle 9i数据库中引入的。以上4种方式中，时间戳方式是使用最为广泛的，在银行业务中采用的就是时间戳方式。第三章 基本技术方法“一切围绕需求”3.1ETL入门2数据转换 数据转换就是将从数据源获取的数据按照业务需求，通过转
9、换、清洗、拆分等，加工成目的数据源所需要的格式。数据转换是ETL过程中最关键的步骤，它主要是对数据格式、数据类型等进行转换。它可以在数据抽取过程中进行，也可以通过ETL引擎进行转换。数据转换的原因非常多，主要包括以下3种：数据不完整，指数据库的数据信息缺失。这种转换需要对数据内容进行二次输入，以进行补全。数据格式错误，指数据超出数据范围。可通过定义完整性进行模式约束。数据不一致，即主表与子表的数据不能匹配。可通过业务主管部门确认后，再进行二次抽取。第三章 基本技术方法“一切围绕需求”3.1ETL入门3数据加载 数据加载是ETL的最后一个步骤，即将数据从临时表或文件中，加载到指定的数据仓库中。一
10、般来说，有直接SQL语句操作和利用装载工具进行加载两种方式，最佳装载方式取决于操作类型以及数据的加载量。第三章 基本技术方法“一切围绕需求”3.1ETL入门3.1.3 ETL技术选型 ETL技术的选型，主要从成本、人员、案例和技术支持来衡量。目前流行的3种主要技术为Datastage、Powercenter和ETL Automation。在Datastage和Powercenter中，ETL技术选型可以从对ETL流程的支持，对元数据的支持和对数据质量的支持来考虑，同时从兼顾维护的实用性、定制开发的支持等方面考虑。在ETL中，数据抽取过程多则上百，少则十几个，它们之间的依赖关系、出错控制及恢复的
11、流程都是需要考虑的。第三章 基本技术方法第三章基本技术方法3.1ETL入门3.2技术路线3.3ETL工具3.4ETL子系统习题大数据应用人才培养系列教材数据的来源不同文本清洗RDBMS清洗Web内容清洗3.2技术路线第三章 基本技术方法第三章 基本技术方法3.2 技术路线3.2.1文本清洗路线 对文本进行清洗主要包括电子表格中的数据清洗和文本编辑器的数据清洗。对于电子表格中的数据清洗，主要是利用表格中的行和列，以及电子表格中的内置函数。我们通常把一些数据复制到电子表格中，电子表格根据相应分隔符（制表位或逗号或其他）把数据分成不同的列。有时候会根据系统不同来人为地制定分隔符。对于文本编辑器中的数
12、据清洗，主要是许多操作系统中集成了文本编辑器，如Windows操作系统中的文本编辑器。在进行文本清洗前，需要对数据进行整理，包括对数据中的数据改变大小写、在文本每一行前端增加前缀，主要是为了在转换过程中，有可以参考的分隔符。第三章 基本技术方法3.2 技术路线 3.2.2 RDBMS清洗路线 RDBMS即关系型数据库管理系统，它作为经典的、长期使用的数据存储解决方案，成为数据存储的标准。但由于不同的人在设计数据库时，往往存在设计缺陷，需要对数据库的数据进行清洗。通过清洗可以找到异常数据，通常使用不同的策略来清洗不同类型的数据。对于RDBMS数据的清洗，有两种方式可以选择，即可以先把数据导入数据
13、库，然后在数据库端进行清洗；也可以在电子表格或文本编辑器中进行清洗。具体选择哪种方案，会根据不同的数据进行不同的选择。第三章 基本技术方法3.2 技术路线 3.2.3 Web内容清洗路线 Web内容清洗，主要是清洗来自网络的数据，为其构建合理的清洗方案。Web数据主要来自HTML网页。HTML网页的页面结构决定了采取哪种方式。1HTML页面结构2清洗方式第三章 基本技术方法3.2 技术路线 1HTML页面结构 文本组成。所以从Web中进行数据抽取，可有两种不同的方式，一种是行分隔方式，另一种是树形结构方式。在行分隔方式中，我们把网页的数据看作文本内容，把网页中的标签理解为分隔符，这样在进行数据
14、抽取时就比较容易。2清洗方式 Web内容清洗可以有两种方式，一种是逐行方式，另一种是使用树形结构方式。逐行方式中，采用基于正则表达式的HTML分析技术，它是基于文件中的分隔符，配合正则表达式，获取需要的数据。第三章基本技术方法3.3ETL工具3.2技术路线3.1ETL入门3.4ETL子系统习题大数据应用人才培养系列教材多平台管理和调试功能集成性和开放性3.3ETL工具数据转换多种数据源管理元数据第三章 基本技术方法ETL功能第三章 基本技术方法3.3 ETL工具 3.3.1 ETL功能 评价ETL设计的好坏需要从多个不同的角度来考虑，主要包括对多平台的支持、数据源格式的支持、数据的转换、数据的
15、管理和调试、数据的集成和开放性以及对元数据的管理等方面。1多平台业务数据量的飞速增长，对系统的可靠性提出了更高的要求。对于海量的数据抽取，往往要求在有限的时间内完成。所以，平台对ETL开发工具的支持成为衡量一个开发工具的重要指标。目前主流的平台包括Windows、Linux、IBM AIX、Mac OS等。第三章 基本技术方法3.3 ETL工具 3.3.1 ETL功能 2多种数据源 开发工具对数据源的支持非常重要，不仅要考虑项目开发中各种不同类型的数据源，还要考虑数据源的接口类型。例如，在数据抽取时，使用原厂商自己的专用接口，还是通用接口，效率会大不一样。数据源包括Oracle、SQL Ser
16、ver、DB2、Sybase、Microsoft Excel等。3数据转换 由于在业务系统中的数据，存在数据时间跨度大、数据量多而乱的特点，就会造成在数据业务系统中可能会有多种完全不同的存储格式，也有可能业务系统存储的数据需要进行计算才能够抽取，因此，ETL功能中必须要有对数据进行计算、合并、拆分等转换功能。第三章 基本技术方法3.3 ETL工具 3.3.1 ETL功能 4具备管理和调试功能 由于数据业务量的增大，对数据抽取的要求也越来越高，专业的ETL工具要求具有管理和调度的功能，主要包括抽取过程的备份和恢复、版本升级、版本管理、支持统一的管理平台等功能。5集成性和开放性 随着国内数据仓库技
17、术的不断发展，大多数情况下一般项目只会用到ETL工具的少数几个功能，开发商将ETL工具的主要功能模块集成到自己的系统中，这样可以减少用户的操作错误。这就要求ETL能够具有较好的集成性和开放性。第三章 基本技术方法3.3 ETL工具 3.3.1 ETL功能6管理元数据 元数据是描述数据的数据，它是对业务数据本身及其运行环境的描述与定义，主要用于支持业务系统应用。元数据的主要表现是对对象的描述，即对数据库、表、列、主键等的描述。在当前信息化建设中，一些应用的异构性和分布性越来越普遍，使用统一的元数据成为重要的选择，合理的元数据可以打破以往信息化建设中的“信息孤岛”等问题。第三章 基本技术方法of4
18、0263.3 ETL工具 3.3.2 开源ETL工具1Pentaho Kettle Kettle是一款国外的开源ETL工具，纯Java编写，可以在Windows、Linux、UNIX上运行，无须安装，数据抽取高效稳定。Kettle（中文译名：水壶），该项目的主程序员Matt 希望把各种数据放到一个壶里，然后以一种指定的格式流出。Kettle将ETL流程编译为XML格式，学起来十分简单，Pentaho Data Integration（Kettle）使用Java（Swing）开发。Kettle作为编译器对以XML格式书写的流程进行编译。Kettle的JavaScript引擎（和Java引擎）可以
19、深层地控制对数据的处理。第三章 基本技术方法3.3 ETL工具 3.3.2 开源ETL工具2OpenRefine OpenRefine最初叫作Freebase Gridworks，由一家名为Metaweb的公司开发，主要用于调试各种表格，以避免随着时间的推移出现错误，这对于任何数据库来说都是一个很大的问题。后来，该软件被谷歌收购，更名为Google Refine，并发布了第2版。2012年10月，Google Refine被社区接管，并以OpenRefine为名进行了开源。第三章 基本技术方法3.3 ETL工具 3.3.2 开源ETL工具3DataWrangler DataWrangler（中
20、文译名：牧马人）是一款由斯坦福大学开发的在线数据清洗、数据重组软件，主要用于去除无效数据，将数据整理成用户需要的格式等。使用 DataWrangler能节约用户花在数据整理上的时间，从而使其有更多的精力用于数据分析。（校企）第三章 基本技术方法3.3 ETL工具 3.3.2 开源ETL工具4Hawk Hawk是一种数据抓取和清洗工具，依据GPL协议开源，软件基于C#实现，其前端界面使用WPF开发，支持插件扩展。能够灵活高效地采集网页、数据库、文件等来源的数据，并通过可视化拖曳操作，快速地进行生成、过滤、转换等数据操作，快速建立解决方案。非常适合作为网页爬虫和数据清洗工具。Hawk含义为“鹰”，
21、形容能够高效、准确地抓取和清洗数据。（校企）第三章基本技术方法3.4ETL子系统3.2技术路线3.3ETL工具3.1ETL入门习题大数据应用人才培养系列教材第三章 基本技术方法3.4 ETL子系统3.4.1 抽取 抽取类子系统中，主要包括数据分析系统、增量捕获系统和数据抽取系统。数据分析系统主要用来分析不同类型的数据源，包括数据源的格式、数据的类型、数据的内容等。数据增量捕获系统主要是捕获数据源中发生了改变的数据，在Kettle中可通过时间戳的方式来捕获数据的变化。数据抽取系统主要是从不同的数据源抽取数据，通过数据的过滤和排序，数据格式的转换，迁移到ETL环境，进行数据暂存。第三章 基本技术方
22、法3.4 ETL子系统3.4.2 清洗和更正数据 清洗和更正数据子系统主要包括数据清洗系统、错误处理系统、审计维度系统、重复数据排查系统和数据一致性系统。数据清洗系统主要是根据系统业务需求对数据源中的数据进行清洗，提高数据的质量。通过清洗，可以找到错误的数据，并进行更正。在数据清洗系统中，数据业务人员、源系统开发人员、ELT开发人员都有义务来完成数据的清洗。第三章 基本技术方法3.4 ETL子系统3.4.3 数据发布 数据发布类子系统主要是加载和更新数据仓库数据，包括数据缓慢变化维度处理系统、迟到维度处理系统、代理键生成系统等。这里主要讲述数据缓慢变化维度处理系统。数据缓慢变化维度处理系统是多
23、维度数据仓库的基础，它保存了对事实表进行分析的信息。例如，如果业务系统修改了客户的信息，维度变更也会根据不同的规则变更数据仓库中的数据维度。变更方式可采用覆盖、增加新行、增加新列、增加小维度表、分离历史表等方式。第三章 基本技术方法3.4 ETL子系统 3.4.4 管理ETL 管理ETL系统主要是对ETL开发环境进行设置，包括备份系统、恢复和重新启动子系统、工作流监控系统、问题报告系统、版本控制系统等。第三章基本技术方法3.4ETL子系统3.2技术路线3.3ETL工具3.1ETL入门习题大数据应用人才培养系列教材1什么是ETL，其主要功能是什么？2对数据仓库典型的需求包括哪几个方面？3在数据评估中，对数据源进行清洁处理主要包括哪几个方面？4简述比较流行的开源ETL工具。5ETL子系统主要包括哪4种类型？习题：感谢聆听
