chDB

DataStore provides a high-performance alternative to pandas with the following coverage:

• 209 pandas DataFrame methods implemented
• 56 pandas .str accessor methods
• 42+ pandas .dt accessor methods (plus ClickHouse datetime extras)
• 334 ClickHouse functions mapped to Pandas-like API
• ClickHouse-specific accessors: .arr (37 methods), .json, .url, .ip, .geo

Key features include automatic DataFrame/Series wrapping, performance optimization through caching, and thread-safe, immutable operations.

The chDB API is designed for data science workflows, providing a Pythonic interface that bridges the gap between SQL-based ClickHouse engines and the Python ecosystem (Pandas). Key concepts include:

• DataStore: An abstraction of data (similar to a database table) that encapsulates various ClickHouse table engines. It supports schema inference or explicit schema setting via setSchema or inferSchema.
• Function Chaining: A fluent interface supporting .function().function() syntax. It uses Lazy Evaluation, meaning operations are recorded and optimized into a single query plan, only executing when .execute() is called.
• Zero-copy Pandas Integration: chDB supports zero-copy reading of Pandas DataFrames. The resulting DataFrames are fully compatible with Pandas (not Polars), allowing for seamless conversion and use of existing Pandas-based workflows.

DataStore uses a Mixed Execution Engine that allows arbitrary mixing of SQL-style operations and pandas operations.

Execution Stages:

1. SQL Query Building (Lazy): Methods like .select() and .filter() build a query without executing it.
2. Execution (Triggered by pandas): The first pandas-style operation (e.g., .add_prefix(), .fillna()) triggers the SQL engine to execute the built query and cache the resulting DataFrame.
3. SQL on DataFrame (chDB Magic): Subsequent SQL operations (like .filter() on a new column created by pandas) are executed directly on the cached DataFrame using chDB's Python() table function.

When using @chdb_udf():

• Ensure the function is stateless.
• Import modules inside the function to avoid scope issues.
• If the return type is not a String, specify it using the return_type parameter (e.g., return_type="UInt64").
• Note that all input arguments are passed as strings; convert them inside the function if needed.

from chdb.udf import chdb_udf
from chdb import query

@chdb_udf()
def clean_text(text):
    import re
    return re.sub(r'[\w\s]', '', text.lower())

@chdb_udf(return_type="UInt64")
def calculate_sum(a, b):
    return int(a) + int(b)

result = query("SELECT clean_text('Hello, World!') as cleaned")

If you encounter permission errors or file not found errors:

• Use absolute paths via os.path.abspath().
• Explicitly specify the format (e.g., 'CSV', 'Parquet', 'JSONEachRow') if auto-detection fails.
• You can also provide an explicit schema in the file() function to resolve type issues.

import os
import chdb

# Use absolute path
file_path = "data.csv"
abs_path = os.path.abspath(file_path)
result = chdb.query(f"SELECT * FROM file('{abs_path}', 'CSV')")

# Explicitly specify format and schema
result = chdb.query("""
    SELECT * FROM file('data.csv', 'CSV', 
                      'id UInt32, name String, age UInt8')
""")

# Query remote files via URL
result = chdb.query("""
    SELECT * FROM url('https://example.com/data.csv', 'CSV')
""")

To add a new string method, you must register it in function_definitions.py and, if it requires execution that changes the structure, implement it in ColumnExprStringAccessor.

1. Register the function using the @register_function decorator.
2. Automatic Injection: The method will be automatically available via the .str accessor.
3. Implementation: If the method needs to trigger execution, implement it in ColumnExprStringAccessor using self._execute_series().

@register_function(
    name='my_method',
    clickhouse_name='myClickHouseFunc',
    func_type=FunctionType.SCALAR,
    category=FunctionCategory.STRING,
    doc='Description of what it does.',
)
def _build_my_method(expr, arg1, alias=None):
    from .functions import Function
    from .expressions import Literal
    return Function('myClickHouseFunc', expr, Literal(arg1), alias=alias)

To perform distributed query processing across cluster nodes and enable parallel file reading, wrap a standard table function with a Cluster function using the following pattern:

<function_name>Cluster('cluster_name', <original_function_parameters>)

Note: While this enables parallel reading, writes are routed through the initiator node, which can become a bottleneck.

Install the core chDB package with essential functionality using pip.

Requirements:

• Python 3.9 or higher
• 64-bit architecture
• Supported platforms: macOS and Linux (x86_64 and ARM64)

Note: Windows is not currently supported.

pip install chdb

chDB provides two primary ways to work with Pandas DataFrames:

1. chdb.dataframe (cdf) module:

   • Use cdf.query(sql, tbl1=df1, ...) to join multiple DataFrames using the __tbl1__, __tbl2__ syntax.
   • Use cdf.Table(dataframe=df) to wrap a DataFrame and query it using the __table__ keyword.
   • cdf.Table objects provide metadata via .rows_read(), .bytes_read(), and .elapsed().

2. Python() engine:

   • Pass a DataFrame directly into a SQL string using the Python(df_name) syntax within chdb.query().