如何使用 DataX 工具迁移 Oracle 数据到 OceanBase

SQL> CREATE TABLE departments (
    dept_id NUMBER(10) PRIMARY KEY,
    dept_name VARCHAR2(50) NOT NULL,
    dept_location VARCHAR2(50),
    dept_manager VARCHAR2(50)
);

Table created.

SQL> INSERT INTO departments (dept_id, dept_name, dept_location, dept_manager)
VALUES (1, '人事部', '北京市', '王经理');

SQL> INSERT INTO departments (dept_id, dept_name, dept_location, dept_manager)
VALUES (2, '财务部', '上海市', '张经理');

-- 省略其余插入数据

SQL> INSERT INTO departments (dept_id, dept_name, dept_location, dept_manager)
VALUES (6, '运营部', '武汉市', '周经理');

SQL> INSERT INTO departments (dept_id, dept_name, dept_location, dept_manager)
VALUES (7, '客服部', '南京市', '黄经理');

1 row created.

SQL> SELECT count(1) FROM departments;

  COUNT(1)
----------
         7

obclient> CREATE TABLE departments (
    ->     dept_id NUMBER(10) PRIMARY KEY,
    ->     dept_name VARCHAR2(50) NOT NULL,
    ->     dept_location VARCHAR2(50),
    ->     dept_manager VARCHAR2(50)
    -> );
Query OK, 0 rows affected (0.99 sec)

obclient> SELECT count(1) FROM departments;
+----------+
| COUNT(1) |
+----------+
|        0 |
+----------+
1 row in set (0.14 sec)

wget https://datax-opensource.oss-cn-hangzhou.aliyuncs.com/202303/datax.tar.gz
tar zxvf datax.tar.gz
cd datax

$ tree -L 1 --filelimit 30
.
├── bin
├── conf
├── job
├── lib
├── log
├── log_perf
├── plugin
├── script
└── tmp

{
    "job": {
        "setting": {
            "speed": {
                "channel": 32
            },
            "errorLimit": {
                "percentage": 0.1
            }
        },
        "content": [
            {
                "reader": {
                    "name": "oraclereader",
                    "parameter": {
                        "username": "TEST2",
                        "password": "xxxxxx",
                        "column": ["*"],
                        "connection": [
                            {
                                "table": ["TEST2.DEPARTMENTS"],
                                "jdbcUrl": ["jdbc:oracle:thin:@xx.xx.xx.xx:1521:helowin"]
                            }
                        ]
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "writeMode": "insert",
                        "batchSize": 5000,
                        "memstoreThreshold": "90",
                        "username": "TEST2@oracle#obcluster",
                        "password": "xxxxxx",
                        "column": ["*"],
                        "connection": [
                            {
                                "table": ["TEST2.DEPARTMENTS"],
                                "jdbcUrl": "jdbc:oceanbase://xx.xx.xx.xx:2883/TEST2"
                            }
                        ]
                    }
                }
            }
        ]
    }
}

cd datax/bin
python datax.py ../job/oraclejob4.json

obclient> SELECT * FROM departments;
+---------+-----------+---------------+--------------+
| DEPT_ID | DEPT_NAME | DEPT_LOCATION | DEPT_MANAGER |
+---------+-----------+---------------+--------------+
|       1 | 人事部    | 北京市        | 王经理       |
|       2 | 财务部    | 上海市        | 张经理       |
|       3 | 市场部    | 广州市        | 李经理       |
|       4 | 研发部    | 深圳市        | 赵经理       |
|       5 | 销售部    | 成都市        | 刘经理       |
|       6 | 运营部    | 武汉市        | 周经理       |
|       7 | 客服部    | 南京市        | 黄经理       |
+---------+-----------+---------------+--------------+
7 rows in set (0.03 sec)