Materialized Columns

MATERIALIZED columns compute and store values at INSERT time, making queries faster at the cost of disk space and insert overhead.

When to use

• Pre-compute expensive operations: hash functions, JSON parsing, complex calculations
• Index computed values for faster lookups
• Partition or order by derived values (dates from timestamps, hashes from IDs)

Basic Usage

import { OlapTable, Key, DateTime, ClickHouseMaterialized, ClickHouseCodec, UInt64 } from "@514labs/moose-lib"; interface UserEvents {  id: Key<string>;  timestamp: DateTime;  userId: string;  logBlob: Record<string, any> & ClickHouseCodec<"ZSTD(3)">;   // Extract date for partitioning  eventDate: Date & ClickHouseMaterialized<"toDate(timestamp)">;   // Precompute hash for fast lookups  userHash: UInt64 & ClickHouseMaterialized<"cityHash64(userId)">;   // Parse JSON once at insert (expensive)  combinationHash: UInt64[] &    ClickHouseMaterialized<"arrayMap(kv -> cityHash64(kv.1, kv.2), JSONExtractKeysAndValuesRaw(toString(logBlob)))"> &    ClickHouseCodec<"ZSTD(1)">;} export const UserEventsTable = new OlapTable<UserEvents>("UserEvents", {  orderByFields: ["userHash", "eventDate"],  partitionBy: "toYYYYMM(eventDate)"});

Common Patterns

Date/Time Extraction:

• toDate(timestamp) - Extract date for partitioning
• toHour(timestamp) - Extract hour for time-series analysis
• toStartOfMonth(timestamp) - Monthly aggregation key

Hash Functions:

• cityHash64(user_id) - Fast user lookups
• cityHash64(user_id, session_id) - Combined hash for deduplication

JSON Processing:

• JSONExtractString(log_blob, 'level') - Extract specific field
• arrayMap(kv -> cityHash64(...), JSONExtractKeysAndValuesRaw(...)) - Hash all key-value pairs

Important Notes

Restrictions:

• Cannot combine MATERIALIZED with DEFAULT (mutually exclusive)
• Cannot be primary keys
• Can combine with ClickHouseCodec and ClickHouseTTL

Schema Changes:

• Add: ALTER TABLE ADD COLUMN ... MATERIALIZED expr
• Modify: ALTER TABLE MODIFY COLUMN ... MATERIALIZED new_expr (preserves existing values)
• Remove: ALTER TABLE MODIFY COLUMN ... REMOVE MATERIALIZED

Syncing from Remote

When using moose init --from-remote, MATERIALIZED column definitions are automatically preserved:

Related

• Data Types - All supported column types
• Schema Optimization - Optimize storage
• TTL (Time-to-Live) - Auto-expire data
• ClickHouse Docs - Detailed reference