数据仓库etl

《数据仓库etl》由会员分享，可在线阅读，更多相关《数据仓库etl（29页珍藏版）》请在金锄头文库上搜索。

1、数据抽取、转换、加载ETL,2010.10.9,目 录,ETL建模 ETL增量机制 ETL数据质量控制机制 ETL并行技术,ETL工作流模型,在ETL流程设计中，首先应该根据业务需求和相关数据源的结构建立概念模型，确定源数据库与目标数据库之间的映射关系，然后根据概念模型建立逻辑模型。,ETL概念模型,概念建模是整个ETL流程设计的最初阶段，在这个阶段， ETL流程设计者的主要任务是搜集用户的需求，然后分析相关数据源的结构及其内容，确定抽取操作所使用的数据源。ETL概念模型主要是建立数据源与数据仓库的模式或者属性之间的映射关系，以及在映射过程所需要的转换和清洗操作。 下面给出在本文中使用的几个E

2、TL概念模型基本元素的定义： 转换(transform) 是从数据源到目标数据仓库属性映射过程中对数据进行必要的转换、清洗操作。它包括了数据清 洗过滤操作与模式转换操作。 约束(ETL constrain) 对数据集合中的数据必须满足的某些条件进行建模的结果。 数据供应关系(provider relationship) 定义从数据源到目标数据仓库的属性之间的映射关系，以及中间可选的相关转换。,例子,下面以一个例子来说明ETL流程的概念建模。一家电子商务跨国企业，需要从中国和美国两个分公司的数据库抽取注册用户的信息到集中的数据仓库中进行分析，假设数据源和数据仓库中的模式如下： S1custome

3、r(customerlD，name，citylD，cmail，date) S1city(cityID。cityNamecountryName) S2customer(name，countryName，cilyName，email，date) DWcustomer(customerlDname，countryName，cityName，email，date) 对于这个ETL场景，可以建立如图1所示的概念模型。其中：Sl和s2表示数据源中的表，DSA表示中间数据库，DW表示目标数据仓库。,在上面这个例子中，为了保证目标数据仓库中数据的完整性，需要从Sl和s2两个数据源抽取数据，对它们执行并(uni

4、on)操作。为了与数据仓库中事实表的模式一致，必须对S1中的customer表和city表执行外连接，而且还要将S2中 date属性的数据类型为日期格式(mmddyyyy)转换成中国的日期格式(yymmdd)，还需要根据s2的其他属性组合(name、email)为S2表计算生成一个主键。其中，并运算、外连接运算以及日期转换属于概念模型中的转换transform)，其他的操作如PK、SK则属于约束(ETL constrain)。,ETL逻辑模型,ETL概念模型并不是一个完整的工作流模型，它定义了数据源与目标数据仓库属性之间的映射关系以及中间必要的转换，但并不关注转换的语义以及执行顺序。 ETL流

5、程的逻辑模型是一个以数据为中心的工作流模型，在逻辑建模阶段，ETL流程的设计者需要明确定义数据流所经过的各个活动的操作语义，并确定整个流程中各个活动的执行顺序。,活动(activity)是ETL工作流的基本执行单元，是ETL逻辑模型中一个重要元素。它定义了输入数据模式、输出数据模式、活动的操作语义以及活动的执行优先级。活动的操作语义定义了活动对输入数据所执行的操作，以及输入数据模式到输出数据模式之间的映射关系，可以使用SOL语言或者其他的形式化语言对语义进行描述，也可以使用ETL工具箱中的操作组件和相应的运行时参数进行表示。在一个ETL流程中，必须明确规定每一个活动的执行优先级，ETL流程中的

6、任意两个活动，如果存在依赖关系，即一个活动的输入模式依赖于另一个活动的输出模式(不一定是相邻活动)，那么，这两个活动构成严格偏序关系，可以确定其执行的先后顺序。一般情况下，在一个ETL流程中，抽取操作(extract)总是最先执行，加载操作(load)最后执行。,数据转换,在设计数据转换时，由于数据源之间往往存在着不一致的问题，因此数据转换必须做到数据名称及格式的统一，同时对于源数据库中可能不存在的数据需要创建新的数据逻辑视图并进行相应的转换。概括起来需要如下的处理：,直接映射：数据源字段和目标字段长度或精度相同，则无需做任何处理。 字符串处理：从数据源的字符串字段中获取特定信息作为目标数据库

7、的某个字段，则对字符串的操作有类型转换、字符串截取等。由于字符类型字段的随意性也可能造成脏数据的出现，所以在处理这种规则的时候，需要异常处理。 字段运算：对于数值型字段来说，有时数据源的一个或多个字段进行数学运算而得到目标字段，则需要某些字段运算。 空值判断：对于数据源字段中的NULL值，可能在目标数据库进行分析处理时会出问题，因此必须对空值进行判断，并转换成特定的值。 日期转换：由于目标数据库中的日期类型格式是统一的，所以对数据源字段的日期格式需要相应的转换。 聚集运算：对于目标数据库事实表中的一些度量字段，通常需要通过数据源一个或多个字段运用聚集函数得来的，比如sumcount，avg，m

8、in，max，因此需要做相应的转换。 既定取值：这条规则对于目标字段取一个固定的或是依赖系统的值，而不依赖于数据源字段。,ETL增量抽取机制,要实现增量抽取，关键是如何准确快速的捕获变化的数据。优秀的增量抽取机制要求ETL能够将业务系统中的变化数据按一定的频率准确地捕获到，同时不能对业务系统造成太大的压力，影响现有业务。相对全量抽取而言，增量抽取的设计更复杂。增量数据抽取中常用的捕获变化数据的方法有以下几种：,触发器方式,触发器方式是普遍采取的一种增量抽取机制。该方式是根据抽取要求，在要被抽取的源表上建立插入、修改、删除3个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个

9、增量日志表，ETL的增量抽取则是从增量日志表中而不是直接在源表中抽取数据，同时增量日志表中抽取过的数据要及时被标记或删除。,时间戳方式,时间戳方式是指增量抽取时，抽取进程通过比较系统时间与抽取源表的时间戳字段的值来决定抽取哪些数据。这种方式需要在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。 有的数据库(例如Sql Server)的时间戳支持自动更新，即表的其它字段的数据发生改变时，时间戳字段的值会被自动更新为记录改变的时刻。在这种情况下，进行ETL实施时就只需要在源表加上时间戳字段就可以了。对于不支持时间戳自动更新的数据库，这就要求业务系统在更新业务数据时，通

10、过编程的方式手工更新时间戳字段。 使用时间戳方式可以正常捕获源表的插入和更新操作， 但对于删除操作则无能为力，需要结合其它机制才能完成。,全表删除插入方式,全表删除插入方式是指每次抽取前先删除目标表数据，抽取时全新加载数据。该方式实际上将增量抽取等同于全量抽取。对于数据量不大，全量抽取的时间代价小于执行增量抽取的算法和条件代价时，可以采用该方式。,全表比对方式,全表比对即在增量抽取时，ETL进程逐条比较源表和目标表的记录，将新增和修改的记录读取出来。,日志表方式,对于建立了业务系统的生产数据库，可以在数据库中创建业务日志表，当特定需要监控的业务数据发生变化时，由相应的业务系统程序模块来更新维护

11、日志表内容。增量抽取时，通过读日志表数据决定加载哪些数据及如何加载。日志表的维护需要由业务系统程序用代码来完成。,系统日志分析方式,该方式通过分析数据库自身的日志来判断变化的数据。关系型数据库系统都会将所有的DML操作存储在日志文件中，以实现数据库的备份和还原功能。ETL增量抽取进程通过对数据库的日志进行分析，提取对相关源表在特定时间后发生的DML操作信息，就可以得知自上次抽取时刻以来该表的数据变化情况，从而指导增量抽取动作。有些数据库系统提供了访问日志的专用的程序包(例如 Oracle的LogMiner)，使数据库日志的分析工作得到大大简化。,ETL数据质量控制机制,ETL是一个以数据处理为

12、中心的工作流过程，其中包含数据抽取(Extract)、转换(Transform)、装载(Load)等操作，负责完成数据从数据源向目标数据仓库的转化。传统ETL过程基于数据源的数据本身没有错误的假设条件。因此，传统的ETL过程没有考虑数据质量控制的功能。实际上，由于数据仓库所使用的数据往往来自多个异构数据源，在数据集成过程中，会出现许多影响数据质量的问题，如：对象同一性问题、拼写错误和记录之间数据矛盾问题等，必须进行数据清洗并进行数据质量评估与控制，才能保证进行目标数据仓库中数据的质量。,数据质量问题分类,在实施数据ETL 过程中，可能导致许许多多的数据质量问题，为了方便这些问题的管理和解决，必

13、须对它们进行分类抽象。可以根据处理的是单数据源还是多数据源以及问题出在模式层还是实例层，将数据质量问题分为四类：单数据源模式层问题、单数据源实例层问题、多数据源模式层问题和多数据源实例层问题。,ETL并行控制技术,在ETL逻辑模型中，必须确定每一个活动的执行优先级，以确定整个ETL工作流的执行顺序。在确定了执行的优先级之后，可对相互之间没有依赖关系的活动并行执行，以提高整个ETL工作流的执行效率。,确定ETL工作流活动优先级,算法思想：在ETL逻辑模型中，可能会存在多个数据供应关系，每一个数据供应关系都是以抽取操作开始，以加载操作结束的。在确定活动优先级的时候，把每一个抽取操作(即起始操作)的

14、优先级定义为1(假定ETL工作流的优先级从数值1开始，依次递增，数值越小优先级越高)，然后分析其他的非起始活动。对于每一个尚未确定其优先级的活动，如果它的所有前驱活动都已经确定了优先级，那么可以通过计算它的所有前驱活动优先级的最大值，再加1取得。通过遍历活动集中的所有活动，就可以确定ETL工作流中所有活动的优先级。,在上面的算法描述中，Ll将活动节点集的所有活动都放 在unvisited数组中，表示所有活动都是未访问的；L2一L6遍历整个节点集并判断活动act是否是起始操作，如果是则将其优先级设置为1，将其加入visited数组，并从unvisited数组中将其移除；L7一L12不断遍历unv

15、isited数组直到其为空，L9表示如果一个活动act的所有前驱活动的优先级都已经确定，则act的优先级也可以确定，L10计算act所有前驱活动优先级的最大值，再加1作为act的优先级，同时将act加入visited数组，并从unvisited数组中将其移除。,并行执行ETL工作流中的活动,确定了ETL逻辑模型中每一个活动的优先级之后，ETL工作流引擎会按照优先级从高到低依次执行工作流中的每一个活动。 在使用串行方法执行的工作流引擎中，每次从活动集中获 取优先级最高的一个活动并执行，执行完一个活动之后再选择另一个当前优先级最高的活动执行，不断重复这样的过程直到ETL工作流中的所有活动执行完毕。 如果存在多个活动的优先级相同，在上述的串行方法中需 要依次顺序执行这些活动，但是这些活动是可以并行执行的。把这些活动放进一个执行阶段，每一个执行阶段都是一个容器，存放多个优先级相同的活动。同一执行阶段的活动不存在依赖关系，根据前面的讨论，存在依赖关系的活动构成严格偏序关系，其优先级不可能相等。并行执行优先级相同的多个活动能够获得更高的时间效率。,