ETL异构数据源Datax_Oracle同步MySQL(全量)_04

旧城等待， 2022-10-09 02:55 192阅读 0赞

### 文章目录 ###

*   *   *   *   *  一、Oracle同步Mysql
                 *   *  1. 构建json
                     *  2. 执行数据同步
                     *  3. 查看同步数据
                     *  4. 同步数据正确性和准确性
                     *  5. 同步日志分析

##### 一、Oracle同步Mysql #####

###### 1. 构建json ######

vim oracle2mysql.json

{ 
        "job": { 
            "setting": { 
                "speed": { 
                    "channel": 3
                },
                "errorLimit": { 
                    "record": 0,
                    "percentage": 0.02
                }
            },
            "content": [
                { 
                    "reader": { 
                        "name": "oraclereader",
                        "parameter": { 
                            "column": [
                                "IDNO",
                                "COL1",
                                "COL2",
                                "COL3",
                                "DT",
                                "COL5",
                                "COL6",
                                "COL7",
                                "COL8",
                                "COL9",
                                "COL10"
                            ],
                            "connection": [
                                { 
                                    "jdbcUrl": [
                                        "jdbc:oracle:thin:@192.xxx.xxx.xxx:1521:orcl"
                                    ],
                                    "table": [
                                        "TEST.OTBS1"
                                    ]
                                }
                            ],
                            "username": "username",
                            "password": "password"
                        }
                    },
                    "writer": { 
                        "name": "mysqlwriter",
                        "parameter": { 
                            "column": [
                                "IDNO",
                                "COL1",
                                "COL2",
                                "COL3",
                                "DT",
                                "COL5",
                                "COL6",
                                "COL7",
                                "COL8",
                                "COL9",
                                "COL10"
                            ],
                            "connection": [
                                { 
                                    "jdbcUrl": "jdbc:mysql://127.0.0.1:3306/datax?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT&nullCatalogMeansCurrent=true",
                                    "table": [
                                        "otbs1"
                                    ]
                                }
                            ],
                            "username": "root",
                            "password": "123456"
                        }
                    }
                }
            ]
        }
    }

###### 2. 执行数据同步 ######

python /app/datax/bin/datax.py /app/datax/job/oracle2mysql.json

2021-06-23 12:28:12.390 [job-0] INFO  StandAloneJobContainerCommunicator - Total 1048576 records, 69143488 bytes | Speed 1.65MB/s, 26214 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 26.038s |  All Task WaitReaderTime 8.483s | Percentage 100.00%
    2021-06-23 12:28:12.402 [job-0] INFO  JobContainer - 
    任务启动时刻                    : 2021-06-23 12:27:31
    任务结束时刻                    : 2021-06-23 12:28:12
    任务总计耗时                    :                 41s
    任务平均流量                    :            1.65MB/s
    记录写入速度                    :          26214rec/s
    读出记录总数                    :             1048576
    读写失败总数                    :                   0

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

###### 3. 查看同步数据 ######

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

###### 4. 同步数据正确性和准确性 ######

数据量的准确性和数据的正确性  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 3]  
Oracle同步数据量：1048576  
Mysql插入数据量：1048576

DT同步日期没问题  
COL5数据也没有都是精度

###### 5. 同步日志分析 ######

2021-06-23 12:27:32.228 [job-0] INFO  JobContainer - jobContainer starts to do prepare ...
    2021-06-23 12:27:32.229 [job-0] INFO  JobContainer - DataX Reader.Job [oraclereader] do prepare work .
    2021-06-23 12:27:32.230 [job-0] INFO  JobContainer - DataX Writer.Job [mysqlwriter] do prepare work .
    2021-06-23 12:27:32.232 [job-0] INFO  JobContainer - jobContainer starts to do split ...
    2021-06-23 12:27:32.233 [job-0] INFO  JobContainer - Job set Channel-Number to 3 channels.
    2021-06-23 12:27:32.242 [job-0] INFO  JobContainer - DataX Reader.Job [oraclereader] splits to [1] tasks.
    2021-06-23 12:27:32.243 [job-0] INFO  JobContainer - DataX Writer.Job [mysqlwriter] splits to [1] tasks.
    2021-06-23 12:27:32.276 [job-0] INFO  JobContainer - jobContainer starts to do schedule ...
    2021-06-23 12:27:32.280 [job-0] INFO  JobContainer - Scheduler starts [1] taskGroups.
    2021-06-23 12:27:32.283 [job-0] INFO  JobContainer - Running by standalone Mode.
    2021-06-23 12:27:32.300 [taskGroup-0] INFO  TaskGroupContainer - taskGroupId=[0] start [1] channels for [1] tasks.
    2021-06-23 12:27:32.314 [taskGroup-0] INFO  Channel - Channel set byte_speed_limit to -1, No bps activated.
    2021-06-23 12:27:32.315 [taskGroup-0] INFO  Channel - Channel set record_speed_limit to -1, No tps activated.
    2021-06-23 12:27:32.348 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] taskId[0] attemptCount[1] is started

结果输出:  
可以看到channel设置为3,限速为-1.  
虽然channel设置为3,但是只启动了一个task.  
splitPk如果不填写，将视作用户不对单表进行切分，OracleReader  
使用单通道同步全量数据。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70]: /images/20221005/d6474690cec747c7bd82729f587473ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 1]: /images/20221005/b7734ae6022c4854b6e4728603e505a4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 2]: /images/20221005/c0b364f14d964a60a6d3a907666414a8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgxNjczOA_size_16_color_FFFFFF_t_70 3]: /images/20221005/3f2cbd3627c44ed3ab0933bf584edec7.png