chDB is an in-process SQL OLAP Engine powered by ClickHouse 1 For more details: The birth of chDB
- In-process SQL OLAP Engine, powered by ClickHouse
- No need to install ClickHouse
- Minimized data copy from C++ to Python with python memoryview
- Input&Output support Parquet, CSV, JSON, Arrow, ORC and 60+more formats, samples
- Support Python DB API 2.0, example
Get started with chdb using our Installation and Usage Examples
Currently, chDB supports Python 3.8+ on macOS and Linux (x86_64 and ARM64).
pip install chdb
python3 -m chdb SQL [OutputFormat]
python3 -m chdb "SELECT 1,'abc'" Pretty
The following methods are available to access on-disk and in-memory data formats:
(Parquet, CSV, JSON, Arrow, ORC and 60+)
You can execute SQL and return desired format data.
import chdb
res = chdb.query('select version()', 'Pretty'); print(res)
# See more data type format in tests/format_output.py
res = chdb.query('select * from file("data.parquet", Parquet)', 'JSON'); print(res)
res = chdb.query('select * from file("data.csv", CSV)', 'CSV'); print(res)
print(f"SQL read {res.rows_read()} rows, {res.bytes_read()} bytes, elapsed {res.elapsed()} seconds")
# See more in https://clickhouse.com/docs/en/interfaces/formats
chdb.query('select * from file("data.parquet", Parquet)', 'Dataframe')
(Pandas DataFrame, Parquet file/bytes, Arrow bytes)
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': ["โ ", "โก", "โข"]})
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'))
from chdb import session as chs
## Create DB, Table, View in temp session, auto cleanup when session is deleted.
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_xxx (x String, y Int) ENGINE = Log;")
sess.query("INSERT INTO db_xxx.log_table_xxx VALUES ('a', 1), ('b', 3), ('c', 2), ('d', 5);")
sess.query(
"CREATE VIEW db_xxx.view_xxx AS SELECT * FROM db_xxx.log_table_xxx LIMIT 4;"
)
print("Select from view:\n")
print(sess.query("SELECT * FROM db_xxx.view_xxx", "Pretty"))
see also: test_stateful.py.
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()
from chdb.udf import chdb_udf
from chdb import query
@chdb_udf()
def sum_udf(lhs, rhs):
return int(lhs) + int(rhs)
print(query("select sum_udf(12,22)"))
Some notes on chDB Python UDF(User Defined Function) decorator.
- The function should be stateless. So, only UDFs are supported, not UDAFs(User Defined Aggregation Function).
- Default return type is String. If you want to change the return type, you can pass in the return type as an argument. The return type should be one of the following: https://clickhouse.com/docs/en/sql-reference/data-types
- The function should take in arguments of type String. As the input is TabSeparated, all arguments are strings.
- The function will be called for each line of input. Something like this:
def sum_udf(lhs, rhs): return int(lhs) + int(rhs) for line in sys.stdin: args = line.strip().split('\t') lhs = args[0] rhs = args[1] print(sum_udf(lhs, rhs)) sys.stdout.flush()
- The function should be pure python function. You SHOULD import all python modules used IN THE FUNCTION.
def func_use_json(arg): import json ...
- Python interpertor used is the same as the one used to run the script. Get from
sys.executable
see also: test_udf.py.
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()
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", "debug"
).show()
- You must inherit from chdb.PyReader class and implement the
read
method. - The
read
method should:- return a list of lists, the first demension is the column, the second dimension is the row, the columns order should be the same as the first arg
col_names
ofread
. - return an empty list when there is no more data to read.
- be stateful, the cursor should be updated in the
read
method.
- return a list of lists, the first demension is the column, the second dimension is the row, the columns order should be the same as the first arg
- An optional
get_schema
method can be implemented to return the schema of the table. The prototype isdef get_schema(self) -> List[Tuple[str, str]]:
, the return value is a list of tuples, each tuple contains the column name and the column type. The column type should be one of the following: https://clickhouse.com/docs/en/sql-reference/data-types
import chdb
class myReader(chdb.PyReader):
def __init__(self, data):
self.data = data
self.cursor = 0
super().__init__(data)
def read(self, col_names, count):
print("Python func read", col_names, count, self.cursor)
if self.cursor >= len(self.data["a"]):
return []
block = [self.data[col] for col in col_names]
self.cursor += len(block[0])
return block
reader = myReader(
{
"a": [1, 2, 3, 4, 5, 6],
"b": ["tom", "jerry", "auxten", "tom", "jerry", "auxten"],
}
)
chdb.query(
"SELECT b, sum(a) FROM Python(reader) GROUP BY b ORDER BY b"
).show()
see also: test_query_py.py.
- Column types supported: pandas.Series, pyarrow.array, chdb.PyReader
- Data types supported: Int, UInt, Float, String, Date, DateTime, Decimal
- Python Object type will be converted to String
- Pandas DataFrame performance is all of the best, Arrow Table is better than PyReader
For more examples, see examples and tests.
- Project Documentation and Usage Examples
- Colab Notebooks and other Script Examples
- For chdb specific examples and documentation refer to chDB docs
- For SQL syntax, please refer to ClickHouse SQL Reference
- Demo chDB at ClickHouse v23.7 livehouse! and Slides
Contributions are what make the open source community such an amazing place to be learn, inspire, and create. Any contributions you make are greatly appreciated. There are something you can help:
- Help test and report bugs
- Help improve documentation
- Help improve code quality and performance
We welcome bindings for other languages, please refer to bindings for more details.
Apache 2.0, see LICENSE for more information.
chDB is mainly based on ClickHouse 1 for trade mark and other reasons, I named it chDB.
- Discord: https://discord.gg/D2Daa2fM5K
- Email: [email protected]
- Twitter: @chdb
Footnotes
-
ClickHouseยฎ is a trademark of ClickHouse Inc. All trademarks, service marks, and logos mentioned or depicted are the property of their respective owners. The use of any third-party trademarks, brand names, product names, and company names does not imply endorsement, affiliation, or association with the respective owners. โฉ โฉ2