Chdb Datastore

chdb DataStore — It's Just Faster Pandas

The Key Insight

# Change this:
import pandas as pd
# To this:
import chdb.datastore as pd
# Everything else stays the same.

DataStore is a lazy, ClickHouse-backed pandas replacement. Your existing pandas code works unchanged — but operations compile to optimized SQL and execute only when results are needed (e.g., print(), len(), iteration).

pip install chdb

Decision Tree: Pick the Right Approach

1. "I have a file/database and want to analyze it with pandas"
   → DataStore.from_file() / from_mysql() / from_s3() etc.
   → See references/connectors.md

2. "I need to join data from different sources"
   → Create DataStores from each source, use .join()
   → See examples/examples.md #3-5

3. "My pandas code is too slow"
   → import chdb.datastore as pd — change one line, keep the rest

4. "I need raw SQL queries"
   → Use the chdb-sql skill instead

Connect to Any Data Source — One Pattern

from datastore import DataStore

# Local file (auto-detects .parquet, .csv, .json, .arrow, .orc, .avro, .tsv, .xml)
ds = DataStore.from_file("sales.parquet")

# Database
ds = DataStore.from_mysql(host="db:3306", database="shop", table="orders", user="root", password="pass")

# Cloud storage
ds = DataStore.from_s3("s3://bucket/data.parquet", nosign=True)

# URI shorthand — auto-detects source type
ds = DataStore.uri("mysql://root:pass@db:3306/shop/orders")

All 16+ sources and URI schemes → connectors.md

After Connecting — Full Pandas API

result = ds[ds["age"] > 25]                                          # filter
result = ds[["name", "city"]]                                        # select columns
result = ds.sort_values("revenue", ascending=False)                  # sort
result = ds.groupby("dept")["salary"].mean()                         # groupby
result = ds.assign(margin=lambda x: x["profit"] / x["revenue"])     # computed column
ds["name"].str.upper()                                               # string accessor
ds["date"].dt.year                                                   # datetime accessor
result = ds1.join(ds2, on="id")                                      # join
result = ds.head(10)                                                 # preview
print(ds.to_sql())                                                   # see generated SQL

209 DataFrame methods supported. Full API → api-reference.md

Cross-Source Join — The Killer Feature

from datastore import DataStore

customers = DataStore.from_mysql(host="db:3306", database="crm", table="customers", user="root", password="pass")
orders = DataStore.from_file("orders.parquet")

result = (orders
    .join(customers, left_on="customer_id", right_on="id")
    .groupby("country")
    .agg({"amount": "sum", "rating": "mean"})
    .sort_values("sum", ascending=False))
print(result)

More join examples → examples.md

Writing Data

source = DataStore.from_mysql(host="db:3306", database="shop", table="orders", user="root", password="pass")
target = DataStore("file", path="summary.parquet", format="Parquet")

target.insert_into("category", "total", "count").select_from(
    source.groupby("category").select("category", "sum(amount) AS total", "count() AS count")
).execute()

Troubleshooting

References

• API Reference — Full DataStore method signatures
• Connectors — All 16+ data source connection methods
• Examples — 10+ runnable examples with expected output
• Verify Install — Environment verification script
• Official Docs

Note: This skill teaches how to use chdb DataStore.

For raw SQL queries, use the chdb-sql skill.

For contributing to chdb source code, see CLAUDE.md in the project root.

DataStore Examples

All examples are self-contained and runnable.

Expected output is shown in comments.

Table of Contents

1. Pandas Replacement: One Import Change 2. Analyze Local Files 3. Cross-Source Join: MySQL + Parquet 4. Cross-Source Join: S3 + PostgreSQL 5. Three-Way Join: File + Database + Cloud 6. Data Lake Formats: Iceberg, Delta, Hudi 7. URI Shorthand Access 8. Cloud Storage Variants (S3/GCS/Azure/HDFS) 9. Cross-Source Write 10. Explore Remote Schema 11. Common Errors & Fixes

---

1. Pandas Replacement: One Import Change

The simplest way to use chdb — change one line, keep everything else:

# Before (standard pandas):
# import pandas as pd

# After (chdb-accelerated):
import chdb.datastore as pd

df = pd.DataStore({"name": ["Alice", "Bob", "Carol", "Dave"],
                   "dept": ["Eng", "Sales", "Eng", "Sales"],
                   "salary": [95000, 72000, 110000, 68000]})

# Same pandas API — everything works
result = (df[df["salary"] > 70000]
    .groupby("dept")
    .agg({"salary": ["mean", "count"]})
    .sort_values("mean", ascending=False))

print(result)
# Expected output:
#     dept  mean  count
# 0    Eng  102500    2
# 1  Sales   72000    1

Why it's faster: Operations compile to ClickHouse SQL and execute as a single optimized query, instead of step-by-step Python evaluation.

---

2. Analyze Local Files

from datastore import DataStore

# Parquet — pandas-style analysis
ds = DataStore.from_file("sales.parquet")
top_products = (ds[ds['revenue'] > 0]
    .groupby('product')
    .agg({'revenue': 'sum', 'quantity': 'sum'})
    .sort_values('revenue', ascending=False)
    .head(10))
print(top_products)

# CSV with filtering
ds = DataStore.from_file("employees.csv")
senior = ds[(ds['years'] > 5) & (ds['dept'] == 'Engineering')]
print(senior[['name', 'title', 'salary']].sort_values('salary', ascending=False))

# Glob pattern — query all matching files at once
ds = DataStore.from_file("logs/2024-*.csv")
errors = ds[ds['level'] == 'ERROR'].groupby('module')['message'].count()
print(errors.sort_values(ascending=False))

# See the SQL behind any query
print(top_products.to_sql())

---

3. Cross-Source Join: MySQL + Parquet

from datastore import DataStore

customers = DataStore.from_mysql(
    host="db:3306", database="crm", table="customers",
    user="reader", password="pass")

orders = DataStore.from_file("orders.parquet")

result = (customers
    .join(orders, left_on="id", right_on="customer_id", how="inner")
    .groupby("country")
    .agg({"amount": ["sum", "mean"], "order_id": "count"})
    .sort_values("sum", ascending=False))

print(result)
# Expected: country-level order summary with total, average, and count

print(result.to_sql())
# Shows the cross-source SQL generated by chdb

---

4. Cross-Source Join: S3 + PostgreSQL

from datastore import DataStore

events = DataStore.from_s3(
    "s3://analytics/events/2024-*.parquet",
    access_key_id="AKIA...", secret_access_key="secret...")

profiles = DataStore.from_postgresql(
    host="pg.example.com:5432", database="users",
    table="profiles", user="analyst", password="pass")

result = (events
    .join(profiles, left_on="user_id", right_on="id")
    .filter(events['event_type'] == 'purchase')
    .groupby(["country", "age_group"])
    .agg({"amount": "sum", "event_id": "count"})
    .sort_values("sum", ascending=False))

print(result)
# Expected: purchase events aggregated by country and age group

---

5. Three-Way Join: File + Database + Cloud

from datastore import DataStore

products = DataStore.from_file("products.csv")
orders = DataStore.from_mysql(
    host="db:3306", database="shop", table="orders",
    user="root", password="pass")
reviews = DataStore.from_s3("s3://feedback/reviews.parquet", nosign=True)

result = (orders
    .join(products, left_on="product_id", right_on="id")
    .join(reviews, left_on="product_id", right_on="product_id")
    .groupby("category")
    .agg({"amount": "sum", "rating": "mean", "review_id": "count"})
    .sort_values("sum", ascending=False))

print(result)
# Expected: category-level summary combining order amounts, review ratings, and counts

---

6. Data Lake Formats: Iceberg, Delta, Hudi

from datastore import DataStore

# Apache Iceberg on S3
ds = DataStore.from_iceberg(
    "s3://warehouse/iceberg/events",
    access_key_id="KEY", secret_access_key="SECRET")
print(ds.head(10))

# Delta Lake
ds = DataStore.from_delta(
    "s3://warehouse/delta/transactions",
    access_key_id="KEY", secret_access_key="SECRET")
summary = (ds.groupby("category")
    .agg({"amount": "sum"})
    .sort_values("sum", ascending=False))
print(summary)

# Hudi
ds = DataStore.from_hudi(
    "s3://warehouse/hudi/logs",
    access_key_id="KEY", secret_access_key="SECRET")
errors = ds[ds['level'] == 'ERROR']
print(errors.head(20))

---

7. URI Shorthand Access

from datastore import DataStore

# One-liner for any source
ds = DataStore.uri("sales.parquet")
ds = DataStore.uri("s3://public-data/dataset.parquet?nosign=true")
ds = DataStore.uri("mysql://root:pass@localhost:3306/shop/orders")
ds = DataStore.uri("postgresql://analyst:pass@pg:5432/analytics/events")
ds = DataStore.uri("clickhouse://ch:9440/analytics/hits?user=reader&password=pass")
ds = DataStore.uri("mongodb://user:pass@mongo:27017/logs.app_events")
ds = DataStore.uri("sqlite:///data/local.db?table=users")
ds = DataStore.uri("deltalake:///data/delta/events")

# After creating from any source, same pandas API
result = (ds[ds['value'] > 100]
    .groupby('category')
    .sum()
    .sort_values('value', ascending=False))
print(result)

---

8. Cloud Storage Variants

from datastore import DataStore

# AWS S3 (private)
ds = DataStore.from_s3("s3://my-bucket/data.parquet",
    access_key_id="AKIA...", secret_access_key="secret...")

# AWS S3 (public)
ds = DataStore.from_s3("s3://public-data/dataset.parquet", nosign=True)

# Google Cloud Storage
ds = DataStore.from_gcs("gs://my-bucket/data.parquet",
    hmac_key="KEY", hmac_secret="SECRET")
ds = DataStore.from_gcs("gs://public-bucket/data.parquet", nosign=True)

# Azure Blob Storage
ds = DataStore.from_azure(
    connection_string="DefaultEndpointsProtocol=https;AccountName=...;AccountKey=...",
    container="data", path="analytics/events.parquet")

# HDFS
ds = DataStore.from_hdfs("hdfs://namenode:9000/warehouse/events/*.parquet")

---

9. Cross-Source Write

from datastore import DataStore

# Read from MySQL, transform, write to Parquet
source = DataStore.from_mysql(
    host="db:3306", database="shop", table="orders",
    user="root", password="pass")
target = DataStore("file", path="output/orders_summary.parquet", format="Parquet")

target.insert_into("category", "total_revenue", "order_count").select_from(
    source
        .groupby("category")
        .select("category", "sum(amount) AS total_revenue", "count() AS order_count")
        .filter(source['amount'] > 0)
).execute()

# Read from S3, filter, write to local file
source = DataStore.from_s3("s3://logs/events.parquet", nosign=True)
target = DataStore("file", path="filtered_events.parquet", format="Parquet")

target.insert_into("user_id", "event_type", "ts").select_from(
    source.select("user_id", "event_type", "ts")
          .filter(source['event_type'] == 'error')
).execute()

---

10. Explore Remote Schema

from datastore import DataStore

# Connect to MySQL and browse the schema
mysql_ds = DataStore.from_mysql(
    host="db:3306", database="ecommerce",
    user="analyst", password="pass")

print(mysql_ds.databases())       # list all databases
print(mysql_ds.tables("ecommerce"))  # list tables in a database

# Quick preview of a specific table
orders = DataStore.from_mysql(
    host="db:3306", database="ecommerce",
    table="orders", user="analyst", password="pass")

print(orders.columns)      # → ['id', 'customer_id', 'amount', ...]
print(orders.dtypes)        # → {'id': 'UInt64', 'amount': 'Float64', ...}
print(orders.describe())    # → statistics for numeric columns
print(orders.head(5))       # → first 5 rows

---

11. Common Errors & Fixes

File not found

from datastore import DataStore

# Error: file not found
ds = DataStore.from_file("nonexistent.parquet")
# → Exception: FILE_NOT_FOUND

# Fix: check the path
import os
print(os.path.exists("nonexistent.parquet"))  # → False
ds = DataStore.from_file("data/sales.parquet")  # use correct path

Database host without port

# Error: connection timeout / refused
ds = DataStore.from_mysql(host="db", database="shop", table="orders",
    user="root", password="pass")
# → Connection refused

# Fix: include port in host string
ds = DataStore.from_mysql(host="db:3306", database="shop", table="orders",
    user="root", password="pass")

Join key type mismatch

# Error: join returns empty or wrong results
users = DataStore({"id": [1, 2, 3], "name": ["Alice", "Bob", "Carol"]})     # id is Int
orders = DataStore({"user_id": ["1", "2", "3"], "amount": [100, 200, 300]}) # user_id is String

result = users.join(orders, left_on="id", right_on="user_id")
print(result)  # → empty or incorrect

# Fix: ensure matching types — use .to_sql() to diagnose
print(result.to_sql())  # reveals the type mismatch in the JOIN condition
# Cast in source data, or use assign() to convert types before joining

Debugging with .to_sql()

from datastore import DataStore

ds = DataStore.from_file("sales.parquet")
result = (ds[ds['revenue'] > 1000]
    .groupby('product')
    .agg({'revenue': 'sum'})
    .sort_values('revenue', ascending=False)
    .head(10))

# See exactly what SQL will execute
print(result.to_sql())
# Output:
# SELECT "product", sum("revenue") AS "revenue"
# FROM file('sales.parquet', Parquet)
# WHERE "revenue" > 1000
# GROUP BY "product"
# ORDER BY "revenue" DESC
# LIMIT 10

chdb DataStore

Agent skill for using chdb's pandas-compatible DataStore API — a drop-in pandas replacement backed by ClickHouse.

Installation

npx skills add clickhouse/agent-skills

What's Included

Trigger Phrases

This skill activates when you:

• "Analyze this file with pandas"
• "Speed up my pandas code"
• "Query this MySQL/PostgreSQL/S3 table as a DataFrame"
• "Join data from different sources"
• "Use DataStore to..."
• "Import datastore as pd"

Related

• chdb-sql — For raw ClickHouse SQL queries, use the chdb-sql skill instead
• clickhouse-best-practices — For ClickHouse schema/query optimization

Documentation

• chdb docs
• chdb GitHub

DataStore API Reference

Complete method signatures for the DataStore class.

DataStore provides a pandas-compatible API backed by ClickHouse.

Table of Contents

• Import & Construction
• Selection & Filtering
• Sorting & Limiting
• GroupBy & Aggregation
• Joins
• Mutation
• String Accessor (.str)
• DateTime Accessor (.dt)
• Inspection & Execution Triggers
• Writing Data
• Configuration

---

Import & Construction

from datastore import DataStore
# or: from chdb.datastore import DataStore
# or: import chdb.datastore as pd  (drop-in replacement)

Constructor

DataStore(source=None, table=None, database=":memory:", connection=None, **kwargs)

Factory Methods

See connectors.md for all factory methods (from_file, from_mysql, from_s3, uri, etc.).

---

Selection & Filtering

result = ds[ds["age"] > 25]
result = ds[(ds["status"] == "active") & (ds["revenue"] > 1000)]
result = ds[["name", "city", "revenue"]]
result = ds.select("name", "revenue * 1.1 AS adjusted_revenue")
result = ds.filter(ds["country"] == "US")
result = ds.where(ds["age"] > 25)  # keeps all rows; non-matching values become NaN

---

Sorting & Limiting

result = ds.sort_values("revenue", ascending=False)
result = ds.sort_values(["country", "city"])
result = ds.head(10)
result = ds.limit(100).offset(50)

---

GroupBy & Aggregation

grouped = ds.groupby(*columns)          # returns LazyGroupBy
grouped = ds.groupby("dept")
grouped = ds.groupby(["region", "product"])

result = ds.groupby("dept")["salary"].mean()
result = ds.groupby("dept").agg({"salary": "mean", "bonus": "sum"})
result = ds.groupby(["region", "product"]).agg(
    total_revenue=("revenue", "sum"),
    avg_quantity=("quantity", "mean"))

---

Joins

.join(other, on=None, how='inner', left_on=None, right_on=None, suffixes=('_x', '_y'))
.merge(other, on=None, how='inner')

result = orders.join(customers, left_on="customer_id", right_on="id")
result = orders.join(customers, on="customer_id", how="left")
result = ds1.merge(ds2, on="key", how="outer")

Cross-source joins work transparently — join a MySQL table with a Parquet file:

mysql_ds = DataStore.from_mysql(host="db:3306", database="crm", table="users", user="root", password="pass")
parquet_ds = DataStore.from_file("orders.parquet")
result = mysql_ds.join(parquet_ds, left_on="id", right_on="user_id")

---

Mutation

result = ds.assign(
    profit=ds["revenue"] - ds["cost"],
    margin=lambda x: x["profit"] / x["revenue"])
result = ds.drop("temp_column")
result = ds.rename(columns={"old_name": "new_name"})
result = ds.fillna(0)
result = ds.dropna(subset=["email", "phone"])
result = ds.distinct(subset=["user_id"], keep="first")

---

String Accessor (.str)

Access via ds['column'].str.*. 56 methods available, including:

ds["name"].str.upper()
ds["email"].str.contains("@gmail")
ds["code"].str.slice(0, 3)

---

DateTime Accessor (.dt)

Access via ds['column'].dt.*. 42+ methods available, including:

ds["order_date"].dt.year
ds["order_date"].dt.month
ds["timestamp"].dt.hour
ds["created_at"].dt.strftime("%Y-%m-%d")

---

Inspection & Execution Triggers

These properties/methods trigger execution of the lazy query:

These methods do not trigger execution:

print(ds.columns)          # → ['name', 'age', 'city']
print(ds.shape)            # → (1000, 3)
print(ds.to_sql())         # → SELECT ... FROM ... WHERE ...
print(ds.describe())       # → statistics table

---

Writing Data

Use the insert_into / select_from pattern:

source = DataStore.from_mysql(host="db:3306", database="shop", table="orders", user="root", password="pass")
target = DataStore("file", path="output.parquet", format="Parquet")

target.insert_into("col1", "col2").select_from(
    source.select("col1", "col2").filter(source['value'] > 100)
).execute()

---

Configuration

from datastore import config

config.use_chdb()           # force chDB/SQL backend
config.use_pandas()         # force pandas backend
config.prefer_chdb()        # prefer chDB when possible, fallback to pandas
config.prefer_pandas()      # prefer pandas when possible, fallback to chDB
config.enable_debug()       # verbose logging (shows generated SQL)
config.enable_profiling()   # performance profiling

DataStore Connectors — All Data Sources

Quick reference for connecting DataStore to any data source.

After connecting, all sources share the same pandas API.

Table of Contents

• Local Files
• Cloud Storage
• Databases
• Data Lakes
• URI Shorthand
• In-Memory Data

---

Local Files

DataStore.from_file(path, format=None, structure=None, compression=None, **kwargs)

Format is auto-detected by extension: .parquet, .csv, .tsv, .json, .jsonl, .arrow, .orc, .avro, .xml.

from datastore import DataStore

ds = DataStore.from_file("sales.parquet")
ds = DataStore.from_file("data.csv")
ds = DataStore.from_file("events.jsonl")
ds = DataStore.from_file("logs/*.csv")              # glob pattern
ds = DataStore.from_file("data/2024-*/events.parquet")  # nested glob
ds = DataStore.from_file("data.csv.gz")             # compressed, auto-detected
ds = DataStore.from_file("data.tsv", format="TabSeparatedWithNames")  # explicit format

Notes:

• Glob patterns (*, **) work for querying multiple files at once
• Compression (.gz, .zst, .bz2, .xz, .lz4) is auto-detected from extension
• Use structure parameter to specify column types: structure="id UInt64, name String"

---

Cloud Storage

S3

DataStore.from_s3(url, access_key_id=None, secret_access_key=None, format=None, nosign=False, **kwargs)

# Public bucket (no auth)
ds = DataStore.from_s3("s3://public-data/dataset.parquet", nosign=True)

# Private bucket
ds = DataStore.from_s3("s3://my-bucket/data.parquet",
    access_key_id="AKIA...", secret_access_key="secret...")

# Glob pattern
ds = DataStore.from_s3("s3://bucket/logs/2024-*.parquet", nosign=True)

GCS (Google Cloud Storage)

DataStore.from_gcs(url, hmac_key=None, hmac_secret=None, format=None, nosign=False, **kwargs)

ds = DataStore.from_gcs("gs://my-bucket/data.parquet", nosign=True)
ds = DataStore.from_gcs("gs://private/data.parquet", hmac_key="KEY", hmac_secret="SECRET")

Azure Blob Storage

DataStore.from_azure(connection_string, container, path="", format=None, **kwargs)

ds = DataStore.from_azure(
    connection_string="DefaultEndpointsProtocol=https;AccountName=...;AccountKey=...",
    container="data", path="analytics/events.parquet")

HDFS

DataStore.from_hdfs(uri, format=None, structure=None, **kwargs)

ds = DataStore.from_hdfs("hdfs://namenode:9000/warehouse/events/*.parquet")

HTTP/HTTPS URL

DataStore.from_url(url, format=None, structure=None, headers=None, **kwargs)

ds = DataStore.from_url("https://example.com/data.csv")

---

Databases

MySQL

DataStore.from_mysql(host, database=None, table=None, user=None, password="", port=None, **kwargs)

ds = DataStore.from_mysql(
    host="db.example.com:3306", database="shop",
    table="orders", user="root", password="pass")

Note: Port must be included in host string (e.g., "db:3306") or passed via port parameter.

PostgreSQL

DataStore.from_postgresql(host, database=None, table=None, user=None, password="", port=None, **kwargs)

ds = DataStore.from_postgresql(
    host="pg:5432", database="analytics",
    table="events", user="user", password="pass")

ClickHouse (Remote)

DataStore.from_clickhouse(host, database=None, table=None, user="default", password="", secure=False, port=None, **kwargs)

ds = DataStore.from_clickhouse(host="ch:9000", database="logs", table="access_log")
ds = DataStore.from_clickhouse(host="ch:9440", database="logs", table="hits",
    user="reader", password="pass", secure=True)

MongoDB

DataStore.from_mongodb(host, database, collection, user, password="", **kwargs)

ds = DataStore.from_mongodb(
    host="mongo:27017", database="app",
    collection="users", user="user", password="pass")

SQLite

DataStore.from_sqlite(database_path, table, **kwargs)

ds = DataStore.from_sqlite("/data/local.db", "users")

Redis

DataStore.from_redis(host, key, structure, password=None, db_index=0, **kwargs)

ds = DataStore.from_redis("localhost:6379", key="mydata",
    structure="id UInt64, name String, value Float64")

---

Data Lakes

Apache Iceberg

DataStore.from_iceberg(url, access_key_id=None, secret_access_key=None, **kwargs)

ds = DataStore.from_iceberg("s3://warehouse/iceberg/events",
    access_key_id="KEY", secret_access_key="SECRET")

Delta Lake

DataStore.from_delta(url, access_key_id=None, secret_access_key=None, **kwargs)

ds = DataStore.from_delta("s3://warehouse/delta/transactions",
    access_key_id="KEY", secret_access_key="SECRET")

Apache Hudi

DataStore.from_hudi(url, access_key_id=None, secret_access_key=None, **kwargs)

ds = DataStore.from_hudi("s3://warehouse/hudi/logs",
    access_key_id="KEY", secret_access_key="SECRET")

---

URI Shorthand

DataStore.uri(uri_string, **kwargs)

Universal one-liner that auto-detects source type from the URI scheme:

from datastore import DataStore

ds = DataStore.uri("s3://public-data/dataset.parquet?nosign=true")
ds = DataStore.uri("mysql://root:pass@localhost:3306/shop/orders")
ds = DataStore.uri("postgresql://analyst:pass@pg:5432/analytics/events")
ds = DataStore.uri("clickhouse://ch:9440/analytics/hits?user=reader&password=pass")
ds = DataStore.uri("mongodb://user:pass@mongo:27017/logs.app_events")
ds = DataStore.uri("sqlite:///data/local.db?table=users")
ds = DataStore.uri("deltalake:///data/delta/events")

---

In-Memory Data

From dict

ds = DataStore({"name": ["Alice", "Bob"], "age": [25, 30]})

From pandas DataFrame

ds = DataStore(df)
ds = DataStore.from_df(df, name="my_data")

Generated sequences

ds = DataStore.from_numbers(100)                   # 0..99
ds = DataStore.from_numbers(10, start=5, step=2)   # 5, 7, 9, ...

Random data (for testing)

ds = DataStore.from_random(
    structure="id UInt64, name String, value Float64",
    random_seed=42, max_string_length=10)