drop table if exists test;
create temp table test
(
id varchar(100)
,name varchar(100)
,state varchar(100)
,start_date varchar(100)
,end_date varchar(100)
)
distributed by (id);
comment on table test is '员工状态表';
comment on column test.id is '员工的id';
comment on column test.name is '员工的name';
comment on column test.state is '员工的状态';
comment on column test.start_date is '开始时间';
comment on column test.end_date is '结束时间';
insert into test values('1','A','1','20150101','99991231');
insert into test values('2','B','1','20150101','99991231');
insert into test values('3','C','1','20150101','99991231');
insert into test values('4','D','1','20150101','99991231');
drop table if exists org_test;
create temp table org_test
(
id varchar(100)
,name varchar(100)
,state varchar(100)
)
distributed by (id);
comment on table org_test is '员工状态表';
comment on column org_test.id is '员工的id';
comment on column org_test.name is '员工的name';
comment on column org_test.state is '员工的状态';
insert into org_test values('1','A','1');
insert into org_test values('2','B','0');
insert into org_test values('4','D','0');
insert into org_test values('5','E','1');
select * from org_test;
--创建三张临时表
drop table if exists test1;
drop table if exists test2;
drop table if exists test3;
create temp table test1--存放源数据
(
id varchar(100)
,name varchar(100)
,state varchar(100)
,start_date varchar(100)
,end_date varchar(100)
)
distributed by (id);
create temp table test2--存放更新和新增的数据
(
id varchar(100)
,name varchar(100)
,state varchar(100)
,start_date varchar(100)
,end_date varchar(100)
)
distributed by (id);
create temp table test3--存放没有变更的数据
(
id varchar(100)
,name varchar(100)
,state varchar(100)
,start_date varchar(100)
,end_date varchar(100)
)
distributed by (id);
--第二部:将源数据加载到临时表1中
insert into test1
select id,name,state,'20150501','99991231'
from org_test;
--第三部
--删除目标表中的大于等于今天日期的所有数据
delete from test where start_date >='20150501';
--将修改的数据全部欢颜
update test set end_date ='99991231' where end_date ='20150501';
--第四步:将新增和修改的数据添加到临时表2中
insert into test2
select t1.id,t1.name,t1.state,t1.start_date,t1.end_date--,t2.*
from test1 t1
left join test t2
on t1.id=t2.id
and t1.name=t2.name
and t2.state=t1.state
where t2.id is null
and t2.end_date is null;
select * from test order by 1;
select * from test3 order by 1;
--第四步:将没有改变的数据添加到
insert into test3
select t.id,t.name,t.state,t.start_date--,t2.*
,case when t1.id is not null and t1.end_date is not null and t.end_date ='99991231'
then '20150501'
when t2.id is null and t2.end_date is null and t.end_date ='99991231'
then '20150501'
else t.end_date end end_date
from test t
left join test2 t1
on t1.id=t.id
left join test1 t2
on t.id=t2.id
and t.name=t2.name
and t.state=t2.state;
--第五步:清空目标表
truncate test;
--第六步:将更新的数据添加到目标表中
insert into test
select * from test2;
--第七部:将没有变更的数据添加到目标表中
insert into test
select * from test3;
select * from test order by 1;
--第八步:提交事务
commit;
--第十步:分析处理目标表
VACUUM ANALYZE org_whh;
--|ˈvækjuəm|真空; 空白; 空虚; 清洁;
create temp table test
(
id varchar(100)
,name varchar(100)
,state varchar(100)
,start_date varchar(100)
,end_date varchar(100)
)
distributed by (id);
comment on table test is '员工状态表';
comment on column test.id is '员工的id';
comment on column test.name is '员工的name';
comment on column test.state is '员工的状态';
comment on column test.start_date is '开始时间';
comment on column test.end_date is '结束时间';
insert into test values('1','A','1','20150101','99991231');
insert into test values('2','B','1','20150101','99991231');
insert into test values('3','C','1','20150101','99991231');
insert into test values('4','D','1','20150101','99991231');
drop table if exists org_test;
create temp table org_test
(
id varchar(100)
,name varchar(100)
,state varchar(100)
)
distributed by (id);
comment on table org_test is '员工状态表';
comment on column org_test.id is '员工的id';
comment on column org_test.name is '员工的name';
comment on column org_test.state is '员工的状态';
insert into org_test values('1','A','1');
insert into org_test values('2','B','0');
insert into org_test values('4','D','0');
insert into org_test values('5','E','1');
select * from org_test;
--创建三张临时表
drop table if exists test1;
drop table if exists test2;
drop table if exists test3;
create temp table test1--存放源数据
(
id varchar(100)
,name varchar(100)
,state varchar(100)
,start_date varchar(100)
,end_date varchar(100)
)
distributed by (id);
create temp table test2--存放更新和新增的数据
(
id varchar(100)
,name varchar(100)
,state varchar(100)
,start_date varchar(100)
,end_date varchar(100)
)
distributed by (id);
create temp table test3--存放没有变更的数据
(
id varchar(100)
,name varchar(100)
,state varchar(100)
,start_date varchar(100)
,end_date varchar(100)
)
distributed by (id);
--第二部:将源数据加载到临时表1中
insert into test1
select id,name,state,'20150501','99991231'
from org_test;
--第三部
--删除目标表中的大于等于今天日期的所有数据
delete from test where start_date >='20150501';
--将修改的数据全部欢颜
update test set end_date ='99991231' where end_date ='20150501';
--第四步:将新增和修改的数据添加到临时表2中
insert into test2
select t1.id,t1.name,t1.state,t1.start_date,t1.end_date--,t2.*
from test1 t1
left join test t2
on t1.id=t2.id
and t1.name=t2.name
and t2.state=t1.state
where t2.id is null
and t2.end_date is null;
select * from test order by 1;
select * from test3 order by 1;
--第四步:将没有改变的数据添加到
insert into test3
select t.id,t.name,t.state,t.start_date--,t2.*
,case when t1.id is not null and t1.end_date is not null and t.end_date ='99991231'
then '20150501'
when t2.id is null and t2.end_date is null and t.end_date ='99991231'
then '20150501'
else t.end_date end end_date
from test t
left join test2 t1
on t1.id=t.id
left join test1 t2
on t.id=t2.id
and t.name=t2.name
and t.state=t2.state;
--第五步:清空目标表
truncate test;
--第六步:将更新的数据添加到目标表中
insert into test
select * from test2;
--第七部:将没有变更的数据添加到目标表中
insert into test
select * from test3;
select * from test order by 1;
--第八步:提交事务
commit;
--第十步:分析处理目标表
VACUUM ANALYZE org_whh;
--|ˈvækjuəm|真空; 空白; 空虚; 清洁;