{"skill":{"slug":"clickhouse-developer","displayName":"Clickhouse for Developers","summary":"Comprehensive ClickHouse skill covering everything you need to work with a ClickHouse analytics database: schema design, query optimization, insert strategie...","description":"---\nname: clickhouse\ndescription: >\n  Comprehensive ClickHouse skill covering everything you need to work with a ClickHouse analytics database:\n  schema design, query optimization, insert strategies, CLI usage, table creation and migrations,\n  backend integration (Node.js, Python, Go), Redis caching strategy, cluster vs single-node differences,\n  and how to test/debug data in the database. MUST USE whenever the user mentions ClickHouse, asks about\n  analytics tables, high-volume insert pipelines, MergeTree schemas, ORDER BY / PRIMARY KEY design,\n  materialized views, ClickHouse query performance, connecting to ClickHouse from code, or running\n  ClickHouse CLI commands to inspect data.\n---\n\n# ClickHouse Skill\n\nA complete reference for designing, operating, querying, and integrating ClickHouse from backend services.\nClickHouse is a **columnar, append-optimized** analytics database — it is NOT a transactional database.\nDesign everything around that fact.\n\n> Official docs: https://clickhouse.com/docs/best-practices\n\n---\n\n## Quick Reference — Read the Right Section\n\n| Task | Go To |\n|------|-------|\n| Design a new table | [Schema Design](#schema-design) |\n| Write a migration | [Migrations](#migrations) |\n| Insert data from code | [Insert Strategy](#insert-strategy) |\n| Run queries / inspect the DB | [CLI Reference](#cli-reference) |\n| Connect from Node.js / Python / Go | [Backend Integration](#backend-integration) |\n| Optimize a slow query | [Query Optimization](#query-optimization) |\n| Decide on Redis vs direct query | [Redis Caching Strategy](#redis-caching-strategy) |\n| Understand cluster behavior | [Cluster Considerations](#cluster-considerations) |\n\n---\n\n## Core Mental Model\n\nClickHouse is an **append-only, batch-oriented** analytics database.\nThe biggest performance wins come from:\n\n1. **Writing large batches** (10K–100K rows), not individual rows\n2. **Choosing ORDER BY carefully** — it is immutable and drives all query performance\n3. **Using native types** — never store everything as String\n4. **Reading many rows across few columns** — not few rows across many columns\n\nAvoid ClickHouse for:\n- OLTP workloads (frequent single-row reads/writes)\n- Complex multi-table JOINs on huge tables\n- Frequent UPDATE/DELETE patterns\n\n---\n\n## Schema Design\n\n### Step 1 — Plan ORDER BY Before Creating Any Table\n\n**ORDER BY is immutable.** Changing it requires creating a new table and migrating all data.\nGet it right before writing a single row.\n\nQuestions to answer first:\n- What columns appear in `WHERE` clauses most often?\n- What is the cardinality (number of distinct values) of each filter column?\n- Is there a mandatory filter that every query has (e.g. `tenant_id`, `app_id`)?\n- Are date ranges a common filter?\n\n```sql\n-- BAD: UUID as first ORDER BY column — no index benefit\nCREATE TABLE events (\n    id UUID,\n    timestamp DateTime,\n    event_type String,\n    user_id UInt64\n) ENGINE = MergeTree()\nORDER BY (id);\n\n-- GOOD: Low cardinality first, then date, then higher cardinality\nCREATE TABLE events (\n    id UUID,\n    timestamp DateTime,\n    event_type LowCardinality(String),\n    user_id UInt64\n) ENGINE = MergeTree()\nORDER BY (event_type, toDate(timestamp), user_id);\n```\n\n**Cardinality ordering rule:** Put columns with **fewer distinct values first**.\n\n| Position | Cardinality | Examples |\n|----------|-------------|----------|\n| 1st | Low (2–1,000) | `event_type`, `status`, `country` |\n| 2nd | Date (coarse) | `toDate(timestamp)` |\n| 3rd+ | Medium-High | `user_id`, `session_id` |\n| Last | High (if needed) | `event_id`, UUID |\n\n**Index usage by query pattern** (for `ORDER BY (event_type, event_date, user_id)`):\n\n| Filter | Index Used? |\n|--------|-------------|\n| `WHERE event_type = 'X'` | ✅ Yes |\n| `WHERE event_type = 'X' AND event_date = '...'` | ✅ Yes |\n| `WHERE event_date = '...'` | ❌ No — skips first column |\n| `WHERE user_id = 123` | ❌ No — skips first two |\n\nFor columns that can't be in ORDER BY, add a **data skipping index** (see [Query Optimization](#query-optimization)).\n\n---\n\n### Step 2 — Choose the Right Engine\n\n| Engine | Use When |\n|--------|----------|\n| `MergeTree` | Standard append-only analytics |\n| `ReplacingMergeTree(ver)` | Need logical \"upserts\" (new version replaces old) |\n| `AggregatingMergeTree` | Pre-aggregated data for materialized views |\n| `CollapsingMergeTree(sign)` | Logical deletes via insert pattern |\n| `SummingMergeTree` | Automatically sum numeric columns on merge |\n| `ReplicatedMergeTree` | Any engine on a cluster with replication |\n\nFor clusters, prefix engine name with `Replicated`: `ReplicatedMergeTree(...)`.\n\n---\n\n### Step 3 — Pick Native Types (Never Store Everything as String)\n\n| Data | Wrong | Right | Savings |\n|------|-------|-------|---------|\n| UUID | `String` | `UUID` | 56% |\n| Timestamp | `String` | `DateTime` / `DateTime64(3)` | 58–79% |\n| Integer ID | `String` | `UInt32` / `UInt64` | varies |\n| Boolean | `String` | `Bool` | 75–80% |\n| IPv4 | `String` | `IPv4` | 43–73% |\n| Decimal amount | `String` | `Decimal(10,2)` | significant |\n\n**Use the smallest numeric type that fits:**\n\n| Type | Range | Use For |\n|------|-------|---------|\n| `UInt8` | 0–255 | age, rating, status code |\n| `UInt16` | 0–65,535 | year, port |\n| `UInt32` | 0–4.2B | most IDs, unix timestamps |\n| `UInt64` | 0–18E | very large counters |\n\n**Use LowCardinality for repeated strings with < 10,000 unique values:**\n```sql\ncountry LowCardinality(String),   -- ~200 unique values\nbrowser LowCardinality(String),   -- ~50 unique values\nevent_type LowCardinality(String) -- ~100 unique values\n```\n\n**Use Enum for fixed, known value sets:**\n```sql\n-- Provides insert-time validation + 1-byte storage\nstatus Enum8('pending' = 1, 'processing' = 2, 'shipped' = 3, 'delivered' = 4)\n```\n\n**Avoid Nullable unless the null is semantically meaningful:**\n```sql\n-- BAD: Nullable everywhere\nname Nullable(String),\nlogin_count Nullable(UInt32)\n\n-- GOOD: Use defaults; Nullable only when null has distinct meaning\nname String DEFAULT '',\nlogin_count UInt32 DEFAULT 0,\ndeleted_at Nullable(DateTime),   -- NULL = \"not deleted\" is semantically distinct\nparent_id Nullable(UInt64)       -- NULL = \"no parent\" is semantically distinct\n```\n\n---\n\n### Step 4 — Partitioning Strategy\n\n**Partition for lifecycle management, NOT for query performance.**\nQuery performance comes from ORDER BY. Partitions enable fast data expiry.\n\n```sql\n-- GOOD: Monthly partitions for TTL and lifecycle\nCREATE TABLE events (\n    timestamp DateTime,\n    event_type LowCardinality(String),\n    user_id UInt64\n) ENGINE = MergeTree()\nPARTITION BY toStartOfMonth(timestamp)\nORDER BY (event_type, toDate(timestamp), user_id)\nTTL timestamp + INTERVAL 90 DAY;\n\n-- Instant deletion of a month\nALTER TABLE events DROP PARTITION '2024-01';\n```\n\n**Keep partition count between 100–1,000.** Daily partitions grow unbounded; monthly is usually safe.\n\n**Tiered storage:**\n```sql\nTTL\n    timestamp + INTERVAL 7 DAY TO VOLUME 'hot',\n    timestamp + INTERVAL 30 DAY TO VOLUME 'warm',\n    timestamp + INTERVAL 365 DAY DELETE;\n```\n\n**If you're unsure, start without partitioning.** You can add it later by creating a new table, migrating data, and renaming.\n\n---\n\n### Complete Table Example\n\n```sql\nCREATE TABLE page_events (\n    -- Identifiers\n    event_id     UUID DEFAULT generateUUIDv4(),\n    tenant_id    UInt32,\n    user_id      UInt64,\n\n    -- Low-cardinality dimensions (great for ORDER BY)\n    event_type   LowCardinality(String),\n    country      LowCardinality(String) DEFAULT '',\n    browser      LowCardinality(String) DEFAULT '',\n    platform     Enum8('web'=1, 'ios'=2, 'android'=3, 'api'=4),\n\n    -- Timestamps\n    occurred_at  DateTime64(3),\n    inserted_at  DateTime DEFAULT now(),\n\n    -- Metrics\n    duration_ms  UInt32 DEFAULT 0,\n    revenue      Decimal(12,4) DEFAULT 0,\n\n    -- Flexible properties\n    properties   JSON,\n\n    -- Skipping index for user lookups\n    INDEX idx_user_id user_id TYPE bloom_filter GRANULARITY 4\n) ENGINE = MergeTree()\nPARTITION BY toStartOfMonth(occurred_at)\nORDER BY (tenant_id, event_type, toDate(occurred_at), user_id)\nTTL occurred_at + INTERVAL 365 DAY\nSETTINGS index_granularity = 8192;\n```\n\n---\n\n## Migrations\n\nClickHouse does NOT support transactional DDL. There is no rollback. Plan carefully.\n\n### ORMs / Migration Tools Compatibility\n\n| Tool | ClickHouse Support |\n|------|--------------------|\n| Prisma | ❌ No native support — use raw SQL migrations |\n| Drizzle | ❌ No native support — use raw SQL migrations |\n| TypeORM | ⚠️ Unofficial community driver only |\n| Flyway | ✅ Supported via JDBC driver |\n| Liquibase | ✅ Supported |\n| golang-migrate | ✅ Works well — recommended for Go |\n| Custom SQL files | ✅ Always works |\n\n**For Node.js projects:** maintain a `migrations/` folder with numbered `.sql` files and a small runner script.\n\n**For Go projects:** use `golang-migrate` with the ClickHouse driver.\n\n**Do NOT use Prisma/Drizzle to generate ClickHouse DDL.** They have no concept of MergeTree engines, ORDER BY, or PARTITION BY.\n\n---\n\n### Migration Patterns\n\n#### Adding a Column\n```sql\n-- Safe: adding a column with a DEFAULT\nALTER TABLE events ADD COLUMN IF NOT EXISTS session_id UUID DEFAULT generateUUIDv4();\n\n-- For a cluster: ON CLUSTER must come first\nALTER TABLE events ON CLUSTER '{cluster}' ADD COLUMN IF NOT EXISTS session_id UUID DEFAULT generateUUIDv4();\n```\n\n#### Changing an ORDER BY (requires table recreation)\n```sql\n-- 1. Create new table with correct ORDER BY\nCREATE TABLE events_v2 AS events;   -- Copies structure\nALTER TABLE events_v2 MODIFY ORDER BY (tenant_id, event_type, toDate(occurred_at), user_id);\n\n-- Or create from scratch:\nCREATE TABLE events_v2 (...) ENGINE = MergeTree() ORDER BY (...);\n\n-- 2. Migrate data\nINSERT INTO events_v2 SELECT * FROM events;\n\n-- 3. Swap\nRENAME TABLE events TO events_old, events_v2 TO events;\n\n-- 4. Verify, then drop\nDROP TABLE events_old;\n```\n\n#### Adding a Skipping Index\n```sql\nALTER TABLE events ADD INDEX IF NOT EXISTS idx_user_id user_id TYPE bloom_filter GRANULARITY 4;\nALTER TABLE events MATERIALIZE INDEX idx_user_id;  -- Backfill existing data\n```\n\n#### Node.js Migration Runner Example\n```javascript\n// migrations/runner.js\nimport { createClient } from '@clickhouse/client';\nimport fs from 'fs';\nimport path from 'path';\n\nconst client = createClient({\n  url: process.env.CLICKHOUSE_URL,\n  username: process.env.CLICKHOUSE_USER,\n  password: process.env.CLICKHOUSE_PASSWORD,\n  database: process.env.CLICKHOUSE_DB,\n});\n\n// Track applied migrations\nawait client.command({\n  query: `CREATE TABLE IF NOT EXISTS _migrations (\n    name String,\n    applied_at DateTime DEFAULT now()\n  ) ENGINE = MergeTree() ORDER BY (applied_at, name)`\n});\n\nconst applied = new Set(\n  (await client.query({ query: 'SELECT name FROM _migrations', format: 'JSONEachRow' }))\n    .json().map(r => r.name)\n);\n\nconst files = fs.readdirSync('./migrations').filter(f => f.endsWith('.sql')).sort();\n\nfor (const file of files) {\n  if (applied.has(file)) continue;\n  const sql = fs.readFileSync(path.join('./migrations', file), 'utf-8');\n  // Execute each statement separately (ClickHouse doesn't support multi-statement by default)\n  for (const stmt of sql.split(';').map(s => s.trim()).filter(Boolean)) {\n    await client.command({ query: stmt });\n  }\n  await client.command({ query: `INSERT INTO _migrations (name) VALUES ('${file}')` });\n  console.log(`Applied: ${file}`);\n}\n```\n\n#### Go Migration Runner (golang-migrate)\n```go\nimport (\n    \"github.com/golang-migrate/migrate/v4\"\n    _ \"github.com/golang-migrate/migrate/v4/database/clickhouse\"\n    _ \"github.com/golang-migrate/migrate/v4/source/file\"\n)\n\nm, err := migrate.New(\n    \"file://migrations\",\n    \"clickhouse://localhost:9000?database=mydb&username=default&password=\",\n)\nif err != nil { log.Fatal(err) }\nif err := m.Up(); err != nil && err != migrate.ErrNoChange {\n    log.Fatal(err)\n}\n```\n\n---\n\n## Insert Strategy\n\n### Rule 1 — Batch 10,000–100,000 Rows Per INSERT\n\nEach `INSERT` creates a **data part**. Many small inserts = many small parts = merge pressure = cluster instability.\n\n```python\n# BAD: one row at a time\nfor event in events:\n    client.execute(\"INSERT INTO events VALUES\", [event])  # Creates 10,000 parts!\n\n# GOOD: batch appropriately\nBATCH_SIZE = 10_000\nfor i in range(0, len(events), BATCH_SIZE):\n    client.execute(\"INSERT INTO events VALUES\", events[i:i+BATCH_SIZE])\n```\n\n**Monitor part health:**\n```sql\nSELECT table, count() as parts, sum(rows) as total_rows\nFROM system.parts\nWHERE active AND database = currentDatabase()\nGROUP BY table\nORDER BY parts DESC;\n-- Warning: > 3,000 parts per table is trouble\n```\n\n### Rule 2 — Use Async Inserts for Many Small Producers\n\nWhen batching client-side isn't practical (many microservices, IoT, etc.):\n\n```sql\nSET async_insert = 1;\nSET async_insert_max_data_size = 10000000;   -- 10MB buffer\nSET async_insert_busy_timeout_ms = 1000;      -- Flush every 1s\nSET wait_for_async_insert = 1;                -- Wait for durability confirmation\n```\n\n### Rule 3 — Avoid Mutations (UPDATE/DELETE)\n\nClickHouse is not built for mutations. They rewrite entire data parts.\n\n| Need | Use Instead |\n|------|-------------|\n| UPDATE rows | `ReplacingMergeTree` + insert new version |\n| DELETE rows frequently | Lightweight DELETE (23.3+): `DELETE FROM events WHERE ...` |\n| Delete old data in bulk | `DROP PARTITION` |\n| Track deletions | `CollapsingMergeTree(sign)` with `sign = -1` row |\n\n**ReplacingMergeTree pattern:**\n```sql\nCREATE TABLE users (\n    user_id UInt64,\n    name String,\n    status LowCardinality(String),\n    updated_at DateTime DEFAULT now()\n) ENGINE = ReplacingMergeTree(updated_at)\nORDER BY user_id;\n\n-- \"Update\" by inserting a new version\nINSERT INTO users (user_id, name, status) VALUES (123, 'Alice', 'inactive');\n\n-- Query deduplicated (FINAL is slower but consistent)\nSELECT * FROM users FINAL WHERE user_id = 123;\n\n-- Or use argMax for better performance at scale\nSELECT user_id, argMax(status, updated_at) as status\nFROM users GROUP BY user_id;\n```\n\n### Rule 4 — Never Run OPTIMIZE TABLE FINAL in Production\n\nBackground merges handle part consolidation automatically. Forcing it:\n- Blocks other operations\n- Causes severe disk I/O spikes\n- Provides no lasting benefit\n\n---\n\n## CLI Reference\n\n### Connect\n\n```bash\n# Basic\nclickhouse-client -h <host> -u <user> --password <pass> -d <database>\n\n# Using env vars (recommended — hides password from process list)\nexport CLICKHOUSE_PASSWORD=yourpassword\nclickhouse-client -h 127.0.0.1 -u app_user -d app_db\n\n# With SSL\nclickhouse-client -h <host> -u <user> -d <db> --secure --port 9440\n```\n\nParse a JDBC URL (`jdbc:clickhouse://host:8123/db`):\n```bash\nJDBC=\"jdbc:clickhouse://myhost.com:8123/mydb\"\nHOST=$(echo $JDBC | sed 's|.*://\\([^:]*\\):.*|\\1|')\nPORT=$(echo $JDBC | sed 's|.*:\\([0-9]*\\)/.*|\\1|')\nDB=$(echo $JDBC   | sed 's|.*/||')\nclickhouse-client -h \"$HOST\" --port \"$PORT\" -d \"$DB\"\n```\n\n### Inspect the Database\n\n```bash\n# List databases\nclickhouse-client -h <host> -u <user> -q \"SHOW DATABASES;\" --format=TSV\n\n# List tables\nclickhouse-client -h <host> -u <user> -d <db> -q \"SHOW TABLES;\" --format=TSV\n\n# Describe a table\nclickhouse-client -h <host> -u <user> -d <db> -q \"DESCRIBE TABLE my_table;\" --format=TSV\n\n# Show CREATE statement (includes engine, ORDER BY, partitioning)\nclickhouse-client -h <host> -u <user> -d <db> -q \"SHOW CREATE TABLE my_table;\" --format=TSV\n\n# Table sizes\nclickhouse-client -h <host> -u <user> -d <db> -q \"\nSELECT table, total_rows as rows,\n       formatReadableSize(total_bytes) as size,\n       formatReadableSize(data_bytes) as data\nFROM system.tables WHERE database = currentDatabase()\nORDER BY total_bytes DESC;\" --format=PrettyCompact\n\n# Check primary key / partition key columns\nclickhouse-client -h <host> -u <user> -d <db> -q \"\nSELECT name, type, is_in_primary_key, is_in_partition_key\nFROM system.columns\nWHERE database = '<db>' AND table = '<table>'\nORDER BY position;\" --format=PrettyCompact\n\n# Part health check\nclickhouse-client -h <host> -u <user> -d <db> -q \"\nSELECT table, count() as parts, sum(rows) as rows,\n       formatReadableSize(sum(bytes_on_disk)) as size\nFROM system.parts WHERE active AND database = currentDatabase()\nGROUP BY table ORDER BY parts DESC;\" --format=PrettyCompact\n```\n\n### Query Data\n\n```bash\n# Basic query — JSON output\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"SELECT * FROM events LIMIT 10;\" --format=JSONEachRow | jq -s '.'\n\n# Aggregation\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"SELECT event_type, count() as n FROM events GROUP BY event_type ORDER BY n DESC;\" \\\n  --format=PrettyCompact\n\n# Export to CSV\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"SELECT * FROM events FORMAT CSV\" > /tmp/events.csv\n```\n\n### Analyze Query Performance\n\n```bash\n# Execution plan\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"EXPLAIN SELECT * FROM events WHERE user_id = 123;\" --format=TSV\n\n# With actual timing (ClickHouse 21.1+)\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"EXPLAIN ANALYZE SELECT * FROM events WHERE user_id = 123;\" --format=TSV\n\n# See which indexes were used\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"EXPLAIN indexes = 1 SELECT * FROM events WHERE user_id = 123;\" --format=TSV\n```\n\nLook for:\n- `Rows` in EXPLAIN output — fewer is better\n- `Skip` entries showing granules skipped by indexes\n- `Full scan` — indicates missing index coverage\n\n### Insert / Modify Data\n\n```bash\n# Insert from file (CSV)\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"INSERT INTO events FORMAT CSV\" < data.csv\n\n# Insert from file (JSONEachRow)\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"INSERT INTO events FORMAT JSONEachRow\" < data.ndjson\n\n# Run a SQL script\nclickhouse-client -h <host> -u <user> -d <db> --multiquery < migration.sql\n\n# Lightweight delete (23.3+)\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"DELETE FROM events WHERE occurred_at < '2023-01-01';\"\n\n# Drop partition (instant, for lifecycle)\nclickhouse-client -h <host> -u <user> -d <db> \\\n  -q \"ALTER TABLE events DROP PARTITION '2023-01';\"\n```\n\n---\n\n## Backend Integration\n\n### Node.js\n\n**Install:**\n```bash\nnpm install @clickhouse/client\n```\n\n**Module setup (`src/clickhouse.js` or `src/clickhouse.ts`):**\n```javascript\n// src/clickhouse.js\nimport { createClient } from '@clickhouse/client';\n\nlet _client = null;\n\nexport function getClickHouseClient() {\n  if (_client) return _client;\n  _client = createClient({\n    url: process.env.CLICKHOUSE_URL ?? 'http://localhost:8123',\n    username: process.env.CLICKHOUSE_USER ?? 'default',\n    password: process.env.CLICKHOUSE_PASSWORD ?? '',\n    database: process.env.CLICKHOUSE_DB ?? 'default',\n    clickhouse_settings: {\n      async_insert: 1,                       // Buffer small inserts server-side\n      wait_for_async_insert: 1,              // Confirm durability\n      async_insert_busy_timeout_ms: 1000,\n    },\n    compression: { request: true },           // Compress inserts\n    request_timeout: 30_000,\n  });\n  return _client;\n}\n```\n\n**Environment variables (`.env`):**\n```env\nCLICKHOUSE_URL=http://localhost:8123\nCLICKHOUSE_USER=default\nCLICKHOUSE_PASSWORD=secret\nCLICKHOUSE_DB=analytics\n```\n\n**Insert (batch — always batch):**\n```javascript\nimport { getClickHouseClient } from './clickhouse.js';\n\n// Accumulate rows, then flush in a batch\nconst BATCH_SIZE = 10_000;\nconst buffer = [];\n\nexport async function trackEvent(event) {\n  buffer.push(event);\n  if (buffer.length >= BATCH_SIZE) {\n    await flush();\n  }\n}\n\nexport async function flush() {\n  if (buffer.length === 0) return;\n  const rows = buffer.splice(0, buffer.length);\n  const client = getClickHouseClient();\n  await client.insert({\n    table: 'events',\n    values: rows,\n    format: 'JSONEachRow',\n  });\n}\n\n// Also flush on process exit / interval\nsetInterval(flush, 5_000);\nprocess.on('beforeExit', flush);\n```\n\n**Query (analytics — aggregate, don't fetch rows one by one):**\n```javascript\nexport async function getEventStats({ startDate, endDate, eventType }) {\n  const client = getClickHouseClient();\n  const result = await client.query({\n    query: `\n      SELECT\n        toStartOfHour(occurred_at) AS hour,\n        count() AS events,\n        uniq(user_id) AS unique_users\n      FROM events\n      WHERE\n        event_type = {eventType: String}\n        AND occurred_at >= {startDate: DateTime}\n        AND occurred_at < {endDate: DateTime}\n      GROUP BY hour\n      ORDER BY hour\n    `,\n    query_params: { eventType, startDate, endDate },\n    format: 'JSONEachRow',\n  });\n  return result.json();\n}\n```\n\n**TypeScript types:**\n```typescript\ninterface EventRow {\n  event_id: string;\n  tenant_id: number;\n  event_type: string;\n  user_id: number;\n  occurred_at: string;  // ClickHouse returns DateTime as string\n  properties: Record<string, unknown>;\n}\n\nconst result = await client.query({\n  query: 'SELECT * FROM events LIMIT 100',\n  format: 'JSONEachRow',\n});\nconst rows = await result.json<EventRow[]>();\n```\n\n---\n\n### Python\n\n**Install:**\n```bash\npip install clickhouse-connect   # Official Anthropic-maintained driver\n# or\npip install clickhouse-driver    # Older but widely used\n```\n\n**Module setup (`clickhouse.py`):**\n```python\n# clickhouse.py\nimport os\nimport clickhouse_connect\nfrom functools import lru_cache\n\n@lru_cache(maxsize=1)\ndef get_client():\n    return clickhouse_connect.get_client(\n        host=os.environ.get('CLICKHOUSE_HOST', 'localhost'),\n        port=int(os.environ.get('CLICKHOUSE_PORT', 8123)),\n        username=os.environ.get('CLICKHOUSE_USER', 'default'),\n        password=os.environ.get('CLICKHOUSE_PASSWORD', ''),\n        database=os.environ.get('CLICKHOUSE_DB', 'default'),\n        settings={\n            'async_insert': 1,\n            'wait_for_async_insert': 1,\n            'async_insert_busy_timeout_ms': 1000,\n        },\n        compress=True,\n    )\n```\n\n**Batch insert:**\n```python\nfrom clickhouse import get_client\nfrom datetime import datetime\n\nBATCH_SIZE = 10_000\n\ndef insert_events(events: list[dict]):\n    \"\"\"Always insert in batches of 10K+ rows.\"\"\"\n    client = get_client()\n    # clickhouse_connect expects column-oriented data\n    column_names = ['tenant_id', 'event_type', 'user_id', 'occurred_at', 'properties']\n    data = [\n        [e['tenant_id'] for e in events],\n        [e['event_type'] for e in events],\n        [e['user_id'] for e in events],\n        [e['occurred_at'] for e in events],\n        [e.get('properties', {}) for e in events],\n    ]\n    client.insert('events', data, column_names=column_names)\n\ndef batch_insert(events: list[dict]):\n    for i in range(0, len(events), BATCH_SIZE):\n        insert_events(events[i:i+BATCH_SIZE])\n```\n\n**Query:**\n```python\ndef get_event_stats(event_type: str, start_date: str, end_date: str):\n    client = get_client()\n    result = client.query(\"\"\"\n        SELECT\n            toStartOfHour(occurred_at) AS hour,\n            count() AS events,\n            uniq(user_id) AS unique_users\n        FROM events\n        WHERE event_type = {event_type:String}\n          AND occurred_at >= {start_date:DateTime}\n          AND occurred_at < {end_date:DateTime}\n        GROUP BY hour\n        ORDER BY hour\n    \"\"\", parameters={'event_type': event_type, 'start_date': start_date, 'end_date': end_date})\n    return result.named_results()  # Returns list of dicts\n```\n\n**With pandas (for data pipelines):**\n```python\ndef get_dataframe(query: str, params: dict = None):\n    client = get_client()\n    return client.query_df(query, parameters=params or {})\n\ndf = get_dataframe(\"SELECT event_type, count() as n FROM events GROUP BY event_type\")\n```\n\n---\n\n### Go\n\n**Install:**\n```bash\ngo get github.com/ClickHouse/clickhouse-go/v2\n```\n\n**Module setup (`internal/clickhouse/client.go`):**\n```go\npackage clickhouse\n\nimport (\n    \"context\"\n    \"crypto/tls\"\n    \"fmt\"\n    \"os\"\n    \"sync\"\n    \"time\"\n\n    ch \"github.com/ClickHouse/clickhouse-go/v2\"\n    \"github.com/ClickHouse/clickhouse-go/v2/lib/driver\"\n)\n\nvar (\n    once   sync.Once\n    client driver.Conn\n)\n\nfunc GetClient() (driver.Conn, error) {\n    var err error\n    once.Do(func() {\n        options := &ch.Options{\n            Addr: []string{fmt.Sprintf(\"%s:%s\",\n                getEnv(\"CLICKHOUSE_HOST\", \"localhost\"),\n                getEnv(\"CLICKHOUSE_PORT\", \"9000\"),\n            )},\n            Auth: ch.Auth{\n                Database: getEnv(\"CLICKHOUSE_DB\", \"default\"),\n                Username: getEnv(\"CLICKHOUSE_USER\", \"default\"),\n                Password: getEnv(\"CLICKHOUSE_PASSWORD\", \"\"),\n            },\n            Settings: ch.Settings{\n                \"async_insert\":                1,\n                \"wait_for_async_insert\":       1,\n                \"async_insert_busy_timeout_ms\": 1000,\n            },\n            DialTimeout:     time.Second * 5,\n            MaxOpenConns:    10,\n            MaxIdleConns:    5,\n            ConnMaxLifetime: time.Hour,\n            Compression: &ch.Compression{\n                Method: ch.CompressionLZ4,\n            },\n        }\n\n        // Enable TLS for production\n        if os.Getenv(\"CLICKHOUSE_TLS\") == \"true\" {\n            options.TLS = &tls.Config{InsecureSkipVerify: false}\n        }\n\n        client, err = ch.Open(options)\n    })\n    return client, err\n}\n\nfunc getEnv(key, fallback string) string {\n    if v := os.Getenv(key); v != \"\" {\n        return v\n    }\n    return fallback\n}\n```\n\n**Batch insert:**\n```go\npackage clickhouse\n\nimport (\n    \"context\"\n    \"time\"\n)\n\ntype Event struct {\n    TenantID   uint32    `ch:\"tenant_id\"`\n    EventType  string    `ch:\"event_type\"`\n    UserID     uint64    `ch:\"user_id\"`\n    OccurredAt time.Time `ch:\"occurred_at\"`\n}\n\nfunc InsertEvents(ctx context.Context, events []Event) error {\n    conn, err := GetClient()\n    if err != nil {\n        return err\n    }\n\n    batch, err := conn.PrepareBatch(ctx, \"INSERT INTO events\")\n    if err != nil {\n        return err\n    }\n\n    for _, e := range events {\n        if err := batch.AppendStruct(&e); err != nil {\n            return err\n        }\n    }\n    return batch.Send()\n}\n```\n\n**Query:**\n```go\ntype HourlyStats struct {\n    Hour        time.Time `ch:\"hour\"`\n    Events      uint64    `ch:\"events\"`\n    UniqueUsers uint64    `ch:\"unique_users\"`\n}\n\nfunc GetEventStats(ctx context.Context, eventType, start, end string) ([]HourlyStats, error) {\n    conn, err := GetClient()\n    if err != nil {\n        return nil, err\n    }\n\n    rows, err := conn.Query(ctx, `\n        SELECT toStartOfHour(occurred_at) AS hour,\n               count() AS events,\n               uniq(user_id) AS unique_users\n        FROM events\n        WHERE event_type = @eventType\n          AND occurred_at >= @start\n          AND occurred_at < @end\n        GROUP BY hour ORDER BY hour`,\n        ch.Named(\"eventType\", eventType),\n        ch.Named(\"start\", start),\n        ch.Named(\"end\", end),\n    )\n    if err != nil {\n        return nil, err\n    }\n    defer rows.Close()\n\n    var stats []HourlyStats\n    for rows.Next() {\n        var s HourlyStats\n        if err := rows.ScanStruct(&s); err != nil {\n            return nil, err\n        }\n        stats = append(stats, s)\n    }\n    return stats, rows.Err()\n}\n```\n\n---\n\n## Query Optimization\n\n### Use ORDER BY Prefix in Every WHERE Clause\n\nAlways filter on the leftmost columns of ORDER BY first.\nIf you can't, add a data skipping index.\n\n### Data Skipping Indexes\n\nFor columns NOT in ORDER BY that you filter on:\n\n```sql\n-- Add bloom filter for high-cardinality equality lookups\nALTER TABLE events ADD INDEX idx_user_id user_id TYPE bloom_filter GRANULARITY 4;\nALTER TABLE events MATERIALIZE INDEX idx_user_id;  -- Backfill\n\n-- Index types:\n-- bloom_filter: equality on high-cardinality (user IDs, session IDs)\n-- set(N):       low-cardinality equality (status IN ('a','b'))  \n-- minmax:       range queries (amount > 1000)\n-- ngrambf_v1:  text search (LIKE '%term%')\n-- tokenbf_v1:  token search (hasToken(text, 'word'))\n\n-- Verify it's being used\nEXPLAIN indexes = 1\nSELECT * FROM events WHERE user_id = 12345;\n-- Look for \"Skip\" entries in output\n```\n\n### JOINs\n\nClickHouse JOINs load the **right table into memory**. Always put the smaller table on the right.\n\n```sql\n-- BAD: large table on right\nSELECT * FROM small_table s JOIN large_table l ON l.id = s.id;\n\n-- GOOD: small table on right\nSELECT * FROM large_table l JOIN small_table s ON s.id = l.id;\n```\n\n**Filter BEFORE joining:**\n```sql\n-- GOOD: reduce data before the join\nSELECT * FROM\n    (SELECT * FROM orders WHERE status = 'completed') o\nJOIN\n    (SELECT * FROM customers WHERE country = 'US') c\nON c.id = o.customer_id;\n```\n\n**Choose the right algorithm:**\n```sql\nSET join_algorithm = 'auto';          -- Default: ClickHouse decides\nSET join_algorithm = 'partial_merge'; -- Large-to-large, memory-constrained\nSET join_algorithm = 'grace_hash';    -- Large datasets, can spill to disk\n```\n\n**Use ANY JOIN when you only need one match:**\n```sql\nSELECT o.*, c.name\nFROM orders o\nANY LEFT JOIN customers c ON c.id = o.customer_id;\n-- Faster and less memory when right table may have duplicates\n```\n\n**Alternatives to JOINs (often faster):**\n```sql\n-- Dictionary for dimension lookups\nSELECT o.*, dictGet('customers_dict', 'name', o.customer_id) as name\nFROM orders o;\n\n-- IN subquery for filtering\nSELECT * FROM orders\nWHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');\n```\n\n### Materialized Views\n\nUse materialized views to pre-aggregate data instead of scanning raw tables.\n\n**Incremental MV (updates in real time):**\n```sql\n-- Destination table\nCREATE TABLE events_hourly (\n    hour DateTime,\n    event_type LowCardinality(String),\n    events AggregateFunction(count, UInt64),\n    unique_users AggregateFunction(uniq, UInt64)\n) ENGINE = AggregatingMergeTree()\nORDER BY (event_type, hour);\n\n-- MV triggers on every INSERT into events\nCREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS\nSELECT\n    toStartOfHour(occurred_at) AS hour,\n    event_type,\n    countState() AS events,\n    uniqState(user_id) AS unique_users\nFROM events\nGROUP BY hour, event_type;\n\n-- Query (reads thousands instead of billions)\nSELECT hour, event_type, countMerge(events), uniqMerge(unique_users)\nFROM events_hourly\nWHERE hour >= now() - INTERVAL 7 DAY\nGROUP BY hour, event_type;\n```\n\n**Refreshable MV (periodic rebuild, good for complex JOINs):**\n```sql\nCREATE MATERIALIZED VIEW customer_summary\nREFRESH EVERY 1 HOUR\nENGINE = MergeTree() ORDER BY customer_id\nAS SELECT\n    c.customer_id, c.name,\n    count() as orders, sum(o.amount) as total_spent\nFROM customers c\nLEFT JOIN orders o ON o.customer_id = c.customer_id\nGROUP BY c.customer_id, c.name;\n\n-- Force refresh\nSYSTEM REFRESH VIEW customer_summary;\n```\n\n### Avoid Small/Single-Row Queries\n\nClickHouse is built for scanning many rows and returning aggregates.\n**Do not use it like a key-value store.**\n\n```javascript\n// BAD: Fetching one user's data from ClickHouse on every request\napp.get('/user/:id/events', async (req, res) => {\n  const events = await ch.query(`SELECT * FROM events WHERE user_id = ${req.params.id}`);\n  res.json(events); // This is a key-value access pattern\n});\n\n// GOOD: Aggregate query that leverages ClickHouse's strength\napp.get('/analytics/summary', async (req, res) => {\n  const stats = await ch.query(`\n    SELECT event_type, count() as n, uniq(user_id) as users\n    FROM events\n    WHERE occurred_at >= today() - 7\n    GROUP BY event_type\n  `);\n  res.json(stats);\n});\n```\n\n---\n\n## Redis Caching Strategy\n\n### When to Use Redis in Front of ClickHouse\n\n| Scenario | Use Redis? | Reason |\n|----------|-----------|--------|\n| Dashboard with same query run by many users | ✅ Yes | Prevents redundant large scans |\n| Single user fetching their own recent events | ✅ Yes | ClickHouse isn't a KV store |\n| Aggregation query taking > 500ms | ✅ Yes | Cache computed result |\n| Real-time per-user event counts | ✅ Yes | Maintain counter in Redis, bulk-sync to CH |\n| Ad-hoc analytics queries (new filters every time) | ❌ No | Cache hit rate will be low |\n| Time-series queries where time range keeps moving | ❌ Careful | Invalidation is complex |\n| Backfill / batch ETL pipeline | ❌ No | No user-facing latency concern |\n\n### Recommended Cache Pattern\n\n```javascript\n// Cache ClickHouse aggregation results in Redis\nasync function getDashboardStats(tenantId, dateRange) {\n  const cacheKey = `stats:${tenantId}:${dateRange}`;\n  const TTL = 300; // 5 minutes\n\n  // 1. Try cache first\n  const cached = await redis.get(cacheKey);\n  if (cached) return JSON.parse(cached);\n\n  // 2. Run the (potentially expensive) ClickHouse query\n  const result = await clickhouse.query({\n    query: `\n      SELECT event_type, count() as n, uniq(user_id) as users\n      FROM events\n      WHERE tenant_id = {tenantId: UInt32}\n        AND occurred_at >= {start: DateTime}\n      GROUP BY event_type\n    `,\n    query_params: { tenantId, start: dateRange },\n    format: 'JSONEachRow',\n  });\n  const data = await result.json();\n\n  // 3. Cache for TTL\n  await redis.setex(cacheKey, TTL, JSON.stringify(data));\n  return data;\n}\n```\n\n### When to Query ClickHouse Directly (No Redis)\n\n- The query is already fast (< 100ms) due to good schema design and materialized views\n- The query parameters are always unique (ad-hoc analytics, no cache benefit)\n- You have a materialized view pre-aggregating the data — query the MV directly\n- It's an internal/batch process with no latency requirement\n\n**The right answer is usually:** build good materialized views so the ClickHouse query is already fast enough that you don't need Redis.\n\n---\n\n## Cluster Considerations\n\nOn a ClickHouse cluster, DDL and certain operations must include `ON CLUSTER`.\n\n### Engine Naming\n\n| Single-node | Cluster |\n|-------------|---------|\n| `MergeTree` | `ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/{table}', '{replica}')` |\n| `ReplacingMergeTree(ver)` | `ReplicatedReplacingMergeTree(...)` |\n| All MergeTree variants | `Replicated` prefix |\n\nIn practice, use macros defined in `config.xml`:\n```sql\nENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/{table}', '{replica}')\n```\n\n### DDL on Cluster\n\n```sql\n-- Always include ON CLUSTER for DDL on distributed setups\nCREATE TABLE events ON CLUSTER '{cluster}' ( ... )\nENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/{table}', '{replica}')\nORDER BY (...);\n\nALTER TABLE events ON CLUSTER '{cluster}' ADD COLUMN new_col UInt32 DEFAULT 0;\n```\n\n### Distributed Tables\n\n```sql\n-- Create local table first (on cluster)\nCREATE TABLE events_local ON CLUSTER '{cluster}' ( ... )\nENGINE = ReplicatedMergeTree(...)\nORDER BY (...);\n\n-- Then create a Distributed table as the access layer\nCREATE TABLE events ON CLUSTER '{cluster}' ( ... )\nENGINE = Distributed('{cluster}', currentDatabase(), 'events_local', rand());\n```\n\nApplications connect to the Distributed table; ClickHouse routes queries to shards transparently.\n\n### INSERT Routing\n\nOn a cluster, insert into the Distributed table (not the local table) unless you are doing a shard-local operation intentionally.\n\n### System Queries on Clusters\n\n```sql\n-- Check part health across all shards\nSELECT hostName(), table, count() as parts\nFROM clusterAllReplicas('{cluster}', system.parts)\nWHERE active GROUP BY hostName(), table ORDER BY parts DESC;\n\n-- Check replication lag\nSELECT database, table, replica_name, queue_size\nFROM system.replication_queue\nWHERE queue_size > 0;\n```\n\n---\n\n## Rules Reference\n\nDetailed per-rule files are in `rules/` (loaded on demand):\n\n- Schema / Primary Key: `rules/schema-pk-*.md`\n- Schema / Types: `rules/schema-types-*.md`\n- Schema / Partitioning: `rules/schema-partition-*.md`\n- Schema / JSON: `rules/schema-json-when-to-use.md`\n- Query / JOINs: `rules/query-join-*.md`\n- Query / Indexes: `rules/query-index-skipping-indices.md`\n- Query / Materialized Views: `rules/query-mv-*.md`\n- Insert / Batching: `rules/insert-batch-size.md`\n- Insert / Async: `rules/insert-async-small-batches.md`\n- Insert / Format: `rules/insert-format-native.md`\n- Insert / Mutations: `rules/insert-mutation-*.md`\n- Insert / Optimize: `rules/insert-optimize-avoid-final.md`\n","tags":{"latest":"1.0.0"},"stats":{"comments":0,"downloads":363,"installsAllTime":14,"installsCurrent":0,"stars":0,"versions":1},"createdAt":1777254029514,"updatedAt":1778492765634},"latestVersion":{"version":"1.0.0","createdAt":1777254029514,"changelog":"Initial major release with new “rules”-based structure and streamlined best practices.\n\n- Switched to a comprehensive set of modular, topic-focused rules for schema design, query optimization, inserts, and engine selection.\n- Removed legacy monolithic documentation files; all guidance is now organized by workflow and use-case.\n- Added step-by-step instructions for schema partitioning, primary key and ORDER BY design, and column types.\n- Explicit reference for backend integration, CLI workflows, caching strategy, and testing/debugging ClickHouse data.\n- Enhanced clarity on when to use which table engines and how to avoid common ClickHouse pitfalls.","license":"MIT-0"},"metadata":null,"owner":{"handle":"encryptshawn","userId":"s17acjjwshpqj4xkbngejj57nx83zbcy","displayName":"EncryptShawn","image":"https://avatars.githubusercontent.com/u/56897824?v=4"},"moderation":null}