ETL异构数据源Datax_MySQL同步Oracle(全量)_07

女爷i 2022-10-09 03:06 219阅读 0赞

### 文章目录 ###

*   *   *   *   *   *  1. 清除Oracle数据库中OTBS1表的数据
                     *  2. 构建json
                     *  3. 执行脚本
                     *  4. 同步验证
                     *  5. 同步分析
                     *  7. 同步结果

###### 1. 清除Oracle数据库中OTBS1表的数据 ######

Truncate TABLE OTBS1;

###### 2. 构建json ######

{ 
        "core": { 
            "transport": { 
                "channel": { 
                    "speed": { 
                        "byte": 1048576
                    }
                }
            }
        },
        "job": { 
            "setting": { 
                "speed": { 
                    "channel": 5
                },
                "errorLimit": { 
                    "record": 0,
                    "percentage": 0.02
                }
            },
            "content": [
                { 
                    "reader": { 
                        "name": "mysqlreader",
                        "parameter": { 
                            "column": [
                                "IDNO",
                                "COL1",
                                "COL2",
                                "COL3",
                                "DT",
                                "COL5",
                                "COL6",
                                "COL7",
                                "COL8",
                                "COL9",
                                "COL10"
                            ],
                            "splitPk": "IDNO",
                            "connection": [
                                { 
                                    "jdbcUrl": [
                                        "jdbc:mysql://127.0.0.1:3306/datax"
                                    ],
                                    "table": [
                                        "otbs1"
                                    ]
                                }
                            ],
                            "username": "root",
                            "password": "123456"
                        }
                    },
                    "writer": { 
                        "name": "oraclewriter",
                        "parameter": { 
                            "column": [
                                "IDNO",
                                "COL1",
                                "COL2",
                                "COL3",
                                "DT",
                                "COL5",
                                "COL6",
                                "COL7",
                                "COL8",
                                "COL9",
                                "COL10"
                            ],
                            "connection": [
                                { 
                                    "jdbcUrl": "jdbc:oracle:thin:@192.xxx.xxx.128xxx1521:orcl",
                                    "table": [
                                        "TEST.OTBS1"
                                    ]
                                }
                            ],
                            "username": "username",
                            "password": "password"
                        }
                    }
                }
            ]
        }
    }

###### 3. 执行脚本 ######

python /app/datax/bin/datax.py /app/datax/job/mysql2oracle

###### 4. 同步验证 ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 1]

###### 5. 同步分析 ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 2]

2021-06-23 14:01:53.357 [job-0] INFO  JobContainer - jobContainer starts to do prepare ...
    2021-06-23 14:01:53.358 [job-0] INFO  JobContainer - DataX Reader.Job [mysqlreader] do prepare work .
    2021-06-23 14:01:53.359 [job-0] INFO  JobContainer - DataX Writer.Job [oraclewriter] do prepare work .
    2021-06-23 14:01:53.360 [job-0] INFO  JobContainer - jobContainer starts to do split ...
    2021-06-23 14:01:53.361 [job-0] INFO  JobContainer - Job set Channel-Number to 5 channels.
    2021-06-23 14:01:53.387 [job-0] INFO  SingleTableSplitUtil - split pk [sql=SELECT MIN(IDNO),MAX(IDNO) FROM otbs1] is running... 
    2021-06-23 14:01:53.450 [job-0] INFO  SingleTableSplitUtil - After split(), allQuerySql=[
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (1 <= IDNO AND IDNO < 41944) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (41944 <= IDNO AND IDNO < 83887) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (83887 <= IDNO AND IDNO < 125830) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (125830 <= IDNO AND IDNO < 167773) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (167773 <= IDNO AND IDNO < 209716) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (209716 <= IDNO AND IDNO < 251659) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (251659 <= IDNO AND IDNO < 293602) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (293602 <= IDNO AND IDNO < 335545) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (335545 <= IDNO AND IDNO < 377488) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (377488 <= IDNO AND IDNO < 419431) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (419431 <= IDNO AND IDNO < 461374) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (461374 <= IDNO AND IDNO < 503317) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (503317 <= IDNO AND IDNO < 545260) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (545260 <= IDNO AND IDNO < 587203) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (587203 <= IDNO AND IDNO < 629146) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (629146 <= IDNO AND IDNO < 671089) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (671089 <= IDNO AND IDNO < 713032) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (713032 <= IDNO AND IDNO < 754975) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (754975 <= IDNO AND IDNO < 796918) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (796918 <= IDNO AND IDNO < 838861) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (838861 <= IDNO AND IDNO < 880804) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (880804 <= IDNO AND IDNO < 922747) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (922747 <= IDNO AND IDNO < 964690) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (964690 <= IDNO AND IDNO < 1006633) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  (1006633 <= IDNO AND IDNO <= 1048576) 
    select IDNO,COL1,COL2,COL3,DT,COL5,COL6,COL7,COL8,COL9,COL10 from otbs1  where  IDNO IS NULL
    ].
    2021-06-23 14:01:53.451 [job-0] INFO  JobContainer - DataX Reader.Job [mysqlreader] splits to [26] tasks.
    2021-06-23 14:01:53.464 [job-0] INFO  JobContainer - DataX Writer.Job [oraclewriter] splits to [26] tasks.
    2021-06-23 14:01:53.539 [job-0] INFO  JobContainer - jobContainer starts to do schedule ...
    2021-06-23 14:01:53.577 [job-0] INFO  JobContainer - Scheduler starts [1] taskGroups.
    2021-06-23 14:01:53.581 [job-0] INFO  JobContainer - Running by standalone Mode.
    2021-06-23 14:01:53.634 [taskGroup-0] INFO  TaskGroupContainer - taskGroupId=[0] start [5] channels for [26] tasks.
    2021-06-23 14:01:53.659 [taskGroup-0] INFO  Channel - Channel set byte_speed_limit to 1048576.
    2021-06-23 14:01:53.659 [taskGroup-0] INFO  Channel - Channel set record_speed_limit to -1, No tps activated.

###### 7. 同步结果 ######

2021-06-23 14:02:13.668 [job-0] INFO  JobContainer - 
    任务启动时刻                    : 2021-06-23 14:01:52
    任务结束时刻                    : 2021-06-23 14:02:13
    任务总计耗时                    :                 21s
    任务平均流量                    :            3.30MB/s
    记录写入速度                    :          52428rec/s
    读出记录总数                    :             1048576
    读写失败总数                    :                   0

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70]: /images/20221005/673df5bae19644378af4de5acc427cc3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 1]: /images/20221005/971947cb2b374879ab62b10abf9c6d7a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 2]: /images/20221005/8b02af7c1972458dafcaaf4a28554a81.png