2 · MERGETREE · EVENT TABLE
MergeTree Event Schedule
CREATE TABLE lesson04.events (
ts DateTime64(3, 'UTC'),
event_type LowCardinality(String),
player_id UInt32, match_id UInt64,
mode LowCardinality(String),
region LowCardinality(String),
damage UInt16
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (event_type, toDate(ts), player_id);
ORDER BY is the key part of the schema.
It specifies the order of rows on disk and defines a sparse index.
By default, the PRIMARY KEY is the same as ORDER BY and is not unique.
KEY
PARTITION
TYPES
ORDER BY
PARTITION BY month
Narrow types
(event type, day, player): “purchases per day,” “player damage per day” consume few granules.
For administrative purposes: delete or move an entire month. Not for speed, and not by player_id.
UInt32 instead of Int64, LowCardinality for strings with hundreds of values, time in UTC. Nullable—as appropriate.