Modeling Materialized Views

import { MaterializedView, OlapTable, ClickHouseEngines, sql } from "@514labs/moose-lib";import { sourceTable } from "path/to/SourceTable"; // or a view // Define the schema of the transformed rows-- this is static and it must match the results of your SELECT. It also represents the schema of your entire destination table.interface TargetSchema {  id: string;  average_rating: number;  num_reviews: number;}export const mv = new MaterializedView<TargetSchema>({  // The transformation to run on the source table  selectStatement: sql`  SELECT    ${sourceTable.columns.id},    avg(${sourceTable.columns.rating}) AS average_rating,     count(*) AS num_reviews     FROM ${sourceTable}    GROUP BY ${sourceTable.columns.id}  `,  // Reference to the source table(s) that the SELECT reads from  selectTables: [sourceTable],    // Creates a new OlapTable named "target_table" where the transformed rows are written to.  targetTable: {    name: "target_table",    orderByFields: ["id"],  },  // The name of the materialized view in ClickHouse  materializedViewName: "mv_to_target_table", });

import type { Sql } from "@514labs/moose-lib"; interface MaterializedViewConfig<T> {  // Static SELECT that computes the destination rows  selectStatement: string | Sql;   // Tables/views the query reads from  selectTables: (OlapTable<any> | View)[];   // Name of the ClickHouse MATERIALIZED VIEW object  materializedViewName: string;   // Destination table where materialized rows are stored  targetTable?:    | OlapTable<T>    | {        name: string;        engine?: ClickHouseEngines;        orderByFields?: (keyof T & string)[];      };   /** @deprecated prefer targetTable */  tableName?: string;  /** @deprecated prefer targetTable */  engine?: ClickHouseEngines;  /** @deprecated prefer targetTable */  orderByFields?: (keyof T & string)[];}

import { MaterializedView, sql } from "@514labs/moose-lib"; interface Dest { id: string; value: number; created_at: string } new MaterializedView<Dest>({  selectStatement: sql`    SELECT id, toInt32(value) AS value, created_at    FROM ${sourceTable}    WHERE active = 1  `,  selectTables: [sourceTable],  targetTable: { name: "proj_table" },  materializedViewName: "mv_to_proj_table",});

interface DailyCounts { day: string; user_id: string; events: number } // Create the destination table explicitlyconst daily = new OlapTable<DailyCounts>("daily_counts", {  engine: ClickHouseEngines.SummingMergeTree,  orderByFields: ["day", "user_id"],});  // MV 1 - write to the daily_counts tableconst webStmt = sql`SELECT toDate(ts) AS day, user_id, 1 AS events FROM ${webEvents}`;const mv1 = new MaterializedView<DailyCounts>({  selectStatement: webStmt,  selectTables: [webEvents],  targetTable: daily,  materializedViewName: "mv_web_to_daily_counts",}); // MV 2 - write to the daily_counts tableconst mobileStmt = sql`SELECT toDate(ts) AS day, user_id, 1 AS events FROM ${mobileEvents}`;const mv2 = new MaterializedView<DailyCounts>({  selectStatement: mobileStmt,  selectTables: [mobileEvents],  targetTable: daily,  materializedViewName: "mv_mobile_to_daily_counts",});

import { MaterializedView, sql } from "@514labs/moose-lib"; interface Dest { id: string; name: string; day: string } const transformation = sql`  SELECT    ${users.columns.id}        AS id,    ${users.columns.name}      AS name,    toDate(${events.columns.ts}) AS day  FROM ${events}  JOIN ${users} ON ${events.columns.user_id} = ${users.columns.id}  WHERE ${events.columns.active} = 1`; new MaterializedView<Dest>({  selectStatement: transformation,  selectTables: [events, users],  targetTable: { name: "user_activity_by_day" },  materializedViewName: "mv_user_activity_by_day",});

import { MaterializedView, ClickHouseEngines, Aggregated, sql } from "@514labs/moose-lib";  interface MetricsById {  id: string;  /**   * Result of avgState(events.rating)   *   - avgState(number) returns number, so model the type as number   *   - Aggregated arg type is [number] because the column (events.rating) is a number   *   - Aggregated function name is "avg"   */  avg_rating: number & Aggregated<"avg", [number]>;  /**   * Result of uniqExactState(events.user_id)  *  - uniqExact returns an integer; use number & ClickHouseInt<"uint64"> for precision  *  - Aggregated arg type is [string] because the column (events.user_id) is a string  *  - Aggregated function name is "uniqExact"  */  daily_uniques: number & ClickHouseInt<"uint64"> & Aggregated<"uniqExact", [string]>;} //  All Aggregate Functions in this query have a [functionName][State]() suffix.const stmt = sql`  SELECT    ${events.columns.id} AS id,    avgState(${events.columns.rating})      AS avg_rating,    uniqExactState(${events.columns.user_id}) AS daily_uniques  FROM ${events}  GROUP BY ${events.columns.id}`;  new MaterializedView<MetricsById>({  selectStatement: stmt,  selectTables: [events],  targetTable: {    name: "metrics_by_id",    engine: ClickHouseEngines.AggregatingMergeTree,    orderByFields: ["id"],  },  materializedViewName: "mv_metrics_by_id",});

import { MaterializedView, ClickHouseEngines, Aggregated, sql } from "@514labs/moose-lib"; interface MetricsById {  id: string;  avg_rating: number & Aggregated<"avg", [number]>;  total_reviews: number & Aggregated<"sum", [number]>;} const aggStmt = sql`  SELECT    ${reviews.columns.id} AS id,    avgState(${reviews.columns.rating}) AS avg_rating,    countState(${reviews.columns.id})   AS total_reviews  FROM ${reviews}  GROUP BY ${reviews.columns.id}`; const mv = new MaterializedView<MetricsById>({  selectStatement: aggStmt,  selectTables: [reviews],  targetTable: {    name: "metrics_by_id",    engine: ClickHouseEngines.AggregatingMergeTree,    orderByFields: ["id"],  },  materializedViewName: "mv_metrics_by_id",});

import { sql } from "@514labs/moose-lib"; // Auto-finalized via Aggregated + sqlconst cols = mv.targetTable.columns; // mv from earlier Agg exampleconst autoFinalized = sql`  SELECT ${cols.avg_rating}, ${cols.total_reviews}  FROM ${mv.targetTable}  WHERE ${cols.id} = '123'`; // Manual finalization (explicit ...Merge)const manual = sql`  SELECT    avgMerge(avg_rating)   AS avg_rating,    countMerge(total_reviews) AS total_reviews  FROM metrics_by_id  WHERE id = '123'`;