🎯

clickhouse

🎯Skill
g1joshi/agent-skills1 Installs9Last Updated Feb 10, 2026

Installation

$npx skills add g1joshi/agent-skills --skill clickhouse

Run it in a terminal; it asks which AI coding tools to install it for (Claude Code, Codex, Cursor and more).

Uses skills.sh Β· Node.js 22.20+

After installing

Ask for related work and the agent uses it on its own, or call it yourself.

Run it (Claude Code)/clickhouse
Install by hand

Copy the whole skill folder from GitHub to the place below. Copying only SKILL.md can leave out files the skill uses.

Open the skill folder β†’
  • This project .claude/skills/clickhouse/
  • All projects ~/.claude/skills/clickhouse/

These paths are for Claude Code. For other tools, the command above puts it in the right place.

Expert ClickHouse columnar database assistance covering MergeTree engines, materialized views, vector search, and real-time ingestion. Use when querying analytical telemetry, logs, time-series data, or building analytics APIs.

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

ErrorCauseSolution
Too many parts in all data in tableIngesting 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 queryQuery 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 inputSchema type mismatch during CSV/JSON ingestion.Verify data format and use input_format_skip_unknown_fields = 1.

References

More from this repository10