clickhouse

ClickHouse

ClickHouse is a column-oriented database management system designed for online analytical processing. Where traditional row-based databases excel at transactional workloads, ClickHouse shines when you need to scan and aggregate billions of rows in seconds. It achieves this through columnar storage, vectorized query execution, and aggressive compression.

This skill walks through deploying ClickHouse with Docker, creating tables with the MergeTree engine family, inserting data, running aggregation queries, and connecting from a Node.js application.

Deploying ClickHouse with Docker

The fastest way to get a ClickHouse instance running is with the official Docker image. This gives you both the server and the built-in clickhouse-client CLI.

# docker-compose.yml — ClickHouse single-node deployment
# Exposes the HTTP interface on 8123 and the native protocol on 9000

docker run -d \
  --name clickhouse-server \
  -p 8123:8123 \
  -p 9000:9000 \
  -v clickhouse-data:/var/lib/clickhouse \
  -v clickhouse-logs:/var/log/clickhouse-server \
  clickhouse/clickhouse-server:latest

Once the container is running, connect with the native client:

# CLI — connect to the running ClickHouse instance
docker exec -it clickhouse-server clickhouse-client

You can also query via HTTP, which is useful for quick health checks and lightweight integrations:

# curl — HTTP interface query example
curl 'http://localhost:8123/' --data-binary 'SELECT version()'

Table Engines and MergeTree

ClickHouse organizes data through table engines, and MergeTree is the workhorse you will use for almost everything. It supports primary key indexing, data partitioning, and automatic background merges that keep query performance high even under continuous inserts.

-- DDL — create an events table using MergeTree engine
-- Partitioned by month for efficient time-range queries
-- Ordered by event timestamp and user for fast filtered aggregations

CREATE TABLE events (
    event_id     UUID DEFAULT generateUUIDv4(),
    user_id      UInt64,
    event_name   LowCardinality(String),
    properties   String,
    created_at   DateTime DEFAULT now()
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id);