У нас на сайте пользователи ищут авиабилеты. Нужно складывать события поиска в ClickHouse, чтобы аналитики могли считать популярные направления и конверсию в покупку. Спроектируй таблицу и напиши DDL

Условие

У нас на сайте пользователи ищут авиабилеты. Нужно складывать события поиска в ClickHouse, чтобы аналитики могли считать популярные направления и конверсию в покупку. Спроектируй таблицу и напиши DDL

стрим, 5мин в сутки, дата, направление, раз в сутки, 2 года, json

Идентификатор события, время, сессия, пользователь (может быть аноним — без логина), аэропорт/город вылета и прилёта, искомая дата вылета, дата обратно (может быть полет в одну сторону), число пассажиров, класс салона, тип устройства, нашлись ли рейсы, минимальная цена среди найденных, валюта, промокод (может не быть).

CREATE TABLE targer_table ( sob_id UUID, date_sob DateTime(3), ses_id UUID, user_id Uint64, out_city LowCardinality(String), in_city LowCardinality(String), date_search_out Date, date_search_in Date, count_pass UInt16, class LowCardinality(String), devise LowCardinality(String), poisk, min_price, valute, procode )

Ответ

Для поисковых событий подойдёт MergeTree с месячными партициями, ключом под частые аналитические фильтры и TTL два года. Конверсию связывают с отдельными событиями покупки.

Пример локальной таблицы для современной версии ClickHouse:

CREATE TABLE search_events (
    event_id UUID,
    event_time DateTime64(3, 'UTC'),
    session_id UUID,
    user_id Nullable(UInt64),
    origin LowCardinality(String),
    destination LowCardinality(String),
    departure_date Date,
    return_date Nullable(Date),
    passengers UInt16,
    cabin_class LowCardinality(String),
    device_type LowCardinality(String),
    flights_found Bool,
    min_price Nullable(Decimal(18, 2)),
    currency LowCardinality(String),
    promo_code Nullable(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (origin, destination, event_time, event_id)
TTL toDateTime(event_time) + INTERVAL 2 YEAR DELETE;

Ключ сортировки — гипотеза под анализ направлений; его проверяют на реальных фильтрах. NULL сохраняет отсутствие пользователя, обратного рейса или цены. JSON принимают на границе, например через JSONEachRow. Поисков недостаточно для конверсии: нужны покупки и правило атрибуции по search/event ID или сессии. Дубли событий устраняют отдельно; MergeTree не делает event_id уникальным. Объём из условия записан неоднозначно и требует уточнения для расчёта мощности.