OLAP的第一步就是从业务系统中抽取数据到数据仓库系统。
除了ETL工具Kettle,也可以使用PL/SQL
创建Source表,模拟业务系统的数据表。
create table source as select sys_guid() id ,o.* from dba_objects o where rownum<1000;
alter table source add constraint PK_source primary key (id);
创建Target表,模拟数据仓库中的表。
create table target as select * from source where 1=0;
alter table target add constraint PK_target primary key (id);
创建DB LINK,remote模拟业务系统的数据库
create database link remote connect to username identified by xxxxxx using 'remote';
因为业务系统的数据是变化的,相较于数据仓库的表,他可能更新了一些数据,也可能修改了一些数据。
比对业务表和数据仓库表的数据,
如果ID相同,并且数据有变化
则根据ID更新数据仓库的表(target)
如果业务系统的数据ID(source),还没有出现在数据仓库的表中(target)
则在数据仓库的表中新增这个记录。
本质都是Oracle Merge的功能,只不过尝试另外几种方法。
1.merge
2.全局临时表。
首先将远程业务系统的数据放入临时表,
然后根据ID更新数据,如果数据的内容没有变化,则不更新。
最后插入业务系统中新建的数据。
3.集合处理
为了简单,没有进行内容变化的判断
4.内联视图更新
没有写更新后插入的步骤,插入的实现是相同的。
5.Minus
先插入业务表中新增的记录,然后对比修改。
除了ETL工具Kettle,也可以使用PL/SQL
创建Source表,模拟业务系统的数据表。
create table source as select sys_guid() id ,o.* from dba_objects o where rownum<1000;
alter table source add constraint PK_source primary key (id);
创建Target表,模拟数据仓库中的表。
create table target as select * from source where 1=0;
alter table target add constraint PK_target primary key (id);
创建DB LINK,remote模拟业务系统的数据库
create database link remote connect to username identified by xxxxxx using 'remote';
因为业务系统的数据是变化的,相较于数据仓库的表,他可能更新了一些数据,也可能修改了一些数据。
比对业务表和数据仓库表的数据,
如果ID相同,并且数据有变化
则根据ID更新数据仓库的表(target)
如果业务系统的数据ID(source),还没有出现在数据仓库的表中(target)
则在数据仓库的表中新增这个记录。
本质都是Oracle Merge的功能,只不过尝试另外几种方法。
1.merge
- mergeintotarget tusing(select*fromsource@remote)s
- on(t.id=s.id)
- when matched
- then
- updateset t.owner=s.owner,
- t.object_name=s.object_name,
- t.subobject_name=s.subobject_name,
- t.object_id=s.object_id,
- t.data_object_id=s.data_object_id,
- t.object_type=s.object_type,
- t.created=s.created,
- t.last_ddl_time=s.last_ddl_time,
- t.timestamp=s.timestamp,
- t.status=s.status,
- t.temporary=s.temporary,
- t.generated=s.generated,
- t.secondary=s.secondary,
- t.namespace=s.namespace,
- t.edition_name=s.edition_name
- whennotmatched
- then
- insertvalues
- (
- s.id,
- s.owner,
- s.object_name,
- s.subobject_name,
- s.object_id,
- s.data_object_id,
- s.object_type,
- s.created,
- s.last_ddl_time,
- s.timestamp,
- s.status,
- s.temporary,
- s.generated,
- s.secondary,
- s.namespace,
- s.edition_name
- );
首先将远程业务系统的数据放入临时表,
然后根据ID更新数据,如果数据的内容没有变化,则不更新。
最后插入业务系统中新建的数据。
- createglobal temporarytabletmp
- oncommit preserverows
- as
- select*fromtargetwhere1=0;
-
- insertintotmpselect*fromsource@remote;
-
- updatetarget tset
- (
- t.owner,
- t.object_name,
- t.subobject_name,
- t.object_id,
- t.data_object_id,
- t.object_type,
- t.created,
- t.last_ddl_time,
- t.timestamp,
- t.status,
- t.temporary,
- t.generated,
- t.secondary,
- t.namespace,
- t.edition_name
- )
- =
- (select
- tmp.owner,
- tmp.object_name,
- tmp.subobject_name,
- tmp.object_id,
- tmp.data_object_id,
- tmp.object_type,
- tmp.created,
- tmp.last_ddl_time,
- tmp.timestamp,
- tmp.status,
- tmp.temporary,
- tmp.generated,
- tmp.secondary,
- tmp.namespace,
- tmp.edition_name
- fromtmpwheret.id=tmp.id)
- whereexists(
- select*fromtmpwheretmp.id=t.idandnot(
- tmp.owner=t.ownerand
- tmp.object_name=t.object_nameand
- tmp.subobject_name=t.subobject_nameand
- tmp.object_id=t.object_idand
- tmp.data_object_id=t.data_object_idand
- tmp.object_type=t.object_typeand
- tmp.created=t.createdand
- tmp.last_ddl_time=t.last_ddl_timeand
- tmp.timestamp=t.timestampand
- tmp.status=t.statusand
- tmp.temporary=t.temporaryand
- tmp.generated=t.generatedand
- tmp.secondary=t.secondaryand
- tmp.namespace=t.namespaceand
- tmp.edition_name=t.edition_name
- )
- );
-
- insertintotarget
- select*fromtmpwherenotexists(
- select*fromtarget twheret.id=tmp.id);
为了简单,没有进行内容变化的判断
- declare
- typetabistableoftarget%rowtype;
- l_rowtab;
- cursor curisselect*fromsource@remote;
- begin
- open cur;
- fetch cur bulk collectintol_row;
- close cur;
- forall iin1..l_row.count
- updatetargetsetrow=l_row(i)whereid=l_row(i).id;
- insertintotargetselect*fromsource@remote s
- wherenotexists(select*fromtarget twheret.id=s.id);
- commit;
- end;
- /
没有写更新后插入的步骤,插入的实现是相同的。
- update(
- select
- s.id s1,
- s.owner s2,
- s.object_name s3,
- s.subobject_name s4,
- s.object_id s5,
- s.data_object_id s6,
- s.object_type s7,
- s.created s8,
- s.last_ddl_time s9,
- s.timestamp s10,
- s.status s11,
- s.temporary s12,
- s.generated s13,
- s.secondary s14,
- s.namespace s15,
- s.edition_name s16,
- t.id t1,
- t.owner t2,
- t.object_name t3,
- t.subobject_name t4,
- t.object_id t5,
- t.data_object_id t6,
- t.object_type t7,
- t.created t8,
- t.last_ddl_time t9,
- t.timestamp t10,
- t.status t11,
- t.temporary t12,
- t.generated t13,
- t.secondary t14,
- t.namespace t15,
- t.edition_name t16
- fromtarget t inner join source@remote son(s.id=t.id)
- where
- not
- (
- s.owner=t.ownerand
- s.object_name=t.object_nameand
- s.subobject_name=t.subobject_nameand
- s.object_id=t.object_idand
- s.data_object_id=t.data_object_idand
- s.object_type=t.object_typeand
- s.created=t.createdand
- s.last_ddl_time=t.last_ddl_timeand
- s.timestamp=t.timestampand
- s.status=t.statusand
- s.temporary=t.temporaryand
- s.generated=t.generatedand
- s.secondary=t.secondaryand
- s.namespace=t.namespaceand
- s.edition_name=t.edition_name
- )
- )
- set
- t1=s1,
- t2=s2,
- t3=s3,
- t4=s4,
- t5=s5,
- t6=s6,
- t7=s7,
- t8=s8,
- t9=s9,
- t10=s10,
- t11=s11,
- t12=s12,
- t13=s13,
- t14=s14,
- t15=s15,
- t16=s16
- ;
先插入业务表中新增的记录,然后对比修改。
- declare
- typetabistableoftarget%rowtype;
- l_rowtab;
- cursor curisselect*fromsource@remoteminusselect*fromtarget;
- begin
- insertintotargetselect*fromsource@remote s
- wherenotexists(select*fromtarget twheret.id=s.id);
- open cur;
- fetch cur bulk cllectintol_row;
- close cur;
- forall iin1..l_row.count
- updatetargetsetrow=low(i)whereid=l_row(i).id;
- commit;
- end;
- /