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

MergeTree

ReplacingMergeTree

SummingMergeTree

AggregatingMergeTree

CollapsingMergeTree

Common Data Types

UInt8/16/32/64

user_id UInt64

Int8/16/32/64

delta Int32

Float32/64

price Float64

Decimal(P,S)

amount Decimal(18,2)

String

name String

DateTime

created_at DateTime

DateTime64(3)

ts DateTime64(3)

UUID

id UUID

Array(T)

tags Array(String)

LowCardinality(T)

status LowCardinality(String)

Error Handling

Table already exists

IF NOT EXISTS

Unknown column

DESCRIBE TABLE events

Type mismatch

Memory limit exceeded

Resources

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

Next Steps

Proceed to

clickhouse-local-dev-loop