---
id: gh-chdb-sql
name: "chdb-sql"
url: https://skills.yangsir.net/skill/gh-chdb-sql
author: clickhouse
domain: data-analysis
tags: ["sql", "clickhouse", "data-analysis", "python", "database"]
install_count: 5000
rating: 4.40 (120 reviews)
github: https://github.com/clickhouse/agent-skills/tree/main/skills/chdb-sql
---

# chdb-sql

> 此技能让你在 Python 进程中直接运行 ClickHouse SQL，无需安装服务器。可查询本地文件、远程数据库和云存储，支持多源数据联合分析，极大简化数据探索与 ETL 流程，提升数据分析效率。

**Stats**: 5,000 installs · 4.4/5 (120 reviews)

## Before / After 对比

### 数据分析流程耗时对比

**Before**:

用户需要搭建 ClickHouse 服务器或使用其他工具，手动导入数据，编写复杂脚本，整个过程耗时且容易出错，平均每次数据分析耗时 60 分钟。

**After**:

此技能直接在 Python 中运行 SQL，无需服务器，查询本地文件和数据库，平均 10 分钟即可完成相同分析，且无需维护基础设施。

| Metric | Before | After | Change |
|---|---|---|---|
| 数据分析时间 | 60分钟 | 10分钟 | -83% |

## Readme

# 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.

```bash
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

```python
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](references/table-functions.md) | SQL functions → [sql-functions.md](references/sql-functions.md) | Full API → [api-reference.md](references/api-reference.md)

## Session — Stateful Analysis Pipelines

```python
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)

```python
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](references/api-reference.md) — query/Session/connect signatures
- [Table Functions](references/table-functions.md) — All ClickHouse table functions
- [SQL Functions](references/sql-functions.md) — Commonly used SQL functions
- [Examples](examples/examples.md) — 9 runnable examples with expected output
- [Official Docs](https://clickhouse.com/docs/chdb)

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


---

# chdb SQL

Agent skill for using chdb's SQL API — run ClickHouse SQL directly in Python without a server.

## Installation

```bash
npx skills add clickhouse/agent-skills
```

## What's Included

| File | Purpose |
|------|---------|
| `SKILL.md` | Skill definition with quick-start examples |
| `references/api-reference.md` | chdb.query(), Session, Connection signatures |
| `references/table-functions.md` | All ClickHouse table functions (file, s3, mysql, etc.) |
| `references/sql-functions.md` | Commonly used ClickHouse SQL functions |
| `examples/examples.md` | 9 runnable examples with expected output |
| `scripts/verify_install.py` | Environment verification script |

## Trigger Phrases

This skill activates when you:
- "Query this Parquet/CSV file with SQL"
- "Use chdb to run a query"
- "Join MySQL and S3 data with SQL"
- "Create a ClickHouse session"
- "Use ClickHouse table functions"
- "Write a parametrized query"

## Related

- **chdb-datastore** — For pandas-style DataFrame operations, use the `chdb-datastore` skill instead
- **clickhouse-best-practices** — For ClickHouse schema/query optimization

## Documentation

- [chdb docs](https://clickhouse.com/docs/chdb)
- [chdb GitHub](https://github.com/chdb-io/chdb)


---
*Source: https://skills.yangsir.net/skill/gh-chdb-sql*
*Markdown mirror: https://skills.yangsir.net/api/skill/gh-chdb-sql/markdown*