Write ClickHouse data to OmniFabric using DataX

This article describes how to write ClickHouse data offline to a OmniFabric database using the DataX tool.

Prepare before you start

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

Finished installing and starting OmniFabric.
Install JDK 8+ version.
Install Python 3.8 (or plus).
Download the DataX installation package and unzip it.
Completed ClickHouse installation deployment
Download matrixonewriter.zip and extract it to the plugin/writer/ directory in the root of your DataX project.
Install the MySQL Client.

Steps

Log in to the clickhouse database to create test data

create database source_ck_database;
use source_ck_database;

create table if not exists student(
`id` Int64 COMMENT 'student id',
`name` String COMMENT 'student name',
`birthday` String COMMENT 'student birth date',
`class` Int64 COMMENT 'student class number',
`grade` Int64 COMMENT 'student grade number',
`score` decimal(18,0) COMMENT 'student score'
) engine = MergeTree
order by id;

Importing data using datax

Using clickhousereader

Note: Datax cannot synchronize table structures, so you need to create the table OmniFabric build statement in OmniFabric in advance:

CREATE TABLE  datax_db.`datax_ckreader_ck_student` (
  `id` bigint(20) NULL COMMENT "",
  `name` varchar(100) NULL COMMENT "",
  `birthday` varchar(100) NULL COMMENT "",
  `class` bigint(20) NULL COMMENT "",
  `grade` bigint(20) NULL COMMENT "",
  `score` decimal(18, 0) NULL COMMENT ""
);

CREATE TABLE  datax_db.`datax_rdbmsreader_ck_student` (
  `id` bigint(20) NULL COMMENT "",
  `name` varchar(100) NULL COMMENT "",
  `birthday` varchar(100) NULL COMMENT "",
  `class` bigint(20) NULL COMMENT "",
  `grade` bigint(20) NULL COMMENT "",
  `score` decimal(18, 0) NULL COMMENT ""
);

Upload clikchousereader to the $DATAX_HOME/plugin/reader directory Unzip the installation package:

[root@root ~]$ unzip clickhousereader.zip

Move the archive to the /opt/ directory:

[root@root ~] mv clickhousereader.zip /opt/
 ```

Writing a task json file

```bash
[root@root ~] vim $DATAX_HOME/job/ck2sr.json

{
  "job": {
    "setting": {
  "speed": {
"channel": "1"
  }
    },
    "content": [
  {
    "reader": {
      "name": "clickhousereader",
      "parameter": {
        "username": "default",
        "password": "123456",
        "column": [
          "*"
        ],
        "splitPK": "id",
        "connection": [
          {
            "table": [
              "student"
            ],
            "jdbcUrl": [
              "jdbc:clickhouse://xx.xx.xx.xx:8123/source_ck_database"
            ]
          }
        ]
      }
    },
    "writer": {
      "name": "OmniFabricwriter",
      "parameter": {
        "column": [
          "*"
        ],
        "connection": [
          {
            "jdbcUrl": "jdbc:mysql://xx.xx.xx.xx:6001/datax_db",
            "table": [
              "datax_ckreader_ck_student"
            ]
          }
        ],
        "password": "111",
        "username": "root",
        "writeMode": "insert"
      }
    }
  }
    ]
  }
}

Perform import tasks

[root@root ~] cd $DATAX_HOME/bin
[root@root ~] ./python datax.py ../jobs/ck2sr.json

Importing with Rdbmsreader

Upload the ClickHouse JDBC driver to the $DATAX_HOME/plugin/reader/rdbmsreader/libs/ directory

Modify the configuration file

[root@root ~] vim $DATAX_HOME/plugin/reader/rdbmsreader/plugin.json

{
    "name": "rdbmsreader",
    "class": "com.alibaba.datax.plugin.reader.rdbmsreader.RdbmsReader",
    "description": "useScene: prod. mechanism: Jdbc connection using the database, execute select sql, retrieve data from the ResultSet. warn: The more you know about the database, the less problems you encounter.",
    "developer": "alibaba",
    "drivers":["dm.jdbc.driver.DmDriver", "com.sybase.jdbc3.jdbc.SybDriver", "com.edb.Driver", "org.apache.hive.jdbc.HiveDriver","com.clickhouse.jdbc.ClickHouseDriver"]
}

Writing a json task file

[root@root ~]  vim $DATAX_HOME/job/ckrdbms2sr.json

{
  "job": {
    "setting": {
  "speed": {
    "byte": 1048576
  }
    },
    "content": [
  {
    "reader": {
      "name": "rdbmsreader",
      "parameter": {
        "username": "default",
        "password": "123456",
        "column": [
          "*"
        ],
        "splitPK": "id",
        "connection": [
          {
            "table": [
              "student"
            ],
            "jdbcUrl": [
              "jdbc:clickhouse://xx.xx.xx.xx:8123/source_ck_database"
            ]
          }
        ]
      }
    },
    "writer": {
      "name": "OmniFabricwriter",
      "parameter": {
        "column": [
          "*"
        ],
        "connection": [
          {
            "jdbcUrl": "jdbc:mysql://xx.xx.xx.xx:6001/datax_db",
            "table": [
              "datax_rdbmsreader_ck_student"
            ]
          }
        ],
        "password": "111",
        "username": "root",
        "writeMode": "insert"
      }
    }
  }
    ]
  }
}

Perform the import task

[root@root ~] cd $DATAX_HOME/bin
[root@root ~] ./python datax.py ../jobs/ckrdbms2sr.json