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".
Instrucciones de origen · Vista previa de solo lectura
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;
