ClickHouse vs Doris for ADS Analytics: MergeTree Compared on the Same Dataset

Background

ClickHouse and Apache Doris are column‑store OLAP engines used for log, metric and wide‑table analysis. ClickHouse stores data in a single‑process server, offers HTTP (port 8123) and native (port 9000) protocols, and relies on the MergeTree family for storage. Doris separates a front‑end (metadata planning) and back‑end (storage) and uses the MySQL protocol.

Architecture

ClickHouse Server combines query parsing, planning, execution and local column‑store read/write in one process. In a cluster, sharding and replicas are added via ClickHouse Keeper. Key roles:

Server : metadata, planning, execution, local storage.

HTTP (8123) : INSERT, SELECT, Play UI.

Native (9000) : binary protocol used by clickhouse-client.

MergeTree fundamentals

Data is stored column‑wise in separate blocks. A MergeTree table is split into immutable parts . Each part is ordered by an ORDER BY expression, partitioned by PARTITION BY, and indexed by a sparse primary key (granule size ≈8192 rows). Query execution prunes data at the granule level.

Engine variants used in the lab:

MergeTree : basic engine, merges files without aggregation.

SummingMergeTree : during merge, rows with the same sorting key are summed for specified numeric columns. Final results may require FINAL or an extra sum aggregation.

ReplacingMergeTree : keeps the latest row per version column; FINAL or waiting for background merge is needed to see the newest value.

Write and read path

Write : data batches are received by the server, written as new immutable parts according to the partition key and ORDER BY, then a background thread merges small parts into larger ones. The model is append‑only; in‑place updates are not the primary use case.

Read : SQL is parsed, partitions are pruned, the sparse primary key discards irrelevant granules, only the needed column files are read, and vectorized execution aggregates the result.

Lab setup

Environment: ClickHouse 24.8‑alpine single‑node Docker, repository labs/lab08-clickhouse-ads/, same orders.csv used for the Doris lab.

Start the container:

./scripts/up.sh 08-clickhouse
./docker/08-clickhouse/scripts/wait-ready.sh

Create the database and tables:

CREATE DATABASE IF NOT EXISTS ads;

CREATE TABLE IF NOT EXISTS ads.orders_di (
  order_id UInt64,
  user_id UInt32,
  city LowCardinality(String),
  amount Decimal(12,2),
  dt Date
) ENGINE = MergeTree
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, city, order_id);

CREATE TABLE IF NOT EXISTS ads.city_gmv_agg (
  dt Date,
  city LowCardinality(String),
  gmv Decimal(18,2),
  order_cnt UInt64
) ENGINE = SummingMergeTree((gmv, order_cnt))
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, city);

Import data via HTTP INSERT (or the provided http-insert.sh script):

curl -sS 'http://127.0.0.1:8123/?query=INSERT INTO ads.orders_di FORMAT CSVWithNames' \
  --data-binary @labs/lab08-clickhouse-ads/data/orders.csv

Run the seed aggregation script and the ADS query script. The top‑city GMV results should match Doris: Shanghai 553, Beijing 339, Guangzhou 88, Shenzhen 50.

Comparison with Doris

Both engines are columnar OLAP, but they differ in protocol, architecture and update semantics.

Doris uses the MySQL protocol, which is convenient for BI tools and JDBC.

ClickHouse uses HTTP and a native binary protocol; its SQL dialect and functions are distinct.

Doris separates front‑end and back‑end, while ClickHouse runs a single process that handles parsing, planning and storage.

Update handling: Doris provides explicit unique/primary‑key semantics; ClickHouse relies on engines such as ReplacingMergeTree or full table rebuild for updates.

Common pitfalls and troubleshooting

HTTP INSERT syntax errors – ensure the query string is URL‑encoded and use the provided script.

SummingMergeTree may return multiple rows before background merge – query with FINAL or aggregate again.

ReplacingMergeTree may show stale rows – add FINAL or wait for the merge to complete.

Result mismatches with Doris – verify that the table was not imported twice and that the filter amount>0 is applied.

Missing clickhouse-client inside the container – use docker/08-clickhouse/scripts/client.sh to execute SQL files.

Takeaways

ClickHouse runs as a single‑process server with HTTP and native interfaces; clustering adds sharding, replicas and Keeper.

Columnar storage + vectorized execution + MergeTree part pruning yields fast analytics on wide tables.

Writes are append‑only; Summing/Replacing semantics depend on asynchronous background merges or the FINAL modifier at query time.

Typical ADS workflow: create table → HTTP INSERT → aggregate / Top‑N query; compare with Doris on the same dataset before final selection.

All code, Dockerfiles and SQL scripts are available in the repository: https://gitcode.com/qq_37953312/big-data