chdb-core

import chdb
# Basic query with CSV output (default)
result = chdb.query("SELECT 1, 'hello'")
print(result)
# Pandas DataFrame output
df = chdb.query("SELECT number, number * 2 AS double FROM numbers(10)", "DataFrame")
print(df)
# Parameterized queries
df = chdb.query(
 "SELECT toDate({base_date:String}) + number AS date "
 "FROM numbers({total_days:UInt64}) "
 "LIMIT {items_per_page:UInt64}",
 "DataFrame",
 params={"base_date": "2025-01-01", "total_days": 10, "items_per_page": 5},
)
print(df)

import chdb
res = chdb.query('SELECT * FROM file("data.parquet", Parquet)', "JSON")
print(res)
res = chdb.query('SELECT * FROM file("data.csv", CSV)', "CSV")
print(res)
# Query result statistics
print(f"SQL read {res.rows_read()} rows, {res.bytes_read()} bytes, "
 f"storage read {res.storage_rows_read()} rows, {res.storage_bytes_read()} bytes, "
 f"elapsed {res.elapsed()} seconds")
# Pandas DataFrame output
chdb.query('SELECT * FROM file("data.parquet", Parquet)', "Dataframe")

import chdb
conn = chdb.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE test (id UInt32, name String) ENGINE = Memory")
cur.execute("INSERT INTO test VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')")
cur.execute("SELECT * FROM test ORDER BY id")
print(cur.fetchone()) # (1, 'Alice')
print(cur.fetchmany(2)) # ((2, 'Bob'), (3, 'Charlie'))
print(cur.column_names()) # ['id', 'name']
print(cur.column_types()) # ['UInt32', 'String']
# Use the cursor as an iterator
cur.execute("SELECT number FROM system.numbers LIMIT 3")
for row in cur:
 print(row)
# Always close resources when done
cur.close()
conn.close()

# File-based persistent database
conn = chdb.connect("mydata.db")
conn.query("CREATE TABLE IF NOT EXISTS logs (ts DateTime, msg String) ENGINE = MergeTree ORDER BY ts")
conn.query("INSERT INTO logs VALUES (now(), 'started')")
result = conn.query("SELECT * FROM logs", "Pretty")
print(result)
conn.close()

from chdb import session as chs
sess = chs.Session()
sess.query("CREATE DATABASE IF NOT EXISTS db_xxx ENGINE = Atomic")
sess.query("CREATE TABLE IF NOT EXISTS db_xxx.log_table (x String, y Int) ENGINE = Log")
sess.query("INSERT INTO db_xxx.log_table VALUES ('a', 1), ('b', 3), ('c', 2), ('d', 5)")
sess.query("CREATE VIEW db_xxx.view_xxx AS SELECT * FROM db_xxx.log_table LIMIT 4")
print(sess.query("SELECT * FROM db_xxx.view_xxx", "Pretty"))

from chdb import session as chs
sess = chs.Session()
rows_cnt = 0
with sess.send_query("SELECT * FROM numbers(200000)", "CSV") as stream_result:
 for chunk in stream_result:
 rows_cnt += chunk.rows_read()
print(rows_cnt) # 200000
# Example 2: Manual iteration with fetch()
rows_cnt = 0
stream_result = sess.send_query("SELECT * FROM numbers(200000)", "CSV")
while True:
 chunk = stream_result.fetch()
 if chunk is None:
 break
 rows_cnt += chunk.rows_read()
print(rows_cnt) # 200000

import chdb.dbapi as dbapi
print("chdb driver version: {0}".format(dbapi.get_client_info()))
conn1 = dbapi.connect()
cur1 = conn1.cursor()
cur1.execute('select version()')
print("description: ", cur1.description)
print("data: ", cur1.fetchone())
cur1.close()
conn1.close()

import chdb.dataframe as cdf
import pandas as pd
# Join 2 DataFrames
df1 = pd.DataFrame({'a': [1, 2, 3], 'b': ["one", "two", "three"]})
df2 = pd.DataFrame({'c': [1, 2, 3], 'd': ["1", "2", "3"]})
ret_tbl = cdf.query(sql="select * from __tbl1__ t1 join __tbl2__ t2 on t1.a = t2.c",
 tbl1=df1, tbl2=df2)
print(ret_tbl)
# Query on the DataFrame Table
print(ret_tbl.query('select b, sum(a) from __table__ group by b'))
# Pandas DataFrames are automatically registered as temporary tables in ClickHouse
chdb.query("SELECT * FROM Python(df1) t1 JOIN Python(df2) t2 ON t1.a = t2.c").show()

Query on Pandas DataFrame

import chdb
import pandas as pd
df = pd.DataFrame(
 {
 "a": [1, 2, 3, 4, 5, 6],
 "b": ["tom", "jerry", "auxten", "tom", "jerry", "auxten"],
 }
)
chdb.query("SELECT b, sum(a) FROM Python(df) GROUP BY b ORDER BY b").show()

Query on Arrow Table

import chdb
import pyarrow as pa
arrow_table = pa.table(
 {
 "a": [1, 2, 3, 4, 5, 6],
 "b": ["tom", "jerry", "auxten", "tom", "jerry", "auxten"],
 }
)
chdb.query("SELECT b, sum(a) FROM Python(arrow_table) GROUP BY b ORDER BY b").show()

import chdb
from chdb.session import Session
from chdb.sqltypes import INT64, STRING
sess = Session()
# Using the @chdb.func decorator
@chdb.func([INT64, INT64], INT64)
def add(a, b):
 return a + b
print(sess.query("SELECT add(12, 22)"))
# With type annotations (types inferred automatically)
@chdb.func()
def multiply(a: int, b: int) -> int:
 return a * b
print(sess.query("SELECT multiply(3, 7)"))
# Using chdb.create_function directly
chdb.create_function("strlen", len, arg_types=[STRING], return_type=INT64)
print(sess.query("SELECT strlen('hello')"))
# Remove a registered function
chdb.drop_function("strlen")

import chdb
# Auto-detect: TTY progress in terminal, progress bar in notebook
conn = chdb.connect(":memory:?progress=auto")
conn.query("SELECT sum(number) FROM numbers_mt(1e10) GROUP BY number % 10 SETTINGS max_threads=4")

import chdb
conn = chdb.connect("file::memory:?ai_provider=openai&ai_model=gpt-4o-mini")
conn.query("CREATE TABLE nums (n UInt32) ENGINE = Memory")
conn.query("INSERT INTO nums VALUES (1), (2), (3)")
sql = conn.generate_sql("Select all rows from nums ordered by n desc")
print(sql) # SELECT * FROM nums ORDER BY n DESC
print(conn.ask("List the numbers table", format="Pretty"))

python3 -m chdb "SELECT 1,'abc'" Pretty