仓库数据增量更新加载算法(支持混乱日期跑批) - CSDN博客
仓库数据增量更新加载算法(支持混乱日期跑批)
1、建库及测试数据插入脚本
&--建增量更新目标表
-- Create table
create table EDW_T100_BAL_IU
VARCHAR2(8) not null,
NUMBER(22,2),
UPDATE_DT VARCHAR2(8)
-- Add comments to the table
comment on table EDW_T100_BAL_IU
is '余额（增量更新方式加载)表';
-- Add comments to the columns
comment on column EDW_T100_BAL_IU.ID
is '主键ID';
comment on column EDW_T100_BAL_IU.BAL
is '余额';
comment on column EDW_T100_BAL_IU.UPDATE_DT
is '更新日期';
-- Create/Recreate primary, unique and foreign key constraints
alter table EDW_T100_BAL_IU
add constraint EDW_T100_BAL_IU_ID primary key (ID);
--建增量更新用到的临时表
-- Create table
create global temporary table TMP_T100_BAL_IU
VARCHAR2(8) not null,
NUMBER(22,2),
UPDATE_DT VARCHAR2(8) not null
-- Create/Recreate primary, unique and foreign key constraints
alter table TMP_T100_BAL_IU
add constraint TMP_T100_BAL_IU_ID_UPDT primary key (ID, UPDATE_DT);
--建日志表
create table EDW_ETL_LOG_DETAIL
( PROC_NAME
VARCHAR2(50),
VARCHAR2(20),
VARCHAR2(10),
ETL_RECORD_NUM
VARCHAR2(1000),
VARCHAR2(1000),
TABLE_NAME
VARCHAR2(50),
START_TIMESTAMP TIMESTAMP(6),
END_TIMESTAMP
TIMESTAMP(6)
--建源表及插入源测试数据
-- Create table
create table ODS_CMIS_YE
VARCHAR2(8) not null,
NUMBER(22,2),
ODS_DATA_DATE VARCHAR2(8) not null
-- Add comments to the table
comment on table ODS_CMIS_YE
is 'ODS余额表（提供的是增量更新数据）';
-- Add comments to the columns
comment on column ODS_CMIS_YE.ID
is '主键ID';
comment on column ODS_CMIS_YE.BAL
is '余额';
comment on column ODS_CMIS_YE.ODS_DATA_DATE
is 'ODS数据日期';
-- Create/Recreate primary, unique and foreign key constraints
alter table ODS_CMIS_YE
add constraint ODS_CMIS_YE primary key (ID, ODS_DATA_DATE);
--insert test data
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('4', 34545.09, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('1', 10.10, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('2', 20.30, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('3', 22.10, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('4', 33.80, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('5', 66.09, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('6', 8889.08, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('7', 2324.07, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('8', , '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('9', 3434.99, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('1', 23.99, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('2', 20.30, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('1', 3333.98, '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('10', , '');
insert into ods_cmis_ye (ID, BAL, ODS_DATA_DATE) values ('11', , '');
2、增量更新示例代码
&CREATE OR REPLACE PROCEDURE P_T100_BAL_IU
(P_ETLDATE
--日期参数
O_RUNSTATUS OUT
--执行结果
--错误返回
--Procedure Name:P_T100_BAL_IU
--Script File
:P_T100_BAL_IU.SQL
/*###################################################*/
--Souce Databse.table
--ODS_CMIS_YE
ODS余额表（提供的是增量更新数据）
/*###################################################*/
--Target Databse.table :
--EDW_T100_BAL_IU
余额（增量更新方式加载)表
/*###################################################*/
--加载方式:增量更新
--定义存储过程信息
V_PROC_NAME
VARCHAR2(50)
:= 'P_T100_BAL_IU';
V_TABLE_NAME
VARCHAR2(50)
:= 'EDW_T100_BAL_IU';
V_START_TIMESTAMP TIMESTAMP;
--加载开始时间
V_END_TIMESTAMP
TIMESTAMP;
--加载结束时间
V_RECORD_NUMBER
--定义错误代码，错误状态
VARCHAR2(1000);
--异常信息
VARCHAR2(1000);
--出错位置
--捕获过程开始时间
SELECT SYSDATE INTO V_START_TIMESTAMP FROM DUAL;
--增量更新P_ETLDATE后的数据处理
--TMP_T100_BAL_IU存储的是EDW表中大于等于更新日期的所有临时数据，大于的由备份得到，等于的由加载当天数据时得到
--大于跑批日期的的数据备份条件是Update_Dt & P_ETLDATE，而删除目标表时是Update_Dt &= P_ETLDATE
V_ERR_SQL := 'INSERT INTO TMP_T100_BAL_IU FROM EDW_T100_BAL_IU WHERE Update_Dt & P_ETLDATE';
INSERT INTO TMP_T100_BAL_IU
FROM EDW_T100_BAL_IU WHERE Update_Dt & P_ETLDATE;
V_ERR_SQL := 'DELETE FROM EDW_T100_BAL_IU WHERE Update_Dt &= P_ETLDATE';
DELETE FROM EDW_T100_BAL_IU WHERE Update_Dt &= P_ETLDATE;
--加载当天全量数据
V_ERR_SQL := 'INSERT INTO TMP_T100_BAL_IU
FROM ODS_CMIS_YE';
INSERT INTO TMP_T100_BAL_IU--该表无主键，但逻辑主键是(ID、UPDATE_DT)的复合主键
,UPDATE_DT
--更新日期
,a1.ODS_DATA_DATE
--ODS数据日期
ODS_CMIS_YE
Ods_Data_Date = P_ETLDATE
--增量更新操作
--按主键删除更新部分记录
V_ERR_SQL := 'DELETE FROM EDW_T100_BAL_IU WHERE (ID) IN TMP_T100_BAL_IU';
DELETE FROM EDW_T100_BAL_IU
(SELECT ID
FROM TMP_T100_BAL_IU
--插入新增和更新数据
V_ERR_SQL := 'INSERT INTO EDW_T100_BAL_IU WHERE TMP_T100_BAL_IU [ID,MAX(UPDATE_DT)]';
INSERT INTO EDW_T100_BAL_IU
,UPDATE_DT
--更新日期
,UPDATE_DT
--更新日期
TMP_T100_BAL_IU
(ID,UPDATE_DT)
(SELECT ID,MAX(UPDATE_DT)
FROM TMP_T100_BAL_IU
GROUP BY ID
--正常处理
V_RECORD_NUMBER := SQL%ROWCOUNT;
SELECT SYSDATE INTO V_END_TIMESTAMP FROM
INSERT INTO EDW_ETL_LOG_DETAIL(START_TIMESTAMP,END_TIMESTAMP,PROC_NAME,TABLE_NAME,ETL_RECORD_NUM,ETL_MEMO,P_ETLDATE)
VALUES (V_START_TIMESTAMP,V_END_TIMESTAMP,V_PROC_NAME,V_TABLE_NAME,V_RECORD_NUMBER,'成功',P_ETLDATE);
--异常处理
EXCEPTION WHEN OTHERS THEN
V_SQLERRM := SQLERRM;
INSERT INTO EDW_ETL_LOG_DETAIL(START_TIMESTAMP,END_TIMESTAMP,PROC_NAME,TABLE_NAME,ETL_RECORD_NUM,ETL_MEMO,ERR_MSG,ERR_SQL,P_ETLDATE)
VALUES (V_START_TIMESTAMP,V_END_TIMESTAMP,V_PROC_NAME,V_TABLE_NAME,0,'失败',V_SQLERRM,V_ERR_SQL,P_ETLDATE);
O_RUNSTATUS := 1;
O_MSG := 'PROGRAMMING ERROR HAPPENED';
3、结果测试
&Incremental_Update
truncate table edw_t100_bal_
select * from ods_cmis_
select * from edw_t100_bal_
--无论批如何调，只要保证每一天都调了，edw_t100_bal_iu就会和下面的SQL得出的结果一致！
FROM ODS_CMIS_YE
WHERE ODS_DATA_DATE &= ''
AND (ID, ODS_DATA_DATE) IN
(SELECT ID, MAX(ODS_DATA_DATE) FROM ODS_CMIS_YE GROUP BY ID);
p_t100_bal_
p_t100_bal_iu_
select * from edw_etl_log_detail order by 8
4、总结说明
此过程代码，对于如12.5号数据已经跑完但发现12.1号那天的源数据有问题的情况，我们只要重新调度12.1的批就可以了，而不必从12.1号一直跑到当天，且能保证数据的完整性；而对于如果发现某一天数据有问题，需要从出错那一天起一直跑到当天的情况，代码如下，可根据实际情况选用。
&CREATE OR REPLACE PROCEDURE P_T100_BAL_IU
(P_ETLDATE
--日期参数
O_RUNSTATUS OUT
--执行结果
--错误返回
--Procedure Name:P_T100_BAL_IU
--Script File
:P_T100_BAL_IU.SQL
/*###################################################*/
--Souce Databse.table
--ODS_CMIS_YE
ODS余额表（提供的是增量更新数据）
/*###################################################*/
--Target Databse.table :
--EDW_T100_BAL_IU
余额（增量更新方式加载)表
/*###################################################*/
--加载方式:增量更新
--定义存储过程信息
V_PROC_NAME
VARCHAR2(50)
:= 'P_T100_BAL_IU';
V_TABLE_NAME
VARCHAR2(50)
:= 'EDW_T100_BAL_IU';
V_START_TIMESTAMP TIMESTAMP;
--加载开始时间
V_END_TIMESTAMP
TIMESTAMP;
--加载结束时间
V_RECORD_NUMBER
--定义错误代码，错误状态
VARCHAR2(1000);
--异常信息
VARCHAR2(1000);
--出错位置
--捕获过程开始时间
SELECT SYSDATE INTO V_START_TIMESTAMP FROM DUAL;
--增量更新历史回滚
DELETE FROM EDW_T100_BAL_IU WHERE Update_Dt &= P_ETLDATE;
--加载当天全量数据
V_ERR_SQL := 'INSERT INTO TMP_T100_BAL_IU
FROM ODS_CMIS_YE';
INSERT INTO TMP_T100_BAL_IU
,UPDATE_DT
--更新日期
,a1.ODS_DATA_DATE
--ODS数据日期
ODS_CMIS_YE
Ods_Data_Date = P_ETLDATE
--增量更新操作
--按主键删除更新部分记录
DELETE FROM EDW_T100_BAL_IU
(SELECT ID
TMP_T100_BAL_IU
--出入新增和更新数据
INSERT INTO EDW_T100_BAL_IU
,UPDATE_DT
--更新日期
,UPDATE_DT
--更新日期
TMP_T100_BAL_IU
--正常处理
V_RECORD_NUMBER := SQL%ROWCOUNT;
SELECT SYSDATE INTO V_END_TIMESTAMP FROM
INSERT INTO EDW_ETL_LOG_DETAIL(START_TIMESTAMP,END_TIMESTAMP,PROC_NAME,TABLE_NAME,ETL_RECORD_NUM,ETL_MEMO,P_ETLDATE)
VALUES (V_START_TIMESTAMP,V_END_TIMESTAMP,V_PROC_NAME,V_TABLE_NAME,V_RECORD_NUMBER,'成功',P_ETLDATE);
--异常处理
EXCEPTION WHEN OTHERS THEN
V_SQLERRM := SQLERRM;
INSERT INTO EDW_ETL_LOG_DETAIL(START_TIMESTAMP,END_TIMESTAMP,PROC_NAME,TABLE_NAME,ETL_RECORD_NUM,ETL_MEMO,ERR_MSG,ERR_SQL,P_ETLDATE)
VALUES (V_START_TIMESTAMP,V_END_TIMESTAMP,V_PROC_NAME,V_TABLE_NAME,0,'失败',V_SQLERRM,V_ERR_SQL,P_ETLDATE);
O_RUNSTATUS := 1;
O_MSG := 'PROGRAMMING ERROR HAPPENED';
本文已收录于以下专栏：
相关文章推荐
/*********************************************************************************/
--存储过程名称： P_CON...
本文只是用merge语句来实现仓库数据的增量更新，与以往使用的update、delete、insert并没有本质不同；之所以单独记录在这里，就是为了备查。
本文中使用的测试数据及其表结构，参见《仓库...
Android热加载出现的原因在于：第一5.1出现之前没有好的办法解决App方法数超过65536的问题，第二启动特别慢，因为加载的模块比较多。本质上还是虚拟机支持JIT的加载机制。
AndroidD...
我们通常所说的增量数据，其实更确切的说，应该是变量数据，包括对数据的增加、修改和删除。特别是当有些系统存在物理删除数据的情况时，这一点尤为重要。
从各源系统获取增量数据，是DW/BI...
某电子商务网站主要销售电子类产品，产品又分为几个大类别，包括：电脑类、手机类、键盘类等，每个类别内又细分为几个小类别，各类别下又有诸多的商品，每一个商品都有一个唯一的商品编号。用户可以通过注册成为会员...
［Vio.wang 崇尚开源 欢迎点评］
本来是打算将此问题记载到问题反馈中，但是感觉涉及到的面偏大，就单写一篇吧。
其实这也算的上是一个比较常见的问题了，APP被回收之后，重新回...
最近项目遇到一个BUG，就是
他的最新文章
讲师：董岩
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)公积金年冲贷款8月份刚试行，我的放款日期刚好是8月，说要放贷日期前一个月才可以申请，什么道理?_公积金一年-宜人贷问答
公积金年冲贷款8月份刚试行，我的放款日期刚好是8月，说要放贷日期前一个月才可以申请，什么道理?
提问者：n***2
地区：宁德
&&& 1.根据省住建厅住房公积金监管处统一部署及宁德市《逐年提取住房公积金归还住房公积金贷款业务实施方案》的计划安排，我中心于上半年安排业务人员到省住房公积金项目组参加系统测试和培训，现已顺利完成“逐年冲还贷”业务系统的测试工作，整个测试时间全省最短。因“逐年冲还贷”业务需要数据对接、技术支撑，特别是承办银行之间的数据传输对接，存在诸多不确定因素。按照省住房公积金项目组的要求，我市从7月份开始已在市直营业部、蕉城办事处试运行，若试运行成功，将从8月份开始在全市开展，若试运行不成功，则要继续测试，查找原因及时解决。具体正式运行时间待试运行成功后（因冲还贷跑批时间为每月的10至15日，所以试运行的结果要到8月15日后公布），将会在宁德公积金网站上及时公布（网址： ）。 &&& 2．申请“逐年冲还贷”业务需在冲还贷月的上一个月之前办理申请确认手续（即：我市开始正式运行时间到冲还贷月的上一个月之前都可申请，并非要等一年才能申请）。冲还贷月为住房公积金贷款发放的每年对应月。 &&& 3.年冲还贷系统的开发及上线时间都是省住建厅住房公积金监督管理处统一安排的，系统设置的冲还贷时间为贷款发放的对应月，这是省住房公积金项目组根据公积金贷款的实际情况统一设置，我市无权更改，且每项政策的执行都是一个时间点，我市开展年冲还贷业务对每个申请人都是公平、公正、可操作的。以您所述，您今年赶不上年冲还贷的时间，建议您先到公积金缴存服务窗口办理手工提前还贷手续，待年冲还贷业务开始后，再申请签约逐年冲还贷业务。 &&& 如有其他问题，可直接拨打12329住房公积金服务热线咨询。
待省住建厅住房公积金业务系统项目组进行系统升级后报我市住房公积金管委会批准后开展逐年提取冲还贷业务。
根据文件规定，职工申请住房公积金贷款其缴存公积金必须正常缴满1年且距申请贷款日前6个月内缴存公积金正常。
&&& 申请“逐年冲还贷”业务的，需在冲还贷月（即贷款发放的每年对应月）的上一个月之前办理签约手续。以您所述，您的公积金贷款是于2015年11月发放的，即冲还贷月为11月，您可在2016年10月底之前到贷款办事处签约“逐年冲还贷”业务，首次冲还贷时间为2016年11月。 &&& 如有其他问题，可直接拨打12329住房公积金服务热线咨询。
对以上答案仍不满意？您可以向我们的专家咨询问题
获取验证码