Integrating dble with ClickHouse for OLAP: Configuration and Testing Guide

Background dble is a MySQL‑based high‑scalability distributed middleware that excels at OLTP. To meet growing OLAP demands, dble 3.22.01 adds support for ClickHouse as a backend, leveraging ClickHouse’s column‑store performance.

Environment preparation Deploy four Docker containers: two MySQL 5.7.25 instances, a dble 3.22.01 instance, and a ClickHouse 22.6.1 instance. Verify ClickHouse connectivity with:

# clickhouse-client -u test --password password -h 127.0.0.1
ClickHouse client version 22.6.1.1985 (official build).
Connecting to 127.0.0.1:9000 as user test.
...

dble + ClickHouse configuration

1. Add an analysisUser to {install_dir}/dble/conf/user.xml :

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:user SYSTEM "user.dtd">
<dble:user xmlns:dble="http://dble.cloud/">
    <managerUser name="root" password="password"/>
    <analysisUser name="ana1" password="password" dbGroup="ha_group3"/>
</dble:user>

Note: each analysisUser maps to a single dbGroup; multiple analysisUsers may share the same dbGroup.

2. Define the ClickHouse backend in {install_dir}/dble/conf/db.xml :

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:db SYSTEM "db.dtd">
<dble:db xmlns:dble="http://dble.cloud/">
    <dbGroup name="ha_group3" rwSplitMode="0" delayThreshold="100">
        <heartbeat>select user()</heartbeat>
        <dbInstance name="hostM3" password="password" url="172.100.9.13:9004" user="test" maxCon="1000" minCon="10" primary="true" databaseType="clickhouse"/>
    </dbGroup>
</dble:db>

Key points: the heartbeat statement must be ClickHouse‑compatible, the databaseType must be set to clickhouse , and ClickHouse listens on port 9004 (MySQL protocol).

3. Start dble and log in with the analysisUser:

# mysql -h127.0.0.1 -uana1 -ppassword -P8066
mysql> show databases;
+--------------------+
| name               |
+--------------------+
| INFORMATION_SCHEMA |
| default            |
| information_schema |
| system             |
+--------------------+

4. Create a test table via the dble MySQL interface and verify it in ClickHouse:

# mysql -h127.0.0.1 -uana1 -ppassword -P8066
mysql> CREATE TABLE t1 (x String) ENGINE = TinyLog;
mysql> INSERT INTO t1 VALUES (1);
mysql> SELECT x, toTypeName(x) FROM t1;
+---+-------------------+
| x | toTypeName(x)    |
+---+-------------------+
| 1 | String           |
+---+-------------------+

5. Import a 4.4 GB dataset into ClickHouse and compare query results between ClickHouse and dble (omitted for brevity). Screenshots in the original article illustrate identical results.

dble + MySQL + ClickHouse

Update user.xml to include both a shardingUser (for MySQL) and the analysisUser (for ClickHouse). Adjust db.xml to define three dbGroups: two MySQL groups (ha_group1, ha_group2) and the ClickHouse group (ha_group3). Modify sharding.xml to map logical tables to physical sharding nodes and add a hash‑four function.

After restarting dble, the shardingUser can see the MySQL schema, while the analysisUser sees only ClickHouse databases, confirming the mixed OLTP/OLAP architecture:

OLTP: client → dble (shardingUser/rwSplitUser) → MySQL.

Data replication: MySQL → ClickHouse (via external sync tool).

OLAP: client → dble (analysisUser) → ClickHouse.

This guide demonstrates a practical way to combine transactional and analytical workloads using dble as a unified gateway.