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

You can use a formatted string with execute:

BasicQuerying.py

from moose_lib import MooseClientfrom app.UserTable import UserTableclient = MooseClient() status = "active"limit = 10query = """  SELECT id, name, email  FROM {table}  WHERE status = {status}  LIMIT {limit}"""rows = client.query.execute(query, {"table": UserTable, "status": status, "limit": limit})

This allows you to safely interpolate the table and column names while still using your Moose OlapTables and columns.

You can use a formatted string with execute:

QueryMaterializedView.py

from moose_lib import MooseClient client = MooseClient() min_orders = 10query = """  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})

TypedReferences.py

from moose_lib import MooseClientfrom app.UserTable import UserTableclient = MooseClient() status = "active"query = """  SELECT     {column}  FROM {table}  WHERE status = {status}"""rows = client.query.execute(query, {"column": UserTable.cols.id, "table": UserTable, "status": status})

TypedReferences.ts

import { sql } from "@514labs/moose-lib";import { UserTable, OrderTable } from "./tables"; // Reference table columns with type safetyconst cols = UserTable.columns;const query = sql`  SELECT     ${cols.id},    ${cols.name},    ${cols.email}  FROM ${UserTable}  WHERE ${cols.status} = 'active'`; // Multiple table referencesconst joinQuery = sql`  SELECT     ${UserTable.columns.id},    ${UserTable.columns.name},    ${OrderTable.columns.order_value}  FROM ${UserTable}  JOIN ${OrderTable} ON ${UserTable.columns.id} = ${OrderTable.columns.user_id}`;

When you query a materialized view, you reference the MaterializedView.targetTable to get the columns of the target table.

TypedReferences.ts

import { sql } from "@514labs/moose-lib";import { ExampleMaterializedView } from "./materialized-views";  const query = sql`  SELECT     ${ExampleMaterializedView.targetTable.columns.id},    ${ExampleMaterializedView.targetTable.columns.name},    ${ExampleMaterializedView.targetTable.columns.email}  FROM ${ExampleMaterializedView.targetTable}`;

In ClickHouse, when you query a Materialized View that has columns of type AggregateFunction in the result set, ordinarily you would need to run:

SELECT sumMerge(amount) FROM {ExampleMaterializedView}

When querying this with Moose, you can just reference the column name in the sql template literal. The interpolation will be replaced with the correct ClickHouse function:

TypedReferences.ts

import { sql } from "@514labs/moose-lib";import { ExampleMaterializedView } from "./materialized-views"; const query = sql`  SELECT ${ExampleMaterializedView.targetTable.columns.amount}  FROM ${ExampleMaterializedView.targetTable}`; // This will be replaced with:// SELECT sumMerge(amount) FROM {ExampleMaterializedView}

WhereClauses.py

from moose_lib import MooseClient client = MooseClient() status = "active"start_date = "2024-01-01"search_pattern = "%example%"min_age = 18max_age = 65user_ids = [1, 2, 3, 4, 5] # Multiple WHERE conditionsfilter_query = """  SELECT id, name  FROM {table}  WHERE status = {status}    AND created_at > {start_date}    AND email ILIKE {search_pattern}""" # Using BETWEENrange_query = """  SELECT * FROM {table}  WHERE age BETWEEN {min_age} AND {max_age}""" # Using INin_query = """  SELECT * FROM {table}  WHERE id IN {user_ids}""" # Execute examplesfilter_rows = client.query.execute(filter_query, {"table": UserTable, "status": status, "start_date": start_date, "search_pattern": search_pattern})range_rows = client.query.execute(range_query, {"table": UserTable, "min_age": min_age, "max_age": max_age})in_rows = client.query.execute(in_query, {"table": UserTable, "user_ids": user_ids})

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)

execute.py

from moose_lib import MooseClientfrom pydantic import BaseModel, Field, validatorfrom typing import Optional client = MooseClient() # Example: Static query with validated parametersdef 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 inputactive_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})

Use the sql template literal to build safe queries:

SqlTemplateLiterals.ts

import { sql } from "@514labs/moose-lib"; // Safe interpolation with sql template literalconst status = 'active';const limit = 10; const query = sql`  SELECT id, name, email  FROM ${UserTable}  WHERE ${UserTable.columns.status} = ${status}  LIMIT ${limit}`; // Conditional WHERE clausesinterface FilterParams {  minAge?: number;  status?: "active" | "inactive";  searchText?: string;} const buildConditionalQuery = (filters: FilterParams) => {  let conditions = [];    if (filters.minAge !== undefined) {    conditions.push(sql`age >= ${filters.minAge}`);  }    if (filters.status) {    conditions.push(sql`status = ${filters.status}`);  }    if (filters.searchText) {    conditions.push(sql`(name ILIKE ${'%' + filters.searchText + '%'} OR email ILIKE ${'%' + filters.searchText + '%'})`);  }    let query = sql`SELECT * FROM ${UserTable}`;    if (conditions.length > 0) {    query = sql`${query} WHERE ${conditions.join(' AND ')}`;  }    return sql`${query} ORDER BY created_at DESC`;};

Querying Data

Querying with MooseClient

Basic Querying

Querying Materialized Views

Select With Column and Table References

Filtering with WHERE Clauses

Dynamic Query Building

Building APIs

Common Pitfalls

Common Python Issues

Common TypeScript Issues

Common Python Issues

Performance Optimization

Further Reading

Moose APIs

Creating Materialized Views