ClickHouse Reference Architecture

Overview

Production-grade architecture for ClickHouse analytics platforms covering

project layout, data flow, multi-tenancy, and operational patterns.

Prerequisites

• Understanding of ClickHouse fundamentals (engines, ORDER BY, partitioning)
• TypeScript/Node.js project

Instructions

Step 1: Project Structure

my-analytics-platform/
├── src/
│   ├── clickhouse/
│   │   ├── client.ts           # Singleton client with health checks
│   │   ├── schemas/            # SQL DDL files (source of truth)
│   │   │   ├── 001-events.sql
│   │   │   ├── 002-users.sql
│   │   │   └── 003-materialized-views.sql
│   │   ├── queries/            # Named query functions
│   │   │   ├── events.ts
│   │   │   ├── users.ts
│   │   │   └── dashboards.ts
│   │   └── migrations/         # Schema migrations
│   │       ├── runner.ts
│   │       └── 001-add-country.sql
│   ├── ingestion/
│   │   ├── webhook-receiver.ts # HTTP webhook endpoint
│   │   ├── kafka-consumer.ts   # Kafka consumer (if applicable)
│   │   └── buffer.ts           # Insert batching buffer
│   ├── api/
│   │   ├── routes.ts           # API endpoints
│   │   └── middleware.ts       # Auth, rate limiting
│   └── jobs/
│       ├── daily-rollup.ts     # Scheduled aggregations
│       └── cleanup.ts          # TTL enforcement
├── tests/
│   ├── unit/
│   └── integration/
├── docker-compose.yml          # Local ClickHouse
├── init-db/                    # Docker init scripts
└── config/
    ├── development.env
    ├── staging.env
    └── production.env

Step 2: Data Flow Architecture

                    ┌─────────────────┐
                    │   Data Sources   │
                    │  (Webhooks, API, │
                    │   Kafka, S3)     │
                    └────────┬────────┘
                             │
                    ┌────────▼────────┐
                    │  Ingestion Layer │
                    │  (Buffer + Batch │
                    │   10K+ rows/ins) │
                    └────────┬────────┘
                             │
              ┌──────────────▼──────────────┐
              │       ClickHouse Server      │
              │                              │
              │  ┌────────────────────────┐  │
              │  │   Raw Event Tables     │  │
              │  │   (MergeTree, append)  │  │
              │  └───────────┬────────────┘  │
              │              │               │
              │  ┌───────────▼────────────┐  │
              │  │  Materialized Views    │  │
              │  │  (Auto-aggregate on    │  │
              │  │   INSERT — hourly,     │  │
              │  │   daily, tenant-level) │  │
              │  └───────────┬────────────┘  │
              │              │               │
              │  ┌───────────▼────────────┐  │
              │  │  Aggregate Tables      │  │
              │  │  (A

ClickHouse SDK Patterns

Overview

Production patterns for @clickhouse/client — typed queries, streaming inserts,

error handling, and connection lifecycle management.

Prerequisites

• @clickhouse/client installed (see clickhouse-install-auth)
• Familiarity with async/await and Node.js streams

Instructions

Pattern 1: Typed Query Helper

import { createClient } from '@clickhouse/client';

const client = createClient({
  url: process.env.CLICKHOUSE_HOST!,
  username: process.env.CLICKHOUSE_USER ?? 'default',
  password: process.env.CLICKHOUSE_PASSWORD ?? '',
});

// Generic typed query — returns parsed JSON rows
async function query<T>(sql: string, params?: Record<string, unknown>): Promise<T[]> {
  const rs = await client.query({
    query: sql,
    query_params: params,
    format: 'JSONEachRow',
  });
  return rs.json<T>();
}

// Usage
interface EventCount {
  event_type: string;
  cnt: string;  // ClickHouse JSON returns numbers as strings
}

const rows = await query<EventCount>(
  'SELECT event_type, count() AS cnt FROM events WHERE user_id = {user_id:UInt64} GROUP BY event_type',
  { user_id: 42 }
);

Note on parameterized queries: ClickHouse uses {name:Type} syntax for parameters,

not $1 or ?. Always use typed parameters to prevent SQL injection.

Pattern 2: Streaming Insert (Backpressure-Safe)

import { createClient } from '@clickhouse/client';
import { Readable } from 'stream';

// For large inserts, stream data instead of buffering in memory
async function streamInsert(rows: AsyncIterable<Record<string, unknown>>) {
  const stream = new Readable({
    objectMode: true,
    read() {},  // push-based
  });

  const insertPromise = client.insert({
    table: 'events',
    values: stream,
    format: 'JSONEachRow',
  });

  for await (const row of rows) {
    // Backpressure: if push returns false, wait for drain
    if (!stream.push(row)) {
      await new Promise<void>((resolve) => stream.once('drain', resolve));
    }
  }
  stream.push(null);  // Signal end of stream

  await insertPromise;
}

Pattern 3: Batch Insert with Retry

async function batchInsert<T extends Record<string, unknown>>(
  table: string,
  rows: T[],
  batchSize = 10_000,
  maxRetries = 3,
): Promise<{ inserted: number; errors: Error[] }> {
  let inserted = 0;
  const errors: Error[] = [];

  for (let i = 0; i < rows.length; i += batchSize) {
    const batch = rows.slice(i, i + batchSize);
    let attempt = 0;

    while (attempt < maxRetries) {
      try {
        await client.insert({
   

ClickHouse Security Basics

Overview

Secure a ClickHouse deployment with SQL-based user management, network restrictions,

TLS encryption, and query audit logging.

Prerequisites

• ClickHouse admin access
• CLICKHOUSEDEFAULTACCESS_MANAGEMENT=1 for SQL-based user management
• For self-hosted: access to server config files

Instructions

Step 1: Create Restricted Users (SQL-Based RBAC)

-- Create a read-only analyst user
CREATE USER analyst
    IDENTIFIED WITH sha256_password BY 'strong-password-here'
    DEFAULT DATABASE analytics
    SETTINGS
        readonly = 1,                -- Read-only mode
        max_memory_usage = 5000000000,  -- 5GB per query
        max_execution_time = 60;     -- 60s timeout

GRANT SELECT ON analytics.* TO analyst;

-- Create an application user with insert permissions
CREATE USER app_writer
    IDENTIFIED WITH sha256_password BY 'another-strong-password'
    DEFAULT DATABASE analytics;

GRANT SELECT, INSERT ON analytics.* TO app_writer;
-- Explicitly deny destructive operations
REVOKE DROP, ALTER, CREATE ON *.* FROM app_writer;

-- Create an admin user
CREATE USER ch_admin
    IDENTIFIED WITH sha256_password BY 'admin-password'
    SETTINGS PROFILE 'default';

GRANT ALL ON *.* TO ch_admin WITH GRANT OPTION;

Step 2: Use Roles for Permission Groups

-- Create reusable roles
CREATE ROLE data_reader;
GRANT SELECT ON analytics.* TO data_reader;

CREATE ROLE data_writer;
GRANT SELECT, INSERT ON analytics.* TO data_writer;

CREATE ROLE schema_admin;
GRANT CREATE TABLE, ALTER TABLE, DROP TABLE ON analytics.* TO schema_admin;

-- Assign roles to users
GRANT data_reader TO analyst;
GRANT data_writer TO app_writer;
GRANT schema_admin, data_writer TO ch_admin;

-- Verify grants
SHOW GRANTS FOR analyst;
SHOW GRANTS FOR app_writer;

Step 3: Row-Level Security

-- Create a row policy: tenant users only see their own data
CREATE ROW POLICY tenant_isolation ON analytics.events
    FOR SELECT
    USING tenant_id = currentUser()  -- or a mapped value
    TO data_reader;

-- More practical: map users to tenant IDs via settings
CREATE USER tenant_42
    IDENTIFIED WITH sha256_password BY 'pass'
    SETTINGS custom_tenant_id = 42;

CREATE ROW POLICY tenant_filter ON analytics.events
    FOR SELECT
    USING tenant_id = getSetting('custom_tenant_id')
    TO tenant_42;

Step 4: Network Security

<!-- config.xml — restrict listen addresses -->
<listen_host>0.0.0.0</listen_host>  <!-- or specific IP -->

<!-- IP allowlist per user -->
<users>
    <app_writer>
        <networks>
            <ip>10.0.0.0/8</ip>          <!-- VPC only -->
            <i

ClickHouse Upgrade & Migration

Overview

Safely upgrade ClickHouse server and the @clickhouse/client Node.js SDK,

with rollback procedures and breaking change detection.

Prerequisites

• Current ClickHouse version known (SELECT version())
• Git for version control
• Test suite for integration validation
• Staging environment for pre-production testing

Instructions

Step 1: Check Current Versions

# Check server version (via HTTP)
curl 'http://localhost:8123/?query=SELECT+version()'

# Check Node.js client version
npm list @clickhouse/client

# Check latest available
npm view @clickhouse/client version

-- Server-side version details
SELECT
    version()           AS server_version,
    uptime()            AS uptime_sec,
    currentDatabase()   AS current_db;

Step 2: Review Changelog

# View release notes
open https://github.com/ClickHouse/clickhouse-js/releases

# Server changelog
open https://github.com/ClickHouse/ClickHouse/blob/master/CHANGELOG.md

Key breaking changes to watch for:

• Client API signature changes (createClient options)
• Default setting changes (compression, timeouts)
• New query result format behavior
• Deprecated SQL functions removed in server upgrades
• MergeTree settings renamed or defaults changed

Step 3: Upgrade the Node.js Client

git checkout -b upgrade/clickhouse-client
npm install @clickhouse/client@latest
npm test

Common migration patterns:

// v0.x → v1.x: createClient options restructured
// Before (v0.x)
import { createClient } from '@clickhouse/client';
const client = createClient({
  host: 'http://localhost:8123',
});

// After (v1.x)
const client = createClient({
  url: 'http://localhost:8123',   // 'host' renamed to 'url'
});

// v0.x → v1.x: query result handling
// Before: rs.json() returned { data: [...], statistics: {...} }
// After: rs.json() returns the rows array directly

// Before
const result = await rs.json();
const rows = result.data;

// After
const rows = await rs.json();

Step 4: Upgrade ClickHouse Server

ClickHouse Cloud: Upgrades happen automatically. Check release notes in

the Cloud console.

Self-hosted upgrade procedure:

# 1. Backup current data
clickhouse-client --query "BACKUP DATABASE analytics TO Disk('backups', 'pre-upgrade')"

# 2. Check compatibility
clickhouse-client --query "SELECT * FROM system.settings WHERE changed"

# 3. Stop server gracefully
sudo syst

ClickHouse Data Ingestion

Overview

Build data ingestion pipelines into ClickHouse from HTTP webhooks, Kafka,

and streaming sources with proper batching, deduplication, and error handling.

Prerequisites

• ClickHouse table with appropriate engine (see clickhouse-core-workflow-a)
• @clickhouse/client connected

Instructions

Step 1: Webhook Receiver with Batched Inserts

import express from 'express';
import { createClient } from '@clickhouse/client';

const client = createClient({ url: process.env.CLICKHOUSE_HOST! });
const app = express();
app.use(express.json());

// Buffer for batching — ClickHouse hates one-row-at-a-time inserts
const buffer: Record<string, unknown>[] = [];
const BATCH_SIZE = 5_000;
const FLUSH_INTERVAL_MS = 5_000;

async function flushBuffer() {
  if (buffer.length === 0) return;
  const batch = buffer.splice(0, buffer.length);

  try {
    await client.insert({
      table: 'analytics.events',
      values: batch,
      format: 'JSONEachRow',
    });
    console.log(`Flushed ${batch.length} events to ClickHouse`);
  } catch (err) {
    console.error('Insert failed, re-queuing:', (err as Error).message);
    buffer.unshift(...batch);  // Put back at front for retry
  }
}

// Flush periodically
setInterval(flushBuffer, FLUSH_INTERVAL_MS);

// Webhook endpoint
app.post('/ingest', async (req, res) => {
  const events = Array.isArray(req.body) ? req.body : [req.body];

  for (const event of events) {
    buffer.push({
      event_type: event.type ?? 'unknown',
      user_id: event.userId ?? 0,
      properties: JSON.stringify(event.properties ?? {}),
      created_at: new Date().toISOString().replace('T', ' ').slice(0, 19),
    });
  }

  if (buffer.length >= BATCH_SIZE) {
    await flushBuffer();
  }

  res.status(202).json({ queued: events.length, buffer_size: buffer.length });
});

Step 2: Kafka Table Engine (Server-Side Ingestion)

-- Create a Kafka engine table (consumes messages automatically)
CREATE TABLE analytics.events_kafka (
    event_type  String,
    user_id     UInt64,
    properties  String,
    timestamp   DateTime
)
ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'kafka:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse_consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 2,
    kafka_max_block_size = 65536;

-- Materialized view pipes Kafka → MergeTree automatically
CREATE MATERIALIZED VIEW analytics.events_kafka_mv
TO analytics.events
AS SELECT
    event_type,
    user_id,
    properties,
    timestamp AS created_at
FROM analytics.events_kafka;

-- ClickHouse now consumes from Kafka continuously!
-- Check lag:
SELECT * FROM system.kafka_consumers;

S