examples/examples.md
# chdb SQL Examples
> All examples are self-contained and runnable.
> Expected output is shown in comments.
## Table of Contents
1. [Query Any File](#1-query-any-file)
2. [Cross-Source SQL Joins](#2-cross-source-sql-joins)
3. [Session: Build Analytical Tables](#3-session-build-analytical-tables)
4. [Python Data as SQL Table](#4-python-data-as-sql-table)
5. [Parametrized Queries](#5-parametrized-queries)
6. [Window Functions](#6-window-functions)
7. [User-Defined Functions (UDF)](#7-user-defined-functions-udf)
8. [Streaming Large Results](#8-streaming-large-results)
9. [Common Errors & Fixes](#9-common-errors--fixes)
---
## 1. Query Any File
```python
import chdb
# Parquet
result = chdb.query("""
SELECT country, count() AS cnt
FROM file('users.parquet', Parquet)
GROUP BY country
ORDER BY cnt DESC
LIMIT 10
""", "Pretty")
result.show()
# Expected: top 10 countries by user count, formatted table
# CSV
df = chdb.query("""
SELECT * FROM file('sales.csv', CSVWithNames)
WHERE revenue > 10000
ORDER BY revenue DESC
""", "DataFrame")
print(df)
# Expected: pandas DataFrame with high-revenue rows
# JSON Lines
chdb.query("""
SELECT * FROM file('events.jsonl', JSONEachRow)
WHERE event_type = 'purchase'
""").show()
# Glob pattern — query all matching files
df = chdb.query("""
SELECT level, count() AS cnt
FROM file('logs/2024-*.parquet', Parquet)
GROUP BY level
ORDER BY cnt DESC
""", "DataFrame")
print(df)
# Expected:
# level cnt
# 0 INFO 45230
# 1 WARN 3210
# 2 ERROR 890
```
---
## 2. Cross-Source SQL Joins
```python
import chdb
# MySQL + Parquet join
chdb.query("""
SELECT u.name, u.email, o.product, 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
WHERE o.amount > 100
ORDER BY o.amount DESC
LIMIT 20
""", "Pretty").show()
# S3 + PostgreSQL join
df = chdb.query("""
SELECT e.event_type, p.country, count() AS cnt
FROM s3('s3://bucket/events.parquet', 'KEY', 'SECRET', 'Parquet') AS e
JOIN postgresql('pg:5432', 'users', 'profiles', 'user', 'pass') AS p
ON e.user_id = p.id
GROUP BY e.event_type, p.country
ORDER BY cnt DESC
""", "DataFrame")
print(df)
# ClickHouse + local CSV
chdb.query("""
SELECT r.host, l.status_code, count() AS requests
FROM remote('ch:9000', 'logs', 'access_log', 'default', '') AS r
JOIN file('server_config.csv', CSVWithNames) AS l ON r.host = l.hostname
GROUP BY r.host, l.status_code
ORDER BY requests DESC
""").show()
```
---
## 3. Session: Build Analytical Tables
```python
from chdb import session as chs
sess = chs.Session("./analytics_db")
# Ingest from multiple external sources into local tables
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)
""")
# Analyze locally — fast iterative queries
result = sess.query("""
SELECT
u.country,
e.event_type,
count() AS cnt,
uniqExact(e.user_id) AS unique_users
FROM events e
JOIN users u ON e.user_id = u.id
WHERE e.ts >= today() - 7
GROUP BY u.country, e.event_type
ORDER BY cnt DESC
LIMIT 20
""", "Pretty")
result.show()
# Expected: formatted table with country, event_type, count, unique users
# Check table contents
sess.query("SELECT count() FROM users").show()
sess.query("SELECT count() FROM events").show()
sess.close()
```
---
## 4. Python Data as SQL Table
```python
import chdb
import pandas as pd
# Query a Python dict directly in SQL
scores = {"student": ["Alice", "Bob", "Carol"], "math": [95, 87, 92], "science": [88, 91, 85]}
chdb.query("SELECT student, math + science AS total FROM Python(scores) ORDER BY total DESC").show()
# Expected:
# Alice,183
# Bob,178
# Carol,177
# Query a pandas DataFrame in SQL
users_df = pd.DataFrame({"id": [1, 2, 3], "name": ["Alice", "Bob", "Carol"]})
chdb.query("""
SELECT p.name, o.product, o.amount
FROM Python(users_df) AS p
JOIN file('orders.parquet', Parquet) AS o ON p.id = o.user_id
ORDER BY o.amount DESC
""").show()
# Use Python data for parametrized lookups
allowed_ids = {"id": [1, 3, 5, 7, 9]}
df = chdb.query("""
SELECT * FROM file('data.parquet', Parquet)
WHERE id IN (SELECT id FROM Python(allowed_ids))
""", "DataFrame")
print(df)
```
---
## 5. Parametrized Queries
```python
import chdb
# Date range generation
result = chdb.query(
"""
SELECT
toDate({start:String}) + number AS date,
rand() % 1000 AS value
FROM numbers({days:UInt64})
""",
"DataFrame",
params={"start": "2025-01-01", "days": 30})
print(result)
# Expected: DataFrame with 30 rows, date column from 2025-01-01 to 2025-01-30
# Filtering with parameters
result = chdb.query(
"""
SELECT * FROM file('events.parquet', Parquet)
WHERE event_type = {event:String}
AND created_at >= {since:String}
ORDER BY created_at DESC
LIMIT {limit:UInt64}
""",
"DataFrame",
params={"event": "purchase", "since": "2025-01-01", "limit": 100})
print(result)
```
---
## 6. Window Functions
```python
import chdb
# Ranking within groups
chdb.query("""
SELECT
department,
name,
salary,
rank() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
salary - avg(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM file('employees.parquet', Parquet)
ORDER BY department, dept_rank
""", "Pretty").show()
# Expected: employees ranked within each department
# Running totals and moving averages
df = chdb.query("""
SELECT
date,
revenue,
sum(revenue) OVER (ORDER BY date) AS cumulative_revenue,
avg(revenue) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_avg
FROM file('daily_sales.csv', CSVWithNames)
ORDER BY date
""", "DataFrame")
print(df)
# Expected: daily sales with cumulative and 7-day rolling average
# Top-N per group
df = chdb.query("""
SELECT * FROM (
SELECT
category,
product,
sales,
row_number() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
FROM file('products.parquet', Parquet)
) WHERE rn <= 3
ORDER BY category, rn
""", "DataFrame")
print(df)
# Expected: top 3 products per category by sales
```
---
## 7. User-Defined Functions (UDF)
```python
from chdb.udf import chdb_udf
import chdb
@chdb_udf()
def fahrenheit_to_celsius(f):
return (f - 32) * 5.0 / 9.0
result = chdb.query("""
SELECT
city,
temp_f,
fahrenheit_to_celsius(temp_f) AS temp_c
FROM file('weather.csv', CSVWithNames)
ORDER BY temp_c DESC
LIMIT 10
""", "DataFrame")
print(result)
@chdb_udf()
def classify_age(age):
if age < 18:
return "minor"
elif age < 65:
return "adult"
else:
return "senior"
chdb.query("""
SELECT classify_age(age) AS group, count() AS cnt
FROM file('users.parquet', Parquet)
GROUP BY group
ORDER BY cnt DESC
""", "Pretty").show()
```
---
## 8. Streaming Large Results
```python
from chdb import session as chs
sess = chs.Session()
# Stream results in chunks for memory efficiency
iterator = sess.send_query(
"SELECT * FROM numbers(10000000)",
format="CSV")
row_count = 0
for chunk in iterator:
row_count += chunk.count(b'\n')
print(f"Total rows streamed: {row_count}")
# Expected: Total rows streamed: 10000000
sess.close()
```
---
## 9. Common Errors & Fixes
### File not found
```python
import chdb
# Error:
chdb.query("SELECT * FROM file('missing.parquet', Parquet)")
# → DB::Exception: FILE_NOT_FOUND
# Fix: verify the file path
import os
print(os.path.exists("missing.parquet")) # → False
# Use absolute path or check current working directory
chdb.query("SELECT * FROM file('/absolute/path/to/data.parquet', Parquet)")
```
### Wrong table function name
```python
# Error: function name is case-sensitive for data lake functions
chdb.query("SELECT * FROM deltalake('s3://bucket/table', NOSIGN)")
# → DB::Exception: Unknown table function deltalake
# Fix: use camelCase
chdb.query("SELECT * FROM deltaLake('s3://bucket/table', NOSIGN)")
```
### Database connection refused
```python
# Error: missing port or wrong host format
chdb.query("SELECT * FROM mysql('db', 'shop', 'orders', 'root', 'pass')")
# → Connection refused
# Fix: include port in host string
chdb.query("SELECT * FROM mysql('db:3306', 'shop', 'orders', 'root', 'pass')")
```
### Wrong output format
```python
import chdb
# Error: format name is case-sensitive
df = chdb.query("SELECT 1", "dataframe")
# → might not return expected type
# Fix: use exact format name
df = chdb.query("SELECT 1", "DataFrame") # capital D, capital F
```
### Debugging queries
```python
import chdb
# Use Pretty format to quickly inspect results
chdb.query("SELECT * FROM file('data.parquet', Parquet) LIMIT 5", "Pretty").show()
# Check column types
chdb.query("""
SELECT name, toTypeName(name) AS name_type, toTypeName(value) AS value_type
FROM file('data.parquet', Parquet)
LIMIT 1
""", "Pretty").show()
# Explain query execution plan
chdb.query("EXPLAIN SELECT * FROM file('data.parquet', Parquet) WHERE x > 100").show()
```
metadata.json
{
"version": "4.1.0",
"organization": "ClickHouse Inc",
"date": "March 2026",
"abstract": "In-process ClickHouse SQL engine for Python. Run SQL queries on local files, remote databases, and cloud storage without a server. Covers chdb.query(), Session, DB-API 2.0, parametrized queries, UDFs, streaming, and all ClickHouse table functions.",
"references": [
"https://clickhouse.com/docs/chdb",
"https://github.com/chdb-io/chdb"
]
}
README.md
# 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)
references/api-reference.md
# chdb SQL API Reference
> Complete signatures for the SQL-oriented chdb APIs.
## Table of Contents
- [chdb.query()](#chdbquery)
- [Session](#session)
- [Connection (DB-API 2.0)](#connection-db-api-20)
- [Output Formats](#output-formats)
- [Parametrized Queries](#parametrized-queries)
- [Streaming Queries](#streaming-queries)
- [Progress Callback](#progress-callback)
- [User-Defined Functions (UDF)](#user-defined-functions-udf)
- [AI-Assisted SQL](#ai-assisted-sql)
---
## chdb.query()
```python
chdb.query(sql, output_format="CSV", path="", udf_path="", params=None)
```
| Param | Type | Default | Description |
|-------|------|---------|-------------|
| `sql` | str | _(required)_ | ClickHouse SQL query |
| `output_format` | str | `"CSV"` | Output format (see [Output Formats](#output-formats)) |
| `path` | str | `""` | Database path (empty = in-memory, no state) |
| `udf_path` | str | `""` | Path for UDF scripts |
| `params` | dict | `None` | Named parameters (see [Parametrized Queries](#parametrized-queries)) |
**Returns:** Result object with:
| Property/Method | Description |
|-----------------|-------------|
| `.show()` | Print result to stdout |
| `.bytes()` | Raw bytes of the result |
| `.data()` | Result as string |
| `.rows_read` | Number of rows read |
| `.bytes_read` | Number of bytes read |
| `.elapsed` | Query execution time in seconds |
```python
import chdb
result = chdb.query("SELECT 1 + 1 AS answer")
result.show() # prints: 2
print(result.data()) # "2\n"
df = chdb.query("SELECT * FROM numbers(10)", "DataFrame")
print(df) # pandas DataFrame
```
---
## Session
```python
from chdb import session as chs
sess = chs.Session() # in-memory (no persistence)
sess = chs.Session("./mydb") # persistent to disk
```
| Method | Signature | Description |
|--------|-----------|-------------|
| `query()` | `(sql, fmt="CSV", params=None)` | Execute SQL with session state |
| `send_query()` | `(sql, format="CSV")` | Streaming query (returns iterator) |
| `close()` | `()` | Close session and release resources |
```python
from chdb import session as chs
sess = chs.Session("./analytics")
sess.query("CREATE TABLE t1 (id UInt64, name String) ENGINE = MergeTree() ORDER BY id")
sess.query("INSERT INTO t1 VALUES (1, 'Alice'), (2, 'Bob')")
result = sess.query("SELECT * FROM t1", "Pretty")
result.show()
sess.close()
```
**Key differences from `chdb.query()`:**
- Session maintains state: tables, databases, and settings persist across calls
- Persistent sessions (`path="./dir"`) survive process restarts
- In-memory sessions (`path=":memory:"`) are discarded on close
---
## Connection (DB-API 2.0)
```python
from chdb import dbapi
conn = dbapi.connect() # or: dbapi.connect(path="./mydb")
```
| Method | Description |
|--------|-------------|
| `conn.cursor()` | Create a cursor |
| `cur.execute(sql)` | Execute SQL |
| `cur.execute(sql, params)` | Execute with parameters |
| `cur.fetchone()` | Fetch one row |
| `cur.fetchmany(size)` | Fetch `size` rows |
| `cur.fetchall()` | Fetch all rows |
| `cur.description` | Column metadata |
| `cur.close()` | Close cursor |
| `conn.close()` | Close connection |
```python
from chdb import dbapi
conn = dbapi.connect()
cur = conn.cursor()
cur.execute("SELECT number, number * 2 AS doubled FROM numbers(5)")
print(cur.fetchall())
# [(0, 0), (1, 2), (2, 4), (3, 6), (4, 8)]
cur.close()
conn.close()
```
---
## Output Formats
| Format | Description | Use case |
|--------|-------------|----------|
| `"CSV"` | Comma-separated (default) | General export |
| `"CSVWithNames"` | CSV with header row | Spreadsheet import |
| `"JSON"` | JSON object with metadata | API responses |
| `"JSONEachRow"` | One JSON object per line | Streaming / NDJSON |
| `"DataFrame"` | pandas DataFrame | Python analysis |
| `"Arrow"` | Apache Arrow bytes | IPC format |
| `"ArrowTable"` | pyarrow.Table | Arrow ecosystem |
| `"Parquet"` | Parquet bytes | File export |
| `"Pretty"` | Formatted table | Terminal display |
| `"PrettyCompact"` | Compact table | Terminal display |
| `"TabSeparated"` | TSV | Tab-delimited export |
| `"Debug"` | Debug info | Troubleshooting |
```python
import chdb
chdb.query("SELECT 1", "Pretty").show() # formatted table
df = chdb.query("SELECT * FROM numbers(5)", "DataFrame") # pandas DataFrame
arrow = chdb.query("SELECT 1", "ArrowTable") # pyarrow Table
```
---
## Parametrized Queries
Use `{name:Type}` placeholders in SQL, and pass values via `params`:
```python
import chdb
result = chdb.query(
"""
SELECT toDate({start:String}) + number AS date, rand() % 1000 AS value
FROM numbers({days:UInt64})
""",
"DataFrame",
params={"start": "2025-01-01", "days": 30})
print(result)
```
Supported types: `String`, `UInt8`–`UInt64`, `Int8`–`Int64`, `Float32`, `Float64`, `Date`, `DateTime`.
---
## Streaming Queries
For large results, use `send_query` on a Session to get an iterator:
```python
from chdb import session as chs
sess = chs.Session()
iterator = sess.send_query("SELECT * FROM numbers(1000000)", format="CSV")
for chunk in iterator:
print(chunk[:100]) # process each chunk
sess.close()
```
---
## Progress Callback
Monitor query progress:
```python
import chdb
def on_progress(progress):
print(f"Rows: {progress.read_rows}, Bytes: {progress.read_bytes}")
chdb.query("SELECT * FROM numbers(10000000)", "CSV", progress_callback=on_progress)
```
---
## User-Defined Functions (UDF)
Register Python functions as SQL UDFs using the `@chdb_udf` decorator:
```python
from chdb.udf import chdb_udf
@chdb_udf()
def my_multiply(x, y):
return x * y
import chdb
result = chdb.query("SELECT my_multiply(number, 10) FROM numbers(5)", "DataFrame")
print(result)
```
**Limitations:**
- UDFs execute in-process, not distributed
- Arguments and return values must be scalar types
- Performance may be lower than native ClickHouse functions for large datasets
---
## AI-Assisted SQL
Generate SQL queries from natural language:
```python
import chdb
sql = chdb.generate_sql("top 10 countries by revenue from orders.parquet")
print(sql)
# SELECT country, sum(revenue) AS total_revenue
# FROM file('orders.parquet', Parquet)
# GROUP BY country
# ORDER BY total_revenue DESC
# LIMIT 10
result = chdb.ask("What are the top products by sales?", data="sales.parquet")
print(result)
```
**Note:** These features require an LLM API key configured via environment variables.
references/sql-functions.md
# ClickHouse SQL Functions Quick Reference
> Commonly used SQL functions available in chdb.
> For the full list, see [ClickHouse documentation](https://clickhouse.com/docs/en/sql-reference/functions).
## Table of Contents
- [Aggregate Functions](#aggregate-functions)
- [String Functions](#string-functions)
- [Date & Time Functions](#date--time-functions)
- [Type Conversion](#type-conversion)
- [Conditional Functions](#conditional-functions)
- [Array Functions](#array-functions)
- [JSON Functions](#json-functions)
- [Window Functions](#window-functions)
---
## Aggregate Functions
| Function | Description | Example |
|----------|-------------|---------|
| `count()` | Row count | `SELECT count() FROM t` |
| `count(col)` | Non-null count | `SELECT count(email) FROM users` |
| `sum(col)` | Sum | `SELECT sum(amount) FROM orders` |
| `avg(col)` | Average | `SELECT avg(salary) FROM employees` |
| `min(col)`, `max(col)` | Min/Max | `SELECT min(price), max(price) FROM products` |
| `uniqExact(col)` | Exact distinct count | `SELECT uniqExact(user_id) FROM events` |
| `uniq(col)` | Approximate distinct count (faster) | `SELECT uniq(user_id) FROM events` |
| `groupArray(col)` | Collect values into array | `SELECT dept, groupArray(name) FROM emp GROUP BY dept` |
| `quantile(level)(col)` | Quantile | `SELECT quantile(0.95)(latency) FROM requests` |
| `quantiles(0.5, 0.9, 0.99)(col)` | Multiple quantiles | `SELECT quantiles(0.5, 0.9, 0.99)(duration)` |
| `median(col)` | Median (= quantile(0.5)) | `SELECT median(age) FROM users` |
| `stddevPop(col)` | Population std dev | `SELECT stddevPop(value) FROM measurements` |
| `varPop(col)` | Population variance | `SELECT varPop(value) FROM measurements` |
| `argMax(col, val)` | Value of col at max val | `SELECT argMax(name, score) FROM students` |
| `argMin(col, val)` | Value of col at min val | `SELECT argMin(name, score) FROM students` |
| `topK(N)(col)` | Most frequent N values | `SELECT topK(10)(search_term) FROM queries` |
---
## String Functions
| Function | Description | Example |
|----------|-------------|---------|
| `lower(s)` | Lowercase | `SELECT lower('Hello')` → `'hello'` |
| `upper(s)` | Uppercase | `SELECT upper('Hello')` → `'HELLO'` |
| `trim(s)` | Remove whitespace | `SELECT trim(' hi ')` → `'hi'` |
| `length(s)` | String length | `SELECT length('hello')` → `5` |
| `substring(s, offset, length)` | Extract substring | `SELECT substring('hello', 1, 3)` → `'hel'` |
| `concat(a, b, ...)` | Concatenate | `SELECT concat(first, ' ', last)` |
| `like(s, pattern)` | LIKE match | `WHERE like(email, '%@gmail.com')` |
| `match(s, pattern)` | Regex match | `WHERE match(url, '^https?://')` |
| `extract(s, pattern)` | Regex extract | `SELECT extract(url, '://([^/]+)')` |
| `replaceAll(s, from, to)` | Replace all occurrences | `SELECT replaceAll(text, '\n', ' ')` |
| `replaceOne(s, from, to)` | Replace first occurrence | `SELECT replaceOne(s, 'old', 'new')` |
| `splitByChar(sep, s)` | Split string to array | `SELECT splitByChar(',', 'a,b,c')` |
| `splitByString(sep, s)` | Split by substring | `SELECT splitByString('::', path)` |
| `format(template, ...)` | Format string | `SELECT format('{} - {}', name, dept)` |
| `reverse(s)` | Reverse string | `SELECT reverse('hello')` → `'olleh'` |
| `base64Encode(s)` | Base64 encode | `SELECT base64Encode('hello')` |
| `base64Decode(s)` | Base64 decode | `SELECT base64Decode(encoded)` |
---
## Date & Time Functions
| Function | Description | Example |
|----------|-------------|---------|
| `today()` | Current date | `WHERE date = today()` |
| `now()` | Current datetime | `SELECT now()` |
| `toDate(x)` | Convert to Date | `SELECT toDate('2025-01-15')` |
| `toDateTime(x)` | Convert to DateTime | `SELECT toDateTime('2025-01-15 10:30:00')` |
| `toYear(d)` | Extract year | `SELECT toYear(order_date)` |
| `toMonth(d)` | Extract month | `SELECT toMonth(order_date)` |
| `toDayOfWeek(d)` | Day of week (1=Mon) | `SELECT toDayOfWeek(date)` |
| `toDayOfYear(d)` | Day of year | `SELECT toDayOfYear(date)` |
| `toHour(dt)` | Extract hour | `SELECT toHour(timestamp)` |
| `toMinute(dt)` | Extract minute | `SELECT toMinute(timestamp)` |
| `dateDiff(unit, d1, d2)` | Date difference | `SELECT dateDiff('day', start, end)` |
| `dateAdd(unit, n, d)` | Add to date | `SELECT dateAdd('month', 1, today())` |
| `dateSub(unit, n, d)` | Subtract from date | `SELECT dateSub('day', 7, today())` |
| `formatDateTime(dt, fmt)` | Format datetime | `SELECT formatDateTime(now(), '%Y-%m-%d %H:%M')` |
| `toStartOfMonth(d)` | First day of month | `SELECT toStartOfMonth(date)` |
| `toStartOfWeek(d)` | First day of week | `SELECT toStartOfWeek(date)` |
| `toStartOfHour(dt)` | Truncate to hour | `SELECT toStartOfHour(timestamp)` |
| `toMonday(d)` | Previous Monday | `SELECT toMonday(date)` |
**Date units for dateDiff/dateAdd/dateSub:** `'second'`, `'minute'`, `'hour'`, `'day'`, `'week'`, `'month'`, `'quarter'`, `'year'`.
---
## Type Conversion
| Function | Description | Example |
|----------|-------------|---------|
| `toInt32(x)` | Convert to Int32 | `SELECT toInt32('42')` |
| `toUInt64(x)` | Convert to UInt64 | `SELECT toUInt64(id)` |
| `toFloat64(x)` | Convert to Float64 | `SELECT toFloat64('3.14')` |
| `toString(x)` | Convert to String | `SELECT toString(123)` |
| `CAST(x AS Type)` | SQL-style cast | `SELECT CAST(price AS Decimal(10,2))` |
| `toFixedString(s, n)` | Fixed-length string | `SELECT toFixedString(code, 3)` |
| `toDecimal64(x, s)` | Decimal with scale | `SELECT toDecimal64(price, 2)` |
| `parseDateTimeBestEffort(s)` | Smart datetime parse | `SELECT parseDateTimeBestEffort('Jan 15 2025')` |
| `toTypeName(x)` | Get type name | `SELECT toTypeName(column)` |
---
## Conditional Functions
| Function | Description | Example |
|----------|-------------|---------|
| `if(cond, then, else)` | Ternary | `SELECT if(age >= 18, 'adult', 'minor')` |
| `multiIf(c1,v1, c2,v2, ..., default)` | Multi-branch | `SELECT multiIf(x>100,'high', x>50,'mid', 'low')` |
| `CASE WHEN ... THEN ... END` | SQL CASE | `CASE WHEN status=1 THEN 'active' ELSE 'inactive' END` |
| `coalesce(a, b, ...)` | First non-null | `SELECT coalesce(nickname, name, 'Unknown')` |
| `nullIf(a, b)` | NULL if a=b | `SELECT nullIf(value, 0)` |
| `ifNull(x, alt)` | Replace NULL | `SELECT ifNull(email, 'no-email')` |
| `isNull(x)` | Check NULL | `WHERE isNull(deleted_at)` |
| `isNotNull(x)` | Check not NULL | `WHERE isNotNull(email)` |
---
## Array Functions
| Function | Description | Example |
|----------|-------------|---------|
| `arrayJoin(arr)` | Expand array to rows | `SELECT arrayJoin([1, 2, 3])` |
| `length(arr)` | Array length | `SELECT length(tags)` |
| `arrayMap(f, arr)` | Transform elements | `SELECT arrayMap(x -> x * 2, [1, 2, 3])` |
| `arrayFilter(f, arr)` | Filter elements | `SELECT arrayFilter(x -> x > 1, [1, 2, 3])` |
| `arrayExists(f, arr)` | Any element matches | `WHERE arrayExists(x -> x = 'admin', roles)` |
| `arrayAll(f, arr)` | All elements match | `WHERE arrayAll(x -> x > 0, scores)` |
| `arraySort(arr)` | Sort array | `SELECT arraySort([3, 1, 2])` → `[1, 2, 3]` |
| `arrayDistinct(arr)` | Unique elements | `SELECT arrayDistinct(tags)` |
| `arrayConcat(a, b)` | Merge arrays | `SELECT arrayConcat([1, 2], [3, 4])` |
| `has(arr, elem)` | Contains element | `WHERE has(tags, 'important')` |
| `indexOf(arr, elem)` | Find element index | `SELECT indexOf(arr, 'target')` |
| `arraySlice(arr, offset, length)` | Sub-array | `SELECT arraySlice(arr, 1, 3)` |
---
## JSON Functions
| Function | Description | Example |
|----------|-------------|---------|
| `JSONExtract(json, key, Type)` | Extract typed value | `SELECT JSONExtract(data, 'age', 'Int32')` |
| `JSONExtractString(json, key)` | Extract as string | `SELECT JSONExtractString(data, 'name')` |
| `JSONExtractInt(json, key)` | Extract as integer | `SELECT JSONExtractInt(data, 'count')` |
| `JSONExtractFloat(json, key)` | Extract as float | `SELECT JSONExtractFloat(data, 'price')` |
| `JSONExtractBool(json, key)` | Extract as boolean | `SELECT JSONExtractBool(data, 'active')` |
| `JSONExtractArrayRaw(json, key)` | Extract array as strings | `SELECT JSONExtractArrayRaw(data, 'tags')` |
| `simpleJSONExtractString(json, key)` | Fast string extract (flat JSON) | `SELECT simpleJSONExtractString(log, 'level')` |
| `JSONHas(json, key)` | Key exists | `WHERE JSONHas(data, 'email')` |
| `JSONLength(json, key)` | Array/object length | `SELECT JSONLength(data, 'items')` |
| `JSONType(json, key)` | Value type | `SELECT JSONType(data, 'value')` |
**Nested access:** Use path syntax: `JSONExtractString(data, 'user', 'address', 'city')`
---
## Window Functions
Window functions compute values across a set of rows related to the current row.
### Syntax
```sql
function() OVER (
[PARTITION BY col1, col2, ...]
[ORDER BY col1 [ASC|DESC], ...]
[ROWS|RANGE BETWEEN ... AND ...]
)
```
### Ranking Functions
| Function | Description |
|----------|-------------|
| `row_number()` | Sequential number (no ties) |
| `rank()` | Rank with gaps for ties |
| `dense_rank()` | Rank without gaps |
| `ntile(n)` | Distribute into n buckets |
```sql
SELECT name, dept, salary,
row_number() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
rank() OVER (ORDER BY salary DESC) AS overall_rank
FROM employees
```
### Value Functions
| Function | Description |
|----------|-------------|
| `lag(col, offset, default)` | Previous row value |
| `lead(col, offset, default)` | Next row value |
| `first_value(col)` | First value in window |
| `last_value(col)` | Last value in window |
```sql
SELECT date, revenue,
lag(revenue, 1, 0) OVER (ORDER BY date) AS prev_revenue,
revenue - lag(revenue, 1, 0) OVER (ORDER BY date) AS daily_change
FROM daily_sales
```
### Aggregate as Window
```sql
SELECT date, revenue,
sum(revenue) OVER (ORDER BY date) AS cumulative,
avg(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily_sales
```
references/table-functions.md
# ClickHouse Table Functions for chdb
> Table functions let you query external data sources directly in SQL.
> Use them with `chdb.query()` or inside a `Session`.
## Table of Contents
- [File Sources](#file-sources)
- [Cloud Storage](#cloud-storage)
- [Databases](#databases)
- [Data Lakes](#data-lakes)
- [Utility Functions](#utility-functions)
---
## File Sources
### file()
Query local files. Format is auto-detected from extension or specified explicitly.
```sql
SELECT * FROM file('data.parquet', Parquet)
SELECT * FROM file('data.csv', CSVWithNames)
SELECT * FROM file('events.jsonl', JSONEachRow)
SELECT * FROM file('logs/*.parquet', Parquet) -- glob pattern
SELECT * FROM file('data/2024-*/events.csv', CSVWithNames) -- nested glob
```
**Parameters:** `file(path [, format [, structure [, compression]]])`
Supported formats: `Parquet`, `CSVWithNames`, `CSV`, `TSVWithNames`, `JSONEachRow`, `JSON`, `Arrow`, `ORC`, `Avro`, `XMLWithNames`.
Supported compression: auto-detected from extension (`.gz`, `.zst`, `.bz2`, `.xz`, `.lz4`).
---
## Cloud Storage
### s3()
```sql
-- Public (no auth)
SELECT * FROM s3('s3://bucket/path.parquet', NOSIGN)
-- With credentials
SELECT * FROM s3('s3://bucket/path.parquet', 'ACCESS_KEY', 'SECRET_KEY', 'Parquet')
-- Glob pattern
SELECT * FROM s3('s3://bucket/logs/2024-*.parquet', 'KEY', 'SECRET', 'Parquet')
```
**Parameters:** `s3(url [, NOSIGN | access_key, secret_key] [, format [, structure [, compression]]])`
### gcs()
```sql
SELECT * FROM gcs('gs://bucket/data.parquet', NOSIGN)
SELECT * FROM gcs('gs://bucket/data.parquet', 'HMAC_KEY', 'HMAC_SECRET', 'Parquet')
```
**Parameters:** Same as `s3()`.
### azureBlobStorage()
```sql
SELECT * FROM azureBlobStorage(
'DefaultEndpointsProtocol=https;AccountName=...;AccountKey=...',
'container', 'path/data.parquet', 'Parquet')
```
**Parameters:** `azureBlobStorage(connection_string, container, path [, format [, structure [, compression]]])`
### hdfs()
```sql
SELECT * FROM hdfs('hdfs://namenode:9000/warehouse/data.parquet', 'Parquet')
SELECT * FROM hdfs('hdfs://namenode:9000/logs/*.parquet', 'Parquet')
```
**Parameters:** `hdfs(uri [, format [, structure [, compression]]])`
---
## Databases
### mysql()
```sql
SELECT * FROM mysql('host:3306', 'database', 'table', 'user', 'password')
-- With WHERE pushdown
SELECT * FROM mysql('db:3306', 'shop', 'orders', 'root', 'pass')
WHERE status = 'shipped' AND amount > 100
```
**Parameters:** `mysql(host:port, database, table, user, password)`
**Note:** Port is part of the host string (e.g., `'db:3306'`), not a separate parameter.
### postgresql()
```sql
SELECT * FROM postgresql('host:5432', 'database', 'table', 'user', 'password')
SELECT * FROM postgresql('pg:5432', 'analytics', 'events', 'analyst', 'pass')
ORDER BY created_at DESC LIMIT 100
```
**Parameters:** `postgresql(host:port, database, table, user, password)`
### remote() / remoteSecure()
Query a remote ClickHouse server:
```sql
SELECT * FROM remote('host:9000', 'database', 'table', 'user', 'password')
SELECT * FROM remoteSecure('host:9440', 'database', 'table', 'user', 'password')
```
**Parameters:** `remote(host:port, database, table [, user [, password]])`
### mongodb()
```sql
SELECT * FROM mongodb('host:27017', 'database', 'collection', 'user', 'password')
```
**Parameters:** `mongodb(host:port, database, collection, user, password)`
### sqlite()
```sql
SELECT * FROM sqlite('/path/to/database.db', 'table_name')
```
**Parameters:** `sqlite(database_path, table)`
---
## Data Lakes
### iceberg()
```sql
SELECT * FROM iceberg('s3://bucket/iceberg/table', 'ACCESS_KEY', 'SECRET_KEY')
SELECT * FROM iceberg('s3://bucket/iceberg/table', NOSIGN)
```
**Parameters:** `iceberg(url [, NOSIGN | access_key, secret_key] [, format])`
### deltaLake()
```sql
SELECT * FROM deltaLake('s3://bucket/delta/table', 'ACCESS_KEY', 'SECRET_KEY')
SELECT * FROM deltaLake('s3://bucket/delta/table', NOSIGN)
```
**Parameters:** `deltaLake(url [, NOSIGN | access_key, secret_key])`
**Note:** Function name is `deltaLake` (camelCase), not `deltalake`.
### hudi()
```sql
SELECT * FROM hudi('s3://bucket/hudi/table', 'ACCESS_KEY', 'SECRET_KEY')
SELECT * FROM hudi('s3://bucket/hudi/table', NOSIGN)
```
**Parameters:** `hudi(url [, NOSIGN | access_key, secret_key])`
---
## Utility Functions
### numbers()
Generate a sequence of numbers (useful for testing and date generation):
```sql
SELECT * FROM numbers(100) -- 0 to 99
SELECT * FROM numbers(10, 100) -- 10 to 109
SELECT toDate('2025-01-01') + number AS date FROM numbers(365) -- date range
```
**Parameters:** `numbers([offset,] count)`
### Python()
Use a Python dict or DataFrame as a SQL table:
```python
import chdb
data = {"name": ["Alice", "Bob"], "score": [95, 87]}
chdb.query("SELECT * FROM Python(data) ORDER BY score DESC")
import pandas as pd
df = pd.DataFrame({"id": [1, 2, 3], "value": [10, 20, 30]})
chdb.query("SELECT * FROM Python(df) WHERE value > 15")
```
**Note:** The Python variable must be in scope when the query executes.
### url()
Query data from an HTTP/HTTPS URL:
```sql
SELECT * FROM url('https://example.com/data.csv', CSVWithNames)
SELECT * FROM url('https://api.example.com/data.json', JSONEachRow)
```
**Parameters:** `url(url, format [, structure])`
scripts/verify_install.py
#!/usr/bin/env python3
"""Verify chdb SQL installation and basic functionality."""
import sys
PASS = "OK"
FAIL = "FAIL"
results = []
def check(name, fn):
try:
fn()
results.append((name, PASS, ""))
print(f" [{PASS}] {name}")
except Exception as e:
results.append((name, FAIL, str(e)))
print(f" [{FAIL}] {name}: {e}")
def check_python_version():
if sys.version_info < (3, 9):
raise RuntimeError(f"Python 3.9+ required, got {sys.version}")
def check_chdb_import():
import chdb
if not hasattr(chdb, "__version__"):
raise RuntimeError("chdb imported but missing __version__")
print(f" chdb version: {chdb.__version__}")
def check_basic_query():
import chdb
result = chdb.query("SELECT 1 + 1 AS answer")
data = result.data()
if "2" not in data:
raise RuntimeError(f"Expected '2' in output, got: {data!r}")
def check_dataframe_output():
import chdb
df = chdb.query("SELECT number FROM numbers(5)", "DataFrame")
if len(df) != 5:
raise RuntimeError(f"Expected 5 rows, got {len(df)}")
if "number" not in df.columns:
raise RuntimeError(f"Expected 'number' column, got {list(df.columns)}")
def check_session():
from chdb import session as chs
sess = chs.Session()
try:
sess.query("CREATE TABLE _verify_test (id UInt64) ENGINE = Memory")
sess.query("INSERT INTO _verify_test VALUES (1), (2), (3)")
result = sess.query("SELECT count() AS cnt FROM _verify_test")
data = result.data()
if "3" not in data:
raise RuntimeError(f"Expected '3' in output, got: {data!r}")
finally:
sess.close()
def check_parametrized():
import chdb
result = chdb.query(
"SELECT {x:UInt64} + {y:UInt64} AS sum",
params={"x": 10, "y": 20})
data = result.data()
if "30" not in data:
raise RuntimeError(f"Expected '30' in output, got: {data!r}")
if __name__ == "__main__":
print("chdb SQL Installation Verification")
print("=" * 40)
check("Python version >= 3.9", check_python_version)
check("import chdb", check_chdb_import)
check("Basic query (SELECT 1+1)", check_basic_query)
check("DataFrame output format", check_dataframe_output)
check("Session create + query", check_session)
check("Parametrized query", check_parametrized)
print()
print("=" * 40)
passed = sum(1 for _, s, _ in results if s == PASS)
total = len(results)
print(f"Results: {passed}/{total} passed")
if passed < total:
print("\nFailed checks:")
for name, status, err in results:
if status == FAIL:
print(f" - {name}: {err}")
sys.exit(1)
else:
print("All checks passed!")
SKILL.md
---
name: chdb-sql
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.
license: Apache-2.0
compatibility: Requires Python 3.9+, macOS or Linux. pip install chdb.
metadata:
author: chdb-io
version: "4.1"
homepage: https://clickhouse.com/docs/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.
```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.