DataX is an open-source version of Alibaba Cloud DataWorks Data Integration. It is an offline data synchronization tool widely used by Alibaba Group. It efficiently synchronizes data between heterogeneous data sources such as MySQL, Oracle, SQL Server, PostgreSQL, Hadoop Distributed File System (HDFS), Hive, ADS, HBase, Tablestore (OTS), MaxCompute (formerly known as ODPS), Hologres, Distributed Relational Database Service (DRDS), and OceanBase AI Database.
Framework design
DataX is an offline data synchronization framework that is designed based on the framework + plugin architecture. It abstracts the data source reads and writes into the reader/writer plugins, which are incorporated into the entire synchronization framework.
The reader plugin is a data collection module that collects data from a data source and sends the data to the framework.
The writer plugin is a data write module that retrieves data from the framework and writes the data to the destination.
The framework builds a data transmission channel to connect the reader and the writer and processes core technical issues such as caching, throttling, concurrency control, and data conversion.
DataX migrates data through tasks. Each task migrates only one table and has a configuration file in JSON format. The configuration file contains two sections: reader and writer. reader and writer respectively correspond to the database reader and writer plugins supported by DataX. For example, when you migrate table data from an Oracle database to OceanBase AI Database, the oraclereader plugin of Oracle and the oceanbasev10writer plugin of OceanBase AI Database are used to respectively read data from the Oracle database and write the data to OceanBase AI Database. The following sections describe the oraclereader and oceanbasev10writer plugins.
oraclereader plugin
The oraclereader plugin reads data from an Oracle database. It connects to a remote Oracle database through JDBC and executes the corresponding SQL statements to select data from the Oracle database.
oraclereader connects to a remote Oracle database through JDBC, generates a query statement based on the configured information, and sends the statement to the remote Oracle database. The remote Oracle database assembles the execution result of the SQL statement into an abstract dataset by using the custom data types of DataX and passes the dataset to the downstream writer for processing.
For more information about the features and parameters, see oraclereader plugin.
oceanbasev10writer plugin
The oceanbasev10writer plugin writes data to the destination table in OceanBase AI Database. It connects to a remote OceanBase AI Database from a Java client (MySQL JDBC or OBClient) by using OBProxy and executes the corresponding INSERT statement to write the data to the remote OceanBase AI Database. The data is committed to the remote OceanBase AI Database in batches.
oceanbasev10writer uses the DataX framework to obtain the protocol data generated by the reader and then generates an insert statement. If a primary key or unique key conflict occurs when data is written, a user tenant of OceanBase AI Database can update all fields in the table by using the replace mode. For performance purposes, the batch write mode is used. A write request is initiated only when the number of rows reaches the specified threshold.
DataX configuration file
The following is an example configuration file:
{
"job": {
"content": [
{
"reader": {
"name": "streamreader",
"parameter": {
"sliceRecordCount": 10,
"column": [
{
"type": "long",
"value": "10"
},
{
"type": "string",
"value": "hello,hello,world-DataX"
}
]
}
},
"writer": {
"name": "streamwriter",
"parameter": {
"encoding": "UTF-8",
"print": true
}
}
}
],
"setting": {
"speed": {
"channel": 2
}
}
}
}
Notice
DataX migrates only the data of a table. Therefore, you must create the schema of the table in the destination database in advance.
Place the JSON configuration file in the job directory of DataX or in a custom path. Run the following command:
$bin/datax.py job/stream2stream.json
The output is as follows:
<.....>
2021-08-26 11:06:09.217 [job-0] INFO JobContainer - PerfTrace not enable!
2021-08-26 11:06:09.218 [job-0] INFO StandAloneJobContainerCommunicator - Total 20 records, 380 bytes | Speed 38B/s, 2 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2021-08-26 11:06:09.223 [job-0] INFO JobContainer -
Task start time : 2021-08-26 11:05:59
Task end time : 2021-08-26 11:06:09
Total task duration : 10s
Average task throughput : 38B/s
Record write speed : 2rec/s
Total records read : 20
Total read/write failures : 0
After DataX executes a task, it generates a simple task report that covers the preceding average traffic, write speed, and total number of read/write failures.
You can specify the speed and error record limit in the job parameter settings of DataX.
"setting": {
"speed": {
"channel": 10
},
"errorLimit": {
"record": 10,
"percentage": 0.1
}
}
The parameters are described as follows:
errorLimit: The limit on the number of error records. When this limit is exceeded, the task is terminated.channel: The concurrency. Technically, a higher concurrency value indicates higher migration performance. In actual operations, you must also consider the read pressure on the source database, network transmission performance, and write performance of the destination database.
Prepare the environment
Download the .tar package from http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz.
Decompress the installation package:
tar zxvf datax.tar.gz
cd datax
The directories are as follows:
$tree -L 1 --filelimit 30
.
├── bin
├── conf
├── job
├── lib
├── log
├── log_perf
├── plugin
├── script
└── tmp
The following table describes the directories in the installation package.
Directory name |
Description |
|---|---|
| bin | The directory where the executable file is located. The datax.py file in this directory is the startup script of DataX tasks. |
| conf | The directory where log files are located. The DataX configuration files unrelated to tasks are stored in this directory. |
| lib | The directory where the libraries required for running are located. The global .jar files required for the running of DataX are stored in this directory. |
| job | The directory where the task configuration file for verifying DataX installation is located. |
| log | The directory where log files are located. The running logs of DataX tasks are stored in this directory. By default, when DataX runs, standard logs are generated and written to the log directory. |
| plugin | The directory where the plugin files are located. The data source plugins supported by DataX are stored in this directory. |
Use DataX to migrate data from an Oracle database to OceanBase AI Database
When you migrate data from an Oracle database to OceanBase AI Database, if the source and destination databases cannot concurrently connect to the DataX server, you can export the data as CSV files and then import the CSV files to the destination database. If the source and destination databases can concurrently connect to the DataX server, you can use DataX to directly migrate data from the source to the destination.
Example: Migrate the data of the ZJSZY.TECTM03 table in an Oracle database to the ZJSZY.TECTM03 table in a user tenant of OceanBase AI Database.
Content of the myjob.json configuration file is as follows:
{
"job":{
"setting":{
"speed":{
"channel":32
},
"errorLimit":{
"percentage": 0.1
}
},
"content":[
{
"reader":{
"name":"oraclereader",
"parameter":{
"username": "user_name",
"password":"******",
"column":["*"],
"connection": [
{
"table": ["ZJSZY.TECTM03"],
"jdbcUrl":["jdbc:oracle:thin:@Oracle_ip:1521:orcl"]
}
]
}
},
"writer":{
"name":"oceanbasev10writer",
"parameter":{
"writeMode":"insert",
"batchSize":5000,
"memstoreThreshold":"90",
"username":"user@tenet#cluster",
"password":"******",
"column":["*"],
"connection": [
{
"table": ["ZJSZY.TECTM03"],
"jdbcUrl":"jdbc:oceanbase://odp_ip:3306/zjszy"
}
]
}
}
}
]
}
}
The parameters in the configuration file are described as follows:
Parameter |
Description |
|---|---|
| name | The name of the reader or writer plugin for connecting to the database. The Oracle reader plugin is oraclereader, and the OceanBase AI Database writer plugin is oceanbasev10writer. For more information about the reader and writer plugins, see DataX data source guide. |
| jdbcUrl | The JDBC URL of the database to which you want to connect. The value is a JSON array and multiple URLs can be entered for a database. You need to enter at least one JDBC URL in the JSON array. The value must be in compliance with the official MySQL format. You can also specify a configuration property in the URL. For more information, see Configuration Properties in the MySQL documentation.
Note
|
| username | The username for logging on to the database.
|
| password | The password of the specified username required to log in to the database.
|
| table | The tables to be synchronized. The value is a JSON array and multiple tables can be specified at the same time. When you specify multiple tables, make sure that they use the same schema structure. The oraclereader plugin does not verify whether the specified tables belong to the same logical table.
NoteThe table string must be included in the
|
| column | The set of names of columns to be synchronized in the configured table. The values are specified in a JSON array. We recommend that you do not set the column parameter to ['*'], because this configuration changes when the schema changes. We recommend that you specify specific column names. Column pruning is supported. You can export only the specified columns. Column reordering is supported. You can export columns without following the column order in the table schema. You can specify constants in the MySQL SQL format: ["id", "`table`", "1", "'bazhen.csy'", "null", "to_char(a + 1)", "2.3" , "true"].
Note
|
| where | The filter condition. The oraclereader plugin assembles the specified column, table, and WHERE clause into an SQL statement. Then, it extracts data based on this SQL statement. To synchronize data of the current day, you can specify the WHERE clause as gmt_create > $bizdate.
NoticeYou cannot set the
|
After the job configuration file is configured, execute this job.
python datax.py ../job/myjob.json
References
For more information about DataX, see DataX.