Alias Columns
ALIAS columns define virtual computed values that are calculated at query time, not stored on disk. They behave like SQL views at the column level — the expression is re-evaluated every time the column is read.
When to use
- Derive values without storing them (zero disk cost)
- Expose computed fields in SELECT queries without materializing data
- Prototype computed columns before committing to MATERIALIZED
Computed at Query Time
ALIAS columns are not physically stored. They are calculated on the fly when queried, so they add no insert overhead or disk usage, but do add query-time CPU cost.
Basic Usage
import { OlapTable, Key, DateTime, ClickHouseAlias, UInt64 } from "@514labs/moose-lib"; interface UserEvents { id: Key<string>; timestamp: DateTime; userId: string; // Virtual date column — computed when queried eventDate: Date & ClickHouseAlias<"toDate(timestamp)">; // Virtual hash — no storage cost userHash: UInt64 & ClickHouseAlias<"cityHash64(userId)">;} export const UserEventsTable = new OlapTable<UserEvents>("UserEvents", { orderByFields: ["id"],});ALIAS vs MATERIALIZED
| ALIAS | MATERIALIZED | |
|---|---|---|
| Stored on disk | No | Yes |
| Computed when | Query time | Insert time |
| Disk cost | None | Same as a regular column |
| Query cost | Expression re-evaluated per query | Zero (pre-computed) |
| Best for | Lightweight derivations, prototyping | Expensive computations, indexed lookups |
Use ALIAS when storage matters more than query speed. Use MATERIALIZED when the expression is expensive and queried often.
Common Patterns
Human-Readable Formatting:
formatReadableSize(size_bytes)— Display4678899as4.46 MiBformatReadableQuantity(count)— Display1234567as1.23 millionformatReadableTimeDelta(elapsed_seconds)— Display3661as1 hour and 1 second
Date/Time Derivations:
toDate(timestamp)— Extract date without storing ittoHour(timestamp)— Derive hour on the flytoStartOfMonth(timestamp)— Monthly bucketing
String Transformations:
lower(email)— Case-normalized viewconcat(first_name, ' ', last_name)— Computed full nameextractURLParameter(url, 'utm_source')— Parse URL parameters on read
ALIAS vs MATERIALIZED
If you find yourself querying an ALIAS column frequently or using it in WHERE/ORDER BY clauses, consider promoting it to a MATERIALIZED column so the value is pre-computed and stored.
Insert Behavior
ALIAS columns are computed by ClickHouse — you should not supply values for them when inserting data.
Moose handles this automatically across all ingest paths (streams, HTTP ingest, and direct OlapTable.insert()).
OlapTable.insert() accepts Insertable<T>[] — a type that omits ALIAS/MATERIALIZED fields and
makes DEFAULT fields optional. You don't need to apply Insertable<T> yourself; the method signature handles it.
import { OlapTable, Key, DateTime, ClickHouseAlias, ClickHouseDefault } from "@514labs/moose-lib"; interface UserEvents { id: Key<string>; timestamp: DateTime; userId: string; eventDate: Date & ClickHouseAlias<"toDate(timestamp)">; createdAt: Date & ClickHouseDefault<"now()">;} const table = new OlapTable<UserEvents>("UserEvents");// eventDate is omitted, createdAt is optional — enforced by the method signatureawait table.insert([{ id: "1", timestamp: new Date(), userId: "u1" }]);WithDefault and Insertable
Insertable<T> detects ClickHouseDefault<E> (branded intersection) but not WithDefault<T, E>, which erases to the base type at the type level. If you use WithDefault, mark the field as optional (field?: Type & WithDefault<...>) yourself.
Query Behavior
By default, ClickHouse excludes ALIAS columns from SELECT * results. When you query through MooseClient.query.execute, Moose sets asterisk_include_alias_columns per-query so that SELECT * includes ALIAS values. If you have complex SQL where extra columns could affect column ordering, name your columns explicitly instead of using *.
Important Notes
Column Names in Expressions
Use the exact field names from your data model. Moose preserves your naming convention (camelCase in TypeScript, snake_case in Python) in ClickHouse columns.
Restrictions:
- Cannot combine ALIAS with DEFAULT or MATERIALIZED (mutually exclusive)
- Cannot be primary keys (ALIAS columns are virtual — they don't exist in storage)
- Cannot INSERT values into ALIAS columns
Schema Changes:
- Add:
ALTER TABLE ADD COLUMN ... ALIAS expr - Modify:
ALTER TABLE MODIFY COLUMN ... ALIAS new_expr - Remove:
ALTER TABLE MODIFY COLUMN ... REMOVE ALIAS
Syncing from Remote
When using moose init --from-remote, ALIAS column definitions are automatically preserved:
moose init my-app --from-remote --language typescript
# Generated models include ClickHouseAlias annotationsRelated
- Materialized Columns — Pre-compute and store values at insert time
- Data Types — All supported column types
- Schema Optimization — Optimize storage
- TTL (Time-to-Live) — Auto-expire data
- ClickHouse Docs — Detailed reference