clickhouse

ClickHouse

ClickHouse is a columnar DBMS for Online Analytical Processing (OLAP). It is famous for allowing real-time generation of analytical reports using SQL queries on petabytes of data.

When to Use

• Real-Time OLAP Analytics: Aggregating billions of rows per second for real-time dashboards, security SIEMs, and web analytics.
• High-Compression Columnar Storage: Compressing raw event logs by 80-90% using LZ4 and ZSTD column-level codecs.
• Ad-Hoc Analytical Queries: Executing complex aggregations, window functions, and multi-billion-row vector queries in milliseconds.
• Observability Data Stores: Powering log and trace storage backends (e.g. replacing Elasticsearch for OpenTelemetry metrics and logs).

Quick Start

SELECT
    toStartOfHour(EventTime) as Hour,
    count(),
    avg(Duration)
FROM events
GROUP BY Hour
ORDER BY Hour

Core Concepts

MergeTree Engine Family

The foundational storage engine for ClickHouse; writes data in immutable sorted parts and merges them asynchronously in the background:

CREATE TABLE analytics.page_views
(
    event_time DateTime64(3, 'UTC'),
    site_id UInt32,
    user_id UUID,
    url_path LowCardinality(String),
    response_time_ms UInt16,
    user_agent String CODEC(ZSTD(3))
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (site_id, event_time, user_id)
SETTINGS index_granularity = 8192;

Materialized Views with AggregatingMergeTree

Precomputes rolling aggregations at insertion time with zero background batch job overhead:

-- Pre-aggregated Hourly Metrics
CREATE TABLE analytics.hourly_traffic
(
    hour DateTime,
    site_id UInt32,
    total_views SimpleAggregateFunction(sum, UInt64),
    unique_visitors AggregateFunction(uniq, UUID)
)
ENGINE = AggregatingMergeTree()
ORDER BY (site_id, hour);

CREATE MATERIALIZED VIEW analytics.hourly_traffic_mv TO analytics.hourly_traffic AS
SELECT
    toStartOfHour(event_time) AS hour,
    site_id,
    count() AS total_views,
    uniqState(user_id) AS unique_visitors
FROM analytics.page_views
GROUP BY hour, site_id;