DataWorks:ClickHouse data source

Supported ClickHouse versions

• ApsaraDB for ClickHouse data sources of V20.8 or V21.8 are supported.

• You must make sure that the driver version is compatible with the version of your ClickHouse database. ClickHouse Reader and ClickHouse Writer support only the ClickHouse database driver of the following version:

  <dependency>
      <groupId>ru.yandex.clickhouse</groupId>
      <artifactId>clickhouse-jdbc</artifactId>
      <version>0.2.4</version>
  </dependency>

Limits

Batch data read and write

• Only exclusive resource groups for Data Integration are supported.

• ClickHouse Reader and ClickHouse Writer connect to ClickHouse databases by using Java Database Connectivity (JDBC) and can read data from and write data to the databases only by using JDBC statements.

• ClickHouse Reader allows you to read data from the specified columns in an order different from that specified in the schema of the source table. ClickHouse Writer allows you to write data to the specified columns in an order different from that specified in the schema of the destination table.

• If ClickHouse Writer writes data in INSERT mode, we recommend that you throttle the transactions per second (TPS) to 1,000 to prevent high workloads on ClickHouse.

Supported data types

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following sections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a data source

Before you configure a data synchronization task to synchronize data from or to a specific data source, you must add the data source to DataWorks. For more information, see Add and manage data sources.

Configure a batch synchronization task to synchronize data of a single table

• For more information about the configuration procedure, see Configure a batch synchronization task by using the codeless UI and Configure a batch synchronization task by using the code editor.

• For information about all parameters that are configured and the code that is run when you use the code editor to configure a batch synchronization task, see Appendix: Code and parameters.

Configure synchronization settings to implement batch synchronization of all data in a database

For more information about the configuration procedure, see Configure a synchronization task in Data Integration.

Appendix: Code and parameters

Appendix: Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization task, you must configure parameters for the reader and writer of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization task by using the code editor. The following information describes the configuration details of parameters for the reader and writer in the code editor.

Code for ClickHouse Reader

{
    "type": "job",
    "version": "2.0",
    "steps": [
        {
            "stepType": "clickhouse", // The plug-in name. 
            "parameter": {
                "fetchSize":1024,// The number of data records to read at a time. 
                "datasource": "example",
                "column": [   // The names of the columns. 
                    "id",
                    "name"
                ],
                "where": "",    // The WHERE clause. 
                "splitPk": "",  // The shard key. 
                "table": ""    // The name of the table. 
            },
            "name": "Reader",
            "category": "reader"
        },
        {
            "stepType": "clickhouse",
            "parameter": {
                "postSql": [
                    "update @table set db_modify_time = now() where db_id = 1"
                ],
                "datasource": "example",    // The name of the data source. 
                "batchByteSize": "67108864",
                "column": [
                    "id",
                    "name"
                ],
                "writeMode": "insert",
                "encoding": "UTF-8",
                "batchSize": 1024,
                "table": "ClickHouse_table",
                "preSql": [
                    "delete from @table where db_id = -1"
                ]
            },
            "name": "Writer",
            "category": "writer"
        }
    ],
    "setting": {
        "executeMode": null,
        "errorLimit": {
            "record": "0"  // The maximum number of dirty data records allowed. 
        },
        "speed": {
         "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1 // The maximum number of parallel threads. 
            "mbps":"12",// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order": {
        "hops": [
            {
                "from": "Reader",
                "to": "Writer"
            }
        ]
    }
}

Parameters in code for ClickHouse Reader

Code for ClickHouse Writer

{
    "type":"job",
    "version":"2.0",// The version number. 
    "steps":[
        {
            "stepType":"stream",
            "parameter":{},
            "name":"Reader",
            "category":"reader"
        },
        {
            "stepType":"clickhouse",// The plug-in name. 
            "parameter":{
                "username": "",
                "password": "",
                "column": [// The names of the columns. 
                    "id",
                    "name"
                ],
                "connection": [
                    {
                        "table": [// The name of the table. 
                            "ClickHouse_table"
                        ],
                        "jdbcUrl": "jdbc:clickhouse://ip:port/database"
                    }
                ],
                "preSql": [ // The SQL statement that you want to execute before the synchronization task is run. 
                    "TRUNCATETABLEIFEXISTStablename"
                ],
                "postSql": [// The SQL statement that you want to execute after the synchronization task is run. 
                    "ALTERTABLEtablenameUPDATEcol1=1WHEREcol2=2"
                ],
                "batchSize": "1024",
                "batchByteSize": "67108864",
                "writeMode": "insert"
            },
            "name":"Writer",
            "category":"writer"
        }
    ],
    "setting":{
        "errorLimit":{
            "record":"0"// The maximum number of dirty data records allowed. 
        },
        "speed":{
            "throttle":true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
            "concurrent":1, // The maximum number of parallel threads. 
            "mbps":"12",// The maximum transmission rate. Unit: MB/s. 
        }
    },
    "order":{
        "hops":[
            {
                "from":"Reader",
                "to":"Writer"
            }
        ]
    }
}

Parameters in code for ClickHouse Writer