数据中台--数据迁移相关脚本

1.数据准备

表数据

create table ecp_tables as select * from ALL_TAB_COMMENTS where owner='BIDPRO' order by table_name;

字段数据

create table ecp_columns as
SELECT B1.OWNER       AS OWNER2,       
               B1.TABLE_NAME  AS TABLE_NAME2, --"表名",         
               B1.COLUMN_NAME AS COLUMN_NAME2, --"字段名",  
               B1.DATA_TYPE as     DATA_TYPE,--"字段类型“
               A1.COMMENTS    AS COMMENTS2 ,--"字段说明"  
               B1.COLUMN_ID as     COLUMN_ID --"字段序号"
          FROM ALL_COL_COMMENTS A1, ALL_TAB_COLUMNS B1  
         WHERE A1.OWNER  IN ('BIDPRO')       
            --改为后台数据库用户名      
           AND A1.OWNER = B1.OWNER       
           AND A1.TABLE_NAME = B1.TABLE_NAME       
           AND A1.COLUMN_NAME = B1.COLUMN_NAME           
         ORDER BY B1.OWNER, B1.TABLE_NAME, B1.COLUMN_ID;

部分数据处理

create table ecp_tables_679 as 
select * from ecp_tables  t where  t.table_name in (select table_name  from ecp_679);

create table ecp_columns_679 as 
select * from ecp_columns  t where  t.table_name2 in (select table_name  from ecp_679);

mysql 建表语句

create table ERP_TABLES
(
	id int AUTO_INCREMENT,
	
  OWNER      VARCHAR(200) ,
  TABLE_NAME VARCHAR(200) ,
  TABLE_TYPE VARCHAR(100),
  COMMENTS   VARCHAR(4000),
	PRIMARY KEY (`id`)
);

CREATE TABLE ERP_COLUMNS (
	id INT AUTO_INCREMENT,
	OWNER2 VARCHAR ( 30 ) NOT NULL,
	TABLE_NAME2 VARCHAR ( 30 ) NOT NULL,
	COLUMN_NAME2 VARCHAR ( 30 ) NOT NULL,
	DATA_TYPE VARCHAR ( 106 ),
	COMMENTS2 VARCHAR ( 4000 ),
	COLUMN_ID INT,
PRIMARY KEY ( `id` ) 
)

测试准备

抽取进程

select q'[table BIDPRO.]'||t.table_name||q'[,tokens (TKN-ROWID=@GETENV('RECORD','rowid'));]' from user_tables  t order by table_name;

投递进程

select q'[table BIDPRO.]'||t.table_name||';'  from user_tables  t order by table_name;

　添加表级别的日志

select q'[add trandata BIDPRO.]'||t.table_name from user_tables  t order by table_name;

　添加抽取进程

add extract ext_dh02,tranlog,begin now
add exttrail  ./dirdat/dp, extract ext_dh02

　添加投递进程（特别注意add rmttrail /oggadapter/dirdat/dp, 这一步是目标端的地址，不是源端的）

add extract dp_dh02,exttrailsource ./dirdat/dp
add rmttrail /oggadapter/dirdat/dp,extract dp_dh02

合并脚本1

SELECT  t2.*
FROM    (
            SELECT  ext_rowid
                    ,max(ext_date_time) AS maxdate
            FROM    ods_ogg_ecp1_ebiz_bidpro_bid_task_filedetail
            WHERE   ds >  - 1
            GROUP BY ext_rowid
        ) t1
        ,ods_ogg_ecp1_ebiz_bidpro_bid_task_filedetail t2
WHERE   t1.ext_rowid = t2.ext_rowid
AND     t1.maxdate = t2.ext_date_time
AND     t2.ext_flag <> 'D'
AND     t2.ds >  - 1
;

select t1.* from  (SELECT  t.*
        ,row_number() OVER(PARTITION BY ext_rowid ORDER BY ext_ogg_seq DESC) AS rownumber
FROM    ods_ogg_ecp1_ebiz_bidpro_bid_task_filedetail t where ds>-1 ) t1  where t1.rownumber='1' and t1.ext_flag <>'D'
;

临时保留OGG相关的：

select * from  erp_tables; --2772 +23=2795

create table erp_cd_ogg(num_seq number,table_name varchar2(100),ogg_pump varchar2(100),trial varchar2(10));


select table_name from erp_tables minus select * from erp_cd_0419_2796;
select * from erp_cd_0419_2796 minus select table_name from erp_cd_ogg;

select distinct t.data_type from dba_tab_columns t;
create table erp_tables_23 as select * from erp_tables where 1=0;
create table erp_tables_2796_ogg as  select t1.*,t2.num_seq,t2.ogg_pump,t2.trial from erp_tables_2796 t1, erp_cd_ogg t2 where t1.table_name=t2.table_name order by t2.trial,t2.num_seq ;
select * from erp_cd_ogg;

create table erp_tables_0420_2639_ogg as  select t1.*,t2.num_seq,t2.ogg_pump,t2.trial from erp_cd_0420_2639 t1, erp_cd_ogg t2 
where t1.table_name=t2.table_name order by t2.trial,t2.num_seq ;