chdb-sql

Name: chdb-sql
Author: ClickHouse

494stars32forks

How to install chdb-sql?

clickhouse/agent-skills/chdb-sql

$npx -y skills add ClickHouse/agent-skills --skill chdb-sql

Installs into the current project.

Summary

Use when the user wants to run SQL — especially analytical SQL — on local files (parquet/csv/json), URLs, S3 paths, or remote databases (Postgres, MySQL, MongoDB, ClickHouse Cloud, Iceberg, Delta Lake) without setting up a server. Provides chDB — embedded ClickHouse SQL in Python

Files · 1

View on GitHub

SKILL.md

1	# chdb SQL — ClickHouse in Your Python Process
2
3	Run ClickHouse SQL directly in Python — no server needed. Query local files, remote databases, and cloud storage with full ClickHouse SQL power.
4
5	```bash
6	pip install chdb
7	```
8
9	## Decision Tree: Pick the Right API
10
11	```
12	1. One-off query on files or databases → chdb.query()
13	2. Multi-step analysis with tables → Session
14	3. DB-API 2.0 connection → chdb.connect()
15	4. Pandas-style DataFrame operations → Use chdb-datastore skill instead
16	```
17
18	## chdb.query() — One Line, Any Data
19
20	```python
21	import chdb
22
23	chdb.query("SELECT * FROM file('data.parquet', Parquet) WHERE price > 100 LIMIT 10") # local files
24	chdb.query("SELECT * FROM mysql('db:3306', 'shop', 'orders', 'root', 'pass')") # databases
25	chdb.query("SELECT * FROM s3('s3://bucket/data.parquet', NOSIGN) LIMIT 10") # cloud storage
26	chdb.query("SELECT * FROM deltaLake('s3://bucket/delta/table', NOSIGN) LIMIT 10") # data lakes
27
28	# Cross-source join
29	chdb.query("""
30	SELECT u.name, o.amount FROM mysql('db:3306', 'crm', 'users', 'root', 'pass') AS u
31	JOIN file('orders.parquet', Parquet) AS o ON u.id = o.user_id ORDER BY o.amount DESC
32	""")
33
34	data = {"name": ["Alice", "Bob"], "score": [95, 87]}
35	chdb.query("SELECT * FROM Python(data) ORDER BY score DESC") # Python data
36	df = chdb.query("SELECT * FROM numbers(10)", "DataFrame") # output formats
37	chdb.query("SELECT toDate({d:String}) + number FROM numbers({n:UInt64})",
38	"DataFrame", params={"d": "2025-01-01", "n": 30}) # parametrized
39	```
40
41	Table functions → [table-functions.md](references/table-functions.md) \| SQL functions → [sql-functions.md](references/sql-functions.md) \| Full API → [api-reference.md](references/api-reference.md)
42
43	## Session — Stateful Analysis Pipelines
44
45	```python
46	from chdb import session as chs
47	sess = chs.Session("./analytics_db") # persistent; Session() for in-memory
48
49	sess.query("CREATE TABLE users ENGINE=MergeTree() ORDER BY id AS SELECT * FROM mysql('db:3306','crm','users','root','pass')")
50	sess.query("CREATE TABLE events ENGINE=MergeTree() ORDER BY (ts,user_id) AS SELECT * FROM s3('s3://logs/events/*.parquet',NOSIGN)")
51	sess.query("""
52	SELECT u.country, count() AS cnt, uniqExact(e.user_id) AS users
53	FROM events e JOIN users u ON e.user_id = u.id
54	WHERE e.ts >= today() - 7 GROUP BY u.country ORDER BY cnt DESC
55	""", "Pretty").show()
56	sess.close()
57	```
58
59	## Connection API (DB-API 2.0)
60
61	```python
62	from chdb import dbapi
63	conn = dbapi.connect()
64	cur = conn.cursor()
65	cur.execute("SELECT * FROM file('data.parquet', Parquet) WHERE value > 100")
66	print(cur.fetchall())
67	cur.close()
68	conn.close()
69	```
70
71	## Troubleshooting
72
73	\| Problem \| Fix \|
74	\|---------\|-----\|
75	\| `ImportError: No module named 'chdb'` \| `pip install chdb` \|
76	\| `DB::Exception: FILE_NOT_FOUND` \| Check file path; use absolute path or verify cwd \|
77	\| `DB::Exception: Unknown table function` \| Check function name spelling (e.g., `deltaLake` not `deltalake`) \|
78	\| Connection refused to remote DB \| Check host:port format; ensure remote DB allows connections \|
79	\| Environment check \| Run `python scripts/verify_install.py` (from skill directory) \|
80
81	## References
82
83	- [API Reference](references/api-reference.md) — query/Session/connect signatures
84	- [Table Functions](references/table-functions.md) — All ClickHouse table functions
85	- [SQL Functions](references/sql-functions.md) — Commonly used SQL functions
86	- [Examples](examples/examples.md) — 9 runnable examples with expected output
87	- [Official Docs](https://clickhouse.com/docs/chdb)
88
89	> Note: This skill teaches how to use chdb SQL.
90	> For pandas-style operations, use the `chdb-datastore` skill.
91	> For contributing to chdb source code, see CLAUDE.md in the project root.

People who liked this also viewed

6 picks

Type

chdb-sql

494stars32forks

View on GitHub

How to install chdb-sql?

clickhouse/agent-skills/chdb-sql

$npx -y skills add ClickHouse/agent-skills --skill chdb-sql

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# chdb SQL — ClickHouse in Your Python Process
2
3	Run ClickHouse SQL directly in Python — no server needed. Query local files, remote databases, and cloud storage with full ClickHouse SQL power.
4
5	```bash
6	pip install chdb
7	```
8
9	## Decision Tree: Pick the Right API
10
11	```
12	1. One-off query on files or databases → chdb.query()
13	2. Multi-step analysis with tables → Session
14	3. DB-API 2.0 connection → chdb.connect()
15	4. Pandas-style DataFrame operations → Use chdb-datastore skill instead
16	```
17
18	## chdb.query() — One Line, Any Data
19
20	```python
21	import chdb
22
23	chdb.query("SELECT * FROM file('data.parquet', Parquet) WHERE price > 100 LIMIT 10") # local files
24	chdb.query("SELECT * FROM mysql('db:3306', 'shop', 'orders', 'root', 'pass')") # databases
25	chdb.query("SELECT * FROM s3('s3://bucket/data.parquet', NOSIGN) LIMIT 10") # cloud storage
26	chdb.query("SELECT * FROM deltaLake('s3://bucket/delta/table', NOSIGN) LIMIT 10") # data lakes
27
28	# Cross-source join
29	chdb.query("""
30	SELECT u.name, o.amount FROM mysql('db:3306', 'crm', 'users', 'root', 'pass') AS u
31	JOIN file('orders.parquet', Parquet) AS o ON u.id = o.user_id ORDER BY o.amount DESC
32	""")
33
34	data = {"name": ["Alice", "Bob"], "score": [95, 87]}
35	chdb.query("SELECT * FROM Python(data) ORDER BY score DESC") # Python data
36	df = chdb.query("SELECT * FROM numbers(10)", "DataFrame") # output formats
37	chdb.query("SELECT toDate({d:String}) + number FROM numbers({n:UInt64})",
38	"DataFrame", params={"d": "2025-01-01", "n": 30}) # parametrized
39	```
40
41	Table functions → [table-functions.md](references/table-functions.md) \| SQL functions → [sql-functions.md](references/sql-functions.md) \| Full API → [api-reference.md](references/api-reference.md)
42
43	## Session — Stateful Analysis Pipelines
44
45	```python
46	from chdb import session as chs
47	sess = chs.Session("./analytics_db") # persistent; Session() for in-memory
48
49	sess.query("CREATE TABLE users ENGINE=MergeTree() ORDER BY id AS SELECT * FROM mysql('db:3306','crm','users','root','pass')")
50	sess.query("CREATE TABLE events ENGINE=MergeTree() ORDER BY (ts,user_id) AS SELECT * FROM s3('s3://logs/events/*.parquet',NOSIGN)")
51	sess.query("""
52	SELECT u.country, count() AS cnt, uniqExact(e.user_id) AS users
53	FROM events e JOIN users u ON e.user_id = u.id
54	WHERE e.ts >= today() - 7 GROUP BY u.country ORDER BY cnt DESC
55	""", "Pretty").show()
56	sess.close()
57	```
58
59	## Connection API (DB-API 2.0)
60
61	```python
62	from chdb import dbapi
63	conn = dbapi.connect()
64	cur = conn.cursor()
65	cur.execute("SELECT * FROM file('data.parquet', Parquet) WHERE value > 100")
66	print(cur.fetchall())
67	cur.close()
68	conn.close()
69	```
70
71	## Troubleshooting
72
73	\| Problem \| Fix \|
74	\|---------\|-----\|
75	\| `ImportError: No module named 'chdb'` \| `pip install chdb` \|
76	\| `DB::Exception: FILE_NOT_FOUND` \| Check file path; use absolute path or verify cwd \|
77	\| `DB::Exception: Unknown table function` \| Check function name spelling (e.g., `deltaLake` not `deltalake`) \|
78	\| Connection refused to remote DB \| Check host:port format; ensure remote DB allows connections \|
79	\| Environment check \| Run `python scripts/verify_install.py` (from skill directory) \|
80
81	## References
82
83	- [API Reference](references/api-reference.md) — query/Session/connect signatures
84	- [Table Functions](references/table-functions.md) — All ClickHouse table functions
85	- [SQL Functions](references/sql-functions.md) — Commonly used SQL functions
86	- [Examples](examples/examples.md) — 9 runnable examples with expected output
87	- [Official Docs](https://clickhouse.com/docs/chdb)
88
89	> Note: This skill teaches how to use chdb SQL.
90	> For pandas-style operations, use the `chdb-datastore` skill.
91	> For contributing to chdb source code, see CLAUDE.md in the project root.

People who liked this also viewed

6 picks

Type