Writing MySQL data to MatrixOne using DataX¶
This is a batch example. Pin DataX, reader/writer and JDBC, then validate JSON and mappings. JSON cannot contain SQL comments. This revision verifies example SQL/JSON, not plugin execution; see migration for consistency and cutover.
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:
Complete standalone MatrixOne deployment.
Install a JDK compatible with the DataX/plugin build. For bundled Groovy 2.1.9, see the native DataX guide for Java 8 validation and Java 21 initialization failures; qualify other JDK versions separately.
Install Python 3.8 (or plus).
Download the DataX installation package and unzip it.
Download matrixonewriter.zip and extract it to the
plugin/writer/directory in the root of your DataX project.Download and install MySQL.
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 values
(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"
}
}
}
],
"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)