# Moose / Olap / Read Data Documentation – Python

## Included Files
1. moose/olap/read-data/read-data.mdx

## Querying Data

Source: moose/olap/read-data/read-data.mdx

Query OLAP tables using SQL with type safety

# Querying Data

Moose provides type-safe SQL querying for your `OlapTable` and `MaterializedView` instances. Use cases include:

- Building APIs to expose your data to client/frontend applications
- Building transformation pipelines inside your database with materialized views

## Querying with MooseClient

Use `MooseClient` to query data from existing tables and materialized views.

### Basic Querying

<Tabs.Tab>
You can use a formatted string with `execute`:

```py filename="BasicQuerying.py"
from moose_lib import MooseClient
from app.UserTable import UserTable
client = MooseClient()

status = "active"
limit = 10
query = """
  SELECT id, name, email
  FROM {table}
  WHERE status = {status}
  LIMIT {limit}
"""
rows = client.query.execute(query, {"table": UserTable, "status": status, "limit": limit})

rows = client.query.execute(query)
```
This allows you to safely interpolate the table and column names while still using your Moose OlapTables and columns.

</Tabs.Tab>
<Tabs.Tab>
If you'd rather just use the raw ClickHouse python driver with server-side parameter binding, you can use `execute_raw`:

```py filename="BasicQuerying.py"
from moose_lib import MooseClient

client = MooseClient()

# Query existing table using execute_raw with explicit ClickHouse types
query = """
  SELECT id, name, email
  FROM users
  WHERE status = {status:String}
  LIMIT {limit:UInt32}
"""

rows = client.query.execute_raw(query, {
    "status": "active",
    "limit": 10
})
```
</Tabs.Tab>

### Querying Materialized Views

<Tabs.Tab>
You can use a formatted string with `execute`:

```py filename="QueryMaterializedView.py"
from moose_lib import MooseClient

client = MooseClient()

min_orders = 10
query = """
  SELECT user_id, total_orders, average_order_value
  FROM user_stats_view
  WHERE total_orders > {min_orders}
  ORDER BY average_order_value DESC
"""
rows = client.query.execute(query, {"min_orders": min_orders})

```
</Tabs.Tab>
<Tabs.Tab>
Use `execute_raw` with parameter binding:

```py filename="QueryMaterializedView.py"
from moose_lib import MooseClient

client = MooseClient()

min_orders = 10

# Query existing materialized view
query = """
  SELECT user_id, total_orders, average_order_value
  FROM user_stats_view
  WHERE total_orders > {min_orders:UInt32}
  ORDER BY average_order_value DESC
"""

rows = client.query.execute_raw(query, {"min_orders": min_orders})
```
</Tabs.Tab>

## Select With Column and Table References

<Tabs.Tab>

```py filename="TypedReferences.py"
from moose_lib import MooseClient
from app.UserTable import UserTable
client = MooseClient()

status = "active"
query = """
  SELECT 
    {column}
  FROM {table}
  WHERE status = {status}
"""
rows = client.query.execute(query, {"column": UserTable.cols.id, "table": UserTable, "status": status})

```
</Tabs.Tab>
<Tabs.Tab>

```python copy
from moose_lib import MooseClient

client = MooseClient()

# Use parameter binding with explicit identifiers
query = """
  SELECT 
    id,
    name,
    email
  FROM {table: Identifier}
  WHERE status = {status:String}
"""

rows = client.query.execute_raw(query, {"table": UserTable.name, "status": "active"})
```
</Tabs.Tab>

## Filtering with WHERE Clauses

<Tabs.Tab>

```py copy
from moose_lib import MooseClient

client = MooseClient()

status = "active"
start_date = "2024-01-01"
search_pattern = "%example%"
min_age = 18
max_age = 65
user_ids = [1, 2, 3, 4, 5]

# Multiple WHERE conditions
filter_query = """
  SELECT id, name
  FROM {table}
  WHERE status = {status}
    AND created_at > {start_date}
    AND email ILIKE {search_pattern}
"""

# Using BETWEEN
range_query = """
  SELECT * FROM {table}
  WHERE age BETWEEN {min_age} AND {max_age}
"""

# Using IN
in_query = """
  SELECT * FROM {table}
  WHERE id IN {user_ids}
"""

# Execute examples
filter_rows = client.query.execute(filter_query, {"table": UserTable, "status": status, "startDate": start_date, "searchPattern": search_pattern})
range_rows = client.query.execute(range_query, {"table": UserTable, "minAge": min_age, "maxAge": max_age})
in_rows = client.query.execute(in_query, {"table": UserTable, "userIds": user_ids})
```
</Tabs.Tab>

<Tabs.Tab>
```py filename="WhereClauses.py"
from moose_lib import MooseClient

client = MooseClient()

# Multiple WHERE conditions
filter_query = """
  SELECT id, name
  FROM users
  WHERE status = {status:String}
    AND created_at > {startDate:DateTime}
    AND email ILIKE {searchPattern:String}
"""

# Using BETWEEN
range_query = """
  SELECT * FROM users
  WHERE age BETWEEN {minAge:UInt32} AND {maxAge:UInt32}
"""

# Using IN with typed arrays
in_query = """
  SELECT * FROM users
  WHERE id IN {userIds:Array(UInt32)}
"""

# Execute examples
filter_rows = client.query.execute_raw(filter_query, {
    "status": "active",
    "startDate": "2024-01-01",
    "searchPattern": "%example%"
})

range_rows = client.query.execute_raw(range_query, {
    "minAge": 18,
    "maxAge": 65
})

in_rows = client.query.execute_raw(in_query, {
    "userIds": [1, 2, 3, 4, 5]
})
```
</Tabs.Tab>

## Dynamic Query Building

Moose provides two distinct approaches for executing queries in Python. Choose the right one for your use case:

- Option 1: Use formatted strings with `execute` 
- Option 2: Use `execute_raw` with parameter binding (lowest level of abstraction)

<Tabs.Tab>
```py filename="execute.py"
from moose_lib import MooseClient
from pydantic import BaseModel, Field, validator
from typing import Optional

client = MooseClient()

# Example: Static query with validated parameters
def get_active_users(status: str, limit: int):
    # Static table/column names, validated parameters
    query = """
        SELECT id, name, email
        FROM {table}
        WHERE status = {status}
        LIMIT {limit}
    """
    return client.query.execute(query, {"table": UserTable, "status": status, "limit": limit})

# Usage with validated input
active_users = get_active_users("active", 10)

class UserQueryParams(BaseModel):
    status: str = Field(..., pattern=r"^(active|inactive|pending)$")
    limit: int = Field(default=10, ge=1, le=1000)

def build_validated_query(params: UserQueryParams):
    # All parameters are validated by Pydantic
    query = """
        SELECT id, name, email
        FROM {table}
        WHERE status = {status}
        LIMIT {limit}
    """

    return client.query.execute(query, {"table": UserTable, "status": params.status, "limit": params.limit})
```
</Tabs.Tab>
<Tabs.Tab>
```py filename="ParameterBinding.py"
from moose_lib import MooseClient

client = MooseClient()

# Example: Dynamic table and column selection with server-side parameter binding
def query_user_data(table_name: str, status_filter: str, limit: int):
    # Dynamic identifiers in query structure, bound parameters for values
    query = """
      SELECT id, name, email
      FROM {table_name:Identifier} 
      WHERE status = {status:String} 
        AND created_at > {startDate:DateTime}
      LIMIT {limit:UInt32}
    """

    return client.query.execute_raw(query, {
        "table_name": table_name,  # Bound parameter
        "status": status_filter,  # Bound parameter
        "startDate": "2024-01-01T00:00:00",  # Bound parameter
        "limit": limit  # Bound parameter
    })

# Usage with different tables
users_data = query_user_data("users", "active", 10)
admins_data = query_user_data("admin_users", "pending", 5)

# Conditional WHERE clauses
def build_conditional_query(client: MooseClient, params: FilterParams):
    conditions: list[str] = []
    parameters: dict = {}

    if params.min_age is not None:
        conditions.append("age >= {minAge:UInt32}")
        parameters["minAge"] = params.min_age

    if params.status:
        conditions.append("status = {status:String}")
        parameters["status"] = params.status

    if params.search_text:
        conditions.append("(name ILIKE {searchPattern:String} OR email ILIKE {searchPattern:String})")
        parameters["searchPattern"] = f"%{params.search_text}%"

    query = "SELECT * FROM users"

    if conditions:
        query += " WHERE " + " AND ".join(conditions)

    query += " ORDER BY created_at DESC"

    return client.query.execute_raw(query, parameters)
```
</Tabs.Tab>

## Building APIs

To build REST APIs that expose your data, see the [Bring Your Own API Framework documentation](/moose/app-api-frameworks) for comprehensive examples and patterns using Express, Koa, Fastify, or FastAPI.

## Common Pitfalls

## Performance Optimization
If your query is slower than expected, there are a few things you can check:

- If using filters, try to filter on a column that is defined in the `orderByFields` of the table
- For common queries, consider [creating a materialized view](/stack/olap/create-materialized-view) to pre-compute the result set

## Further Reading