`
superlxw1234
  • 浏览: 552332 次
  • 性别: Icon_minigender_1
  • 来自: 西安
博客专栏
Bd1c0a0c-379a-31a8-a3b1-e6401e2f1523
Hive入门
浏览量:44553
社区版块
存档分类
最新评论

数据仓库之 ETL漫谈

阅读更多

ETL,Extraction-Transformation-Loading的缩写,中文名称为数据抽取、转换和加载。

 

大多数据仓库的数据架构可以概括为:

数据源-->ODS(操作型数据存储)-->DW-->DM(data mart)

ETL贯穿其各个环节。

 

​一、数据抽取:

       可以理解为是把源数据的数据抽取到ODS或者DW中。

       1. 源数据类型:

           关系型数据库,如Oracle,Mysql,Sqlserver等;

           文本文件,如用户浏览网站产生的日志文件,业务系统以文件形式提供的数据等;

           其他外部数据,如手工录入的数据等;

       2. 抽取的频率:

           大多是每天抽取一次,​也可以根据业务需求每小时甚至每分钟抽取,当然得考虑源数据库系统能否承受;

       3. 抽取策略:

           个人感觉这是数据抽取中最重要的部分,可分为全量抽取和增量抽取。

           全量抽取适用于那些数据量比较小,并且不容易判断其数据发生改变的诸如关系表,维度表,配置表等;

           增量抽取,一般是由于数据量大,不可能采用全量抽取,或者为了节省抽取时间而采用的抽取策略;

               如何判断增量,这是增量抽取中最难的部分,一般包括以下几种情况:

               a) 通过时间标识字段抽取增量;源数据表中有明确的可以标识当天数据的字段的流水表,

                   如createtime,updatetime等;

               b) 根据上次抽取结束时候记录的自增长ID来抽取增量;无createtime,但有自增长类型字段的流水表,

                   如自增长的ID,抽取完之后记录下最大的ID,

                   下次抽取可根据上次记录的ID来抽取;

               c)  通过分析数据库日志获取增量数据,无时间标识字段,无自增长ID的关系型数据库中的表;

               d)  通过与前一天数据的Hash比较,比较出发生变化的数据,这种策略比较复杂,在这里描述一下,

                     比如一张会员表,它的主键是memberID,而会员的状态是有可能每天都更新的,

                     我们在第一次抽取之后,生成一张备用表A,包含两个字段,第一个是memberID,

                     第二个是除了memberID之外其他所有字段拼接起来,再做个Hash生成的字段,

                     在下一次抽取的时候,将源表同样的处理,生成表B,将B和A左关联,Hash字段不相等的

                     为发生变化的记录,另外还有一部分新增的记录,

                     根据这两部分记录的memberID去源表中抽取对应的记录;

               e) 由源系统主动推送增量数据;例如订单表,交易表,

                   有些业务系统在设计的时候,当一个订单状态发生变化的时候,是去源表中做update,

                   而我们在数据仓库中需要把一个订单的所有状态都记录下来,

                   这时候就需要在源系统上做文章,数据库​触发器一般不可取。我能想到的方法是在业务系统上做些变动,

                   当订单状态发生变化时候,记一张流水表,可以是写进数据库,也可以是记录日志文件。

               当然肯定还有其他抽取策略,至于采取哪种策略,需要考虑源数据系统情况,

               抽取过来的数据在数据仓库中的存储和处理逻辑,抽取的时间窗口等等因素。

 

二、数据清洗:

       顾名思义​,就是把不需要的,和不符合规范的数据进行处理。数据清洗最好放在抽取的环节进行,

       这样可以节约后续的计算和存储成本;

       当源数据为数据库时候,其他抽取数据的SQL中就可以进行很多数据清洗的工作了。

       ​数据清洗主要包括以下几个方面:

       1. 空值处理;根据业务需要,可以将空值替换为特定的值或者直接过滤掉;

       2. 验证数据正确性;主要是把不符合​业务含义的数据做一处理,比如,把一个表示数量的字段中的字符串

           替换为0,把一个日期字段的非日期字符串过滤掉等等;

       3. 规范数据格式;比如,把所有的日期都格式化成YYYY-MM-DD的格式等;

       4. ​数据转码;把一个源数据中用编码表示的字段,通过关联编码表,转换成代表其真实意义的值等等;

       5. 数据标准,统一;比如在源数据中表示男女的方式有很多种,在抽取的时候,直接根据模型中定义的值做转化,

           统一表示男女;

       6. 其他业务规则定义的数据清洗。。。

 

三、数据转换和加载:

       很多人理解的ETL是在经过前两个部分之后,加载到数据仓库的数据库中就完事了。

       数据转换和加载不仅仅是在源数据-->ODS这一步,ODS-->DW, DW-->DM包含更为重要和复杂的ETL过程。

       1. 什么是ODS?

           ODS(Operational Data Store)是数据仓库体系结构中的一个可选部分,

           ODS具备数据仓库的部分特征和OLTP系统的部分特征,

           它是“面向主题的、集成的、当前或接近当前的、 不断变化的”数据。​---摘自百度百科

           其实大多时候,ODS只是充当了一个数据临时存储,数据缓冲的角色。一般来说,

           数据由源数据加载到ODS之后,会保留一段时间,当后面的数据处理逻辑有问题,需要重新计算的时候,

           可以直接从ODS这一步获取,而不用再从源数据再抽取一次,减少对源系统的压力。

           另外,ODS还会直接给DM或者前端报表提供数据,比如一些维表或者不需要经过计算和处理的数据;

           还有,ODS会完成一些其他事情,比如,存储一些明细数据以备不时之需等等;

       2. 数据转换(刷新):

           数据转换,更多的人把它叫做数据刷新,就是用ODS中的增量或者全量数据来刷新DW中的表。

           DW中的表基本都是按照事先设计好的模型创建的,如事实表,维度表,汇总表等,

           每天都需要把新的数据更新到这些表中。

           更新这些表的过程(程序)都是刚开始的时候开发好的,每天只需要传一些参数,如日期,来运行这些程序即可。

       3. 数据加载:

           个人认为,每insert数据到一张表,都可以称为数据加载,至于是delete+insert、truncate+insert、

           还是merge,这个是由业务规则决定的,这些操作也都是嵌入到数据抽取、转换的程序中的。

 

四、ETL工具:

        在传统行业的数据仓库项目中,大多会采用一些现成的ETL工具,如Informatica、Datastage、微软SSIS等。

        这三种工具我都使用过,优点有:图形界面,开发简单,数据流向清晰;缺点:局限性,不够灵活,

        处理大数据量比较吃力,查错困难,昂贵的费用;

        选择ETL工具需要充分考虑源系统和数据仓库的环境,当然还有成本,如果源数据系统和数据仓库都采用

        ORACLE,那么我觉得所有的ETL,都可以用存储过程来完成了。。

        在大一点的互联网公司,由于数据量大,需求特殊,ETL工具大多为自己开发,

        或者在开源工具上再进行一些二次开发,在实际工作中,

        一个存储过程,一个shell/perl脚本,一个java程序等等,都可以作为ETL工具。

五、ETL过程中的元数据:

       试想一下,你作为一个新人接手别人的工作,没有文档,程序没有注释,

            数据库中的表和字段也没有任何comment,你是不是会骂娘了?

       业务系统发生改变,删除了一个字段,需要数据仓库也做出相应调整的时候,

           你如何知道改这个字段会对哪些程序产生影响?

       。。。。

       源系统表的字段及其含义,源系统数据库的IP、接口人,数据仓库表的字段及其含义,

       源表和目标表的对应关系,一个任务对应的源表和目标表,任务之间的依赖关系,

       任务每次执行情况等等等等,这些元数据如果都能严格的管控起来,上面的问题肯定不会是问题了。。。

1
4
分享到:
评论
6 楼 liu_305 2015-12-26  
想问下楼主这些内容是自己总结的还是书上的? 如果想学习数据仓库相关的技术应该看哪些书?求楼主推荐。
5 楼 xiaohai798 2014-12-11  
楼主 这个博客 是真正的干货
顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶顶
请允许我转载
4 楼 bin_1715575332 2013-05-27  
个人感觉挺好锝!
3 楼 superlxw1234 2012-08-30  
gwp 写道
果然是漫谈,太没层次了

本人不才,能否指点些有层次的东西呢?
2 楼 sunofsummer 2012-08-30  
gwp 写道
果然是漫谈,太没层次了

没看到阁下博客中有何高深的文章 何必在此吐槽。最看不起你这种人。
1 楼 gwp 2012-08-30  
果然是漫谈,太没层次了

相关推荐

    数据仓库与ETL的实现过程

    数据仓库与ETL(Extract, Transform, Load)是企业信息化建设中的关键组成部分,它们主要用于处理和分析大量的业务数据。在本篇文章中,我们将深入探讨这两个概念及其实施过程,同时也会涉及相关的工具和技术。 ...

    数据仓库ETl工具箱3.pdf

    ### 数据仓库数据模型理解 ETL团队必须深入理解数据仓库的物理数据模型,这包括维度模型的概念。数据仓库的物理数据模型通常包括维度表、事实表等,ETL解决方案的实施需要这些模型中各个表的相互作用。理解如何使...

    数据仓库和ETL

    数据仓库和ETL数据仓库和ETL数据仓库和ETL数据仓库和ETL

    传统数据仓库ETL设计报告

    《传统数据仓库ETL设计报告》 ETL(Extract, Transform, Load)是数据仓库建设中的关键步骤,涉及从源系统提取数据、转换数据格式和结构,并加载到目标数据仓库的过程。本报告主要探讨了ETL的升级策略,尤其是如何...

    数据仓库-ETL和元数据

    **ETL**(Extract/Transform/Load)是数据仓库建设中的核心环节之一,主要负责将来自多个源的数据抽取、转换和加载到数据仓库中。 1. **数据抽取**(Extract): 从源系统中提取所需的数据。 2. **数据转换**...

    数据仓库ETL算法详解

    是构建数据仓库的重要一环,用户从数据源抽取出所需的数据,经过数据清洗,最终按照预先定义好的数据仓库模型,将数据加载到数据仓库中去; 2. 常用的ETL工具:主要有三大主流工具,分别是Ascential公司的Datastage...

    数据仓库ETL工具箱 中文版

    数据仓库ETL工具箱中文版是一本专注于数据仓库构建与ETL(提取、转换、加载)过程的专业书籍。ETL是数据仓库系统的核心部分,它负责从不同的源系统中抽取数据,进行清洗、转换,然后加载到数据仓库中,以便进行高效...

    数据仓库与ETL

    数据仓库与ETL:深入解析与实践指南 数据仓库(Data Warehouse)与ETL(Extract, Transform, Load)是现代企业数据分析的关键组成部分,尤其在大数据时代,它们的作用愈发凸显。数据仓库是一个面向主题的、集成的、非易...

    金融数据仓库中ETL的设计与实现

    摘要:本文首先介绍了ETL的相关技术:数据仓库的体系结构和元数据的定义;然后分析了金融数据的特点和ETL技术在金融数据挖掘中的应用;再根据金融数据的特点,对金融数据的ETL进行了分析。接着结合ETL与数据仓库的关系...

    数据仓库ETL工具箱

    通过《数据仓库ETL工具箱》这本书,读者可以深入理解数据仓库的构建过程,学习到ETL的关键技术和策略,并掌握如何设计和管理高效率、高质量的数据集成系统。对于希望提升数据仓库能力的IT专业人士来说,这是一份宝贵...

    数据仓库至ETL

    数据仓库至ETL

    数据仓库ETL

    在详细介绍数据仓库ETL(抽取、转换、装载)技术之前,我们先来了解一下与之相关的背景知识,以便更好地掌握ETL在整个数据处理过程中的作用和重要性。 首先,数据仓库是一种特殊的数据库,专门用于支持决策过程。它...

    数据仓库中的ETL和元数据

    在构建数据仓库的过程中,ETL(Extract/Transform/Load,抽取/转换/加载)和元数据管理是两项核心活动,它们确保数据能够被高效地整合和利用。 ETL过程是数据仓库中的关键步骤,它涉及数据从多个源抽取出来、转换成...

Global site tag (gtag.js) - Google Analytics