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.

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

Common Data Types

Error Handling

Resources

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

Next Steps

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