chdb-sql

Overview of chdb-sql skill

What chdb-sql is for

chdb-sql is the skill to use when you want ClickHouse SQL inside Python without running a separate database server. It fits analysts and backend developers who need to query local files, join external data sources, or build stateful SQL pipelines with Session while staying in a normal Python workflow.

Why it matters

The main value of the chdb-sql skill is speed-to-query and less infrastructure. It is a strong fit for ad hoc file analytics, SQL-heavy data prep, and backend development tasks where ClickHouse syntax is the right tool but a persistent ClickHouse service would be overkill.

Key differentiators

This skill is not just “SQL in Python.” It covers chdb.query(), DB-API-style connections, stateful sessions, parametrized queries, ClickHouse table functions such as file(), s3(), mysql(), and postgresql(), plus advanced SQL features like window functions. It is less suitable for pandas-style transformations, which is a different fit.

How to Use chdb-sql skill

Install and verify it

Use the repository install path for the skill package, then verify the runtime before relying on it in a workflow:

npx skills add ClickHouse/agent-skills --skill chdb-sql
python scripts/verify_install.py

The verify script is useful because adoption issues are often environmental: Python version, missing package, or a broken Session path.

Start from the right API choice

Use the decision pattern implied by the skill: chdb.query() for one-off queries, Session for multi-step work, and a connection object when you need DB-API 2.0 behavior. If your goal is “join a CSV, a Parquet file, and a MySQL table,” the prompt should say that directly so the skill can pick table functions and avoid a generic SQL answer.

Read these files first

For fastest orientation, start with SKILL.md, then references/api-reference.md, references/table-functions.md, and examples/examples.md. Read references/sql-functions.md when your query depends on ClickHouse-specific syntax, and use scripts/verify_install.py to confirm the local environment matches the skill’s assumptions. That path gives better chdb-sql usage than skimming only the landing page.

Prompting pattern that works

Give the skill the data source, output shape, and statefulness requirement in one request. Good input:

• “Use chdb-sql to query sales.parquet, group by region, and return a DataFrame with revenue totals.”
• “Use chdb-sql for Backend Development: join orders.csv with mysql() data, filter by date, and keep it as a reusable Session.”
• “Write a parametrized chdb.query() example for a date range and country filter.”

Weak input:

• “Use chdb-sql on this data.”
  That leaves too much ambiguity about API choice, source type, and whether the result should be streamed, tabular, or stateful.

chdb-sql skill FAQ

Is chdb-sql only for ClickHouse experts?

No. You do not need deep ClickHouse knowledge to start, but you do need to be comfortable specifying SQL results clearly. Beginners usually do fine if they state the source file, desired columns, and output format.

When should I not use chdb-sql?

Do not use it for pandas-first data wrangling or workflows that depend on a full server-side ClickHouse deployment. If the task is mainly DataFrame mutation, use the chdb-datastore path instead of forcing chdb-sql.

How is this different from a normal SQL prompt?

A normal prompt often produces a single query. chdb-sql is better when the task needs concrete API selection, table-function syntax, session state, or Python integration details. That is the main reason to prefer the chdb-sql skill over a generic “write SQL” prompt.

Is it useful for Backend Development?

Yes, especially when backend code needs fast SQL over files, external sources, or temporary analytical state. It is a good fit when you want SQL-powered logic inside Python services, ETL jobs, or internal tools without standing up a separate database.

How to Improve chdb-sql skill

Give source, goal, and output shape

The best chdb-sql results start with a precise input contract: data source, join targets, filters, and final format. For example, say “return a pandas DataFrame with daily totals” instead of “analyze the file.” If you need state, say so explicitly so the skill uses Session instead of a one-shot query.

Include constraints that affect SQL generation

Call out file format, source size, auth needs, and whether the query must be parameterized. These details change the implementation path in meaningful ways:

• local Parquet/CSV/JSON → file()
• cloud objects → s3() or gcs()
• relational source → mysql() or postgresql()
• repeated steps → Session

Watch for the common failure modes

The most common issue is asking for DataFrame-style output but expecting SQL semantics, or vice versa. Another frequent blocker is omitting the exact source format, which makes chdb-sql less precise about table functions and output formatting. If the first result is too generic, refine with the exact table name, expected columns, and one sample row or rule.

Iterate with a concrete correction

When improving a first pass, do not just ask for “better.” Ask for a specific change, such as “convert this to Session,” “parameterize the date range,” “switch to Pretty output,” or “use file('...', Parquet) instead of a plain table name.” Those edits improve chdb-sql guide quality because they target the exact part of the workflow that controls correctness.