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;

Vectorized SIMD Query Execution

ClickHouse processes data in vectorized arrays of column primitives, utilizing hardware CPU SIMD instructions to aggregate millions of values per cycle:

-- Sub-10ms aggregation over 50,000,000 rows
SELECT
    url_path,
    count() AS hits,
    quantile(0.95)(response_time_ms) AS p95_latency
FROM analytics.page_views
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY url_path
ORDER BY hits DESC
LIMIT 10;

Common Patterns

ReplacingMergeTree for Deduplicated Real-Time Analytics

Problem: High-speed real-time event streaming frequently delivers duplicate logs or updated state rows.

Solution: Use ReplacingMergeTree with primary sorting key:

CREATE TABLE telemetry.sensor_metrics (
  sensor_id UInt32,
  recorded_at DateTime64(3),
  reading Float64,
  version UInt64
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (sensor_id, recorded_at);

-- Query latest state across parts
SELECT sensor_id, recorded_at, reading
FROM telemetry.sensor_metrics FINAL
WHERE sensor_id = 101;

Best Practices

Do:

• Insert in Large Batches: Stream data in batches of 10,000 to 100,000 rows (or at 1-second intervals) to prevent creating excessive small parts.
• Use LowCardinality(String): Compress string columns with fewer than 10,000 unique values into indexed integer dictionaries.
• Order by Most-Filtered Columns First: Arrange primary sort keys from lowest cardinality to highest cardinality (site_id, event_time, user_id).
• Use ReplacingMergeTree for Deduplication: Deduplicate rows based on version numbers during background merges.

Don't:

• Perform single-row INSERT statements: Inserting single rows overwhelms the MergeTree engine and exhausts filesystem inodes.
• Use ClickHouse for transactional OLTP: ClickHouse does not support ACID row locking, single-record updates, or foreign key cascades.
• Create too many partitions: Keep partition counts under 1,000 per table; partition by month (toYYYYMM) rather than day.

Troubleshooting

Error	Cause	Solution
Too many parts in all data in table	Ingesting too many tiny micro-batches instead of large batches.	Buffer writes into batches of 10,000+ rows or use an asynchronous insert buffer.
Memory limit exceeded for query	Query scanning uncompressed memory without proper partition filter.	Add WHERE filter on the primary key sorting prefix, or increase max_memory_usage.
DB::Exception: Cannot parse input	Schema type mismatch during CSV/JSON ingestion.	Verify data format and use input_format_skip_unknown_fields = 1.

References

• ClickHouse Documentation