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";
 
// 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 source_table_name`,
  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",
)

Configuration Options

interface MaterializedViewOptions<T> {
  selectStatement: string; // sql query to compute the materialized view
  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
    engine: ClickHouseEngines = ClickHouseEngines.MergeTree
    order_by_fields: List[str] = []

Working with Aggregations

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

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

Aggregation Example

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

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:

const query = sql`SELECT * FROM ${exampleMaterializedView.targetTable.name}`;

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.