Imported from jeremylongshore/tons-of-skills-marketplace (plugins/saas-packs/clickhouse-pack/skills/clickhouse-core-workflow-b/SKILL.md). Install upstream with npx skills add jeremylongshore/tons-of-skills-marketplace --skill clickhouse-core-workflow-b. Copyright stays with the author (MIT).

ClickHouse Insert & Query (Core Workflow B)

Overview

Move data into ClickHouse efficiently, then answer analytical questions with aggregations, funnels, retention, window functions, and materialized views. This skill covers the read/write half of the core workflow: the fast-path insert patterns that avoid "too many parts", the parameterized query API for Node.js, and pre-aggregation via materialized views. The high-frequency patterns live inline below; the deep query library and advanced engine patterns are broken out into references/ so you can drill in only when you need them.

Prerequisites

• Tables already created — run clickhouse-core-workflow-a first if not.
• @clickhouse/client installed and connected (CLICKHOUSE_HOST, CLICKHOUSE_USER, CLICKHOUSE_PASSWORD in the environment).
• A target database/table (examples use analytics.events).

Instructions

Step 1: Bulk insert (the fast path)

Batch rows and let the client buffer. ClickHouse writes a new "part" per INSERT, so many tiny inserts are the number-one performance mistake.

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

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

// Insert many rows efficiently — @clickhouse/client buffers internally
await client.insert({
  table: 'analytics.events',
  values: events,   // Array of objects matching table columns
  format: 'JSONEachRow',
});

Streaming a file (CSV, Parquet, etc.) uses the same call with a read stream and the matching format (e.g. CSVWithNames).

Insert best practices:

• Batch rows: aim for 10K-100K rows per INSERT (not one at a time).
• ClickHouse creates a new "part" per INSERT — too many small inserts cause "too many parts".
• For real-time streams, buffer 1-5 seconds then flush.

Step 2: Analytical queries

Aggregate with count(), uniqExact(), and time filters. The canonical "top events by tenant" shape:

SELECT tenant_id, event_type, count() AS event_count, uniqExact(user_id) AS unique_users
FROM analytics.events
WHERE created_at >= now() - INTERVAL 7 DAY
GROUP BY tenant_id, event_type
ORDER BY event_count DESC
LIMIT 100;

Funnel, retention, and safe parameterized-query patterns are in references/queries.md.

Step 3: Pre-aggregation and windowing

For dashboards, pre-aggregate on INSERT with a materialized view backed by an AggregatingMergeTree target, then merge states at read time. Window functions (row_number(), running totals via OVER (PARTITION BY ...)) and the full function reference table are in references/advanced.md.

Output

Applying this skill produces:

• Insert code — a batched client.insert(...) call (or file stream) that loads rows without triggering "too many parts".
• Query results — aggregation rows returned as JSON via rs.json(), ready to feed a dashboard or API response.
• Materialized view + target table — DDL that keeps a small pre-rolled table updated automatically on every source INSERT.

Error Handling

Examples

• Insert a batch of events — Step 1 above; adapt values to your row shape.
• Top events / funnel / retention / parameterized queries — full runnable SQL and Node.js in references/queries.md.
• Materialized view, window functions, function reference — the pre-aggregation and windowing patterns plus the common-function cheat sheet in references/advanced.md.

Resources

• SQL Reference
• Aggregate Functions
• Materialized Views Guide

Next Steps

For error troubleshooting once queries are running, see clickhouse-common-errors. For table and schema design, revisit clickhouse-core-workflow-a.