/chdb-sql

chdb SQL API Reference

Complete signatures for the SQL-oriented chdb APIs.

Table of Contents

• chdb.query()
• Session
• Connection (DB-API 2.0)
• Output Formats
• Parametrized Queries
• Streaming Queries
• Progress Callback
• User-Defined Functions (UDF)
• AI-Assisted SQL

chdb.query()

chdb.query(sql, output_format="CSV", path="", udf_path="", params=None)

Returns: Result object with:

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

from chdb import session as chs

sess = chs.Session()                    # in-memory (no persistence)
sess = chs.Session("./mydb")            # persistent to disk

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)

from chdb import dbapi

conn = dbapi.connect()    # or: dbapi.connect(path="./mydb")

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

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:

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:

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:

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:

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:

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.