clickhouse-io

Name: clickhouse-io
Author: vibeeval

519stars44forks

How to install clickhouse-io?

vibeeval/vibecosystem/clickhouse-io

$npx -y skills add vibeeval/vibecosystem --skill clickhouse-io

Installs into the current project.

Summary

--- name: clickhouse-io description: ClickHouse database patterns, query optimization, analytics, and data engineering best practices for high-performance analytical workloads. ---

Files · 1

View on GitHub

SKILL.md

1	# ClickHouse Analytics Patterns
2
3	ClickHouse-specific patterns for high-performance analytics and data engineering.
4
5	## Overview
6
7	ClickHouse is a column-oriented database management system (DBMS) for online analytical processing (OLAP). It's optimized for fast analytical queries on large datasets.
8
9	Key Features:
10	- Column-oriented storage
11	- Data compression
12	- Parallel query execution
13	- Distributed queries
14	- Real-time analytics
15
16	## Table Design Patterns
17
18	### MergeTree Engine (Most Common)
19
20	```sql
21	CREATE TABLE markets_analytics (
22	date Date,
23	market_id String,
24	market_name String,
25	volume UInt64,
26	trades UInt32,
27	unique_traders UInt32,
28	avg_trade_size Float64,
29	created_at DateTime
30	) ENGINE = MergeTree()
31	PARTITION BY toYYYYMM(date)
32	ORDER BY (date, market_id)
33	SETTINGS index_granularity = 8192;
34	```
35
36	### ReplacingMergeTree (Deduplication)
37
38	```sql
39	-- For data that may have duplicates (e.g., from multiple sources)
40	CREATE TABLE user_events (
41	event_id String,
42	user_id String,
43	event_type String,
44	timestamp DateTime,
45	properties String
46	) ENGINE = ReplacingMergeTree()
47	PARTITION BY toYYYYMM(timestamp)
48	ORDER BY (user_id, event_id, timestamp)
49	PRIMARY KEY (user_id, event_id);
50	```
51
52	### AggregatingMergeTree (Pre-aggregation)
53
54	```sql
55	-- For maintaining aggregated metrics
56	CREATE TABLE market_stats_hourly (
57	hour DateTime,
58	market_id String,
59	total_volume AggregateFunction(sum, UInt64),
60	total_trades AggregateFunction(count, UInt32),
61	unique_users AggregateFunction(uniq, String)
62	) ENGINE = AggregatingMergeTree()
63	PARTITION BY toYYYYMM(hour)
64	ORDER BY (hour, market_id);
65
66	-- Query aggregated data
67	SELECT
68	hour,
69	market_id,
70	sumMerge(total_volume) AS volume,
71	countMerge(total_trades) AS trades,
72	uniqMerge(unique_users) AS users
73	FROM market_stats_hourly
74	WHERE hour >= toStartOfHour(now() - INTERVAL 24 HOUR)
75	GROUP BY hour, market_id
76	ORDER BY hour DESC;
77	```
78
79	## Query Optimization Patterns
80
81	### Efficient Filtering
82
83	```sql
84	-- ✅ GOOD: Use indexed columns first
85	SELECT *
86	FROM markets_analytics
87	WHERE date >= '2025-01-01'
88	AND market_id = 'market-123'
89	AND volume > 1000
90	ORDER BY date DESC
91	LIMIT 100;
92
93	-- ❌ BAD: Filter on non-indexed columns first
94	SELECT *
95	FROM markets_analytics
96	WHERE volume > 1000
97	AND market_name LIKE '%election%'
98	AND date >= '2025-01-01';
99	```
100
101	### Aggregations
102
103	```sql
104	-- ✅ GOOD: Use ClickHouse-specific aggregation functions
105	SELECT
106	toStartOfDay(created_at) AS day,
107	market_id,
108	sum(volume) AS total_volume,
109	count() AS total_trades,
110	uniq(trader_id) AS unique_traders,
111	avg(trade_size) AS avg_size
112	FROM trades
113	WHERE created_at >= today() - INTERVAL 7 DAY
114	GROUP BY day, market_id
115	ORDER BY day DESC, total_volume DESC;
116
117	-- ✅ Use quantile for percentiles (more efficient than percentile)
118	SELECT
119	quantile(0.50)(trade_size) AS median,
120	quantile(0.95)(trade_size) AS p95,
121	quantile(0.99)(trade_size) AS p99
122	FROM trades
123	WHERE created_at >= now() - INTERVAL 1 HOUR;
124	```
125
126	### Window Functions
127
128	```sql
129	-- Calculate running totals
130	SELECT
131	date,
132	market_id,
133	volume,
134	sum(volume) OVER (
135	PARTITION BY market_id
136	ORDER BY date
137	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
138	) AS cumulative_volume
139	FROM markets_analytics
140	WHERE date >= today() - INTERVAL 30 DAY
141	ORDER BY market_id, date;
142	```
143
144	## Data Insertion Patterns
145
146	### Bulk Insert (Recommended)
147
148	```typescript
149	import { ClickHouse } from 'clickhouse'
150
151	const clickhouse = new ClickHouse({
152	url: process.env.CLICKHOUSE_URL,
153	port: 8123,
154	basicAuth: {
155	username: process.env.CLICKHOUSE_USER,
156	password: process.env.CLICKHOUSE_PASSWORD
157	}
158	})
159
160	// ✅ Batch insert (efficient)
161	async function bulkInsertTrades(trades: Trade[]) {
162	const values = trades.map(trade => `(
163	'${trade.id}',
164	'${trade.market_id}',
165	'${trade.user_id}',
166	${trade.amount},
167	'${trade.timestamp.toISOString()}'
168	)`).join(',')
169
170	await clickhouse.query(`
171	INSERT INTO trades (id, market_id, user_id, amount, timestamp)
172	VALUES ${values}
173	`).toPromise()
174	}
175
176	// ❌ Individual inserts (slow)
177	async function insertTrade(trade: Trade) {
178	// Don't do this in a loop!
179	await clickhouse.query(`
180	INSERT INTO trades VALUES ('${trade.id}', ...)
181	`).toPromise()
182	}
183	```
184
185	### Streaming Insert
186
187	```typescript
188	// For continuous data ingestion
189	import { createWriteStream } from 'fs'
190	import { pipeline } from 'stream/promises'
191
192	async function streamInserts() {
193	const stream = clickhouse.insert('trades').stream()
194
195	for await (const batch of dataSource) {
196	stream.write(batch)
197	}
198
199	await stream.end()
200	}
201	```
202
203	## Mater

People who liked this also viewed

6 picks

Type

azure-storageAzure Storage Services including Blob Storage, File Shares, Queue Storage, Table Storage, and Data Lake. Answers questions about storage access tiers (hot, cool, cold, archive), when to use each tier,SkillsJul 2026471k1.3k
supabase-postgres-best-practicesPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.SkillsJul 2026302k2.4k
supabaseUse when doing ANY task involving Supabase. Triggers: Supabase products (Database, Auth, Edge Functions, Realtime, Storage, Vectors, Cron, Queues); client libraries and SSR integrations (supabase-js, SkillsJul 2026179k2.4k
convex-quickstartCreates or adds Convex to an app. Use for new Convex projects, npm create convex@latest, frontend setup, env vars, or the first npx convex dev run.SkillsJul 202690k39
convex-performance-auditAudits Convex performance for reads, subscriptions, write contention, and function limits. Use for slow features, insights findings, OCC conflicts, or read amplification.SkillsJul 202689k39
convex-setup-authSets up Convex auth, identity mapping, and access control. Use for login, auth providers, users tables, protected functions, or roles in a Convex app.SkillsJul 202689k39

clickhouse-io

519stars44forks

View on GitHub

How to install clickhouse-io?

vibeeval/vibecosystem/clickhouse-io

$npx -y skills add vibeeval/vibecosystem --skill clickhouse-io

Installs into the current project.

Summary

--- name: clickhouse-io description: ClickHouse database patterns, query optimization, analytics, and data engineering best practices for high-performance analytical workloads. ---

Files · 1

View on GitHub

SKILL.md

1	# ClickHouse Analytics Patterns
2
3	ClickHouse-specific patterns for high-performance analytics and data engineering.
4
5	## Overview
6
7	ClickHouse is a column-oriented database management system (DBMS) for online analytical processing (OLAP). It's optimized for fast analytical queries on large datasets.
8
9	Key Features:
10	- Column-oriented storage
11	- Data compression
12	- Parallel query execution
13	- Distributed queries
14	- Real-time analytics
15
16	## Table Design Patterns
17
18	### MergeTree Engine (Most Common)
19
20	```sql
21	CREATE TABLE markets_analytics (
22	date Date,
23	market_id String,
24	market_name String,
25	volume UInt64,
26	trades UInt32,
27	unique_traders UInt32,
28	avg_trade_size Float64,
29	created_at DateTime
30	) ENGINE = MergeTree()
31	PARTITION BY toYYYYMM(date)
32	ORDER BY (date, market_id)
33	SETTINGS index_granularity = 8192;
34	```
35
36	### ReplacingMergeTree (Deduplication)
37
38	```sql
39	-- For data that may have duplicates (e.g., from multiple sources)
40	CREATE TABLE user_events (
41	event_id String,
42	user_id String,
43	event_type String,
44	timestamp DateTime,
45	properties String
46	) ENGINE = ReplacingMergeTree()
47	PARTITION BY toYYYYMM(timestamp)
48	ORDER BY (user_id, event_id, timestamp)
49	PRIMARY KEY (user_id, event_id);
50	```
51
52	### AggregatingMergeTree (Pre-aggregation)
53
54	```sql
55	-- For maintaining aggregated metrics
56	CREATE TABLE market_stats_hourly (
57	hour DateTime,
58	market_id String,
59	total_volume AggregateFunction(sum, UInt64),
60	total_trades AggregateFunction(count, UInt32),
61	unique_users AggregateFunction(uniq, String)
62	) ENGINE = AggregatingMergeTree()
63	PARTITION BY toYYYYMM(hour)
64	ORDER BY (hour, market_id);
65
66	-- Query aggregated data
67	SELECT
68	hour,
69	market_id,
70	sumMerge(total_volume) AS volume,
71	countMerge(total_trades) AS trades,
72	uniqMerge(unique_users) AS users
73	FROM market_stats_hourly
74	WHERE hour >= toStartOfHour(now() - INTERVAL 24 HOUR)
75	GROUP BY hour, market_id
76	ORDER BY hour DESC;
77	```
78
79	## Query Optimization Patterns
80
81	### Efficient Filtering
82
83	```sql
84	-- ✅ GOOD: Use indexed columns first
85	SELECT *
86	FROM markets_analytics
87	WHERE date >= '2025-01-01'
88	AND market_id = 'market-123'
89	AND volume > 1000
90	ORDER BY date DESC
91	LIMIT 100;
92
93	-- ❌ BAD: Filter on non-indexed columns first
94	SELECT *
95	FROM markets_analytics
96	WHERE volume > 1000
97	AND market_name LIKE '%election%'
98	AND date >= '2025-01-01';
99	```
100
101	### Aggregations
102
103	```sql
104	-- ✅ GOOD: Use ClickHouse-specific aggregation functions
105	SELECT
106	toStartOfDay(created_at) AS day,
107	market_id,
108	sum(volume) AS total_volume,
109	count() AS total_trades,
110	uniq(trader_id) AS unique_traders,
111	avg(trade_size) AS avg_size
112	FROM trades
113	WHERE created_at >= today() - INTERVAL 7 DAY
114	GROUP BY day, market_id
115	ORDER BY day DESC, total_volume DESC;
116
117	-- ✅ Use quantile for percentiles (more efficient than percentile)
118	SELECT
119	quantile(0.50)(trade_size) AS median,
120	quantile(0.95)(trade_size) AS p95,
121	quantile(0.99)(trade_size) AS p99
122	FROM trades
123	WHERE created_at >= now() - INTERVAL 1 HOUR;
124	```
125
126	### Window Functions
127
128	```sql
129	-- Calculate running totals
130	SELECT
131	date,
132	market_id,
133	volume,
134	sum(volume) OVER (
135	PARTITION BY market_id
136	ORDER BY date
137	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
138	) AS cumulative_volume
139	FROM markets_analytics
140	WHERE date >= today() - INTERVAL 30 DAY
141	ORDER BY market_id, date;
142	```
143
144	## Data Insertion Patterns
145
146	### Bulk Insert (Recommended)
147
148	```typescript
149	import { ClickHouse } from 'clickhouse'
150
151	const clickhouse = new ClickHouse({
152	url: process.env.CLICKHOUSE_URL,
153	port: 8123,
154	basicAuth: {
155	username: process.env.CLICKHOUSE_USER,
156	password: process.env.CLICKHOUSE_PASSWORD
157	}
158	})
159
160	// ✅ Batch insert (efficient)
161	async function bulkInsertTrades(trades: Trade[]) {
162	const values = trades.map(trade => `(
163	'${trade.id}',
164	'${trade.market_id}',
165	'${trade.user_id}',
166	${trade.amount},
167	'${trade.timestamp.toISOString()}'
168	)`).join(',')
169
170	await clickhouse.query(`
171	INSERT INTO trades (id, market_id, user_id, amount, timestamp)
172	VALUES ${values}
173	`).toPromise()
174	}
175
176	// ❌ Individual inserts (slow)
177	async function insertTrade(trade: Trade) {
178	// Don't do this in a loop!
179	await clickhouse.query(`
180	INSERT INTO trades VALUES ('${trade.id}', ...)
181	`).toPromise()
182	}
183	```
184
185	### Streaming Insert
186
187	```typescript
188	// For continuous data ingestion
189	import { createWriteStream } from 'fs'
190	import { pipeline } from 'stream/promises'
191
192	async function streamInserts() {
193	const stream = clickhouse.insert('trades').stream()
194
195	for await (const batch of dataSource) {
196	stream.write(batch)
197	}
198
199	await stream.end()
200	}
201	```
202
203	## Mater

People who liked this also viewed

6 picks

Type