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".
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;
