Skip to content

Writing MySQL data to MatrixOne using DataX

This article describes how to write MySQL data offline to a MatrixOne database using the DataX tool.

Prepare before you start

Before you can start writing data to MatrixOne using DataX, you need to complete the installation of the following software:

Steps

Create table and insert data in mysql

CREATE TABLE `mysql_datax` (
     `id` bigint(20) NOT NULL,
     `name` varchar(100) DEFAULT NULL,
     `salary` decimal(10,0) DEFAULT NULL,
     `age` int(11) DEFAULT NULL,
     `entrytime` date DEFAULT NULL,
     `gender` char(1) DEFAULT NULL,
      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--Insert sample data
insert into mysql_datax valus
(1,"lisa",15660,30,'2022-10-12',0),
(2,"tom",15060,24,'2021-11-10',1),
(3,"jenny",15000,28,'2024-02-19',0),
(4,"henry",12660,24,'2022-04-22',1);

Create target library table in Matrixone

Since DataX can only synchronize data, not table structure, we need to manually create the table in the target database (Matrixone) before we can perform the task.

CREATE TABLE `mysql_datax` (
     `id` bigint(20) NOT NULL,
     `name` varchar(100) DEFAULT NULL,
     `salary` decimal(10,0) DEFAULT NULL,
     `age` int(11) DEFAULT NULL,
     `entrytime` date DEFAULT NULL,
     `gender` char(1) DEFAULT NULL,
      PRIMARY KEY (`id`)
);

Creating a Job Profile

The task configuration file in DataX is in json format and the built-in task configuration template can be viewed by the following command:

python datax.py -r mysqlreader -w matrixonewriter

Go to the datax/job path and, according to the template, write the job file mysql2mo.json:

{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:mysql://xx.xx.xx.xx:3306/test"],
                                "table": ["mysql_datax"]
                            }
                        ],
                        "password": "root",
                        "username": "root",
                        "where": ""
                    }
                },
                "writer": {
                    "name": "matrixonewriter",
                    "parameter": {
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:mysql://xx.xx.xx.xx:6001/test",
                                "table": ["mysql_datax"]
                            }
                        ],
                        "password": "111",
                        "preSql": [],
                        "session": [],
                        "username": "root",
                        "writeMode": "insert"  --目前仅支持replace,update 或 insert 方式
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": "1"
            }
        }
    }
}

Start the datax job

python /opt/module/datax/bin/datax.py /opt/module/datax/job/mysql2mo.json

View data in a MatrixOne table

mysql> select * from mysql_datax;
+------+-------+--------+------+------------+--------+
| id   | name  | salary | age  | entrytime  | gender |
+------+-------+--------+------+------------+--------+
|    1 | lisa  |  15660 |   30 | 2022-10-12 | 0      |
|    2 | tom   |  15060 |   24 | 2021-11-10 | 1      |
|    3 | jenny |  15000 |   28 | 2024-02-19 | 0      |
|    4 | henry |  12660 |   24 | 2022-04-22 | 1      |
+------+-------+--------+------+------------+--------+
4 rows in set (0.00 sec)