--- name: clickhouse description: "ClickHouse is a column-oriented database management system designed for online analytical processing. Where traditional row-based databases excel at transactional workloads, ClickHouse shines when you need to scan and aggregate billions of rows in seconds. It achieves this through columnar storag..." --- # ClickHouse ClickHouse is a column-oriented database management system designed for online analytical processing. Where traditional row-based databases excel at transactional workloads, ClickHouse shines when you need to scan and aggregate billions of rows in seconds. It achieves this through columnar storage, vectorized query execution, and aggressive compression. This skill walks through deploying ClickHouse with Docker, creating tables with the MergeTree engine family, inserting data, running aggregation queries, and connecting from a Node.js application. ## Deploying ClickHouse with Docker The fastest way to get a ClickHouse instance running is with the official Docker image. This gives you both the server and the built-in `clickhouse-client` CLI. ```bash # docker-compose.yml — ClickHouse single-node deployment # Exposes the HTTP interface on 8123 and the native protocol on 9000 docker run -d \ --name clickhouse-server \ -p 8123:8123 \ -p 9000:9000 \ -v clickhouse-data:/var/lib/clickhouse \ -v clickhouse-logs:/var/log/clickhouse-server \ clickhouse/clickhouse-server:latest ``` Once the container is running, connect with the native client: ```bash # CLI — connect to the running ClickHouse instance docker exec -it clickhouse-server clickhouse-client ``` You can also query via HTTP, which is useful for quick health checks and lightweight integrations: ```bash # curl — HTTP interface query example curl 'http://localhost:8123/' --data-binary 'SELECT version()' ``` ## Table Engines and MergeTree ClickHouse organizes data through table engines, and MergeTree is the workhorse you will use for almost everything. It supports primary key indexing, data partitioning, and automatic background merges that keep query performance high even under continuous inserts. ```sql -- DDL — create an events table using MergeTree engine -- Partitioned by month for efficient time-range queries -- Ordered by event timestamp and user for fast filtered aggregations CREATE TABLE events ( event_id UUID DEFAULT generateUUIDv4(), user_id UInt64, event_name LowCardinality(String), properties String, created_at DateTime DEFAULT now() ) ENGINE = MergeTree() PARTITION BY toYYYYMM(created_at) ORDER BY (created_at, user_id); ``` The `ORDER BY` clause defines the primary key and controls how data is physically sorted on disk. Queries that filter or group by these columns will be significantly faster because ClickHouse can skip entire data granules that don't match. For high-cardinality string columns that still have a bounded set of values (like event names), `LowCardinality(String)` applies dictionary encoding and dramatically reduces storage and speeds up group-by queries. ### ReplacingMergeTree for Deduplication When your ingestion pipeline might send duplicate events, `ReplacingMergeTree` collapses rows with the same sorting key during background merges: ```sql -- DDL — deduplicated user profiles using ReplacingMergeTree -- The latest row (by updated_at) wins during merge CREATE TABLE user_profiles ( user_id UInt64, display_name String, plan LowCardinality(String), updated_at DateTime ) ENGINE = ReplacingMergeTree(updated_at) ORDER BY user_id; ``` ## Inserting Data ClickHouse is optimized for batch inserts. Sending one row at a time works but wastes resources. Aim for batches of at least a few thousand rows. ```sql -- DML — batch insert of event records INSERT INTO events (user_id, event_name, properties) VALUES (1001, 'page_view', '{"page": "/dashboard"}'), (1001, 'button_click', '{"button": "export"}'), (1002, 'page_view', '{"page": "/settings"}'), (1003, 'signup', '{}'); ``` For bulk loading from files, ClickHouse can ingest CSV, JSON, and Parquet directly: ```bash # CLI — bulk insert from a CSV file via clickhouse-client cat events.csv | docker exec -i clickhouse-server \ clickhouse-client --query="INSERT INTO events FORMAT CSVWithNames" ``` ## Aggregation Queries This is where ClickHouse earns its keep. Aggregation queries over millions of rows return in milliseconds thanks to columnar scanning and SIMD-accelerated computation. ```sql -- Analytics — daily active users over the last 30 days SELECT toDate(created_at) AS day, uniqExact(user_id) AS dau FROM events WHERE created_at >= now() - INTERVAL 30 DAY GROUP BY day ORDER BY day; ``` ```sql -- Analytics — top 10 most used features this week SELECT event_name, count() AS total, uniq(user_id) AS unique_users FROM events WHERE created_at >= toMonday(now()) GROUP BY event_name ORDER BY total DESC LIMIT 10; ``` ```sql -- Analytics — retention cohort: users who signed up in January -- and returned within 7 days SELECT toDate(s.created_at) AS signup_date, count(DISTINCT r.user_id) AS retained FROM events s INNER JOIN events r ON s.user_id = r.user_id AND r.created_at BETWEEN s.created_at AND s.created_at + INTERVAL 7 DAY AND r.event_name != 'signup' WHERE s.event_name = 'signup' AND s.created_at BETWEEN '2025-01-01' AND '2025-01-31' GROUP BY signup_date ORDER BY signup_date; ``` ## Materialized Views Materialized views in ClickHouse are triggers that transform data at insert time. They read from a source table and write aggregated results into a destination table, giving you pre-computed rollups without any external scheduler. ```sql -- DDL — destination table for hourly event counts CREATE TABLE hourly_event_counts ( hour DateTime, event_name LowCardinality(String), count UInt64, unique_users UInt64 ) ENGINE = SummingMergeTree() ORDER BY (hour, event_name); ``` ```sql -- DDL — materialized view that populates hourly_event_counts -- on every INSERT into the events table CREATE MATERIALIZED VIEW mv_hourly_events TO hourly_event_counts AS SELECT toStartOfHour(created_at) AS hour, event_name, count() AS count, uniq(user_id) AS unique_users FROM events GROUP BY hour, event_name; ``` Now every insert into `events` automatically updates the rollup. Querying `hourly_event_counts` is near-instant regardless of how large the raw events table grows. ## Node.js Client The official `@clickhouse/client` package provides a typed, streaming-capable client for Node.js applications. ```javascript // clickhouse-client.js — Node.js ClickHouse client setup and query example // npm install @clickhouse/client import { createClient } from '@clickhouse/client'; const client = createClient({ url: 'http://localhost:8123', username: 'default', password: '', database: 'default', }); // Insert a batch of events async function insertEvents(events) { await client.insert({ table: 'events', values: events, format: 'JSONEachRow', }); } // Query daily active users async function getDailyActiveUsers(days = 30) { const result = await client.query({ query: ` SELECT toDate(created_at) AS day, uniqExact(user_id) AS dau FROM events WHERE created_at >= now() - INTERVAL {days:UInt32} DAY GROUP BY day ORDER BY day `, query_params: { days }, format: 'JSONEachRow', }); return result.json(); } // Usage await insertEvents([ { user_id: 1001, event_name: 'page_view', properties: '{"page":"/home"}' }, { user_id: 1002, event_name: 'signup', properties: '{}' }, ]); const dau = await getDailyActiveUsers(7); console.log('DAU last 7 days:', dau); await client.close(); ``` The client supports streaming for large result sets via `result.stream()`, which yields rows in chunks rather than buffering the entire response in memory. For high-throughput ingestion, keep a persistent client instance and batch your inserts on a timer or buffer threshold. --- Source attribution: TerminalSkills Original project: https://github.com/TerminalSkills/skills License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Dave AI. ## Upstream license notice MIT License Copyright (c) 2026 SkillsHub Contributors Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Dave AI resource metadata Creator / publisher: TerminalSkills Original project: https://github.com/TerminalSkills/skills Attribution basis: Original repository namespace preserved by package mirror Fields of work: Data & Analytics, Development & Engineering, Operations Roles: Business Process Analyst, Data Analyst, Data Engineer, Database Administrator, ML / AI Engineer Specialties: Backend, Database Tools: Docker, MongoDB, MySQL, PostgreSQL, Redis Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 9ea70724ec3b5531e52eb2da452d414c33b47f07c25881b1a660a0b33f2ad8b5 Metadata reviewed: 2026-10-27 This metadata supersedes earlier Dave AI catalog attribution. Original instruction and supporting files are preserved.