Table of Contents

Overview

Clickhouse достаточно быстрое хранилище, но когда ваше хранилище перерастает тот момент, когда агрегированные запросы по сырым данным выполняются медленно. В этот момент мы начинаем задумываться об оптимизации. Сегодня я хочу рассказать о подходе где мы будем использоваться AggregatingMergeTree. Materialized View получает все данные по определенному запросу который вы указываете при создании и AggregatingMergeTree агрегирует эти данные по ключу сортировки. Используя этот подход, мы можем группировать данные по определенным полям и это позволит делать тяжелые запросы по большому промежутку времени. Важно, коэффициент оптимизации является отношением всех записей к уникальным записям получившимися в materialized view. Это говорит на сколько эфиктивна будет оптимизация.

Как

CREATE TABLE views
(
  id                UInt64,
  manager           UInt64,
  splitTest         UInt64,
  splitTestOption   UInt64,
  client            UInt64,
  schemeType        UInt64,
  campaign          UInt64,
  creative          UInt64,
  source            UInt64,
  domain            UInt64,
  referrer          UInt64,
  country           String,
  device            String,
  os                String,
  viewAt            DateTime,
  viewDate          Date DEFAULT toDate(viewAt),
  platform          UInt64,
  orientation       UInt64,
  pageType          UInt64,
  categoryName      UInt64,
  widgetName        UInt64,
  widgetElement     UInt64,
  logged            UInt8,
  isVisited         UInt8
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/test/{shard}/views', '{replica}', viewDate,
           (viewDate, client), 8192);

У нас имеется большая таблица которую мы собираемся оптимизировать по полям viewDate, manager, client, campaign, creative, source, country, device, domain. Нам нужно создать Materialized view c движком AggregatingMergeTree.

CREATE MATERIALIZED VIEW views_mat
  engine = AggregatingMergeTree partition by viewDate order by (
    viewDate,
    manager,
    client,
    campaign,
    creative,
    source,
    country,
    device,
    domain,
    landing)
AS
SELECT viewDate,
       manager,
       client,
       campaign,
       creative,
       source,
       country,
       device,
       domainId,
       landing,
       countState() as amount
FROM views
GROUP BY viewDate,
         manager,
         client,
         campaign,
         creative,
         source,
         country,
         device,
         domain,
         landing

Комбинаторы агрегатных функций

Вы можете заметить функцию countState(). Она не возвращает готовый результат, а возвращает промежуточное состояние агрегатной функции. Вам нужно использовать _countMerge() для получения результата. Более подробно о комбинаторах можно прочитать из (документации)[https://clickhouse.yandex/docs/en/query_language/agg_functions/combinators/].

Сделаем запрос и посмотрим разницу между сырыми и агрегированными данными.

Сырые:

SELECT
    campaign,
    count() AS views
FROM views
WHERE (client IN 7559922) AND (country IN 'in') AND ((viewDate >= '2018-06-01') AND (viewDate <= '2018-12-30'))
GROUP BY campaign;

Row 1:
──────
campaignId:  0
impressions: 7955062

Row 2:
──────
campaignId:  812661
impressions: 11003

Row 3:
──────
campaignId:  1334
impressions: 253350

Row 4:
──────
campaignId:  569467
impressions: 93182604

Row 5:
──────
campaignId:  237410
impressions: 7554

Row 6:
──────
campaignId:  23456
impressions: 3

Row 7:
──────
campaignId:  524556
impressions: 438

7 rows in set. Elapsed: 46.781 sec. Processed 9.18 billion rows, 116.92 GB (196.24 million rows/s., 2.50 GB/s.)

Агрегированные:

SELECT
    campaignId,
    countMerge(amount) AS impressions
FROM impressions_mat
WHERE (userId IN 26) AND (country IN 'in') AND ((impressionDate >= '2018-06-01') AND (impressionDate <= '2018-12-30'))
GROUP BY campaignId

Row 1:
──────
campaignId:  0
impressions: 7955062

Row 2:
──────
campaignId:  812661
impressions: 11003

Row 3:
──────
campaignId:  1334
impressions: 253350

Row 4:
──────
campaignId:  569467
impressions: 93182604

Row 5:
──────
campaignId:  237410
impressions: 7554

Row 6:
──────
campaignId:  23456
impressions: 3

Row 7:
──────
campaignId:  524556
impressions: 438

7 rows in set. Elapsed: 0.116 sec. Processed 9.13 million rows, 448.10 MB (78.78 million rows/s., 3.86 GB/s.)

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

Наполнение

CREATE MATERIALIZED VIEW имеет опцию POPULATE. Она позволяет наполнить таблицу с данными из запроса, но есть и подводный камень. Отображение не получит данные которые появились в период наполнения. Таким образом, мы создаем отображение и наполняем его после создания. В нашем случае, мы останавливаем вставку данные, наполняем и запускаем вставку.

Итоги

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