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