Creating Materialized Views

Materialized views help summarize and transform large datasets into easily queryable tables, precomputing results for efficient data retrieval.

Setting up Materialized Views with createAggregation()create_aggregation()

The createAggregation()create_aggregation() function in Moose makes it easy to define a materialized view by specifying a query and a destination table for your aggregated data.

Example

import {
  createAggregation,
  AggregationCreateOptions,
  Blocks,
  ClickHouseEngines,
} from "@514labs/moose-lib";
 
const destinationTable = "UserActivitySummary";
const materializedView = "UserActivitySummaryMV";
 
// Define the table options
const tableCreateOptions = {
  name: destinationTable, 
  columns: {
    activity: "String",
    unique_user_count: "AggregateFunction(uniq, String)",
    activity_count: "AggregateFunction(count, String)",
  },
  orderBy: "activity",
  engine: ClickHouseEngines.AggregatingMergeTree,
} as TableCreateOptions;
 
// Define the query to aggregate the data
const selectQuery = `
  SELECT 
    activity,
    uniqState(userId) as unique_user_count, 
    countState(activity) AS activity_count 
  FROM 
    ParsedActivity_0_0 
  GROUP BY 
    activity
`;
 
// Create the aggregation
export default {
  setup: createAggregation({
    tableCreateOptions: tableCreateOptions,
    materializedViewName: materializedView, 
    select: selectQuery,
  }),
} as Blocks;

from moose_lib import (
    AggregationCreateOptions,
    AggregationDropOptions,
    Blocks,
    ClickHouseEngines,
    TableCreateOptions,
    create_aggregation,
    drop_aggregation,
)
 
destination_table = "DailyActiveUsers"
materialized_view = "DailyActiveUsers_mv"
 
select_sql = """
SELECT 
    toStartOfDay(timestamp) as date,
    uniqState(userId) as dailyActiveUsers
FROM ParsedActivity_0_0
WHERE activity = 'Login' 
GROUP BY toStartOfDay(timestamp)
"""
 
teardown_queries = drop_aggregation(
    AggregationDropOptions(materialized_view, destination_table)
)
 
table_options = TableCreateOptions(
    name=destination_table,
    columns={"date": "Date", "dailyActiveUsers": "AggregateFunction(uniq, String)"},
    engine=ClickHouseEngines.MergeTree,
    order_by="date",
)
 
aggregation_options = AggregationCreateOptions(
    table_create_options=table_options,
    materialized_view_name=materialized_view,
    select=select_sql,
)
 
setup_queries = create_aggregation(aggregation_options)
 
block = Blocks(teardown=teardown_queries, setup=setup_queries)

Example Explanation

• tableCreateOptionstable_create_options: Defines the destination table schema
  • name: Table name for aggregated data
  • columns: Column names and data types
  • orderByorder_by: Column for table ordering
  • engine: ClickHouse engine (e.g., AggregatingMergeTree for efficient aggregations)

• select: SQL query for data aggregation from the source table

Materialized View Creation Process

When you save the file, Moose:

1. Creates a new table using the defined structure in tableCreateOptionstable_create_options
2. Executes the select query to aggregate data
3. Populates the destination table with the results

API Reference

Blocks Object

• setup (list[str])(string[]): SQL statements to set up the aggregation
• teardown (list[str])(string[]): SQL statements to tear down the aggregation

createAggregation()create_aggregation() Function

Sets up a materialized view based on a SQL query.

Parameters

• options (AggregationCreateOptions):
  • tableCreateOptionstable_create_options: Destination table creation options
  • materializedViewNamematerialized_view_name: Materialized view name
  • select: Aggregation SQL query

Returns

string[]list[str]: SQL statements to set up the materialized view and destination table

dropAggregation()drop_aggregation() Function

Removes an existing aggregation.

Parameters

• options (AggregationDropOptions):
  • viewNameview_name: Materialized view name to drop
  • tableNametable_name: Destination table name to drop

Returns

string[]list[str]: SQL statements to drop the specified materialized view and table

ClickHouseEngines

Enum defining available ClickHouse storage engines in Moose.

enum ClickHouseEngines {
  MergeTree,
  ReplacingMergeTree,
  SummingMergeTree,
  AggregatingMergeTree,
  CollapsingMergeTree,
  VersionedCollapsingMergeTree,
  GraphiteMergeTree,
}