DataStore Query Building

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/chdb/datastore/query-building.md)

DataStore provides SQL-style query building methods that compile to optimized SQL queries. All operations are lazy until results are needed.

## Query Methods Overview[​](#overview "Direct link to Query Methods Overview")

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Method SQL Equivalent Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `select(*cols)` `SELECT cols` Select columns|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `filter(cond)` `WHERE cond` Filter rows|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `where(cond)` `WHERE cond` Alias for filter|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `sort(*cols)` `ORDER BY cols` Sort rows|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `orderby(*cols)` `ORDER BY cols` Alias for sort|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `limit(n)` `LIMIT n` Limit rows|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `offset(n)` `OFFSET n` Skip rows|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `distinct()` `DISTINCT` Remove duplicates|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `groupby(*cols)` `GROUP BY cols` Group rows|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `having(cond)` `HAVING cond` Filter groups|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | `join(right, ...)` `JOIN` Join DataStores|  |  |  | | --- | --- | --- | | `union(other)` `UNION` Combine results | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

---

## Selection[​](#selection "Direct link to Selection")

### `select`[​](#select "Direct link to select")

Select specific columns from the DataStore.

```
select(*fields: Union[str, Expression]) -> DataStore
```

**Examples:**

```
from chdb.datastore import DataStore
from pathlib import Path
Path("employees.csv").write_text("""\
name,age,city,salary,department,dept_id,status,email,manager_id,bonus
Alice,28,NYC,75000,Engineering,1,active,[email protected],3,5000
Bob,35,LA,85000,Engineering,1,active,[email protected],3,
Charlie,52,NYC,95000,Product,2,active,[email protected],,10000
Diana,32,SF,70000,Design,3,active,[email protected],3,3000
Eve,23,LA,48000,Product,2,inactive,[email protected],2,
""")

ds = DataStore.from_file("employees.csv")

# Select by column names
result = ds.select('name', 'age', 'salary')

# Select all columns
result = ds.select('*')

# Select with expressions
result = ds.select(
    'name',
    (ds['salary'] * 12).as_('annual_salary'),
    ds['age'].as_('employee_age')
)

# Equivalent pandas style
result = ds[['name', 'age', 'salary']]
```

---

## Filtering[​](#filtering "Direct link to Filtering")

### `filter` / `where`[​](#filter "Direct link to filter")

Filter rows based on conditions. Both methods are equivalent.

```
filter(condition) -> DataStore
where(condition) -> DataStore  # alias
```

**Examples:**

```
ds = DataStore.from_file("employees.csv")

# Single condition
result = ds.filter(ds['age'] > 30)
result = ds.where(ds['salary'] >= 50000)

# Multiple conditions (AND)
result = ds.filter((ds['age'] > 30) & (ds['department'] == 'Engineering'))

# Multiple conditions (OR)
result = ds.filter((ds['city'] == 'NYC') | (ds['city'] == 'LA'))

# NOT condition
result = ds.filter(~(ds['status'] == 'inactive'))

# String conditions
result = ds.filter(ds['name'].str.contains('John'))
result = ds.filter(ds['email'].str.endswith('@company.com'))

# NULL checks
result = ds.filter(ds['manager_id'].notnull())
result = ds.filter(ds['bonus'].isnull())

# IN condition
result = ds.filter(ds['department'].isin(['Engineering', 'Product', 'Design']))

# BETWEEN condition
result = ds.filter(ds['salary'].between(50000, 100000))

# Chained filters (AND)
result = (ds
    .filter(ds['age'] > 25)
    .filter(ds['salary'] > 50000)
    .filter(ds['city'] == 'NYC')
)
```

### Pandas-Style Filtering[​](#pandas-filtering "Direct link to Pandas-Style Filtering")

```
# Boolean indexing (equivalent to filter)
result = ds[ds['age'] > 30]
result = ds[(ds['age'] > 30) & (ds['salary'] > 50000)]

# Query method
result = ds.query('age > 30 and salary > 50000')
```

---

## Sorting[​](#sorting "Direct link to Sorting")

### `sort` / `orderby`[​](#sort "Direct link to sort")

Sort rows by one or more columns.

```
sort(*fields, ascending=True) -> DataStore
orderby(*fields, ascending=True) -> DataStore  # alias
```

**Examples:**

```
ds = DataStore.from_file("employees.csv")

# Single column ascending
result = ds.sort('name')

# Single column descending
result = ds.sort('salary', ascending=False)

# Multiple columns
result = ds.sort('department', 'salary')

# Mixed order (use list for ascending parameter)
result = ds.sort('department', 'salary', ascending=[True, False])

# Pandas style
result = ds.sort_values('salary', ascending=False)
result = ds.sort_values(['department', 'salary'], ascending=[True, False])
```

---

## Limiting and Pagination[​](#limiting "Direct link to Limiting and Pagination")

### `limit`[​](#limit "Direct link to limit")

Limit the number of rows returned.

```
limit(n: int) -> DataStore
```

### `offset`[​](#offset "Direct link to offset")

Skip the first n rows.

```
offset(n: int) -> DataStore
```

**Examples:**

```
ds = DataStore.from_file("employees.csv")

# First 10 rows
result = ds.limit(10)

# Skip first 100, take next 50
result = ds.offset(100).limit(50)

# Pandas style
result = ds.head(10)
result = ds.tail(10)
result = ds.iloc[100:150]
```

---

## Distinct[​](#distinct "Direct link to Distinct")

### `distinct`[​](#distinct-method "Direct link to distinct-method")

Remove duplicate rows.

```
distinct(subset=None, keep='first') -> DataStore
```

**Examples:**

```
from pathlib import Path
Path("events.csv").write_text("""\
user_id,event_type,timestamp
1,click,2024-01-15 10:30:00
2,view,2024-01-15 11:00:00
1,purchase,2024-01-15 11:30:00
3,click,2024-01-16 09:00:00
2,click,2024-01-16 10:00:00
""")

ds = DataStore.from_file("events.csv")

# Remove all duplicate rows
result = ds.distinct()

# Remove duplicates based on specific columns
result = ds.distinct(subset=['user_id', 'event_type'])

# Pandas style
result = ds.drop_duplicates()
result = ds.drop_duplicates(subset=['user_id'])
```

---