Clickhouse hello world

ClickHouse Hello World

Overview

Create a MergeTree table, insert rows with JSONEachRow, and run your first analytical query -- all using the official @clickhouse/client. This is the smoke test that proves your connection works and teaches the four MergeTree concepts (ORDER BY, PARTITION BY, TTL, LowCardinality) reused in every real schema.

Prerequisites

• @clickhouse/client installed and connected (see the clickhouse-install-auth skill for connection setup).
• A reachable ClickHouse server (local Docker, ClickHouse Cloud, or self-hosted) with CLICKHOUSE_HOST / CLICKHOUSE_USER / CLICKHOUSE_PASSWORD set as environment variables.

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

For the full engine menu (ReplacingMergeTree, SummingMergeTree, etc.) and the column-type table, see MergeTree engines & data types.

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`);
}

Step 4: Explore System Tables (optional)

Once data lands, inspect on-disk size and part counts via system.parts to confirm your partitioning is healthy. Full query and column reference: exploring system tables.

Output

Running the three core steps against a fresh table produces:

• Step 1 -- Table "events" created. (idempotent via IF NOT EXISTS).
• Step 2 -- Inserted 5 events.
• Step 3 -- one aggregated row per event_type, sorted by count descending:

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

ClickHouse returns numeric aggregates as JSON strings, so cast total / unique_users before doing arithmetic in TypeScript.

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

Examples

Steps 1-3 form the canonical end-to-end example: create → insert → aggregate. Two extensions live in the reference files:

• Different engine or column types -- swap MergeTree for ReplacingMergeTree (upserts) or add a Decimal(18,2) column: MergeTree engines & data types.
• Verifying on-disk layout -- the system.parts size/part-count query: exploring system tables.

Resources

• MergeTree Engine Docs
• Data Types Reference
• CREATE TABLE Syntax

Next Steps

Proceed to the clickhouse-local-dev-loop skill for Docker-based local development and an iterative schema-editing workflow.