chDB

Warnings

• gotcha chDB is an in-process engine and shares memory with your application. Running complex queries that process large datasets (e.g., aggregating 10GB on an 8GB RAM machine) can lead to out-of-memory crashes for the entire Python process, unlike server-side databases that can spill to disk.
  Fix: Monitor memory usage for complex queries. For extremely large datasets, consider pre-processing or using a full ClickHouse server. Optimize SQL queries to reduce memory footprint where possible.
• gotcha chDB operates in a single process and lacks built-in authentication, multi-tenancy, or fine-grained resource limits per user. This makes it unsuitable for multi-user applications or highly concurrent environments where resource isolation and access control are critical.
  Fix: Design your application with chDB as a single-user, embedded analytical tool. Implement access control and resource management at the application layer if necessary, or opt for a full ClickHouse server for multi-tenant scenarios.
• gotcha When using the DataStore (Pandas-compatible API) with chained operations in chDB v4.x, each intermediate step can materialize a new DataFrame in memory. This can lead to higher memory consumption than anticipated for large datasets, potentially negating some performance benefits.
  Fix: Be mindful of chained DataFrame operations. For large datasets, consider explicitly performing operations that avoid intermediate materialization or breaking down complex chains into optimized SQL queries where possible.
• breaking In version 4.1.0, the `chdb` package was decoupled from `chdb-core`. While this was primarily an architectural change for packaging, users who had deep integrations or relied on specific internal structures related to `chdb-core` might experience breaking changes.
  Fix: Review your code for any direct references to `chdb-core` components. Ensure your environment correctly resolves dependencies after upgrading to 4.1.0 or later.
• gotcha Versions prior to 4.1.0 were known to experience crashes when exiting the Python process, particularly with persistent sessions.
  Fix: Upgrade to chDB version 4.1.0 or newer to benefit from the fix for exit-related crashes.
• gotcha An issue in versions prior to 4.1.4 could lead to a broken module after upgrading, due to a missing `chdb/__init__.py` file.
  Fix: Ensure you are using chDB version 4.1.4 or newer to avoid potential module import issues after package upgrades.

Install

• pip install chdb Install chDB

Imports

• chdb

  import chdb

• Session

  from chdb import session as chs

  Used for stateful sessions to maintain database state across queries.
• dbapi

  import chdb.dbapi as dbapi

  For using chDB with the Python DB-API 2.0 interface.
• DataStore

  from chdb.datastore import DataStore

  While `import chdb.datastore as pd` is often suggested for a Pandas-like API, directly importing `DataStore` or `chdb` for `chdb.query` is the primary usage for the core engine features. The `as pd` pattern aims for a drop-in replacement, which might mask `chdb`'s distinct behaviors.

Quickstart

This quickstart demonstrates how to execute a basic SQL query using `chdb.query` and receive the results directly as a Pandas DataFrame. It also shows how to query an existing Pandas DataFrame using ClickHouse SQL syntax via the `python(df_name)` table function.

import chdb
import pandas as pd

# Run a simple SQL query and get results as a Pandas DataFrame
result_df = chdb.query("SELECT 1 as id, 'Hello chDB!' as message, version() as chdb_version", "DataFrame")
print("Query Result (DataFrame):\n", result_df)

# Query an existing Pandas DataFrame directly
data = {'col1': [1, 2, 3], 'col2': ['A', 'B', 'C']}
mypandas_df = pd.DataFrame(data)
sql_on_df = "SELECT col1, upper(col2) FROM python(mypandas_df) WHERE col1 > 1"
queried_df_from_pandas = chdb.query(sql_on_df, "DataFrame")
print("\nQuery Result from Pandas DataFrame (DataFrame):\n", queried_df_from_pandas)