DataStore 快速入门

只需几分钟即可开始使用 DataStore。本指南涵盖安装、从 pandas 迁移以及基本用法。

安装

使用 pip 安装 chDB：

pip install "chdb>=4.0"

对于可选依赖项：

# For pandas DataFrame support
pip install "chdb[pandas]>=4.0"

# For PyArrow support
pip install "chdb[arrow]>=4.0"

# All optional dependencies
pip install "chdb[all]>=4.0"

验证安装

import chdb
print(chdb.__version__)  # Should print 4.x.x or higher

from chdb import datastore as pd
print("DataStore ready!")

从 Pandas 进行一行迁移

开始使用 DataStore 最简单的方式，就是修改导入语句：

# Before (pandas)
import pandas as pd

# After (DataStore)
from chdb import datastore as pd

就是这样！您现有的 pandas 代码现在将使用 DataStore，并从 SQL 优化中受益。

迁移示例

from pathlib import Path
Path("employees.csv").write_text("""\
name,age,city,salary,department,dept_id,status,email
Alice,28,NYC,75000,Engineering,1,active,alice@company.com
Bob,35,LA,85000,Engineering,1,active,bob@company.com
Charlie,52,NYC,95000,Product,2,active,charlie@company.com
Diana,32,SF,70000,Design,3,active,diana@company.com
Eve,23,LA,48000,Product,2,inactive,eve@company.com
""")

# Original pandas code
import pandas as pd

df = pd.read_csv("employees.csv")
result = (df[df['salary'] > 50000]
          .groupby('department')['salary']
          .agg(['mean', 'count'])
          .sort_values('mean', ascending=False))
print(result)

# DataStore version - just change the import!
from chdb import datastore as pd

df = pd.read_csv("employees.csv")
result = (df[df['salary'] > 50000]
          .groupby('department')['salary']
          .agg(['mean', 'count'])
          .sort_values('mean', ascending=False))
print(result)  # Same result, faster execution!

基础用法

创建 DataStore

from chdb import datastore as pd

# From a dictionary
ds = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35],
    'city': ['NYC', 'LA', 'NYC']
})

# From a pandas DataFrame
import pandas
pdf = pandas.DataFrame({'a': [1, 2, 3], 'b': [4, 5, 6]})
ds = pd.DataFrame(pdf)

# From a CSV file
ds = pd.read_csv("data.csv")

# From a Parquet file (recommended for large datasets)
ds = pd.read_parquet("data.parquet")

数据筛选

from chdb import datastore as pd

ds = pd.read_csv("employees.csv")

# Single condition
senior = ds[ds['age'] > 30]

# Multiple conditions (AND)
senior_nyc = ds[(ds['age'] > 30) & (ds['city'] == 'NYC')]

# Multiple conditions (OR)
young_or_senior = ds[(ds['age'] < 25) | (ds['age'] > 50)]

# Using filter method (SQL-style)
result = ds.filter(ds['salary'] > 50000)

选择列

# Pandas style
subset = ds[['name', 'age']]

# SQL style
subset = ds.select('name', 'age')

排序

# Pandas style
sorted_ds = ds.sort_values('salary', ascending=False)

# SQL style
sorted_ds = ds.sort('salary', ascending=False)

分组与聚合

from pathlib import Path
Path("sales.csv").write_text("""\
region,product,category,amount,quantity,price,date,order_id
East,Widget,Electronics,5200,10,120,2024-01-15,1001
West,Gadget,Electronics,800,5,160,2024-02-20,1002
East,Gizmo,Home,6500,3,100,2024-03-10,1003
North,Widget,Electronics,4500,6,150,2024-06-18,1004
West,Gadget,Electronics,2000,8,250,2024-09-14,1005
""")

from chdb import datastore as pd

ds = pd.read_csv("sales.csv")

# Group by single column
by_region = ds.groupby('region')['amount'].sum()

# Group by multiple columns
by_region_product = ds.groupby(['region', 'product']).agg({
    'amount': ['sum', 'mean'],
    'quantity': 'sum'
})

# Multiple aggregations
summary = ds.groupby('category').agg({
    'price': ['min', 'max', 'mean'],
    'quantity': 'sum'
})

连接 DataStore

from pathlib import Path
Path("departments.csv").write_text("""\
dept_id,department_name
1,Engineering
2,Product
3,Design
""")

from chdb import datastore as pd

employees = pd.read_csv("employees.csv")
departments = pd.read_csv("departments.csv")

# Inner join
result = employees.join(departments, on='dept_id', how='inner')

# Left join
result = employees.join(departments, on='dept_id', how='left')

# Using merge (pandas style)
result = pd.merge(employees, departments, on='dept_id')

获取结果

DataStore 使用惰性求值——只有在需要结果时，操作才会执行。

触发执行

# Automatic triggers
print(ds)           # Displaying results
len(ds)             # Getting row count
ds.columns          # Accessing properties
list(ds)            # Converting to list

# Explicit conversion
df = ds.to_df()     # Convert to pandas DataFrame
df = ds.to_pandas() # Same as to_df()

查看自动生成的 SQL

Query

# See what SQL DataStore will execute
query = ds.filter(ds['age'] > 25).groupby('city').agg({'salary': 'mean'})
print(query.to_sql())

Response

SELECT city, AVG(salary) AS mean
FROM file('data.csv', 'CSVWithNames')
WHERE age > 25
GROUP BY city

使用不同数据源

本地文件

from chdb import datastore as pd

# CSV
ds = pd.read_csv("data.csv")

# Parquet (best performance)
ds = pd.read_parquet("data.parquet")

# JSON
ds = pd.read_json("data.json")

云存储

from chdb.datastore import DataStore

# S3 (anonymous)
ds = DataStore.uri("s3://bucket/data.parquet?nosign=true")

# S3 (with credentials)
ds = DataStore.from_s3(
    "s3://bucket/data.parquet",
    access_key_id="KEY",
    secret_access_key="SECRET"
)

# HTTP/HTTPS
ds = DataStore.uri("https://example.com/data.csv")

数据库

from chdb.datastore import DataStore

# MySQL
ds = DataStore.from_mysql(
    host="localhost",
    database="mydb",
    table="users",
    user="root",
    password="pass"
)

# PostgreSQL
ds = DataStore.from_postgresql(
    host="localhost",
    database="mydb",
    table="users",
    user="postgres",
    password="pass"
)

# Using URI
ds = DataStore.uri("mysql://user:pass@localhost:3306/mydb/users")

String 与日期时间操作

String操作

# All pandas .str methods work
ds['name_upper'] = ds['name'].str.upper()
ds['name_len'] = ds['name'].str.len()
ds['has_a'] = ds['name'].str.contains('a')

日期时间操作

# All pandas .dt methods work
ds['year'] = ds['date'].dt.year
ds['month'] = ds['date'].dt.month
ds['day_of_week'] = ds['date'].dt.dayofweek

ClickHouse 扩展

# URL parsing (not available in pandas!)
ds['domain'] = ds['url'].url.domain()

# JSON extraction
ds['user_name'] = ds['json_data'].json.get_string('name')

# IP address operations
ds['is_ipv4'] = ds['ip_addr'].ip.is_ipv4_string()

最佳实践

1. 大文件请使用 Parquet

# CSV - slower, reads entire file
ds = pd.read_csv("large_data.csv")

# Parquet - faster, columnar format, reads only needed columns
ds = pd.read_parquet("large_data.parquet")

2. 尽早进行过滤

# Good - filter first, then aggregate
result = (ds
    .filter(ds['date'] >= '2024-01-01')
    .groupby('category')['amount'].sum()
)

# Less optimal - aggregate first
result = ds.groupby('category')['amount'].sum()

3. 仅选择必要的列

# Good - select specific columns
result = ds.select('name', 'age', 'city').filter(ds['age'] > 25)

# Less optimal - work with all columns
result = ds.filter(ds['age'] > 25)

4. 使用 SQL 进行复杂操作

# For complex queries, use SQL directly
ds = DataStore()
result = ds.sql("""
    SELECT category, 
           SUM(amount) as total,
           COUNT(*) as count,
           AVG(amount) as avg
    FROM file('sales.csv', 'CSVWithNames')
    WHERE date >= '2024-01-01'
    GROUP BY category
    HAVING total > 10000
    ORDER BY total DESC
    LIMIT 10
""")

后续步骤

了解用于创建 DataStore 的所有工厂方法
探索查询构建，了解 SQL 风格的操作
查看 Accessors，了解字符串、日期时间等相关功能
阅读性能指南获取优化建议

入门

语言集成

DataStore

配置

调试

开发者指南

技术参考文档

安装

验证安装

从 Pandas 进行一行迁移

迁移示例

基础用法

创建 DataStore

数据筛选

选择列

排序

分组与聚合

连接 DataStore

获取结果

触发执行

查看自动生成的 SQL

使用不同数据源

本地文件

云存储

数据库

String 与日期时间操作

String操作

日期时间操作

ClickHouse 扩展

最佳实践

1. 大文件请使用 Parquet

2. 尽早进行过滤

3. 仅选择必要的列

4. 使用 SQL 进行复杂操作

后续步骤

​安装

​验证安装

​从 Pandas 进行一行迁移

​迁移示例

​基础用法

​创建 DataStore

​数据筛选

​选择列

​排序

​分组与聚合

​连接 DataStore

​获取结果

​触发执行

​查看自动生成的 SQL

​使用不同数据源

​本地文件

​云存储

​数据库

​String 与日期时间操作

​String操作

​日期时间操作

​ClickHouse 扩展

​最佳实践

​1. 大文件请使用 Parquet

​2. 尽早进行过滤

​3. 仅选择必要的列

​4. 使用 SQL 进行复杂操作

​后续步骤

安装

验证安装

从 Pandas 进行一行迁移

迁移示例

基础用法

创建 DataStore

数据筛选

选择列

排序

分组与聚合

连接 DataStore

获取结果

触发执行

查看自动生成的 SQL

使用不同数据源

本地文件

云存储

数据库

String 与日期时间操作

String操作

日期时间操作

ClickHouse 扩展

最佳实践

1. 大文件请使用 Parquet

2. 尽早进行过滤

3. 仅选择必要的列

4. 使用 SQL 进行复杂操作

后续步骤