Modeling Views
Overview
Views are read-time projections in ClickHouse. A static SELECT defines the view over one or more base tables or other views. Moose wraps ClickHouse VIEW with a simple View class in TypeScript or Python. You provide the view name, the SELECT, and the list of source tables/views so Moose can order DDL correctly during migrations.
When to use a View
Use View when you want a virtual read-time projection and don't need write-time transformation or a separate storage table. For write-time pipelines and backfills, use a Materialized View instead.
Basic Usage
import { View, sql } from "@514labs/moose-lib";import { users } from "./Users";import { events } from "./Events"; export const activeUserEvents = new View("active_user_events", { selectStatement: sql.statement` SELECT ${events.columns.id} AS event_id, ${users.columns.id} AS user_id, ${users.columns.name} AS user_name, ${events.columns.ts} AS ts FROM ${events} JOIN ${users} ON ${events.columns.user_id} = ${users.columns.id} WHERE ${users.columns.active} = 1 `, baseTables: [events, users],});Multi-Database Views
Add the database property to the ViewConfig to create the view in a specific ClickHouse database:
import { View, sql } from "@514labs/moose-lib";import { events } from "./Events"; export const rawEvents = new View("raw_events", { selectStatement: sql.statement`SELECT * FROM ${events}`, baseTables: [events], database: "analytics",});When database is set, Moose creates the view as `database`.`view_name` in ClickHouse. Source table references that also carry a database config are automatically qualified as `database`.`table` in source_tables.
Quick Reference
// new View(name: string, config: ViewConfig)new View("view_name", { selectStatement: sql.statement`SELECT ... FROM ${someTable}`, baseTables: [someTable /* , other tables or views */], database: "optional_db", // optional});