1、www.si-胡红强胡红强神州数码思特奇信息技术股份有限公司神州数码思特奇信息技术股份有限公司20092009年年0909月月0101日日ETL加载模式概述加载模式概述2 23 3ETL加载模式举例加载模式举例数据仓库概述数据仓库概述1 1数据仓库的概念由被誉为“数据仓库之父”的WilliamH.Inmon博士提出的:数据仓库是一个面向主题的、集成的、随时间变化的、信息相:数据仓库是一个面向主题的、集成的、随时间变化的、信息相对稳定的数据集合,它用于对企业管理和决策提供支持。对稳定的数据集合,它用于对企业管理和决策提供支持。所谓主题:所谓主题:是指用户使用数据仓库进行决策时所关心的重点方面,如
2、:客户、产品、账务、事件、服务使用、资源、客户服务、地域等;所谓面向主题,是指数据仓库内的信息是按主题进行组织的,而不是像业务支撑系统那样是按照业务功能进行组织的;分析和明确企业所涵盖的业务范围,并且对企业业务进行高度概括性的描述,把密切相关业务对象进行归类,它没有统一的标准,主要根据设计者的经验。不同的行业会有不同的主题域划分方式。所谓集成:所谓集成:是指数据仓库中的信息不是从各个业务系统中简单抽取出来的,而是经过一系列加工、整理和汇总的过程,必须消除源数据中的不一致性,因此数据仓库中的信息是关于整个企业的一致的全局信息;各个业务系统可能由不同的厂家独立承建,它们的数据模型设计、编码规则等都
3、是不同的,这些数据加载到数据仓库之后,需要进行一个加工转换的过程。BOSS系统中,那地市的编码来说,CRM系统是的编码为1、2等,而BILLING系统可能根据长途区号来编码:451、452等,那么在数据仓库中,需要将各个业务系统中相同含义的数据通过规则映射为同一个编码。所谓随时间变化:所谓随时间变化:是指数据仓库内的信息并不只是反映企业当前的信息,而是记录了从过去某一时点到当前各个阶段的信息。通过这些信息,可以对企业的发展历程和未来趋势做出定量分析和预测;业务系统只记录当前的最新状态,数据仓库中可以反映一个用户的状态变化过程以及分析变化的原因。所谓信息相对稳定:所谓信息相对稳定:是指一旦某个数
4、据进入数据仓库以后,一般很少进行修改,更多的是对信息进行查询操作,通常只需要进行定期的加载和刷新。数据仓库中几乎很少对历史数据进行修改,6月2日用户单停,那么这天的数据就是这个状态;而对于业务系统中,它总是最新的状态,所以数据库的中的数据总是不断变化的。操作型系统操作型系统数据仓库系统数据仓库系统面向应用面向主题详细的数据综合的或汇总的为日常工作服务为管理决策服务可更新不更新事务处理驱动分析驱动非冗余数据经常有冗余一次处理很小的数据一次处理大量的数据高访问性适量的访问度当前数据历史数据ETL模式加载概述模式加载概述2 23 3ETL加载模式举例加载模式举例数据仓库概述数据仓库概述1 1镜像增量
5、:镜像增量:数据仓库数据具有生效日期字段以保存数据的历史信息,而源数据不保留历史并且每天都可能被更新。因此,只能将新的镜像数据与上次加载的数据的镜像进行比较,找出变更部分,更新历史数据被更新记录的生效终止日期,并添加变更后的数据。大多数源数据中需保存历史信息的维表;事件增量:事件增量:每一个记录是一个新的事件,相互之间没有必然的联系,新记录不是对原有记录数值的变更,记录包括时间字段,可以通过时间字段将新增数据抽取出来加载到数据库中;全量数据:全量数据:数据仓库数据表中只包括最新的数据,每次加载均删除原有数据,然后完全加载最新的源数据。这种模式下,数据抽取程序抽取源数据中的所有记录,在加载前,将
6、目标数据表清空,然后加载所有记录。为提高删除数据的速度,一般是采用truncate清空数据表。如本系统中的入库当前信息表采用此种模式;ETL加载模式概述加载模式概述2 23 3ETL加载模式举例加载模式举例数据仓库概述数据仓库概述1 1对于镜像增量数据一般我们有两种加载方式,增量抽取和全量抽取,对于镜像增量数据一般我们有两种加载方式,增量抽取和全量抽取,一般做增量抽取数据则会做镜像比较加载数据;如果做全量抽取,一一般做增量抽取数据则会做镜像比较加载数据;如果做全量抽取,一般就会做全量加载数据。全量加载的数据情况同上表数据。般就会做全量加载数据。全量加载的数据情况同上表数据。数据抽取方式数据抽取
7、方式建议加载方式建议加载方式优缺点优缺点数据量数据量适用数据库适用数据库增量抽取镜像比较冗余数据少,可以节省大量空间;处理较复杂小各类数据库大TERADATA、DB2全量抽取全量刷新大量冗余数据,浪费很多空间;处理方式简单不区分各类数据库,TERADATA在大数据量情况下还可以做镜像比较假如有如下数据业务系统一个时间段的数据为:假如有如下数据业务系统一个时间段的数据为:A为正常用户为正常用户,B为单停用户,为单停用户,C为双停用户,为双停用户,a为离网用户,用为离网用户,用户户ID为主键;为主键;数据周期数据周期用户用户ID用户状态用户状态状态变更时间状态变更时间200907011001A20
8、09-05-21 14:09:19200907011002A2009-03-20 09:40:20200907021001B2009-07-02 11:13:45200907021002A2009-03-20 09:40:20200907041001C2009-07-04 02:00:00200907041002A2009-03-20 09:40:20200907051001A2009-07-05 10:23:43200907051002a2009-07-05 15:42:10200907051003A2009-07-05 11:36:28200907051004A2009-07-05 13:
9、11:40镜像数据查询方法:镜像数据查询方法:SELECT*FROM TAB WHERE DAT_EFF_DATE20090701;20090701查询到表的全量数据:20090702的数据变为,20090703的数据没有发生变化,所以与前一天的数据是完全一致的:用户用户ID用户状态用户状态状态变更时间状态变更时间数据生效日期数据生效日期数据失效日期数据失效日期1001A2009-05-21 14:09:1920090701299912311002A2009-03-20 09:40:202009070129991231用户用户ID用户状态用户状态状态变更时间状态变更时间数据生效日期数据生效日期
10、数据失效日期数据失效日期1001A2009-05-21 14:09:1920090701200907021002A2009-03-20 09:40:2020090701299912311001B2009-07-02 11:13:452009070229991231镜像数据查询方法:镜像数据查询方法:SELECT*FROM TAB WHERE DAT_EFF_DATE20090704;20090704数据处理之后查询到表的全量数据:处理方式:1、20090704的增量数据为上面第四条数据(先找出变化的数据);2、更新该增量数据用户ID在TAB表的数据失效日期为当日(该日的全部有效数据进行比较);
11、3、然后插入当日增量数据,其数据生效日期置为当日,数据失效日期置为日期最大值,29991231;当日更新的数据为UPDATE为数据失效日期为当日以及插入生效日期数据位当日,所以在处理之前,需要将该数据还原:UPDATE TAB SET DAT_EXP_DATE=29991231 WHERE DAT_EXP_DATE=20090704;DELETE FROM TAB WHERE DAT_EFF_DATE=20090704;用户用户ID用户状态用户状态状态变更时间状态变更时间数据生效日期数据生效日期数据失效日期数据失效日期1001A2009-05-21 14:09:1920090701200907
12、021002A2009-03-20 09:40:2020090701299912311001B2009-07-02 11:13:4520090702200907041001C2009-07-04 02:00:002009070429991231用户用户ID用户状态用户状态状态变更时间状态变更时间数据生效日期数据生效日期数据失效日期数据失效日期1001A2009-05-21 14:09:1920090701299912311002A2009-03-20 09:40:202009070129991231用户用户ID用户状态用户状态状态变更时间状态变更时间数据生效日期数据生效日期数据失效日期数据失效
13、日期1001A2009-05-21 14:09:1920090701200907021002A2009-03-20 09:40:2020090701299912311001B2009-07-02 11:13:452009070229991231用户用户ID用户状态用户状态状态变更时间状态变更时间数据生效日期数据生效日期数据失效日期数据失效日期1001A2009-05-21 14:09:1920090701200907021002A2009-03-20 09:40:2020090701299912311001B2009-07-02 11:13:4520090702200907041001C200
14、9-07-04 02:00:002009070429991231Teradata:用户用户ID用户状态用户状态状态变更时间状态变更时间数据生效日期数据生效日期数据失效日期数据失效日期1001A2009-05-21 14:09:1920090701200907021002A2009-03-20 09:40:2020090701200907051001B2009-07-02 11:13:4520090702200907041001C2009-07-04 02:00:0020090704200907051001A2009-07-05 10:23:4320090705299912311002a2009
15、-07-05 15:42:1020090705299912311003A2009-07-05 11:36:2820090705299912311004A2009-07-05 13:11:402009070529991231一般来说,镜像比较增量数据加载分为实时(准实时)的数据加载或一般来说,镜像比较增量数据加载分为实时(准实时)的数据加载或日增量数据加载。日增量数据加载。比如,在天津电信比如,在天津电信ODS项目中,对于产品域以及参与人域的部分数据项目中,对于产品域以及参与人域的部分数据,我们需要做实时加载,在,我们需要做实时加载,在ODS系统中,能够准实时的反应业务系统系统中,能够准实时的反
16、应业务系统中的一些新增或变更的数据。实时镜像比较数据的处理方法与一般增中的一些新增或变更的数据。实时镜像比较数据的处理方法与一般增量数据方法相同。下为实时数据加载例子:量数据方法相同。下为实时数据加载例子:用户用户ID用户状态用户状态状态变更时间状态变更时间数据生效日期数据生效日期数据失效日期数据失效日期1001A2009-05-21 14:09:1920090701200907021002A2009-03-20 09:40:2020090701299912311001B2009-07-05 08:13:4520090705200907051001A2009-07-05 10:23:43200
17、9070529991231一般来说,对于镜像比较的数据,我们需要准确的选取表的主键(或一般来说,对于镜像比较的数据,我们需要准确的选取表的主键(或联合主键)来进行数据比较。对于上述的用户状态数据表,它的主键联合主键)来进行数据比较。对于上述的用户状态数据表,它的主键为用户为用户ID,任何一个时候它的记录都是唯一的,对应的用户状态也是,任何一个时候它的记录都是唯一的,对应的用户状态也是唯一的。对应我们的镜像表,选取任何一个数据日期,其用户唯一的。对应我们的镜像表,选取任何一个数据日期,其用户ID也是也是唯一的。(唯一的。(DAT_EFF_DATE?)对于实时增量(或日增量)的数据一般在月初需要做
18、一次全量比较,对于实时增量(或日增量)的数据一般在月初需要做一次全量比较,这是为了防止实时抽取的数据产生纰漏,没有正常抽取,后期也不会这是为了防止实时抽取的数据产生纰漏,没有正常抽取,后期也不会补抽。补抽。全量比较的数据比对的字段最好是尽量多的字段进行比较,才能找到全量比较的数据比对的字段最好是尽量多的字段进行比较,才能找到已经发生变化的数据,但字段越多越影响比较的性能。所以,一般来已经发生变化的数据,但字段越多越影响比较的性能。所以,一般来说,全量比较需要选择主键和状态变更日期进行比较,如果没有状态说,全量比较需要选择主键和状态变更日期进行比较,如果没有状态变更日期,就选择表中容易发生变化的
19、字段进行比较。但是,比较的变更日期,就选择表中容易发生变化的字段进行比较。但是,比较的时候,字段不要为空,否则,比较容易发生错误。时候,字段不要为空,否则,比较容易发生错误。定义术语:事实表(定义术语:事实表(TRG):需要处理的数据的目标表,接口表():需要处理的数据的目标表,接口表(INF):需要处理的数据的数据源表;将全量表接口表发生变化的数):需要处理的数据的数据源表;将全量表接口表发生变化的数据处理到事实表中。据处理到事实表中。1、将在、将在INF表中存在而在表中存在而在TRG表中不存在的数据插入到临时表表中不存在的数据插入到临时表ADD2、将在、将在TRG表中存在而在表中存在而在I
20、NF表中不存在的数据插入到临时表表中不存在的数据插入到临时表DEL3、用、用DEL表的数据将表的数据将TRG表中相对应的数据的失效日期置为当日;表中相对应的数据的失效日期置为当日;4、将、将ADD表的数据插入到表的数据插入到TRG中,生效日期置为当日;中,生效日期置为当日;在做在做ETL的过程中,一定要理解和注意对于数据的时间日期,这是一的过程中,一定要理解和注意对于数据的时间日期,这是一个非常重要的内容。在天津电信个非常重要的内容。在天津电信ODS项目中,我们就发现了一些问题项目中,我们就发现了一些问题。对于日数据来说,一般数据日期是系统日期的前一天;比如,一般是对于日数据来说,一般数据日期是系统日期的前一天;比如,一般是凌晨时候抽取前一天的数据;凌晨时候抽取前一天的数据;但在实施数据抽取,一定要注意,数据日期要用抽取的时候的数据日但在实施数据抽取,一定要注意,数据日期要用抽取的时候的数据日期,而不能是前一天的日期,否则会造成数据日期与实际情况不符。期,而不能是前一天的日期,否则会造成数据日期与实际情况不符。