ClickHouse for Analytics: Fast Aggregations on Billions of Rows

Why ClickHouse?

ClickHouse is a columnar OLAP database that processes billions of rows per second. It's built for analytics, not transactions.

Core Architecture

ClickHouse stores data column-by-column instead of row-by-row:

• Reads only needed columns
• Extreme compression ratios
• SIMD vectorized processing
• Perfect for SELECT with many rows, few columns

MergeTree Table Engine

-- The primary table engine for analytics
CREATE TABLE events (
  event_date Date,
  event_time DateTime,
  user_id UInt64,
  session_id String,
  event_type LowCardinality(String),
  properties JSON,
  revenue Decimal(10, 2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)   -- Partition by month
ORDER BY (event_type, user_id, event_time)  -- Sorting key
TTL event_date + INTERVAL 1 YEAR DELETE   -- Auto-delete old data
SETTINGS index_granularity = 8192;

ReplicatedMergeTree (HA)

CREATE TABLE events ON CLUSTER '{cluster}'
(...)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, user_id, event_time);

Materialized Views for Pre-Aggregation

-- Create aggregated view (updated on insert)
CREATE MATERIALIZED VIEW events_daily_mv
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type)
AS
SELECT
  event_date,
  event_type,
  uniqState(user_id) AS unique_users,
  countState() AS event_count,
  sumState(revenue) AS total_revenue
FROM events
GROUP BY event_date, event_type;

-- Query the view (10000x faster than scanning raw events)
SELECT
  event_date,
  event_type,
  uniqMerge(unique_users) AS unique_users,
  countMerge(event_count) AS events,
  sumMerge(total_revenue) AS revenue
FROM events_daily_mv
WHERE event_date >= today() - 30
GROUP BY event_date, event_type
ORDER BY event_date, revenue DESC;

Fast Analytics Queries

-- Count unique users in last 7 days
SELECT uniq(user_id) as unique_users
FROM events
WHERE event_date >= today() - 7;

-- Funnel analysis
SELECT
  countIf(step >= 1) AS step1,
  countIf(step >= 2) AS step2,
  countIf(step >= 3) AS step3
FROM (
  SELECT
    user_id,
    windowFunnel(86400)(  -- 24-hour window
      event_time,
      event_type = 'page_view',
      event_type = 'add_to_cart',
      event_type = 'purchase'
    ) AS step
  FROM events
  WHERE event_date >= today() - 30
  GROUP BY user_id
);

-- Retention cohort
SELECT
  toStartOfWeek(first_seen) AS cohort,
  dateDiff('week', first_seen, activity_week) AS week_number,
  count(DISTINCT user_id) AS users
FROM (
  SELECT user_id, MIN(event_date) AS first_seen FROM events GROUP BY user_id
) first_events
JOIN events USING (user_id)
RENAME COLUMN event_date TO activity_week
GROUP BY cohort, week_number
ORDER BY cohort, week_number;

Kafka Integration

-- Read directly from Kafka
CREATE TABLE kafka_events (
  event_type String,
  user_id UInt64,
  timestamp UInt64
)
ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list = 'events',
         kafka_group_name = 'clickhouse-consumer',
         kafka_format = 'JSONEachRow';

-- Materialize Kafka stream into ClickHouse table
CREATE MATERIALIZED VIEW kafka_to_events TO events AS
SELECT
  toDate(fromUnixTimestamp64Milli(timestamp)) AS event_date,
  fromUnixTimestamp64Milli(timestamp) AS event_time,
  user_id,
  event_type
FROM kafka_events;

Node.js Client

import { createClient } from '@clickhouse/client'

const client = createClient({
  host: 'https://clickhouse.example.com:8443',
  username: 'default',
  password: process.env.CLICKHOUSE_PASSWORD,
  database: 'analytics',
})

// Streaming large result
const resultSet = await client.query({
  query: 'SELECT event_date, count() FROM events GROUP BY event_date',
  format: 'JSONEachRow',
})

for await (const row of resultSet.stream()) {
  console.log(row)
}

// Insert batch
await client.insert({
  table: 'events',
  values: events,
  format: 'JSONEachRow',
})