yugong之单张表迁移

场景

要对愚公二开，所以先做一个测试，将愚公跑通，之前一直比较忙，今天将日志补上，以防遗忘

开始

单张表从orale到mysql的迁移，源端oracle和目标端mysql的表结构不完全相同
项

项	源库	目标库
数据库类型	ORACLE	MYSQL
用户	user1	root
密码	123456	root
URL	jdbc:oracle:thin:@127.0.0.1:1521:user1	jdbc:mysql://127.0.0.1:3306/root
表名称	yugong_example_oracle	yugong_example_mysql_mysql

源端oracle

创建user1用户 直接授权dba
原因：oracle全量基于JDBC拉取数据，增量基于物化视图来实现，所以这里需要给oracle数据库账号开权限

创建测试表

create table yugong_example_oracle(

id NUMBER(11)  ,

name varchar2(32) ,

alias_name  char(32) default ' ' not null,

amount number(11,2),

score  number(20),

text_b blob,

text_c clob,

gmt_create date not null,

gmt_modified date not null,

CONSTRAINT yugong_example_oracle_pk_id  PRIMARY   KEY (id)
);

插入2条测试数据

insert into yugong_example_oracle values(1,'herche','herche',11.2,100, NULL , NULL ,sysdate,sysdate);

insert into yugong_example_oracle values(2,'naraku','naraku',20.33,8888, NULL , NULL ,sysdate,sysdate);

commit;

目标端MySQL

create table yugong_example_mysql

 ( id bigint(20) unsigned auto_increment,

     display_name varchar(128) ,

     amount varchar(32),

     score bigint(20) unsigned ,

     text_b blob,

     text_c text,

     gmt_create timestamp not null,

     gmt_modified timestamp not null,

     gmt_move timestamp not null,

     CONSTRAINT yugong_example_mysql_pk_id  PRIMARY KEY (id)

 );

拉取愚公代码之后改配置

#源库配置

yugong.database.source.username=user1

yugong.database.source.password=123456

yugong.database.source.type=ORACLE

yugong.database.source.url=jdbc:oracle:thin:@127.0.0.1:1521:user1

yugong.database.source.encode=UTF-8

yugong.database.source.poolSize=30

#目标库配置

yugong.database.target.url=jdbc:mysql://127.0.0.1:3306/demo01

yugong.database.target.username=root

yugong.database.target.password=root

yugong.database.target.type=MYSQL

yugong.database.target.encode=UTF-8

yugong.database.target.poolSize=30

 

yugong.table.batchApply=true

yugong.table.onceCrawNum=1000

yugong.table.tpsLimit=0

# use connection default schema

yugong.table.ignoreSchema=false

# skip Applier Load Db failed data

yugong.table.skipApplierException=false

 

#需要同步表的黑白名单：表可加逗号分隔，白名单为空，代表整个库所有表


yugong.table.white= yugong_example_oracle

yugong.table.black=

 

# tables use multi-thread enable or disable

yugong.table.concurrent.enable=true

# tables use multi-thread size

yugong.table.concurrent.size=5

# retry times

yugong.table.retry.times = 3

# retry interval or sleep time (ms)

yugong.table.retry.interval = 1000

# MARK/FULL/INC/ALL(REC+FULL+INC)/CHECK/CLEAR

yugong.table.mode=ALL     # (自动全量+增量模式)

 

# yugong extractor

yugong.extractor.dump=false

yugong.extractor.concurrent.enable=true

yugong.extractor.concurrent.size=20

yugong.extractor.noupdate.sleep=1000

yugong.extractor.noupdate.thresold=0

yugong.extractor.once=false

# {0} is all columns , {1}.{2} is schemaName.tableName , {3} is primaryKey

#yugong.extractor.sql=select /*+parallel(t)*/ {0} from {1}.{2} t

#yugong.extractor.sql=select * from (select {0} from {1}.{2} t where {3} > ? order by {3} asc) where rownum <= ?

 

# yugong applier

yugong.applier.concurrent.enable=true

yugong.applier.concurrent.size=20

yugong.applier.dump=false

 

# stats

yugong.stat.print.interval=5

yugong.progress.print.interval=1

 

# alarm email

yugong.alarm.email.host = smtp.163.com

yugong.alarm.email.username = test@163.com

yugong.alarm.email.password =

yugong.alarm.email.stmp.port = 465

配置数据转换逻辑

上文准备的测试环境的源端oracle的表yugong_example_oracle和目标端mysql的表yugong_example_mysql有如下不同的地方：

1. table名不同. oracle中为yugong_example_oracle，mysql中为yugong_example_mysql

2. 字段名字不同. oracle中的name字段，映射到mysql的display_name

3. 字段逻辑处理. mysql的display_name字段数据来源为oracle库的:name+’(‘alias_name+’)’

4. 字段类型不同. oracle中的amount为number类型，映射到mysql的amount为varchar文本型

5. 源库多一个字段. oracle中多了一个alias_name字段

6. 目标库多了一个字段.mysql中多了一个gmt_move字段，(简单的用迁移时的当前时间进行填充)

故，需要根据不同的地方定义DataTranslator.java,下文的YugongExampleOrackeDataTranslator.java中配置这里的6项不同之处

public class YugongExampleOracleDataTranslator extends AbstractDataTranslator implements DataTranslator {

 

    public boolean translator(Record record) {

        // 1. schema/table名不同

    

        record.setTableName("yugong_example_mysql");

            //至此可知道源表为yugong_example_oracle，目标表为yugong_example_mysql

      

 // 2. 字段名字不同,

//源表的name字段,目标表中叫display_name

        ColumnValue nameColumn = record.getColumnByName("name");

        if (nameColumn != null) {

            nameColumn.getColumn().setName("display_name");

        }

       

 // 3. 字段逻辑处理

        ColumnValue aliasNameColumn = record.getColumnByName("alias_name");

        if (aliasNameColumn != null) {

            StringBuilder displayNameValue = new StringBuilder(64);

            displayNameValue.append(ObjectUtils.toString(nameColumn.getValue()))

                .append('(')

                .append(ObjectUtils.toString(aliasNameColumn.getValue()))

                .append(')');

            nameColumn.setValue(displayNameValue.toString());

        }

 

        // 4. 字段类型不同

        ColumnValue amountColumn = record.getColumnByName("amount");

        if (amountColumn != null) {

            amountColumn.getColumn().setType(Types.VARCHAR);

            amountColumn.setValue(ObjectUtils.toString(amountColumn.getValue()));

        }

 

        // 5. 源库多一个字段

        record.removeColumnByName("alias_name");

 

        // 6. 目标库多了一个字段

        ColumnMeta gmtMoveMeta = new ColumnMeta("gmt_move", Types.TIMESTAMP);

        ColumnValue gmtMoveColumn = new ColumnValue(gmtMoveMeta, new Date());

        gmtMoveColumn.setCheck(false);// 该字段不做对比

        record.addColumn(gmtMoveColumn);


        return super.translator(record);

    }

}

启动

以增量形式启动即可。
创建物化视图日志：

create materialized view log on user1."yugong_example_oracle" with SEQUENCE including new values