ETL
概述
ETL是将数据进行抽取(Extract)、转换(Transform)和加载(Load)的过程。
数据从不同的数据源抽取到ODS(操作性数据存储)进行清洗转换后,加载到DW(数据仓库)中。
其中花费时间最长的是清洗转换的部分。
一、ETL的实现
ETL工具
Oracle的OWB、SQL Server 2000的DTS、SQL Server2005的SSIS服务和Informatic
优点:可以快速建立ETL工程,不需要复杂的编码,提高效率,降低难度
缺点:缺少灵活性
SQL
优点:灵活,能提高效率
缺点:编码复杂,技术难度高
ETL工具+SQL
综合前两种
二、抽取(Extract)
抽取可以作用在从不同的数据源中,在抽取过程中可以进行一些清洗转换工作,最后会将数据抽取到ODS中。
同时需要明白数据是存放于什么业务系统,业务系统中的数据库是使用的什么类型数据库,是否有手工数据,手工数据量有多大,是否存在非结构化的数据。
还要确定数据抽取的方法:主动抽取或由数据库推送。增量抽取还是全量。每日抽取还是每月抽取。
1、相同数据库的数据源处理方法
数据库(如oracle,SQL Server)会提供同种数据库相互连接功能,在建立了连接关系后直接使用select语句就可以访问。
2、不同数据库的数据源处理方法
可以通过ODBC的方式建立连接,如果相互之间不能建立连接,可以通过数据库提供的工具将数据导出,再导入到ODS。还可以通过程序的方式来处理。
3、文件类型数据源
直接按照第二种方式,将文件导入到ODS中。
4、增量更新
如果数据量庞大,需要考虑进行增量更新。在进行增量更新时,需要记录下时间戳,每次进行抽取时判断ODS中最大的时间,再根据这个时间去数据库中取大于这个时间的所有记录。
三、数据清洗转换(Transform)
数据仓库分为ODS和DW两部分。
将数据抽取到ODS中做清洗(即将脏数据、不完整数据以及重复数据给过滤掉),再从ODS到DW的过程中转换,进行一些业务规则的计算和整合。
1、数据清洗
数据清洗主要是过滤掉不符合要求的数据,过滤后需要将过滤后的结果交给主管部门,来确定最后是进行过滤还是进行修复。
其中,不符合要求的数据包括不完整的数据、错误的数据以及重复的数据。
(1)不完整的数据:主要是数据中有信息的缺失,对于这类数据,需要将其提取出来交给专门的人员来进行数据修复,修复完整后才能够写入DW。
(2)错误的数据:主要是由于代码没有对用户写入的数据进行判断,导致将不符合规则的数据写入到了数据库,如特殊字符或格式不正确等,对于这类数据,需要通过SQL语句的方式提取出来,然后交给专门的人员来进行修复。
(3)重复的数据:同样的,需要将重复的数据交给主管部门来确认并整理。
2、数据转换
数据转换主要是进行不一致的数据转换、数据粒度的转换以及商务规则的计算。
(1)不一致的数据转换:将不同业务数据库中的相同类型的数据统一,如某个字段在A数据库是A01,在B数据库中是B01,进行统一后,就只使用A01。
(2)数据粒度的转换:业务数据库中会存储很明细的数据,而数据仓库只需要用来分析数据,不需要非常明细的数据,所以会根据数据仓库的粒度来整理业务系统的数据。
(3)商务规则的计算:业务中的数据指标通常不是简单的计算就能完成,需要将这些数据计算好了之后再存入数据仓库库中。
四、ETL和ELT
ETL是按照E T L的操作顺序进行处理的架构:抽取、转换、加载。而ELT是抽取后先进行加载,最后进行转换的架构。
在ETL中如果需要提高效率,则需要提高ETL工具的服务器配置,而ELT则需要对相应的数据库进行调优。
ETL架构的优势
1、可以分担数据库的压力
2、相对于ELT可以实现更复杂的转换逻辑
3、使用单独的服务器,不需要数据库来分担
4、和底层的数据库无关
ELT架构的优势
1、可以充分利用数据库
2、保证数据一直在数据库中,不需要额外的加载操作,提高效率
3、可以对相关数据库进行调优,以此来获得更高的效率
五、ETL的四种模式
ETL有四种主要实现模式:触发器、增量字段、全量同步、日志比对。
1、触发器
触发器是普遍采取的一种模式。这种模式根据抽取的要求,在对应的表上建立对应的插入、修改、删除3个触发器,当源表的数据发生变化时,触发器会把变化的数据写入另一张增量表,后续ETL的抽取就直接从这张增量表中取出而不是源表,增量表中抽取过的数据要进行标记。
优点:抽取的效率高,不需要修改源表结构。
缺点:需要建立触发器,数据库有更大压力。
2、增量字段
增量字段是在源表中增加一个字段,如时间戳,当源表中的数据有新增或者修改等操作时,这个字段也会修改成对应的时间,当使用ETL工具来进行增量数据获取时,只需要对比已经抽取过的数据中最大的时间戳,再使用这个时间戳在数据库中比对,就能够判断出哪些是新增/修改数据。
优点:使用时间戳的方式提取效率快,方式也比较简单。
缺点:时间戳字段的维护需要业务代码来实现,需要在代码中加入额外的时间戳字段,对老数据的删除/更新操作有限制,数据准确度不高。
3、全量同步
全量同步是指在抽取之前先获取源表数据,删除目标表数据,最后再加载进去的方式。但最好是在数据量不大时使用。
优点:对表结构没有影响,不需要修改业务代码,由于是全量的方式,所以基本上没有风险。
缺点:处理时需要对全表的数据进行比对,性能较低。
4、日志比对
日志比对是通过获取数据库的日志来捕获变化数据,使用较成熟的技术是oracle的CDC技术,它能够捕获到上一次抽取之后产生过变化的数据并写入到相关的日志。
优点:同步效率高。
缺点:如果数据库版本或者产品不统一,就不好实现抽取。
5、总结
增量机制 | 兼容性 | 完备性 | 抽取性能 | 源库压力 | 源库改动量 | 实现难度 |
---|---|---|---|---|---|---|
触发器 | 关系型数据库 | 高 | 优 | 高 | 高 | 容易 |
增量字段 | 关系型数据库.具有”字段”结构的其它数据格式 | 低 | 较优 | 低 | 高 | 容易 |
全表同步 | 任何数据格式 | 高 | 极差 | 中 | 无 | 容易 |
日志比对 | 关系型数据库(oracle/mysql) | 高 | 较优 | 中 | 中 | 较难 |