Part 172: ClickHouse SQL: MergeTree Engine Family & High-Speed Ingestion

The open-source real-time analytical database capable of ingesting millions of rows per second.

1. The MergeTree Engine Family

The core foundation of ClickHouse. Data is written in immutable sorted parts on disk and continuously merged in the background: - MergeTree: Default high-performance analytical engine. - ReplacingMergeTree: Automatically deduplicates records based on primary key during background merges. - SummingMergeTree: Pre-aggregates numeric values on identical sorting keys. - AggregatingMergeTree: Stores intermediate states of aggregate functions for ultra-fast dashboards.

2. Table Definition & Primary Key Semantics

CREATE TABLE event_log (
    event_time DateTime,
    tenant_id UInt32,
    user_id UInt64,
    event_type LowCardinality(String),
    response_time_ms UInt16
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_type, event_time);

[!NOTE] In ClickHouse, ORDER BY defines the primary sparse index! Unlike relational engines, ClickHouse indexes one row every 8,192 rows (sparse index), keeping index memory usage tiny.

3. High-Speed Bulk Ingestion Rules

• Never execute individual single-row INSERT statements in ClickHouse.
• Always batch inserts in groups of 10,000 to 100,000 rows per HTTP request to avoid creating millions of tiny parts on disk.

Key Takeaways

• ClickHouse is built for real-time observability, telemetry, and high-frequency financial ticks.

In Part 173, we explore Databricks & Apache Spark SQL: Delta Lake & Time Travel!