CollapsingMergeTree is a MergeTree-family engine that collapses pairs of rows during background merges using a required sign column (where 1 is a “state” row and -1 is a “cancel” row). The merge behavior is defined by ClickHouse.
import { Key, OlapTable, ClickHouseEngines, Int8 } from "@514labs/moose-lib"; interface UserActivity { userId: Key<string>; pageViews: number; duration: number; sign: Int8; // Required: 1 = state row, -1 = cancel row} const userActivity = new OlapTable<UserActivity>("user_activity", { engine: ClickHouseEngines.CollapsingMergeTree, sign: "sign", orderByFields: ["userId"],});| Option | Type | Description |
|---|---|---|
orderByFields | string[] | Sorting key used by ClickHouse for grouping rows |
sign | string |
Name of the Int8 sign column (1 = state, -1 = cancel) |
To update an object, write two rows:
sign = -1) that matches the prior state’s sorting keysign = 1) with the new stateBy default, collapsing happens during ClickHouse background merges. To return fully-collapsed results, ClickHouse documents two common approaches:
FINAL at read time (less efficient; typically avoid on large scans)import { sql } from "@514labs/moose-lib"; // Example: compute fully-collapsed metrics via sign-aware aggregationconst collapsed = sql` SELECT userId, sum(pageViews * sign) AS pageViews, sum(duration * sign) AS duration FROM user_activity GROUP BY userId HAVING sum(sign) > 0`;For full details (including the required Sign column, algorithm, and FINAL semantics), see the ClickHouse docs: CollapsingMergeTree table engine.