精华内容
下载资源
问答
  • etl 数据仓库
    2020-12-30 04:53:04

    ETL构建企业级数据仓库五步法

    在数据仓库构建中,ETL贯穿于项目始终,它是整个数据仓库的生命线,包括了从数据清洗,整合,到转换,加载等的各个过程,如果说数据仓库是一座大 厦,那 么ETL就是大厦的根基,ETL抽取整合数据的好坏直接影响到最终的结果展现。所以ETL在整个数据仓库项目中起着十分关键的作用,必须摆到十分重要的位 置。

    一、什么是ETL

    ETL是数据抽取(Extract)、转换(Transform)、加载(Load

    )的简写,它是将OLTP系统中的数据经过抽取,并将不同数据源的数据进行转换、整合,得出一致性的数据,然后加载到数据仓库中。简而言之ETL是完成从

    OLTP系统到OLAP系统的过程(图一:pic1.jpg)。

    二、数据仓库的架构

    数据仓库(Data Warehouse \

    DW)是基于OLTP系统的数据源,为了便于多维分析和

    多角度展现将其数据按特定的模式进行存储而建立的关系型数据库,它不同于多维数据库,数据仓库中的数据是细节的,集成的,数据仓库是面向主题的,是以

    OLAP系统为分析目的。它包括星型架构(图二:pic2.jpg)与雪花型架构(图三:pic3.jpg),其中星型架构中间为事实表,四周为维度表,

    类似星星;雪花型架构中间为事实表,两边的维度表可以再有其关联子表,而在星型中只允许一张表作为维度表与事实表关联,雪花型一维度可以有多张表,而星型

    不可以。考虑到效率时,星型聚合快,效率高,不过雪花型结构明确,便于与OLTP系统交互。在实际项目中,我们将综合运用星型架构与雪花型架构。

    三、ETL构建企业级数据仓库五步法的流程

    (一)、确定主题

    确定数据分析或前端展现的某一方面的分析主题,例如我们分析某年某月某一地区的啤酒销售情况,就是一个主题。主题要体现某一方面的各分析角度(维度)和统

    计数值型数据(量度),确定主题时要综合考虑,一个主题在数据仓库中即为一个数据集市,数据集市体现了某一方面的信息,多个数据集市构成了数据仓库。

    (二)、确定量度

    确定了主题以后,我们将考虑要分析的技术指标,诸如年销售额此类,一般为数值型数据,或者将该数据汇总,或者将该数据取次数,独立次数或取最大最小值

    等,这样的数据称之为量度。量度是要统计的指标,必须事先选择恰当,基于不同的量度可以进行复杂关键性能指标(KPI)等的计算。

    (三)、确定事实数据粒度

    确定了量度之后我们要考虑到该量度的汇总情况和不同维度下量度的聚合情况,考虑到量度的聚合程度不同,我们将采用“最小粒度原则”,即将量度的粒度设置

    到最小,例如我们将按照时间对销售额进行汇总,目前的数据最小记录到天,即数据库中记录了每天的交易额,那么我们不能在ETL时将数据进行按月或年汇总,

    需要保持到天,以便于后续对天进行分析。而且我们不必担心数据量和数据没有提前汇总带来的问题,因为在后续的建立CUBE时已经将数据提前汇总了。

    (四)、确定维度

    度是要分析的各个角度,例如我们希望按照时间,或者按照地区,或者按照产品进行分析,那么这里的时间、地区、产品就是相应的维度,基于不同的维度我们可

    以看到各量度的汇总情况,我们可以基于所有的维度进行交叉分析。这里我们首先要确定维度的层次(Hierarchy)和级别(Level)(图

    四:pic4.jpg),维度的层次是指该维度的所有级别,包括各级别的属性;维度的级别是指该维度下的成员,例如当建立地区维度时我们将地区维度作为一

    个级别,层次为省、市、县三层,考虑到维度表要包含尽量多的信息,所以建立维度时要符合“矮胖原则”,即维度表要尽量宽,尽量包含所有的描述性信息,而不

    是统计性的数据信息。

    还有一种常见的情况,就是父子型维度(图五:pic5.jpg),该维度一般用于非叶子节点含有成员等情况,例如公司员工

    的维度,在统计员工的工资时,部

    门主管的工资不能等于下属成员工资的简单相加,必须对该主管的工资单独统计,然后该主管部门的工资等于下属员工工资加部门主管的工资,那么在建立员工维度

    时,我们需要将员工维度建立成父子型维度,这样在统计时,主管的工资会自动加上,避免了都是叶子节点才有数据的情况。

    另外,在建立维度表时要充

    分使用代理键,代理键是数值型的ID号码,好处是代理键唯一标识了每一维度成员信息,便于区分,更重要的是在聚合时由于数值型匹

    配,JOIN效率高,便于聚合,而且代理键对缓慢变化维度有更重要的意义,它起到了标识历史数据与新数据的作用,在原数据主键相同的情况下,代理键起到了

    对新数据与历史数据非常重要的标识作用。

    有时我们也会遇到维度缓慢变化的情况,比如增加了新的产品,或者产品的ID号码修改了,或者产品增加了一个新的属性,此时某一维度的成员会随着新的数据的加入而增加新的维度成员,这样我们要考虑到缓慢变化维度的处理,对于缓慢变化维度,有三种情况:

    1、

    缓慢变化维度第一种类型:历史数据需要修改。这样新来的数据要改写历史数据,这时我们要使用UPDATE,例如产品的ID号码为123,后来发现ID

    号码错误了,需要改写成456,那么在修改好的新数据插入时,维度表中原来的ID号码会相应改为456,这样在维度加载时要使用第一种类型,做法是完全更

    改。

    2、缓慢变化维度第二种类型:历史数据保留,新增数据也要保留。这时要将原数据更新,将新数据插入,需要使用UPDATE /

    INSERT,比如某一员工2005年在A部门,2006年时他调到了B部门。那么在统计2005年的数据时就应该将该员工定位到A部门;而在统计

    2006年数据时就应该定位到B部门,然后再有新的数据插入时,将按照新部门(B部门)进行处理,这样我们的做法是将该维度成员列表加入标识列,将历史的

    数据标识为“过期”,将目前的数据标识为“当前的”。另一种方法是将该维度打上时间戳,即将历史数据生效的时间段作为它的一个属性,在与原始表匹配生成事

    实表时将按照时间段进行关联,这样的好处是该维度成员生效时间明确。

    3、缓慢变化维度第三种类型:新增数据维度成员改变了属性。例如某一维度成

    员新加入了一列,该列在历史数据中不能基于它浏览,而在目前数据和将来数据中可

    以按照它浏览,那么此时我们需要改变维度表属性,即加入新的列,那么我们将使用存储过程或程序生成新的维度属性,在后续的数据中将基于新的属性进行查看。

    (五)、创建事实表

    在确定好事实数据和维度后,我们将考虑加载事实表。

    在公司的大量数据堆积如山时,我们想看看里面究竟是什么,结果发现里面是一笔笔生产记录,一笔笔交易记录… 那么这些记录是我们将要建立的事实表的原始数据,即关于某一主题的事实记录表。

    们的做法是将原始表与维度表进行关联,生成事实表(图六:pic6.jpg)。注意在关联时有为空的数据时(数据源脏),需要使用外连接,连接后我们将

    各维度的代理键取出放于事实表中,事实表除了各维度代理键外,还有各量度数据,这将来自原始表,事实表中将存在维度代理键和各量度,而不应该存在描述性信

    息,即符合“瘦高原则”,即要求事实表数据条数尽量多(粒度最小),而描述性信息尽量少。

    如果考虑到扩展,可以将事实表加一唯一标识列,以为了以后扩展将该事实作为雪花型维度,不过不需要时一般建议不用这样做。

    实数据表是数据仓库的核心,需要精心维护,在JOIN后将得到事实数据表,一般记录条数都比较大,我们需要为其设置复合主键和索引,以为了数据的完整性和

    基于数据仓库的查询性能优化,事实数据表与维度表一起放于数据仓库中,如果前端需要连接数据仓库进行查询,我们还需要建立一些相关的中间汇总表或物化视

    图,以方便查询。

    三、ETL中高级技巧的运用

    (一)、准备区的运用

    在构建数据仓库时,如果数据源位于一服务器上,数据仓库在另一

    服务器端,考虑到数据源Server端访问频繁,并且数据量大,需要不断更新,所以可以建立准备区数据库(图七:pic7.jpg)。先将数据抽取到准备

    区中,然后基于准备区中的数据进行处理,这样处理的好处是防止了在原OLTP系统中中频繁访问,进行数据运算或排序等操作。例如我们可以按照天将数据抽取

    到准备区中,基于数据准备区,我们将进行数据的转换,整合,将不同数据源的数据进行一致性处理。数据准备区中将存在原始抽取表,一些转换中间表和临时表以

    及ETL日志表等。

    (二)、时间戳的运用

    时间维度对于某一事实主题来说十分重要,因为不同的时间有不同的统计数据信息,那么按照时间记录

    的信息将发挥很重要的作用。在ETL中,时间戳有其特殊的

    作用,在上面提到的缓慢变化维度中,我们可以使用时间戳标识维度成员;在记录数据库和数据仓库的操作时,我们也将使用时间戳标识信息,例如在进行数据抽取

    时,我们将按照时间戳对OLTP系统中的数据进行抽取,比如在午夜0:00取前一天的数据,我们将按照OLTP系统中的时间戳取GETDATE到

    GETDATE减一天,这样得到前一天数据。

    (三)、日志表的运用

    在对数据进行处理时,难免会发生数据处理错误,产生出错信息,那么我们

    如何获得出错信息并及时修正呢?

    方法是我们使用一张或多张Log日志表,将出错信息记录下来,在日志表中我们将记录每次抽取的条数,处理成功的条数,处理失败的条数,处理失败的数据,处

    理时间等等,这样当数据发生错误时,我们很容易发现问题所在,然后对出错的数据进行修正或重新处理。

    (四)、使用调度

    在对数据仓库进行

    增量更新时必须使用调度(图八:pic8.jpg),即对事实数据表进行增量更新处理,在使用调度前要考虑到事实数据量,需要多长时间更

    新一次,比如希望按天进行查看,那么我们最好按天进行抽取,如果数据量不大,可以按照月或半年对数据进行更新,如果有缓慢变化维度情况,调度时需要考虑到

    维度表更新情况,在更新事实数据表之前要先更新维度表。

    调度是数据仓库的关键环节,要考虑缜密,在ETL的流程搭建好后,要定期对其运行,所以

    调度是执行ETL流程的关键步骤,每一次调度除了写入Log日志表

    的数据处理信息外,还要使用发送Email或报警信息等,这样也方便的技术人员对ETL流程的把握,增强了安全性和数据处理的准确性。

    四、总结

    ETL构建数据仓库需要简单的五步,掌握了这五步的方法我们将构建一个强大的数据仓库,不过每一步都有很深的需要研究与挖掘,尤其在实际项目中,我们要综合考虑,例如如果数据源的脏数据很多,在搭建数据仓库之前我们首先要进行数据清洗,以剔除掉不需要的信息和脏数据。

    之,ETL是数据仓库的核心,掌握了ETL构建数据仓库的五步法,就掌握了搭建数据仓库的根本方法。不过,我们不能教条,基于不同的项目,我们还将要进行

    具体分析,如父子型维度和缓慢变化维度的运用等。在数据仓库构建中,ETL关系到整个项目的数据质量,所以马虎不得,必须将其摆到重要位置,将ETL这一

    大厦根基筑牢.

    更多相关内容
  • ETL数据仓库技术面试题
  • 商业智能和数据仓库气候变化,污染和能源消耗:世界正面临许多重要且令人恐惧的问题。 我在本研究中的工作重点就是这些主要问题。目录基本信息为了帮助公司做出更多由数据驱动的决策,有两种截然不同的技术情报和...
  • 数据仓库ETL技术探究

    千次阅读 2022-01-28 11:13:32
    ETL是指把数据从数据源装人数据仓库的过程,即数据的抽取(Extract)、转换(Transform)和装载(Load)过程。ETL过程的实质就是符合特定规则的数据流动过程,从不同异构数据源流向统一的目标数据。其间,数据的抽取、清洗...

    ETL概述

    在构建商业智能系统的时候,如何正确有效地将分散在各个不同数据源中的信息整合到系统中成为了整个系统成败的关键,直接影响到系统的运行效率和最终结果。
    ETL正是解决这一问题的有力工具。
    ETL是指把数据从数据源装人数据仓库的过程,即数据的抽取(Extract)、转换(Transform)和装载(Load)过程。ETL过程的实质就是符合特定规则的数据流动过程,从不同异构数据源流向统一的目标数据。其间,数据的抽取、清洗、转换和装载形成串行或并行的过程,每个过程都必须符合特定的规则。根据国内外众多实践得到的共识,ETL规则设计和实施所需工作量约占整个项目的60%~80%。由于ETL过程的重要性和复杂性,如何设计正确、高效的ETL过程已经成为了商业智能系统构建过程中无法回避的重要问题。

    1 数据理解

    在设计ETL过程之前,有一项非常重要但经常被人们所忽略的工作,那就是数据理解。
    数据理解是通过大量的调研和统计工作,了解数据的存储方式、数据量的大小、数据的格式、数据的业务含义等信息,同时还需要统计各种数值型数据的最大值、最小值和平均值,统计非数值型数据中各种不同的取值以及各种不同取值的个数。有了以上信息,ETL以后各个步骤的设计才能做到有的放矢,达到正确、高效的目的。

    2 数据抽取

    从源文件和源数据库中获取相关数据用于填充数据仓库,称为数据抽取。并非所有包含在不同操作型业务系统中的数据都需要抽取,通常只需要其中的一个子集。抽取数据的一个子集是基于对源系统和目标系统的扩展分析,一般会由终端用户和数据仓库专家共同决定。
    在集成端进行数据的初始化时,一般需要将数据源端的全部数据装载进来,这时需要进行全量抽取。全量抽取类似于数据迁移或数据复制,它将数据源中的表或视图的数据全部从数据库中抽取出来,再进行后续的转换和加载操作。全量抽取可以使用数据复制、导入或者备份的方式完成,实现机制比较简单。
    全量抽取完成后,后续的抽取操作只需抽取自上次抽取以来表中新增或修改的数据,这就是增量抽取

    目前,数据抽取所涉及的单个技术环节都已相对成熟,但整体的集成度还很不够。市场上所提供的大多是数据抽取工具,这些工具通过用户选定源数据和目标数据的对应关系,会自动生成数据抽取的代码。但数据抽取工具支持的数据种类是有限的,同时数据抽取过程涉及数据的转换,它是一个与实际应用密切相关的部分,其复杂性使得不可嵌入用户编程的抽取工具往往不能满足要求。
    因此,实际的数据仓库实施过程中往往不一定使用抽取工具。整个抽取过程能否因工具的使用而纳入有效的管理、调度和维护则更为重要。从市场发展来看,以数据抽取、异构互连产品为主项的数据仓库厂商一般都很有可能被其它拥有数据库产品的公司吞并。在数据仓库的世界里,它们只能成为辅助的角色。

    3 数据清洗

    数据仓库的外部数据源所提供的数据内容并不完美,存在着“脏数据”,即数据有空缺、噪声等缺陷,而且在数据仓库的各数据源之间,其内容也存在着不一致的现象。
    为了控制这些“脏数据”对数据仓库分析结果的影响程度,必须采取各种有效的措施,对其进行处理,这一处理过程称为**“数据清洗”(Data Cleaning)。对于任何数据仓库而言,数据清洗过程都是必不可少的。
    不同类型的“脏数据”,清洗处理的方法是不同的。
    对于
    数据空缺**:忽略元组、用一个全局常量填充、用属性的平均值填充、使用与给定元组同类的所有样本的平均值填充空缺值、使用最可能的值填充空缺值、使用像Bayesian公式或判定树这样的基于推断的方法;
    对于噪声数据,可以用分箱或聚类等方法处理;
    对于不一致的数据,则必须依据数据仓库所应用领域的特点,使用特定的方法加以解决。

    4 数据转换

    数据转换指接收来自不同运作系统的输入并将其转换成目标数据仓库中需要的格式的过程,包括数据的合并、汇总、过滤、转换等。
    在设计数据转换时,由于数据源之间往往存在着不一致的问题,因此数据转换必须做到数据名称及格式的统一,同时对于源数据库中可能不存在的数据需要创建新的数据逻辑视图并进行相应的转换。

    (1)直接映射。数据源字段和目标字段长度或精度相同,则无需做任何处理。
    (2)字符串处理。从数据源的字符串字段中获取特定信息作为目标数据库的某个字段,则对字符串的操作有类型转换、字符串截取等。由于字符类型字段的随意性也可能造成脏数据的出现,所以在处理这种规则的时候,需要异常处理。
    (3)字段运算。对于数值型字段来说,有时数据源的一个或多个字段进行数学运算而得到目标字段,则需要某些字段运算。
    (4)空值判断。对于数据源字段中的NULL值,可能在目标数据库进行分析处理时会出问题,因此必须对空值进行判断,并转换成特定的值。
    (5)日期转换。由于目标数据库中的日期类型格式是统一的,所以对数据源字段的日期格式需要相应的转换。
    (6)聚集运算。对于目标数据库事实表中的一些度量字段,通常需要通过数据源一个或多个字段运用聚集函数进行聚集运算得来,常用的聚集函数有: sum. count,avg,min,max。
    (7)既定取值。这条规则对于目标字段取一个固定的或是依赖系统的值,而不依赖于数据源字段。

    5 数据加载

    数据加载负责将经过前几步清洗和转换后的数据按照目标数据库元数据定义的表结构装入数据仓库。加载数据到目标数据仓库的两个基本方式是刷新方式和更新方式
    **刷新方式(Refresh Mode)**是一种填充数据仓库的方法,采用在定期的间隔对目标数据进行批量重写的技术。也就是说,目标数据起初被写进数据仓库,然后每隔一定的时间,数据仓库被重写,替换以前的内容。这种加载方式越来越不流行。
    **更新方式(Update Mode)**是一种只将源数据中的数据改变写进数据仓库的方法。为了支持数据仓库的周期,便于历史分析,新记录通常被写进数据仓库中,但不覆盖或删除以前的记录,而是通过时间戳来分辩它们。
    刷新方式通常用于数据仓库首次被创建时填充数据仓库。更新方式通常用于目标数据仓库的维护。刷新方式通常与全量抽取相结合,而更新方式常与增量抽取相结合。

    6 ETL过程建模

    ETL过程是传统的数据处理过程,其输入是数据仓库数据源的各种业务处理系统的数据库,输出部分是数据仓库。ETL过程通常可以看做是一个以数据处理为中心的工作流,工作流中包括了数据抽取、数据转换、清洗以及数据加载等操作。ETL工具是负责处理这一类流程的数据集成工具。ETL过程设计的正确与否关系到数据仓库的可用性。
    ETL过程的设计质量往往取决于能否对业务需求和数据仓库环境进行形式化建模。ET工作流模型包括ETL概念模型和ETL逻辑模型两部分。
    在ETL流程设计中,首先应该根据业务需求和相关数据源的结构建立概念模型,确定源数据库与目标数据库之间的映射关系,然后根据概念模型建立逻辑模型。

    6.1 ETL概念模型

    概念建模是整个ETL流程设计的最初阶段。在这个阶段,ETL流程设计者的主要任务是搜集用户的需求,然后分析相关数据源的结构及其内容,确定抽取操作所使用的数据源。ETl概念模型主要是建立数据源与数据仓库的模式或者属性之间的映射关系,以及在映射过程所需要的转换和清洗操作。
    ETL概念模型并不针对具体的工具制定,与数据库系统无关,与应用程序无关,与工具无关。在这一概念级中,不必考虑物理实现的细节,只把注意力集中在构造源与目标实体及属性之间的映射和转换关系上。

    在这里插入图片描述
    在这里插入图片描述
    1.为了保证目标数据仓库中数据的完整性,需要从sl和s2两个数据源抽取数据,对它们执行并(union)操作。
    2.为了与数据仓库中事实表的模式一致,必须对s1中的customer表和city表执行外连接,而且还要将s2中date属性的数据类型为日期格式(mm / dd / yyyy)转换成中国的日期格式(vy / mm / dd),还需要根据s2的其他属性组合(name、email)为s2表计算生成一个主键。
    3.并运算、外连接运算以及日期转换属于概念模型中的转换transform),PK则属于约束(ETLconstrain)。

    6.2 ETL逻辑模型

    ETL概念模型并不是一个完整的工作流模型,它定义了数据源与目标数据仓库属性之间的映射关系以及中间必要的转换,但并不关注转换的语义以及执行顺序。
    ETL流程的逻辑模型是一个以数据为中心的工作流模型,在逻辑建模阶段,ETL流程的设计者需要明确定义数据流所经过的各个活动的操作语义,并确定整个流程中各个活动的执行顺序。

    展开全文
  • 数据仓库ETl工具箱5.pdf
  • 数据仓库ETL

    2021-09-22 15:54:15
    数据仓库ETL ETL,是英文Extract-...​ ETL数据仓库的流水线,也可以认为是数据仓库的血液,它维系着数据仓库中数据的新陈代谢,而数据仓库日常的管理和维护工作大部分精力就是保持ETL的正常和稳定。 ...

    数据仓库之ETL

    ETL,是英文Extract-Transform-Load的缩写,用来描述将数据从源端经过**抽取(extract)、转换(transform)、加载(load)**至目的端的过程。ETL是将业务系统的数据经过抽取、清洗、转换之后加载到数据仓库的过程,目的是将企业中散乱、零乱、标准不同意的数据整个在一起。

    ​ ETL是数据仓库的流水线,也可以认为是数据仓库的血液,它维系着数据仓库中数据的新陈代谢,而数据仓库日常的管理和维护工作大部分精力就是保持ETL的正常和稳定。

    image-20210922155321857

    展开全文
  • GoodReads数据管道建筑学管道包括各种模块:ETL职位Redshift仓库模块分析模块概述使用Goodreads Python包装器从Goodreads API实时捕获数据(查看用法 )。从Goodreads API收集的数据存储在本地磁盘上,并及时移至AWS...
  • ETL数据整合和处理》教学大纲.pdf《ETL数据整合和处理》教学大纲.pdf《ETL数据整合和处理》教学大纲.pdf《ETL数据整合和处理》教学大纲.pdf《ETL数据整合和处理》教学大纲.pdf《ETL数据整合和处理》教学大纲.pdf
  • ETL一词较常用在数据仓库,但其对象并不限于数据仓库数据仓库是数据库的更高层次的应用,数据仓库的数据是历史的不可更改的,只有在全局逐步演进的数据才允许更新,与数据挖掘有关,主要是为大数据分析和领导决策...

    目录

    简介

    ETL与ELT

    工具

    参见

    ETL架构级别概述

     数据的抽取(Extract)

    数据的清洗转换(Cleaning、Transform)

    ETL日志、警告发送

    ETL特点

    案例参考


    ETL,是英文Extract-Transform-Load的缩写,用来描述将数据从来源端经过抽取(extract)、转换(transform)、加载(load)至目的端的过程。ETL一词较常用在数据仓库,但其对象并不限于数据仓库数据仓库是数据库的更高层次的应用,数据仓库的数据是历史的不可更改的,只有在全局逐步演进的数据才允许更新,与数据挖掘有关,主要是为大数据分析和领导决策使用。

     

    数据仓库架构图

    简介

    ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程,目的是将企业中的分散、零乱、标准不统一的数据整合到一起,为企业的决策提供分析依据, ETL是BI(商业智能)项目重要的一个环节。

    ETL与ELT

    ETL所描述的过程,一般常见的作法包含ETL或是ELT(Extract-Load-Transform),并且混合使用。通常愈大量的数据、复杂的转换逻辑、目的端为较强运算能力的数据库,愈偏向使用ELT,以便运用目的端数据库的平行处理能力。

    工具

    ETL(orELT)的流程可以用任何的编程语言去开发完成,由于ETL是极为复杂的过程,而手写程序不易管理,有愈来愈多的企业采用工具协助ETL的开发,并运用其内置的metadata功能来存储来源与目的的对应(mapping)以及转换规则。

    工具可以提供较强大的连接功能(connectivity)来连接来源端及目的端,开发人员不用去熟悉各种相异的平台及数据的结构,亦能进行开发。

    当然,为了这些好处,付出的代价便是金钱。ETL常用的三种工具——Datastage,Informatica,Kettle:

    1、Datastage

    IBM公司的商业软件,最专业的ETL工具,但同时价格不菲,适合大规模的ETL应用。

    使用难度:★★★★

    2、Informatica

    商业软件,相当专业的ETL工具。价格上比Datastage便宜一点,也适合大规模的ETL应用。

    使用难度:★★

    3、Kettle

    免费,最著名的开源产品,是用纯java编写的ETL工具,只需要JVM环境即可部署,可跨平台,扩展性好。

    使用难度:★★

    参见

    • 数据仓库(DW)

    • 决策支持系统(DSS)

    • 在线分析处理(OLAP)

    • 数据挖掘(DM)

    • 商业智能(BI)

    ETL架构级别概述

            ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程,目的是将企业中的分散、零乱、标准不统一的数据整合到一起,为企业的决策提供分析依据。 ETL是BI项目重要的一个环节。 通常情况下,在BI项目中ETL会花掉整个项目至少1/3的时间,ETL设计的好坏直接关接到BI项目的成败。       

      ETL的设计分三部分:数据抽取、数据的清洗转换、数据的加载。在设计ETL的时候我们也是从这三部分出发。数据的抽取是从各个不同的数据源抽取到ODS(Operational Data Store,操作型数据存储)中——这个过程也可以做一些数据的清洗和转换),在抽取的过程中需要挑选不同的抽取方法,尽可能的提高ETL的运行效率。ETL三个部分中,花费时间最长的是“T”(Transform,清洗、转换)的部分,一般情况下这部分工作量是整个ETL的2/3。数据的加载一般在数据清洗完了之后直接写入DW(Data Warehousing,数据仓库)中去。

      ETL的实现有多种方法,常用的有三种。一种是借助ETL工具(如Oracle的OWB、SQL Server 2000的DTS、SQL Server2005的SSIS服务、Informatic等)实现,一种是SQL方式实现,另外一种是ETL工具和SQL相结合。前两种方法各有各的优缺点,借助工具可以快速的建立起ETL工程,屏蔽了复杂的编码任务,提高了速度,降低了难度,但是缺少灵活性。SQL的方法优点是灵活,提高ETL运行效率,但是编码复杂,对技术要求比较高。第三种是综合了前面二种的优点,会极大地提高ETL的开发速度和效率。

     数据的抽取(Extract)

      这一部分需要在调研阶段做大量的工作,首先要搞清楚数据是从几个业务系统中来,各个业务系统的数据库服务器运行什么DBMS,是否存在手工数据,手工数据量有多大,是否存在非结构化的数据等等,当收集完这些信息之后才可以进行数据抽取的设计。

      1、对于与存放DW的数据库系统相同的数据源处理方法

      这一类数据源在设计上比较容易。一般情况下,DBMS(SQLServer、Oracle)都会提供数据库链接功能,在DW数据库服务器和原业务系统之间建立直接的链接关系就可以写Select 语句直接访问。

      2、对于与DW数据库系统不同的数据源的处理方法

      对于这一类数据源,一般情况下也可以通过ODBC的方式建立数据库链接——如SQL Server和Oracle之间。如果不能建立数据库链接,可以有两种方式完成,一种是通过工具将源数据导出成.txt或者是.xls文件,然后再将这些源系统文件导入到ODS中。另外一种方法是通过程序接口来完成。

      3、对于文件类型数据源(.txt,.xls),可以培训业务人员利用数据库工具将这些数据导入到指定的数据库,然后从指定的数据库中抽取。或者还可以借助工具实现。

      4、增量更新的问题

      对于数据量大的系统,必须考虑增量抽取。一般情况下,业务系统会记录业务发生的时间,我们可以用来做增量的标志,每次抽取之前首先判断ODS中记录最大的时间,然后根据这个时间去业务系统取大于这个时间所有的记录。利用业务系统的时间戳,一般情况下,业务系统没有或者部分有时间戳。

    数据的清洗转换(Cleaning、Transform)

      一般情况下,数据仓库分为ODS、DW两部分。通常的做法是从业务系统到ODS做清洗,将脏数据和不完整数据过滤掉,在从ODS到DW的过程中转换,进行一些业务规则的计算和聚合。

      1、 数据清洗

      数据清洗的任务是过滤那些不符合要求的数据,将过滤的结果交给业务主管部门,确认是否过滤掉还是由业务单位修正之后再进行抽取。

    不符合要求的数据主要是有不完整的数据、错误的数据、重复的数据三大类。

      (1)不完整的数据:这一类数据主要是一些应该有的信息缺失,如供应商的名称、分公司的名称、客户的区域信息缺失、业务系统中主表与明细表不能匹配等。对于这一类数据过滤出来,按缺失的内容分别写入不同Excel文件向客户提交,要求在规定的时间内补全。补全后才写入数据仓库。

      (2)错误的数据:这一类错误产生的原因是业务系统不够健全,在接收输入后没有进行判断直接写入后台数据库造成的,比如数值数据输成全角数字字符、字符串数据后面有一个回车操作、日期格式不正确、日期越界等。这一类数据也要分类,对于类似于全角字符、数据前后有不可见字符的问题,只能通过写SQL语句的方式找出来,然后要求客户在业务系统修正之后抽取。日期格式不正确的或者是日期越界的这一类错误会导致ETL运行失败,这一类错误需要去业务系统数据库用SQL的方式挑出来,交给业务主管部门要求限期修正,修正之后再抽取。

      (3)重复的数据:对于这一类数据——特别是维表中会出现这种情况——将重复数据记录的所有字段导出来,让客户确认并整理。

      数据清洗是一个反复的过程,不可能在几天内完成,只有不断的发现问题,解决问题。对于是否过滤,是否修正一般要求客户确认,对于过滤掉的数据,写入Excel文件或者将过滤数据写入数据表,在ETL开发的初期可以每天向业务单位发送过滤数据的邮件,促使他们尽快地修正错误,同时也可以做为将来验证数据的依据。数据清洗需要注意的是不要将有用的数据过滤掉,对于每个过滤规则认真进行验证,并要用户确认。

      2、 数据转换

      数据转换的任务主要进行不一致的数据转换、数据粒度的转换,以及一些商务规则的计算。

      (1)不一致数据转换:这个过程是一个整合的过程,将不同业务系统的相同类型的数据统一,比如同一个供应商在结算系统的编码是XX0001,而在CRM中编码是YY0001,这样在抽取过来之后统一转换成一个编码。

      (2)数据粒度的转换:业务系统一般存储非常明细的数据,而数据仓库中数据是用来分析的,不需要非常明细的数据。一般情况下,会将业务系统数据按照数据仓库粒度进行聚合。

      (3)商务规则的计算:不同的企业有不同的业务规则、不同的数据指标,这些指标有的时候不是简单的加加减减就能完成,这个时候需要在ETL中将这些数据指标计算好了之后存储在数据仓库中,以供分析使用。

    ETL日志、警告发送

      1、 ETL日志

      ETL日志分为三类。

    一类是执行过程日志,这一部分日志是在ETL执行过程中每执行一步的记录,记录每次运行每一步骤的起始时间,影响了多少行数据,流水账形式。

    一类是错误日志,当某个模块出错的时候写错误日志,记录每次出错的时间、出错的模块以及出错的信息等。

    第三类日志是总体日志,只记录ETL开始时间、结束时间是否成功信息。如果使用ETL工具,ETL工具会自动产生一些日志,这一类日志也可以作为ETL日志的一部分。

    记录日志的目的是随时可以知道ETL运行情况,如果出错了,可以知道哪里出错。

      2、 警告发送

      如果ETL出错了,不仅要形成ETL出错日志,而且要向系统管理员发送警告。发送警告的方式多种,一般常用的就是给系统管理员发送邮件,并附上出错的信息,方便管理员排查错误。

      ETL是BI项目的关键部分,也是一个长期的过程,只有不断的发现问题并解决问题,才能使ETL运行效率更高,为BI项目后期开发提供准确与高效的数据。

    后记

         做数据仓库系统,ETL是关键的一环。说大了,ETL是数据整合解决方案,说小了,就是倒数据的工具。回忆一下工作这么长时间以来,处理数据迁移、转换的工作倒还真的不少。但是那些工作基本上是一次性工作或者很小数据量。可是在数据仓库系统中,ETL上升到了一定的理论高度,和原来小打小闹的工具使用不同了。究竟什么不同,从名字上就可以看到,人家已经将倒数据的过程分成3个步骤,E、T、L分别代表抽取、转换和装载。

    其实ETL过程就是数据流动的过程,从不同的数据源流向不同的目标数据。但在数据仓库中,

    ETL特点

        一是数据同步,它不是一次性倒完数据就拉到,它是经常性的活动,按照固定周期运行的,甚至现在还有人提出了实时ETL的概念。

        二是数据量,一般都是巨大的,值得你将数据流动的过程拆分成E、T和L。

        现在有很多成熟的工具提供ETL功能,且不说他们的好坏。从应用角度来说,ETL的过程其实不是非常复杂,这些工具给数据仓库工程带来和很大的便利性,特别是开发的便利和维护的便利。但另一方面,开发人员容易迷失在这些工具中。举个例子,VB是一种非常简单的语言并且也是非常易用的编程工具,上手特别快,但是真正VB的高手有多少?微软设计的产品通常有个原则是“将使用者当作傻瓜”,在这个原则下,微软的东西确实非常好用,但是对于开发者,如果你自己也将自己当作傻瓜,那就真的傻了。ETL工具也是一样,这些工具为我们提供图形化界面,让我们将主要的精力放在规则上,以期提高开发效率。从使用效果来说,确实使用这些工具能够非常快速地构建一个job来处理某个数据,不过从整体来看,并不见得他的整体效率会高多少。问题主要不是出在工具上,而是在设计、开发人员上。他们迷失在工具中,没有去探求ETL的本质。可以说这些工具应用了这么长时间,在这么多项目、环境中应用,它必然有它成功之处,它必定体现了ETL的本质。如果我们不透过表面这些工具的简单使用去看它背后蕴涵的思想,最终我们作出来的东西也就是一个个独立的job,将他们整合起来仍然有巨大的工作量。大家都知道“理论与实践相结合”,如果在一个领域有所超越,必须要在理论水平上达到一定的高度.

    案例参考

    亚马逊ETL

     

    展开全文
  • etl数据仓库技术

    千次阅读 2017-02-09 08:56:55
    ETL的考虑     做数据仓库系统,ETL是关键的一环。说大了,ETL是数据整合解决方案,说小了,...可是在数据仓库系统中,ETL上升到了一定的理论高度,和原来小打小闹的工具使用不同了。究竟什么不同,从名字
  • ├─02-生成明细表数据.avi ├─02-电商行业与电商系统介绍.avi ├─02-网站流量日志获取方式介绍.mp4 ├─02-订单时间维度指标需求分析.avi ├─03-cube优化-聚合组.avi ├─03-kylin的优势以及数据流程图.avi ├─...
  • 数据仓库ETL

    2018-04-20 10:59:57
    数据仓库 ETL,实例演示,技术原理讲解。 数据仓库 ETL,实例演示,技术原理讲解。 数据仓库 ETL,实例演示,技术原理讲解。 数据仓库 ETL,实例演示,技术原理讲解。
  • ETL数据仓库工程师笔试题和面试题整理

    万次阅读 多人点赞 2020-05-01 01:08:22
    [这个人怎么那么多面试题] 如何排重(考察sql),就是group by xxx having count(1)>1。 shell的一些基本命令,如tail、seed、crontab等 如如何查看文件第几行?如何动态查看文件?...还有就是数据仓库...
  • 数据仓库ETl工具箱8.pdf
  • 数据仓库ETL模式详解.ppt
  • datastage etl 数据仓库

    2010-03-29 12:35:18
    数据仓库软件,etl工具ascential.datastage.v7.5.1下载
  • ETL 开发规划2.1 第 1 步:设计高层规划2.2 第 2 步:选择 ETL 工具2.3 第 3 步:开发默认策略2.4 第 4 步:按照目标表钻取数据2.5 开发 ETL 规范文档三. 开发一次性的历史加载过程3.1 第 5 步:用历史数据填充维度...
  • 商场数据仓库ETL系统架构设计.pdf商场数据仓库ETL系统架构设计.pdf商场数据仓库ETL系统架构设计.pdf商场数据仓库ETL系统架构设计.pdf商场数据仓库ETL系统架构设计.pdf商场数据仓库ETL系统架构设计.pdf商场数据仓库...
  • ETL数据整合与处理(Kettle)》教学教案 —05高级转换.pdf《ETL数据整合与处理(Kettle)》教学教案 —05高级转换.pdf《ETL数据整合与处理(Kettle)》教学教案 —05高级转换.pdf《ETL数据整合与处理(Kettle)》教学教案 ...
  • ETL及其在数据仓库创建中的重要作用,王咏梅,,介绍了数据仓库ETL(Extract-Transform-Load 的缩写,数据抽取(Extract)、 转换(Transform)、装载(Load)的过程)以及ETL 工具的选择。
  • ETL一次较常用在数据仓库,但其对象并不限于数据仓库数据仓库 数据仓库,英文名称为Data WareHouse,可简写为DW或DWH。数据仓库,是为企业所有级别的决策制定过程,提供类型数据支持的战略集合。它是单个数据存储...
  • 清晰的数据仓库ETL流程(有干货)

    千次阅读 2020-10-22 20:25:25
    数据仓库ETL流程整体思路具体步骤总结 整体思路 要想开发一个完整的数据仓库etl流程必须先从整个项目的结构层次入手,逐个剖析每一层的程序,在纸上画出整个的流程图,剩下的就自然而然攻克了,下面请看具体的步骤。...
  • 数据仓库中的ETL,到底是什么

    千次阅读 2021-03-15 16:01:15
    ETL是建立数据仓库最重要的处理过程,是Extract、Transform、Load三个英文单词首字母的简写,中文意为抽取、转换、装载。” 数据的ETL过程 在日常的开发或交流中,会多次提到了ETL一词,它是Extract、...
  • ETL数据仓库技术

    2019-10-10 08:38:27
    1、ETL是什么? ETL,是英文Extract-Transform-Load的缩写...ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程,目的是将企业中的分散、零乱、标准不统一的数据整合到一起,为企业的决策提供分...
  • 最近做的项目用到了ETL工具Kettle,这个工具相当好用,可以将各种类型数据作为数据流,经过处理后再生成各种类型的数据。正如其名“水壶”,将各个地方的水倒进水壶里,再用水壶倒入不同的容器。 本教程主要介绍...
  • 数据仓库分层架构 按照数据流入流出的过程,数据仓库架构可分为三层: 1. 源数据层(ODS层) 作用:对接数据源,将数据源的数据完整的拷贝在数仓中 2. 数据仓库层(DW层)作用: 用于进行数据分析操作 3. 数据应用层(DA...
  • 对此, 在对数据仓库ETL 任务调度规则进行归纳总结的基础上, 以数据仓库总的ETL 完成时间最短为目标, 建立了数据仓库ETL 任务调度模型. 同时结合问题的特点, 采用同层划分的思想, 提出基于同层划分遗传算法求解问题...
  • 数据仓库中的ETL和元数据,数据仓库中的ETL和元数据,数据仓库中的ETL和元数据,数据仓库中的ETL和元数据
  • 商场数据仓库ETL系统架构设计.docx商场数据仓库ETL系统架构设计.docx商场数据仓库ETL系统架构设计.docx商场数据仓库ETL系统架构设计.docx商场数据仓库ETL系统架构设计.docx商场数据仓库ETL系统架构设计.docx商场数据...
  • 数据仓库ETL

    2018-05-21 07:30:16
    数据仓库和ETL数据仓库ETL数据仓库ETL数据仓库和ETL

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 29,200
精华内容 11,680
关键字:

etl 数据仓库