Chdb Datastore

# DataStore Examples

> All examples are self-contained and runnable.
> Expected output is shown in comments.

## Table of Contents

1. [Pandas Replacement: One Import Change](#1-pandas-replacement-one-import-change)
2. [Analyze Local Files](#2-analyze-local-files)
3. [Cross-Source Join: MySQL + Parquet](#3-cross-source-join-mysql--parquet)
4. [Cross-Source Join: S3 + PostgreSQL](#4-cross-source-join-s3--postgresql)
5. [Three-Way Join: File + Database + Cloud](#5-three-way-join-file--database--cloud)
6. [Data Lake Formats: Iceberg, Delta, Hudi](#6-data-lake-formats-iceberg-delta-hudi)
7. [URI Shorthand Access](#7-uri-shorthand-access)
8. [Cloud Storage Variants (S3/GCS/Azure/HDFS)](#8-cloud-storage-variants)
9. [Cross-Source Write](#9-cross-source-write)
10. [Explore Remote Schema](#10-explore-remote-schema)
11. [Common Errors & Fixes](#11-common-errors--fixes)

---

## 1. Pandas Replacement: One Import Change

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

```python
# 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

```python
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

```python
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

```python
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

```python
from datastore import DataStore

products