Insert, query, and aggregate data in ClickHouse with real SQL patterns.
Use when writing analytical queries, inserting data at scale,
building dashboards, or implementing materialized views.
Trigger: "clickhouse query", "clickhouse insert", "clickhouse aggregate",
"clickhouse materialized view", "clickhouse SQL".
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
The command stays on one line. Scroll horizontally to inspect it before copying.
Prefer a local copy? Download the files currently available to SkillsMP.
Showing SKILL.md
SKILL.md
Source instructions · Read-only preview
name
clickhouse-core-workflow-b
description
Insert, query, and aggregate data in ClickHouse with real SQL patterns.
Use when writing analytical queries, inserting data at scale,
building dashboards, or implementing materialized views.
Trigger: "clickhouse query", "clickhouse insert", "clickhouse aggregate",
"clickhouse materialized view", "clickhouse SQL".
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
-- Top events by tenant in the last 7 daysSELECT
tenant_id,
event_type,
count() AS event_count,
uniqExact(user_id) AS unique_users,
min(created_at) AS first_seen,
max(created_at) AS last_seen
FROM analytics.events
WHERE created_at >= now() -INTERVAL7DAYGROUPBY tenant_id, event_type
ORDERBY event_count DESC
LIMIT 100;
-- Funnel analysis: signup → activation → purchaseSELECT
level,
count() AS users
FROM (
SELECT
user_id,
groupArray(event_type) AS journey
FROM analytics.events
WHERE event_type IN ('signup', 'activation', 'purchase')
AND created_at >= today() -30GROUPBY user_id
)
ARRAYJOIN arrayEnumerate(journey) AS level
GROUPBY level
ORDERBY level;
-- Retention: users active this week who were also active last weekSELECTcount(DISTINCT curr.user_id) AS retained_users
FROM analytics.events AS curr
INNERJOIN analytics.events AS prev
ON curr.user_id = prev.user_id
WHERE curr.created_at >= toMonday(today())
AND prev.created_at >= toMonday(today()) -7AND prev.created_at < toMonday(today());
Step 3: Parameterized Queries in Node.js
// Use {param:Type} syntax for safe parameterized queriesconst rs = await client.query({
query: `
SELECT event_type, count() AS cnt
FROM analytics.events
WHERE tenant_id = {tenant_id:UInt32}
AND created_at >= {from_date:DateTime}
GROUP BY event_type
ORDER BY cnt DESC
`,
query_params: {
tenant_id: 1,
from_date: '2025-01-01 00:00:00',
},
format: 'JSONEachRow',
});
const rows = await rs.json();
Step 4: Materialized Views (Pre-Aggregation)
-- Source table receives raw events-- Materialized view aggregates automatically on INSERTCREATE MATERIALIZED VIEW analytics.hourly_stats_mv
TO analytics.hourly_stats -- target tableASSELECT
toStartOfHour(created_at) AShour,
tenant_id,
event_type,
count() AS event_count,
uniqState(user_id) AS unique_users_state
FROM analytics.events
GROUPBYhour, tenant_id, event_type;
-- Target table uses AggregatingMergeTreeCREATE TABLE analytics.hourly_stats (
hour DateTime,
tenant_id UInt32,
event_type LowCardinality(String),
event_count UInt64,
unique_users_state AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDERBY (tenant_id, event_type, hour);
-- Query the materialized view (merge aggregation states)SELECThour,
sum(event_count) AS events,
uniqMerge(unique_users_state) AS unique_users
FROM analytics.hourly_stats
WHERE tenant_id =1GROUPBYhourORDERBYhour;
Step 5: Window Functions
-- Running total and rank within each tenantSELECT
tenant_id,
event_type,
count() AS cnt,
sum(count()) OVER (PARTITIONBY tenant_id ORDERBYcount() DESC) AS running_total,
row_number() OVER (PARTITIONBY tenant_id ORDERBYcount() DESC) AS rank
FROM analytics.events
WHERE created_at >= today() -7GROUPBY tenant_id, event_type
ORDERBY tenant_id, rank;
