Эффективные методы подсчета уникальных значений в ClickHouse: сравнительный анализ

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

Набор данных

Для наших экспериментов создадим следующую таблицу:

CREATE TABLE default.billion_dataset
(     `timestamp` DateTime64(3),
    `row_uuid` UUID,     `high_cardinality_data` String,     `low_cardinality_data_without_low_cardinality_type` String,     `low_cardinality_data_with_low_cardinality_type` LowCardinality(String) ) ENGINE = MergeTree PRIMARY KEY timestamp
ORDER BY timestamp
SETTINGS index_granularity = 8192

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

Теперь вставим в нашу таблицу миллиард строк.

INSERT INTO billion_dataset SELECT
    now() - toIntervalSecond(rand() % 1000) AS timestamp,
    generateUUIDv4() AS row_uuid,
    cityHash64(row_uuid) % 100000000 AS high_cardinality_data,
    cityHash64(row_uuid) % 1000
      AS low_cardinality_data_withhout_low_cardinality_type,
    low_cardinality_data_withhout_low_cardinality_type        AS low_cardinality_data_with_low_cardinality_type
FROM numbers(1000000000)

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

Данные с высокой кардинальностью
Данные с низкой кардинальностью (без использования типа LowCardinality)
Данные с низкой кардинальностью (с использованием типа LowCardinality)

Метод 1: подсчет уникальных значений с помощью countDistinct / uniqExact

Тестируем данные с высокой кардинальностью

SELECT uniqExact(high_cardinality_data)
FROM billion_dataset
SETTINGS min_bytes_to_use_direct_io = 1
Elapsed: 31.128 sec. Processed 147.94 million rows, 2.50 GB
(4.75 million rows/s., 80.27 MB/s.)
Peak memory usage: 7.45 GiB.
Received exception from server (version 24.5.3):Code: 241.
DB::Exception: Received from localhost:9000.
DB::Exception: Memory limit (for query) exceeded: would use 7.46 GiB
(attempt to allocate chunk of 6291456 bytes), maximum: 7.45 GiB.:
While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

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

Тестируем данные с низкой кардинальностью (без использования типа LowCardinality)

SELECT uniqExact(low_cardinality_data_without_low_cardinality_type)
FROM billion_dataset
SETTINGS min_bytes_to_use_direct_io = 1
   ┌─uniqExact(low_cardinality_data_without_low_cardinality_type)─┐ 
1. │                                                         1000 │
   └──────────────────────────────────────────────────────────────┘ 
1 row in set. Elapsed: 5.199 sec.
Processed 1.00 billion rows, 11.89 GB (192.34 million rows/s., 2.29 GB/s.)
Peak memory usage: 7.51 MiB.

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

Тестируем данные с низкой кардинальностью (с использованием типа LowCardinality)

SELECT uniqExact(low_cardinality_data_with_low_cardinality_type)
FROM billion_dataset
SETTINGS min_bytes_to_use_direct_io = 1
   ┌─uniqExact(low_cardinality_data_with_low_cardinality_type)─┐ 
1. │                                                      1000 │
   └───────────────────────────────────────────────────────────┘ 
1 row in set. Elapsed: 3.909 sec.
Processed 1.00 billion rows, 2.00 GB (255.80 million rows/s., 511.60 MB/s.)
Peak memory usage: 1.36 MiB.

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

Метод 2: Использование алгоритмов приближения

2.1 Подсчет уникальных значений с помощью uniq

Тестируем данные с высокой кардинальностью

SELECT uniq(high_cardinality_data)
FROM billion_dataset
SETTINGS min_bytes_to_use_direct_io = 1
   ┌─uniq(high_cardinality_data)─┐ 
1. │                    99516759 │ -- 99.52 million
   └─────────────────────────────┘ 
1 row in set. Elapsed: 5.001 sec.
Processed 1.00 billion rows, 16.89 GB (199.98 million rows/s., 3.38 GB/s.)
Peak memory usage: 2.52 MiB.

Тестируем данные с низкой кардинальностью (без использования типа LowCardinality)

SELECT uniq(low_cardinality_data_without_low_cardinality_type)
FROM billion_dataset
SETTINGS min_bytes_to_use_direct_io = 1
   ┌─uniq(low_cardinality_data_without_low_cardinality_type)─┐ 
1. │                                                    1000 │
   └─────────────────────────────────────────────────────────┘ 
1 row in set. Elapsed: 3.876 sec.
Processed 1.00 billion rows, 11.89 GB (258.02 million rows/s., 3.07 GB/s.)
Peak memory usage: 1.82 MiB.

Тестируем данные с низкой кардинальностью (с использованием типа LowCardinality)

SELECT uniq(low_cardinality_data_with_low_cardinality_type)
FROM billion_dataset
SETTINGS min_bytes_to_use_direct_io = 1
   ┌─uniq(low_cardinality_data_with_low_cardinality_type)─┐ 
1. │                                                 1000 │
   └──────────────────────────────────────────────────────┘ 
1 row in set. Elapsed: 3.038 sec.
Processed 1.00 billion rows, 2.00 GB (329.21 million rows/s., 658.43 MB/s.)
Peak memory usage: 1.02 MiB.

Функция uniq вычисляет приблизительное количество уникальных значений для каждого столбца. Сначала функция вычисляет хэш значений в столбце, а затем использует алгоритм адаптивной выборки (использует выборку хэш-значений элементов до 65536).

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

2.2 Подсчет уникальных значений с помощью uniqCombined

Тестируем данные с высокой кардинальностью

SELECT uniqCombined(high_cardinality_data)
FROM billion_dataset
SETTINGS min_bytes_to_use_direct_io = 1
   ┌─uniqCombined(high_cardinality_data)─┐ 
1. │                           100194211 │ -- 100.19 million
   └─────────────────────────────────────┘ 
1 row in set. Elapsed: 6.192 sec.
Processed 1.00 billion rows, 16.89 GB (161.50 million rows/s., 2.73 GB/s.)
Peak memory usage: 818.55 KiB.

Тестируем данные с низкой кардинальностью (без использования типа LowCardinality)

SELECT uniqCombined(low_cardinality_data_without_low_cardinality_type)
FROM billion_dataset
SETTINGS min_bytes_to_use_direct_io = 1
   ┌─uniqCombined(low_cardinality_data_without_low_cardinality_type)─┐ 
1. │                                                            1000 │
   └─────────────────────────────────────────────────────────────────┘ 
1 row in set. Elapsed: 3.359 sec.
Processed 1.00 billion rows, 11.89 GB (297.74 million rows/s., 3.54 GB/s.)
Peak memory usage: 1.06 MiB.

Тестируем данные с низкой кардинальностью (с использованием типа LowCardinality)

SELECT uniqCombined(low_cardinality_data_with_low_cardinality_type)
FROM billion_dataset
SETTINGS min_bytes_to_use_direct_io = 1
   ┌─uniqCombined(low_cardinality_data_with_low_cardinality_type)─┐ 
1. │                                                         1000 │
   └──────────────────────────────────────────────────────────────┘ 
1 row in set. Elapsed: 2.456 sec.
Processed 1.00 billion rows, 2.00 GB (407.20 million rows/s., 814.40 MB/s.)
Peak memory usage: 334.30 KiB.

Сначала функция вычисляет хэш значений в столбце. Для вычисления количества уникальных значений она использует комбинацию трех алгоритмов:

При небольшом количестве отдельных элементов используется массив.
Если размер набора больше, используется хэш-таблица.
При большом количестве элементов используется HyperLogLog, занимающий фиксированный объем памяти.

Функция uniqCombined имеет более высокую точность, чем функция uniq. Кроме того, по сравнению с uniq она занимает меньше памяти.

Метод 3: использование group by

Тестируем данные с высокой кардинальностью

SELECT count()
FROM
(     SELECT high_cardinality_data
    FROM billion_dataset
    GROUP BY 1
) SETTINGS min_bytes_to_use_direct_io = 1
Received exception from server (version 24.5.3):
Code: 241.
DB::Exception: Memory limit (for query) exceeded: would use 5.60 GiB
(attempt to allocate chunk of 6291456 bytes), maximum: 5.59 GiB.:
While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

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

Тестируем данные с низкой кардинальностью (без использования типа LowCardinality)

SELECT count()
FROM
(     SELECT low_cardinality_data_without_low_cardinality_type
    FROM billion_dataset
    GROUP BY 1
) SETTINGS min_bytes_to_use_direct_io = 1
   ┌─count()─┐
1. │    1000 │
   └─────────┘ 1 row in set. Elapsed: 3.220 sec.
Processed 1.00 billion rows, 11.89 GB (310.55 million rows/s., 3.69 GB/s.)
Peak memory usage: 1.32 MiB.

Тестируем данные с низкой кардинальностью (с использованием типа LowCardinality)

SELECT count()
FROM
(     SELECT low_cardinality_data_with_low_cardinality_type
    FROM billion_dataset
    GROUP BY 1
) SETTINGS min_bytes_to_use_direct_io = 1
   ┌─count()─┐
1. │    1000 │
   └─────────┘ 1 row in set. Elapsed: 1.087 sec.
Processed 1.00 billion rows, 2.00 GB (920.22 million rows/s., 1.84 GB/s.)
Peak memory usage: 1.95 MiB.

Для данных с низкой кардинальностью расход памяти значительно меньше.

Обратите внимание на то, что в случае работы с данными с низкой кардинальностью, использующих тип LowCardinality, скорость получения уникальных значений увеличивается более чем в 3 раза. Это связано с тем, что при хранении типов LowCardinality Clickhouse использует словарное кодирование, что обеспечивает эффективное извлечение данных.

Сравнительный анализ

В следующей таблице приведен сравнительный анализ методов, рассмотренных выше.

Заключение

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

Ресурсоемкий метод, особенно с точки зрения использования памяти.

Приблизительное количество уникальных значений для столбцов, содержащих данные с высокой кардинальностью:

Функции uniq или uniqCombined чрезвычайно быстры и требует значительно меньше памяти по сравнению с uniqExact.
Функция uniqCombined более эффективно использует память, но по сравнению с uniq может выполняться немного медленнее.

Данные с низкой кардинальностью:

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

Group By для данных с высокой кардинальностью:

Данная операция крайне неэффективна и связана со слишком большими затратам памяти.

← Предыдущая статья

ClickHouse для новичков: введение, характеристики и начало работы

Следующая статья →

Легкое развертывание ClickHouse на Kubernetes: подробная инструкция