ClickHouse Hello World Skill

ClickHouse Hello World

Overview

Create a MergeTree table, insert rows with JSONEachRow, and run your first analytical query -- all using the official @clickhouse/client.

Prerequisites

@clickhouse/client installed and connected (see clickhouse-install-auth)

Instructions

Step 1: Create a MergeTree Table

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

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

await client.command({
  query: `
    CREATE TABLE IF NOT EXISTS events (
      event_id    UUID DEFAULT generateUUIDv4(),
      event_type  LowCardinality(String),
      user_id     UInt64,
      payload     String,
      created_at  DateTime DEFAULT now()
    )
    ENGINE = MergeTree()
    ORDER BY (event_type, created_at)
    PARTITION BY toYYYYMM(created_at)
    TTL created_at + INTERVAL 90 DAY
  `,
});
console.log('Table "events" created.');

Key concepts:

MergeTree() -- the foundational ClickHouse engine for analytics
ORDER BY -- defines the primary index (sort key); pick columns you filter/group on
PARTITION BY -- splits data into parts by month for efficient pruning
TTL -- automatic data expiration
LowCardinality(String) -- dictionary-encoded string, ideal for columns with < 10K distinct values

Step 2: Insert Data with JSONEachRow

await client.insert({
  table: 'events',
  values: [
    { event_type: 'page_view', user_id: 1001, payload: '{"url":"/home"}' },
    { event_type: 'click',     user_id: 1001, payload: '{"button":"signup"}' },
    { event_type: 'page_view', user_id: 1002, payload: '{"url":"/pricing"}' },
    { event_type: 'purchase',  user_id: 1002, payload: '{"amount":49.99}' },
    { event_type: 'page_view', user_id: 1003, payload: '{"url":"/docs"}' },
  ],
  format: 'JSONEachRow',
});
console.log('Inserted 5 events.');

Step 3: Query the Data

// Count events by type
const rs = await client.query({
  query: `
    SELECT
      event_type,
      count()          AS total,
      uniqExact(user_id) AS unique_users
    FROM events
    GROUP BY event_type
    ORDER BY total DESC
  `,
  format: 'JSONEachRow',
});

const rows = await rs.json<{
  event_type: string;
  total: string;        // ClickHouse returns numbers as strings in JSON
  unique_users: string;
}>();

for (const row of rows) {
  console.log(`${row.event_type}: ${row.total} events, ${row.unique_users} users`);
}

Expected output:

page_view: 3 events, 3 users
click: 1 events, 1 users
purchase: 1 events, 1 users

Step 4: Explore System Tables

// Check table size and row count
const stats = await client.query({
  query: `
    SELECT
      table,
      formatReadableSize(sum(bytes_on_disk)) AS disk_size,
      sum(rows) AS row_count,
      count() AS part_count
    FROM system.parts
    WHERE active AND database = currentDatabase() AND table = 'events'
    GROUP BY table
  `,
  format: 'JSONEachRow',
});
console.log('Table stats:', await stats.json());

MergeTree Engine Quick Reference

| Engine | Use Case | |--------|----------| | MergeTree | General-purpose analytics | | ReplacingMergeTree | Upserts (dedup by ORDER BY key) | | SummingMergeTree | Auto-sum numeric columns on merge | | AggregatingMergeTree | Pre-aggregated materialized views | | CollapsingMergeTree | State changes / versioned rows |

Common Data Types

| Type | Example | Notes | |------|---------|-------| | UInt8/16/32/64 | user_id UInt64 | Unsigned integers | | Int8/16/32/64 | delta Int32 | Signed integers | | Float32/64 | price Float64 | IEEE 754 | | Decimal(P,S) | amount Decimal(18,2) | Exact decimal | | String | name String | Variable-length bytes | | DateTime | created_at DateTime | Unix timestamp (seconds) | | DateTime64(3) | ts DateTime64(3) | Millisecond precision | | UUID | id UUID | 128-bit UUID | | Array(T) | tags Array(String) | Variable-length array | | LowCardinality(T) | status LowCardinality(String) | Dictionary encoding |

Error Handling

| Error | Cause | Solution | |-------|-------|----------| | Table already exists | Re-running CREATE | Use IF NOT EXISTS | | Unknown column | Typo in column name | Check DESCRIBE TABLE events | | Type mismatch | Wrong data type in insert | Match types to schema | | Memory limit exceeded | Query too broad | Add WHERE clauses, use LIMIT |

Resources

Next Steps

Proceed to clickhouse-local-dev-loop for Docker-based local development.

Agent Skills: ClickHouse Hello World

Install this agent skill to your local

Skill Files