Agent Skills › vemetric/vemetric › chdb-sql

chdb-sql

GitHub

提供在Python中无需服务器即可运行ClickHouse SQL的能力，支持查询本地文件、远程数据库及云存储。

.agents/skills/chdb-sql/SKILL.md vemetric/vemetric

Trigger Scenarios

需要对parquet/csv/json等本地文件或远程数据源执行SQL查询需要使用ClickHouse特有SQL功能如窗口函数或参数化查询

Install

npx skills add vemetric/vemetric --skill chdb-sql -g -y

More Options

Non-standard path

npx skills add https://github.com/vemetric/vemetric/tree/main/.agents/skills/chdb-sql -g -y

Use without installing

npx skills use vemetric/vemetric@chdb-sql

指定 Agent (Claude Code)

npx skills add vemetric/vemetric --skill chdb-sql -a claude-code -g -y

安装 repo 全部 skill

npx skills add vemetric/vemetric --all -g -y

预览 repo 内 skill

npx skills add vemetric/vemetric --list

SKILL.md

Frontmatter

{
    "name": "chdb-sql",
    "license": "Apache-2.0",
    "metadata": {
        "author": "chdb-io",
        "version": "4.1",
        "homepage": "https:\/\/clickhouse.com\/docs\/chdb"
    },
    "description": "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.",
    "compatibility": "Requires Python 3.9+, macOS or Linux. pip install chdb."
}

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.