Использование AggregatingMergeTree в Clickhouse

Игорь Грегорченко

22.12.2020 16:58

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

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

**date       | user_id | url | device**
%TODAY% |       1 | /   | Desktop
%TODAY% |       1 | /a  | Desktop
%TODAY% |       2 | /   | Tablet
%TODAY% |       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**
%TODAY% | Desktop | 2
%TODAY% | 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

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

<h2>TL;DR

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