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", 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 `, [events, users],);Multi-Database Views
Pass a database argument 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", sql.statement`SELECT * FROM ${events}`, [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, selectStatement, baseTables, config?, metadata?)new View( "view_name", sql.statement`SELECT ... FROM ${someTable}`, [someTable, /* other tables or views */], { database: "optional_db" }, // optional ViewConfig);