In-Database Transformations

Overview

Materialized views allow you to precompute and store the results of complex queries. This can be useful for improving query performance, reducing the load on your database, and providing a more efficient way to access data.

Basic Example

import { MaterializedView, sql } from "@514labs/moose-lib";
import { sourceTable } from "path/to/SourceTable";
 
// 1. Define the schema of the materialized view result set
interface TargetSchema {
  id: string;
  average_rating: number;
  num_reviews: number;
}
 
// 2. Write a SELECT query to compute the result set
const exampleMaterializedView = new MaterializedView<TargetSchema>({
  selectStatement: sql` SELECT * FROM ${sourceTable}`,
  selectTables: [sourceTable],
  tableName: "my_table",
  materializedViewName: "example_materialized_view",
});

from moose_lib import MaterializedView, sql
 
# 1. Define the schema of the materialized view result set
class TargetSchema(BaseModel):
    id: str
    average_rating: float
    num_reviews: int
 
# 2. Write a SELECT query to compute the result set
example_materialized_view = MaterializedView[TargetSchema](
    select_statement=f"SELECT * FROM source_table_name",
    table_name="my_table",
    materialized_view_name="example_materialized_view",
    select_tables=[source_table_name],
)

Configuration Options

interface MaterializedViewOptions<T> {
  selectStatement: sql; // sql query to compute the materialized view
  selectTables: (OlapTable<any> | View)[]; // tables the query reads from
  tableName: string; // name of the table where materialized view result set will be stored
  materializedViewName: string; // name of the materialized view in the database
 
  engine?: ClickHouseEngines; // default is MergeTree
  orderByFields?: (keyof T & string)[]; // default is the primary key of the source table
}

from moose_lib import MaterializedView, sql
from source_table import source_table
 
class MaterializedViewOptions(BaseModel):
    select_statement: str
    table_name: str
    materialized_view_name: str
    select_tables: List[OlapTable | View]
    engine: ClickHouseEngines = ClickHouseEngines.MergeTree
    order_by_fields: List[str] = []

Working with Aggregations

Materialized views support aggregations using the Aggregated type in your schema definition and the AggregatingMergeTree engine. This is crucial for efficiently maintaining pre-aggregated data in real-time systems.

Why Use ClickHouse Aggregate Functions

ClickHouse’s aggregate functions offer several advantages when used in materialized views:

1. Incremental computation: ClickHouse can update aggregations incrementally when new data arrives, rather than recomputing the entire result set.
2. Memory efficiency: Aggregate states are stored in a compact format optimized for the specific function.
3. High performance: Native implementation of complex statistical functions that scale with your data volume.
4. Type safety: The Aggregated type in Moose ensures correct mapping between your code and ClickHouse aggregation types.

When using the AggregatingMergeTree engine, ClickHouse maintains the state of your aggregations between updates, making it much more efficient than re-aggregating from raw data each time.

Common Aggregate Functions

Aggregated<AggregationFunction extends string, ArgTypes extends any[]>

from moose_lib import Annotated, AggregateFunction
 
Annotated[U, AggregateFunction(...)]

Aggregation Example

import { MaterializedView, ClickHouseEngines, Aggregated, sql } from "@514labs/moose-lib";
import { sourceTable } from "./SourceTable";
 
interface TargetTableSchema {
  id: string;
  average_rating: number & Aggregated<"avg", [number]>;
  num_reviews: number & Aggregated<"sum", [number]>;
}
 
const query = sql`
  SELECT 
    id, 
    avgState(${sourceTable.columns.rating}) as average_rating, 
    countState(${sourceTable.columns.id}) as num_reviews 
  FROM ${sourceTable}
  GROUP BY ${sourceTable.columns.id}
  `;
 
const exampleMaterializedView = new MaterializedView<TargetTableSchema>({
  selectStatement: query,
  selectTables: [sourceTable],
  tableName: "my_table",
  materializedViewName: "example_materialized_view",
  engine: ClickHouseEngines.AggregatingMergeTree, 
  orderByFields: ["id"],
});

from moose_lib import MaterializedView, ClickHouseEngines, AggregateFunction, MaterializedViewOptions
from app.source_table import source_table
 
class TargetTableSchema(BaseModel):
    id: str
    average_rating: Annotated[float, AggregateFunction(agg_func="avg", param_types=[float])]
    num_reviews: Annotated[int, AggregateFunction(agg_func="sum", param_types=[int])]
 
query = f"""
  SELECT 
    id, 
    avgState(rating) as average_rating, 
    countState(id) as num_reviews 
  FROM {source_table.name}
  GROUP BY id
"""
 
example_materialized_view = MaterializedView[TargetTableSchema](MaterializedViewOptions(
    select_statement=query,
    table_name="my_table",
    materialized_view_name="example_materialized_view",
    select_tables=[source_table],
    engine=ClickHouseEngines.AggregatingMergeTree,
    order_by_fields=["id"],
))

avgState(rating) as average_rating, 
countState(id) as num_reviews 

SELECT avgMerge(average_rating), countMerge(num_reviews) FROM my_table

Best Practices for Aggregations

When working with aggregated materialized views:

1. Choose the right engine: Always use AggregatingMergeTree for views with aggregate functions
2. Match schema to query: Ensure your TargetSchema fields match the aggregate functions in your query
3. Consider data volume: Aggregations are most valuable for large datasets where full scans are expensive
4. Group wisely: Be thoughtful about your GROUP BY clauses as they affect both storage and query patterns
5. Combine with filtering: Pre-filter data in your select statement to improve performance

When these practices are followed, materialized views with aggregations can provide dramatic performance improvements for analytical queries while maintaining near real-time data freshness.

Querying Materialized Views

You can query materialized views just like any other table in Moose. When instantiating a MaterializedView, Moose will create an OlapTable for the materialized view result set. You can access the OlapTable from the MaterializedView object:

import { sql } from "@514labs/moose-lib";
 
// Query the materialized view
const query = sql`SELECT * FROM ${exampleMaterializedView.targetTable}`;
 
// Retrieve the materialized view result set as an OlapTable
const table: OlapTable<TargetTableSchema> = exampleMaterializedView.targetTable;

from moose_lib import OlapTable
 
## Querying the materialized view
query = f"SELECT * FROM {example_materialized_view.target_table}"
 
## Or
table: OlapTable[TargetTableSchema] = example_materialized_view.target_table

How It Works

When you create a materialized view in Moose:

1. A new table is automatically created in your database using the provided schema
2. The selectStatement is used to compute the initial results
3. The results are stored in the new table
4. In ClickHouse, materialized views function as triggers on the source table
5. Whenever new data is inserted into the source table, the materialized view automatically updates
6. The update uses the selectStatementselect_statement to compute the latest results from the source data

This ensures your precomputed views stay in sync with your underlying data without manual intervention.