ApsaraDB for SelectDB:Import data using DataX

Step 1: Set up the DataX environment

1. Download the DataX package from the Doris extension on GitHub.

2. Run the initialization script:

   sh init-env.sh

3. Compile the DataX project:

   If you need the hdfsreader, hdfswriter, ossreader, or osswriter plug-ins, additional JAR packages are required. To skip these plug-ins, remove their modules from DataX/pom.xml before compiling.

   cd DataX/
   mvn package assembly:assembly -Dmaven.test.skip=true

   The compiled output is stored in the target/datax/datax/ directory.

4. Compile the mysqlreader and doriswriter plug-ins separately:

   mvn clean install -pl plugin-rdbms-util,mysqlreader,doriswriter -DskipTests

Step 2: Prepare test data in MySQL

1. Create a test table in MySQL:

   CREATE TABLE `employees` (
     `emp_no`     int          NOT NULL,
     `birth_date` date         NOT NULL,
     `first_name` varchar(14)  NOT NULL,
     `last_name`  varchar(16)  NOT NULL,
     `gender`     enum('M','F') NOT NULL,
     `hire_date`  date         NOT NULL,
     PRIMARY KEY (`emp_no`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

2. Generate test data using Data Management (DMS).

Step 3: Create the destination table in ApsaraDB for SelectDB

1. Connect to your ApsaraDB for SelectDB instance over the MySQL protocol. For details, see Connect to an instance.

2. Create a database and a destination table:

   CREATE DATABASE test_db;
   
   USE test_db;
   CREATE TABLE employees (
       emp_no     int NOT NULL,
       birth_date date,
       first_name varchar(20),
       last_name  varchar(20),
       gender     char(2),
       hire_date  date
   )
   UNIQUE KEY(`emp_no`)
   DISTRIBUTED BY HASH(`emp_no`) BUCKETS 1;

Step 4: Run the DataX import job

1. Apply for a public endpoint for your ApsaraDB for SelectDB instance. For details, see Apply for or release a public endpoint.

2. Add the public IP address of the DataX host to the IP address whitelist of the instance. For details, see Configure an IP address whitelist.

3. Create the job configuration file mysqlToSelectDB.json:

   {
     "job": {
       "content": [
         {
           "reader": {
             "name": "mysqlreader",
             "parameter": {
               "column": [
                 "emp_no", "birth_date", "first_name",
                 "last_name", "gender", "hire_date"
               ],
               "where": "emp_no>0",
               "connection": [
                 {
                   "jdbcUrl": [
                     "jdbc:mysql://host:port/test_db?useUnicode=true&allowPublicKeyRetrieval=true&characterEncoding=utf-8"
                   ],
                   "table": ["employees"]
                 }
               ],
               "password": "123456",
               "splitPk": "emp_no",
               "username": "admin"
             }
           },
           "writer": {
             "name": "doriswriter",
             "parameter": {
               "loadUrl": [
                 "selectdb-cn-xxx-public.selectdbfe.rds.aliyuncs.com:8080"
               ],
               "loadProps": {
                 "format": "json",
                 "strip_outer_array": "true"
               },
               "column": [
                 "emp_no", "birth_date", "first_name",
                 "last_name", "gender", "hire_date"
               ],
               "username": "admin",
               "password": "123456",
               "postSql": [],
               "preSql": [],
               "connection": [
                 {
                   "jdbcUrl": "jdbc:mysql://selectdb-cn-xxx-public.selectdbfe.rds.aliyuncs.com:9030/test_db",
                   "table": ["employees"],
                   "selectedDatabase": "test_db"
                 }
               ],
               "maxBatchRows": 1000000,
               "batchSize": 536870912000
             }
           }
         }
       ],
       "setting": {
         "errorLimit": {
           "percentage": 0.02,
           "record": 0
         },
         "speed": {
           "channel": 5
         }
       }
     }
   }

4. Submit the job:

   cd target/datax/datax/bin
   python datax.py ../mysqlToSelectDB.json

Use custom delimiters

To change the delimiters, set them in loadProps. Use non-printable characters such as \x01 and \x02 to avoid conflicts with data that contains tabs or newlines:

"loadProps": {
    "format": "csv",
    "column_separator": "\\x01",
    "line_delimiter": "\\x02"
}

Use JSON format

To import data in JSON format instead:

"loadProps": {
    "format": "json",
    "strip_outer_array": true
}