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, UInt64 } from "@514labs/moose-lib";
import typia from "typia";
 
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)"
});

from typing import Annotated, Any
from datetime import datetime, date
from moose_lib import OlapTable, OlapConfig, Key, ClickHouseMaterialized, ClickHouseCodec, UInt64
from pydantic import BaseModel
 
class UserEvents(BaseModel):
    id: Key[str]
    timestamp: datetime
    user_id: str
    log_blob: Annotated[Any, ClickHouseCodec("ZSTD(3)")]
 
    # Extract date for partitioning
    event_date: Annotated[date, ClickHouseMaterialized("toDate(timestamp)")]
 
    # Precompute hash for fast lookups
    user_hash: Annotated[UInt64, ClickHouseMaterialized("cityHash64(user_id)")]
 
    # Parse JSON once at insert (expensive)
    combination_hash: Annotated[
        list[UInt64],
        ClickHouseMaterialized("arrayMap(kv -> cityHash64(kv.1, kv.2), JSONExtractKeysAndValuesRaw(toString(log_blob)))"),
        ClickHouseCodec("ZSTD(1)")
    ]
 
user_events_table = OlapTable[UserEvents](
    "UserEvents",
    OlapConfig(
        order_by_fields=["user_hash", "event_date"],
        partition_by="toYYYYMM(event_date)"
    )
)

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:

moose init my-app --from-remote --language typescript
# Generated models include ClickHouseMaterialized annotations

Related

• Supported Types - All column types
• Compression Codecs - Optimize storage
• TTL (Time-to-Live) - Auto-expire data
• ClickHouse Docs - Detailed reference