1、ETL通用解决方案oracle+存储过程实现ETL通用解决方案oracle+存储过程实现方案理念-四化理念1. 数据规格化 2. 处理自动化 3. 信息集中化 4. 操作人性化 架构通过视图实现来至两个不同数据库的表的结构完全一致, 在结构完全相同的两个表之间进行数据同步, 问题变得相当简单. 同步代码如下.ETL-初始同步delete from ods_table;insert into v_tableselect * from db_table;commit;-新增同步insert into v_tableselect * from db_table twhere t.id not in
2、(select id from v_table);commit;-变更同步update ods_table tset t.c_number = (select db.c_number from db_table db where db.id = t.id)where t.c_number != (select db.c_number from db_table db where db.id = t.id);commit;实现两个表结构完全一致的方法如下-建表CREATE SEQUENCE SEQ_ETL_INCREASE_IDINCREMENT BY 1START WITH 1NOCACHE;
3、/*=*/* Table: ETL_TABLES */*=*/CREATE TABLE ETL_TABLES ( ID NUMBER DEFAULT -1 NOT NULL, TABLE_NAME VARCHAR2(100) NOT NULL, TABLE_TYPE VARCHAR2(30) NOT NULL, TABLE_ROOT_IN VARCHAR2(30), TABLE_NEED_CREATE_VIEW NUMBER DEFAULT 1, TABLE_CREATE_VIEW_NAME_PREFIX VARCHAR2(30) DEFAULT v, DB_LINK_NAME VARCHAR
4、2(100), CURRENT_VERSION NUMBER DEFAULT 1 NOT NULL, VERSION_HISTORY VARCHAR2(3000) DEFAULT init input NOT NULL, DEVELOP_DATE DATE DEFAULT SYSDATE NOT NULL, DEVELOP_BY VARCHAR2(100) DEFAULT cyyanisoftstone NOT NULL, LAST_MAINTAIN_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_MAINTAIN_BY VARCHAR2(100) DEFAU
5、LT cyyanisoftstone NOT NULL, MEMO VARCHAR2(500), STATUS NUMBER DEFAULT 1, CONSTRAINT PK_ETL_TABLES PRIMARY KEY (ID);COMMENT ON TABLE ETL_TABLES IS此表用于维护ETL涉及到所有表, 包括:1, db-业务系统数据库2, ods-操作数据数据库3, dw-数据仓库;/*=*/* Table: ETL_VIEWS */*=*/CREATE TABLE ETL_VIEWS ( ID NUMBER DEFAULT -1 NOT NULL, VIEW_NAME
6、VARCHAR2(100) NOT NULL, VIEW_TYPE VARCHAR2(30) NOT NULL, VIEW_ROOT_IN VARCHAR2(30), VIEW_SELECT VARCHAR2(4000) NOT NULL, VIEW_FROM VARCHAR2(600) NOT NULL, VIEW_WHERE VARCHAR2(2000), VIEW_ORDER_BY VARCHAR2(600), VIEW_GROUP_BY VARCHAR2(600), VIEW_HAVING VARCHAR2(600), VIEW_DB_LINK_NAME VARCHAR2(100),
7、CURRENT_VERSION NUMBER DEFAULT 1 NOT NULL, VERSION_HISTORY VARCHAR2(3000) DEFAULT init input NOT NULL, DEVELOP_DATE DATE DEFAULT SYSDATE NOT NULL, DEVELOP_BY VARCHAR2(100) DEFAULT cyyanisoftstone NOT NULL, LAST_MAINTAIN_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_MAINTAIN_BY VARCHAR2(100) DEFAULT cyyan
8、isoftstone NOT NULL, MEMO VARCHAR2(500), STATUS NUMBER DEFAULT 1, CONSTRAINT PK_ETL_VIEWS PRIMARY KEY (ID);COMMENT ON TABLE ETL_VIEWS IS此表用于维护ETL涉及到所有视图, 包括:1, v1-db表中与ods对应到视图2, v2-ods表中与db对应到视图3, v3-ods表中与dw对应到视图4, v4-dw表中与ods中对应到视图;/*=*/* Table: ETLS */*=*/CREATE TABLE ETLS ( ID NUMBER NOT NULL,
9、ETL_NAME VARCHAR2(300) NOT NULL, ETL_TYPE VARCHAR2(30) NOT NULL, ETL_SRC_VIEW_OR_TABLE NUMBER NOT NULL, ETL_DES_VIEW_OR_TABLE NUMBER NOT NULL, ETL_INIT_ENABLE NUMBER(1) DEFAULT 1 NOT NULL, ETL_ADD_ENABLE NUMBER(1) DEFAULT 1 NOT NULL, ETL_CHARGE_ENABLE NUMBER(1) DEFAULT 1 NOT NULL, CURRENT_VERSION NU
10、MBER DEFAULT 1 NOT NULL, VERSION_HISTORY VARCHAR2(3000) DEFAULT init input NOT NULL, DEVELOP_DATE DATE DEFAULT SYSDATE NOT NULL, DEVELOP_BY VARCHAR2(100) DEFAULT cyyanisoftstone NOT NULL, LAST_MAINTAIN_DATE DATE DEFAULT SYSDATE NOT NULL, LAST_MAINTAIN_BY VARCHAR2(100) DEFAULT cyyanisoftstone NOT NUL
11、L, MEMO VARCHAR2(500), STATUS NUMBER DEFAULT 1, CONSTRAINT PK_ETLS PRIMARY KEY (ID);COMMENT ON TABLE ETLS IS此表用于维护ETL转换时设计到源表和目的表源表(或视图)-目的表(或视图)-(推荐全部使用视图, 视图具有更过到灵活性, 而且更统一)整体架构是在完全相同两张表(或视图)之间进行同步处理规范:1, 源表(或视图)-和目的表(或视图)-完全相同2, 目的视图必须是单表;-存储过程/*=*/* Database name: %DATABASE% */* DBMS name: ORACL
12、E Version 10g */* Created on: 2009-2-1 23:29:27 */*=*/- INTEGRITY PACKAGE DECLARATIONCREATE OR REPLACE PACKAGE INTEGRITYPACKAGE ASPROCEDURE INITNESTLEVEL;FUNCTION GETNESTLEVEL RETURN NUMBER;PROCEDURE NEXTNESTLEVEL;PROCEDURE PREVIOUSNESTLEVEL;END INTEGRITYPACKAGE;/- INTEGRITY PACKAGE DEFINITIONCREATE
13、 OR REPLACE PACKAGE BODY INTEGRITYPACKAGE ASNESTLEVEL NUMBER;- PROCEDURE TO INITIALIZE THE TRIGGER NEST LEVELPROCEDURE INITNESTLEVEL ISBEGINNESTLEVEL := 0;END;- FUNCTION TO RETURN THE TRIGGER NEST LEVELFUNCTION GETNESTLEVEL RETURN NUMBER ISBEGINIF NESTLEVEL IS NULL THEN NESTLEVEL := 0;END IF;RETURN(
14、NESTLEVEL);END;- PROCEDURE TO INCREASE THE TRIGGER NEST LEVELPROCEDURE NEXTNESTLEVEL ISBEGINIF NESTLEVEL IS NULL THEN NESTLEVEL := 0;END IF;NESTLEVEL := NESTLEVEL + 1;END;- PROCEDURE TO DECREASE THE TRIGGER NEST LEVELPROCEDURE PREVIOUSNESTLEVEL ISBEGINNESTLEVEL := NESTLEVEL - 1;END;END INTEGRITYPACK
15、AGE;/CREATE OR REPLACE PROCEDURE PRO_CREATE_VIEW_BY_ETL_VIEWSAS-PRO_CREATE_VIEW_BY_ETL_VIEWS- CREATED ON 2009-2-1 BY CYYANISOFTSTONE- 功能 : 根据ETL_VIEWS中到数据生成视图-VIEW_CREATE_CODE VARCHAR2(10000); -生成视图到代码VIEW_NAME VARCHAR2(100); -视图名称VIEW_SELECT VARCHAR2(4000); -视图的SELECT部分VIEW_FROM VARCHAR2(300); -视图的
16、FROM部分VIEW_WHERE VARCHAR2(3000); -视图的WHERE部分VIEW_ORDER_BY VARCHAR2(600); -视图的ORDER BY部分VIEW_GROUP_BY VARCHAR2(600); -视图的GROUP BY部分VIEW_HAVING VARCHAR2(600); -视图的HAVING部分VIEW_DB_LINK_NAME VARCHAR2(100); -视图的DB LINK部分ROW_COUNT NUMBER; -行数CURSOR ETL_VIEWS_CURSOR IS -提取创建视图需要到信息 SELECT VIEW_NAME, VIEW_S
17、ELECT, VIEW_FROM, VIEW_WHERE, VIEW_ORDER_BY, VIEW_GROUP_BY, VIEW_HAVING, VIEW_DB_LINK_NAME FROM ETL_VIEWS T WHERE T.CURRENT_VERSION = (SELECT MAX(T2.CURRENT_VERSION) FROM ETL_VIEWS T2 WHERE T.VIEW_NAME = T2.VIEW_NAME);BEGIN- 统计行数SELECT COUNT(*) INTO ROW_COUNT FROM ETL_VIEWS T WHERE T.CURRENT_VERSION
18、 = (SELECT MAX(T2.CURRENT_VERSION) FROM ETL_VIEWS T2 WHERE T.VIEW_NAME = T2.VIEW_NAME);OPEN ETL_VIEWS_CURSOR; -打开游标FOR I IN 1 . ROW_COUNT LOOP -遍历 FETCH ETL_VIEWS_CURSOR INTO VIEW_NAME, VIEW_SELECT, VIEW_FROM, VIEW_WHERE, VIEW_ORDER_BY, VIEW_GROUP_BY, VIEW_HAVING, VIEW_DB_LINK_NAME; -拼接创建视图到语句 VIEW_
19、CREATE_CODE := create or replace view | VIEW_NAME | as select | VIEW_SELECT | from | VIEW_FROM; IF VIEW_DB_LINK_NAME IS NOT NULL THEN VIEW_CREATE_CODE := VIEW_CREATE_CODE | | VIEW_DB_LINK_NAME; END IF; IF VIEW_WHERE IS NOT NULL THEN VIEW_CREATE_CODE := VIEW_CREATE_CODE | where | VIEW_WHERE; END IF;
20、IF VIEW_ORDER_BY IS NOT NULL THEN VIEW_CREATE_CODE := VIEW_CREATE_CODE | order by | VIEW_ORDER_BY; END IF; IF VIEW_GROUP_BY IS NOT NULL THEN VIEW_CREATE_CODE := VIEW_CREATE_CODE | group by | VIEW_GROUP_BY; END IF; IF VIEW_HAVING IS NOT NULL THEN VIEW_CREATE_CODE := VIEW_CREATE_CODE | having | VIEW_H
21、AVING; END IF; -输出创建语句 -DBMS_OUTPUT.PUT_LINE(VIEW_CREATE_CODE); -DBMS_OUTPUT.PUT_LINE(); -执行创建视图 EXECUTE IMMEDIATE VIEW_CREATE_CODE;END LOOP;CLOSE ETL_VIEWS_CURSOR; -关闭游标END;/CREATE OR REPLACE PROCEDURE PRO_INSERT_INTO_ETL_VIEWSAS-ADD BY CYYANISOFTSTONE-2009年2月1日21:33:37-此存储过程用于 将ETL_TABLE中标识需要创建VIE
22、W 到TABLE, 进行自动提起转换到ETL_VIEWS中.-处理过程用到啦系统表COL从此表中获取列名TABLE_NAME VARCHAR2(100); -表名COL_NAME VARCHAR2(100); -列名TABLE_COUNT NUMBER; -表到行数-COL_COUNT NUMBER; -列数ETL_VIEWS_INSERT_CODE VARCHAR2(600); -插入语句到 INSERT部分ETL_VIEWS_VALUES_CODE VARCHAR2(16000); -插入语句到VALUES部分-ETL_VIEWS的到列VIEW_NAME_PREFIX VARCHAR2(30);-实体名到前缀TABLE_TYPE VARCHAR2(30); -表类型 如 DB, ODS, DWTABLE_ROOT_IN VARCHAR2(30); -表来源, 来自那个系统, 如资金系统NHZJ, 财务系统NHCWVIEW_SELECT VARCHAR2(10000); -VIEW 语句到SELECT部分, 这个需要遍历一个表到所有列DB_LINK_NAME VARCHAR2(100);CURRENT_VERSION VARCHAR2(600); -版本部分, 这里没更新, 只要全部删除, 或不断插入, 此字段定义了版本, 没有变更都形成新到版本,
