Querying Data

Viewing:

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

BasicQuerying.ts

import { MooseClient, sql } from "@514labs/moose-lib";
import { UserTable } from "./tables/UserTable";
 
const client = new MooseClient();
 
// Query existing table
const query = sql`
  SELECT id, name, email
  FROM ${UserTable}
  WHERE status = 'active'
  LIMIT 10
`;
 
const result = await client.query.execute(query);
const data = await result.json();

You can use a formatted string with execute:

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.

Querying Materialized Views

QueryMaterializedView.ts

import { MooseClient, sql } from "@514labs/moose-lib";
 
const client = new MooseClient();
 
// Query existing materialized view
const query = sql`
  SELECT user_id, total_orders, average_order_value
  FROM user_stats_view
  WHERE total_orders > 10
  ORDER BY average_order_value DESC
`;
 
const result = await client.query.execute(query);

You can use a formatted string with execute:

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})

Select With Column and Table References

TypedReferences.ts

import { sql } from "@514labs/moose-lib";
import { UserTable, OrderTable } from "./tables";
 
// Reference table columns with type safety
const cols = UserTable.columns;
const query = sql`
  SELECT 
    ${cols.id},
    ${cols.name},
    ${cols.email}
  FROM ${UserTable}
  WHERE ${cols.status} = 'active'
`;
 
// Multiple table references
const 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}

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})

Filtering with WHERE Clauses

WhereClauses.ts

import { sql } from "@514labs/moose-lib";
 
// Multiple WHERE conditions
const filterQuery = sql`
  SELECT ${UserTable.columns.id}, ${UserTable.columns.name}
  FROM ${UserTable}
  WHERE ${UserTable.columns.status} = 'active'
    AND ${UserTable.columns.created_at} > '2024-01-01'
    AND ${UserTable.columns.email} ILIKE ${'%' + searchTerm + '%'}
`;
 
// Using IN clauses
const inQuery = sql`
  SELECT * FROM ${UserTable}
  WHERE ${UserTable.columns.id} IN (${userIds})
`;
 
// Using BETWEEN
const rangeQuery = sql`
  SELECT * FROM ${UserTable}
  WHERE ${UserTable.columns.age} BETWEEN ${minAge} AND ${maxAge}
`;

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})

Dynamic Query Building

Use the sql template literal to build safe queries:

SqlTemplateLiterals.ts

import { sql } from "@514labs/moose-lib";
 
// Safe interpolation with sql template literal
const status = 'active';
const limit = 10;
 
const query = sql`
  SELECT id, name, email
  FROM ${UserTable}
  WHERE ${UserTable.columns.status} = ${status}
  LIMIT ${limit}
`;
 
// Conditional WHERE clauses
interface 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`;
};

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 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})

Building APIs

To build REST APIs that expose your data, see the Analytics APIs documentation for comprehensive examples and patterns.

Common Pitfalls

Common TypeScript Issues

Column name typos: Use UserTable.columns.columnName for autocomplete
Type mismatches: Ensure your schema types match ClickHouse types
Missing imports: Import your table definitions before using them
Template literal syntax: Use backticks sql not regular strings
Forgetting await: Always await client.query.execute()

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 to pre-compute the result set

Moose APIs

Build REST APIs for your data with built-in Moose API module

Creating Materialized Views

Define data transformations

Inserting Data

Moose Streaming

Querying Data

Viewing:

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

Performance Optimization

Further Reading

Moose APIs

Creating Materialized Views