CREATE TABLE в ClickHouse: MergeTree, ORDER BY и база данных

CREATE TABLE в ClickHouse похож на SQL из других баз данных, но у него есть важные особенности: нужно выбрать движок таблицы, правильно задать ORDER BY и не ждать от primary key поведения как в PostgreSQL. В этом уроке создадим базу, таблицу событий и разберем, почему схема влияет на скорость запросов

Создаем базу данных

CREATE DATABASE IF NOT EXISTS demo;

Если не указывать имя базы в запросе создания таблицы, ClickHouse создаст таблицу в текущей базе. Для учебных примеров лучше явно писать demo.events, чтобы не гадать, где появилась таблица

Минимальный CREATE TABLE

CREATE TABLE demo.events
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    amount Decimal(12, 2)
)
ENGINE = MergeTree
ORDER BY (event_date, event_type, user_id);

В этой схеме есть все, что нужно для первой аналитической таблицы: дата события, точное время, пользователь, тип события и сумма. Движок MergeTree подходит для большинства обычных таблиц ClickHouse. Он хранит данные частями, сортирует их по ключу и выполняет фоновые слияния

Что делает ENGINE

В ClickHouse движок таблицы определяет, как данные хранятся, как читаются, поддерживают ли репликацию, как работают слияния и какие возможности доступны. Для новичка главный ориентир простой: если это обычная аналитическая таблица на одном сервере, начинайте с MergeTree

Позже вы встретите ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, Distributed, Kafka, S3. Но не стоит начинать с них, пока не понятен базовый MergeTree

ORDER BY — главный выбор в схеме

ORDER BY в ClickHouse не про сортировку результата на экране. Он задает физический порядок данных внутри частей таблицы. От него зависит, сможет ли ClickHouse быстро пропускать ненужные диапазоны

Если большинство запросов фильтрует данные по дате и типу события, порядок (event_date, event_type, user_id) логичен. Если главные запросы всегда начинаются с пользователя, можно рассмотреть (user_id, event_date). Универсального ключа нет: он выбирается под реальные запросы

PRIMARY KEY не гарантирует уникальность

Это одна из самых частых ошибок после PostgreSQL и MySQL. В ClickHouse primary key помогает индексировать и читать данные, но сам по себе не запрещает дубликаты. Если два одинаковых события попадут в таблицу, обычный MergeTree не удалит одно из них просто потому, что совпал ключ

Если нужна модель "оставить последнюю версию строки", смотрите в сторону ReplacingMergeTree. Если нужна строгая транзакционная уникальность, ClickHouse обычно не та система, где это стоит реализовывать

Когда нужен PARTITION BY

Партиции помогают управлять большими таблицами: удалять старые месяцы, переносить данные, ускорять некоторые операции обслуживания. Для событий часто используют месяц события:

CREATE TABLE demo.events_by_month
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    amount Decimal(12, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_type, user_id);

Не делайте слишком мелкие партиции без причины. Партиция по часу или пользователю может создать много маленьких частей и усложнить жизнь серверу

Проверка созданной таблицы

SHOW CREATE TABLE demo.events;
DESCRIBE TABLE demo.events;

SHOW CREATE TABLE полезен после изменений: он показывает итоговую DDL-команду. DESCRIBE TABLE быстро выводит список колонок и типов

Типичные ошибки при создании таблиц

• Все поля как String. Это ухудшает фильтрацию, сжатие и агрегации.
• ORDER BY tuple() без понимания. Иногда уместно, но чаще лишает таблицу полезного порядка.
• Nullable везде. Nullable добавляет служебную колонку и усложняет обработку.
• Слишком мелкие партиции. Много маленьких частей ухудшает обслуживание.
• Ожидание уникального primary key. В ClickHouse это не ограничение уникальности.

Как выбирать ORDER BY на практике

Начинайте не со списка колонок, а со списка запросов. Если почти каждый отчет содержит WHERE event_date BETWEEN ..., дата должна быть в начале ключа. Если внутри даты часто фильтруют по типу события, добавьте event_type. Если отчеты смотрят историю конкретного пользователя, может быть полезен user_id, но не всегда первым полем

Плохой ключ сортировки обычно проявляется так: запросы читают слишком много строк, хотя условие кажется узким. В таком случае проверьте, совпадает ли фильтр с началом ORDER BY. ClickHouse хорошо пропускает диапазоны, когда условия работают по префиксу ключа

Если есть сомнения, сделайте две тестовые таблицы с разными ключами, загрузите небольшой реальный срез и сравните запросы через EXPLAIN и фактическое количество прочитанных строк. Для ClickHouse это нормальная инженерная практика

Что проверить вокруг схемы

Почему схема важнее первого INSERT?

Потому что ORDER BY, типы колонок и партиции определяют, сколько данных будет читать каждый будущий отчет. Перед массовой загрузкой стоит пройти урок про типы данных ClickHouse, а уже потом переходить к INSERT INTO и первым SELECT-запросам

Что делать, если ключ сортировки выбран неудачно?

На маленьком стенде проще создать вторую таблицу с другим ORDER BY, переложить данные и сравнить запросы. На больших таблицах изменение модели часто превращается в миграцию; похожая логика обсуждается в материале про UPDATE, DELETE, TTL и ReplacingMergeTree

Когда сразу думать о витринах?

Если один и тот же тяжелый агрегат нужен в дашборде постоянно. В таком случае схема source table должна быть совместима с будущей target table, а следующий материал — Materialized View в ClickHouse