Даже колоночные базы данных, вроде Clickhouse, сталкиваются с проблемами слишком большого количества данных и слишком медленных запросов. Для таких случаев используют методики агрегирования данных, что позволяет экономить место и упрощать выборки.

Пусть мы храним события (например, просмотры страниц) в следующем виде:

date       | user_id | url | device
2018-05-21 |       1 | /   | Desktop
2018-05-21 |       1 | /a  | Desktop
2018-05-21 |       2 | /   | Tablet
2018-05-21 |       3 | /   | Tablet

# Обычный лог всех просмотров страниц

Представим, что эта таблица огромная. Агрегатные функции на таблицах с 1+ млрд записей часто работают не так быстро:

SELECT count(*)
FROM log_41949_events

┌────count()─┐
│ 1894260239 │
└────────────┘

1 rows in set. Elapsed: 8.430 sec. Processed 1.89 billion rows, 3.79 GB (224.71 million rows/s., 449.41 MB/s.)

# Один сервер CH на обычном HDD

Пусть нам необходимо часто делать выборки с фильтром по устройству (device):

SELECT 
    count(*), 
    device
FROM log_41949_events 
GROUP BY device

┌────count()─┬─device───────────┐
│ 1260916969 │ desktop          │
│  454097963 │ smart (Android)  │
│   94101924 │ smart (iOS)      │
│   17922239 │ tablet (iOS)     │
│   67682187 │ tablet (Android) │
│     215990 │ smart (Other)    │
└────────────┴──────────────────┘

6 rows in set. Elapsed: 4.022 sec. Processed 1.89 billion rows, 1.89 GB (90.82 million rows/s., 90.82 MB/s.)

# Пример запроса с настоящей таблицы на сервере, который собирает около 200 млн событий в сутки

В целом, скорость приемлемая для аналитических выборок. Если же задача требует более производительного решения, стоит использовать агрегации на основе AggregatingMergeTree.

AggregatingMergeTree

Этот движок похож на ReplacingMergeTree и CollapsingMergeTree. В нем также определяется ключ по которому выполняется слияние данных. Но кроме этого, движок позволяет использовать агрегатные функции (count, sum, uniq и т.п.). Т.е. вместо хранения всей таблицы, мы могли бы хранить данные в таком виде:

date       | device  | total
2018-05-21 | Desktop | 2
2018-05-21 | Tablet  | 2

# Агрегированная таблица

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

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

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

Для создания агрегационного представления достаточно объявить специальную таблицу:

CREATE MATERIALIZED VIEW log_41949_devices
ENGINE = AggregatingMergeTree(date, (date, device), 8192) POPULATE AS
SELECT 
    date, 
    device, 
    countState(*) AS total
FROM log_41949_events
GROUP BY 
    date, 
    device

# Создание агрегационной таблицы для хранения количества событий по устройству и дате

Создаем материализованное представление log_41949_devices. Для него указываем тип движка AggregatingMergeTree. Инструкция POPULATE попросит сервер заполнить новую таблицу всеми данными из исходной таблицы сразу после создания. Кроме этого, созданная таблица будет автоматически заполняться данными, которые будут записываться в оригинальную таблицу.

Для того, чтобы заработали агрегационные функции, вместо них необходимо указывать их состояния: [функция]State() (существуют для всех агрегатных функций: countState, sumState, uniqState, minState, maxState). В нашем случае, мы считаем количество countState(*).

После заполнения таблицы, она станет доступна для выборок. Опять, вместо обычной агрегатной функции необходимо вызывать ее версию для слияния ([функция]Merge()):

SELECT 
    countMerge(total),
    device
FROM log_41949_devices
GROUP BY device

┌─countMerge(total)─┬─device───────────┐
│        1266626129 │ desktop          │
│         457198984 │ smart (Android)  │
│          94784896 │ smart (iOS)      │
│          18007576 │ tablet (iOS)     │
│          68026713 │ tablet (Android) │
│            217264 │ smart (Other)    │
└───────────────────┴──────────────────┘

6 rows in set. Elapsed: 0.001 sec.

# Запрос к агрегационной таблице

Скорость выполнения такого запроса в несколько тысяч раз больше исходного.

В запросе для построения таблицы можно использовать обычный SQL, например фильтрацию:

CREATE MATERIALIZED VIEW log_41949_devices
ENGINE = AggregatingMergeTree(date, (date, device), 8192) POPULATE AS
SELECT 
    date, 
    device, 
    countState(*) AS total
FROM log_41949_events
WHERE country = 'Planet Earth'
GROUP BY 
    date, 
    device

Используйте агрегации только в крайних случаях, т.к. они усложняют поддержку приложения.

TL;DR

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