Skip to main content

MergeTree

Движок MergeTree, а также другие движки этого семейства (*MergeTree) — это наиболее функциональные движки таблиц ClickHouse.

Основная идея, заложенная в основу движков семейства MergeTree следующая. Когда у вас есть огромное количество данных, которые должны быть вставлены в таблицу, вы должны быстро записать их по частям, а затем объединить части по некоторым правилам в фоновом режиме. Этот метод намного эффективнее, чем постоянная перезапись данных в хранилище при вставке.

Основные возможности:

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

  • Позволяет оперировать партициями, если задан ключ партиционирования. ClickHouse поддерживает отдельные операции с партициями, которые работают эффективнее, чем общие операции с этим же результатом над этими же данными. Также, ClickHouse автоматически отсекает данные по партициям там, где ключ партиционирования указан в запросе. Это также увеличивает эффективность выполнения запросов.

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

  • Поддерживает сэмплирование данных. При необходимости можно задать способ сэмплирования данных в таблице.

    info

    Движок Merge не относится к семейству *MergeTree.

Создание таблицы​

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...]

Описание параметров смотрите в описании запроса CREATE.

Секции запроса​

  • ENGINE — имя и параметры движка. ENGINE = MergeTree(). MergeTree не имеет параметров.

  • ORDER BY — ключ сортировки.

    Кортеж столбцов или произвольных выражений. Пример: ORDER BY (CounterID, EventDate).

    ClickHouse использует ключ сортировки в качестве первичного ключа, если первичный ключ не задан в секции PRIMARY KEY.

    Чтобы отключить сортировку, используйте синтаксис ORDER BY tuple(). Смотрите выбор первичного ключа.

  • PARTITION BY — ключ партиционирования. Необязательный параметр.

    Для партиционирования по месяцам используйте выражение toYYYYMM(date_column), где date_column — столбец с датой типа Date. В этом случае имена партиций имеют формат "YYYYMM".

  • PRIMARY KEY — первичный ключ, если он отличается от ключа сортировки. Необязательный параметр.

    По умолчанию первичный ключ совпадает с ключом сортировки (который задаётся секцией ORDER BY.) Поэтому в большинстве случаев секцию PRIMARY KEY отдельно указывать не нужно.

  • SAMPLE BY — выражение для сэмплирования. Необязательный параметр.

    Если используется выражение для сэмплирования, то первичный ключ должен содержать его. Результат выражения для сэмплирования должен быть беззнаковым целым числом. Пример: SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)).

  • TTL — список правил, определяющих длительности хранения строк, а также задающих правила перемещения частей на определённые тома или диски. Необязательный параметр.

    Выражение должно возвращать столбец Date или DateTime. Пример: TTL date + INTERVAL 1 DAY.

    Тип правила DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'|GROUP BY указывает действие, которое будет выполнено с частью: удаление строк (прореживание), перемещение (при выполнении условия для всех строк части) на определённый диск (TO DISK 'xxx') или том (TO VOLUME 'xxx'), или агрегирование данных в устаревших строках. Поведение по умолчанию соответствует удалению строк (DELETE). В списке правил может быть указано только одно выражение с поведением DELETE.

    Дополнительные сведения смотрите в разделе TTL для столбцов и таблиц

  • SETTINGS — дополнительные параметры, регулирующие поведение MergeTree (необязательные):

    • index_granularity — максимальное количество строк данных между засечками индекса. По умолчанию — 8192. Смотрите Хранение данных.
    • index_granularity_bytes — максимальный размер гранул данных в байтах. По умолчанию — 10Mb. Чтобы ограничить размер гранул только количеством строк, установите значение 0 (не рекомендовано). Смотрите Хранение данных.
    • min_index_granularity_bytes — минимально допустимый размер гранул данных в байтах. Значение по умолчанию — 1024b. Для обеспечения защиты от случайного создания таблиц с очень низким значением index_granularity_bytes. Смотрите Хранение данных.
    • enable_mixed_granularity_parts — включает или выключает переход к ограничению размера гранул с помощью настройки index_granularity_bytes. Настройка index_granularity_bytes улучшает производительность ClickHouse при выборке данных из таблиц с большими (десятки и сотни мегабайтов) строками. Если у вас есть таблицы с большими строками, можно включить эту настройку, чтобы повысить эффективность запросов SELECT.
    • use_minimalistic_part_header_in_zookeeper — Способ хранения заголовков кусков данных в ZooKeeper. Если use_minimalistic_part_header_in_zookeeper = 1, то ZooKeeper хранит меньше данных. Подробнее читайте в описании настройки в разделе "Конфигурационные параметры сервера".
    • min_merge_bytes_to_use_direct_io — минимальный объём данных при слиянии, необходимый для прямого (небуферизованного) чтения/записи (direct I/O) на диск. При слиянии частей данных ClickHouse вычисляет общий объём хранения всех данных, подлежащих слиянию. Если общий объём хранения всех данных для чтения превышает min_bytes_to_use_direct_io байт, тогда ClickHouse использует флаг O_DIRECT при чтении данных с диска. Если min_merge_bytes_to_use_direct_io = 0, тогда прямой ввод-вывод отключен. Значение по умолчанию: 10 * 1024 * 1024 * 1024 байтов.
    • merge_with_ttl_timeout — минимальное время в секундах перед повторным слиянием для удаления данных с истекшим TTL. По умолчанию: 14400 секунд (4 часа).
    • merge_with_recompression_ttl_timeout — минимальное время в секундах перед повторным слиянием для повторного сжатия данных с истекшим TTL. По умолчанию: 14400 секунд (4 часа).
    • try_fetch_recompressed_part_timeout — время ожидания (в секундах) перед началом слияния с повторным сжатием. В течение этого времени ClickHouse пытается извлечь сжатую часть из реплики, которая назначила это слияние. Значение по умолчанию: 7200 секунд (2 часа).
    • write_final_mark — включает или отключает запись последней засечки индекса в конце куска данных, указывающей за последний байт. По умолчанию — 1. Не отключайте её.
    • merge_max_block_size — максимальное количество строк в блоке для операций слияния. Значение по умолчанию: 8192.
    • storage_policy — политика хранения данных. Смотрите Хранение данных таблицы на нескольких блочных устройствах.
    • min_bytes_for_wide_part, min_rows_for_wide_part — минимальное количество байт/строк в куске данных для хранения в формате Wide. Можно задать одну или обе настройки или не задавать ни одной. Подробнее см. в разделе Хранение данных.
    • max_parts_in_total — максимальное количество кусков во всех партициях.
    • max_compress_block_size — максимальный размер блоков несжатых данных перед сжатием для записи в таблицу. Вы также можете задать этот параметр в глобальных настройках (смотрите max_compress_block_size). Настройка, которая задается при создании таблицы, имеет более высокий приоритет, чем глобальная.
    • min_compress_block_size — минимальный размер блоков несжатых данных, необходимых для сжатия при записи следующей засечки. Вы также можете задать этот параметр в глобальных настройках (смотрите min_compress_block_size). Настройка, которая задается при создании таблицы, имеет более высокий приоритет, чем глобальная.
    • max_partitions_to_read — Ограничивает максимальное число партиций для чтения в одном запросе. Также возможно указать настройку max_partitions_to_read в глобальных настройках.

Пример задания секций

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

В примере мы устанавливаем партиционирование по месяцам.

Также мы задаем выражение для сэмплирования в виде хэша по идентификатору посетителя. Это позволяет псевдослучайным образом перемешать данные в таблице для каждого CounterID и EventDate. Если при выборке данных задать секцию SAMPLE, то ClickHouse вернёт равномерно-псевдослучайную выборку данных для подмножества посетителей.

index_granularity можно было не указывать, поскольку 8192 — это значение по умолчанию.

Устаревший способ создания таблицы
"Attention"

Не используйте этот способ в новых проектах и по возможности переведите старые проекты на способ, описанный выше.

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE [=] MergeTree(date-column [, sampling_expression], (primary, key), index_granularity)

Параметры MergeTree()

  • date-column — имя столбца с типом Date. На основе этого столбца ClickHouse автоматически создаёт партиции по месяцам. Имена партиций имеют формат "YYYYMM".
  • sampling_expression — выражение для сэмплирования.
  • (primary, key) — первичный ключ. Тип — Tuple()
  • index_granularity — гранулярность индекса. Число строк данных между «засечками» индекса. Для большинства задач подходит значение 8192.

Пример

MergeTree(EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID)), 8192)

Движок MergeTree сконфигурирован таким же образом, как и в примере выше для основного способа конфигурирования движка.

Хранение данных​

Таблица состоит из кусков данных (data parts), отсортированных по первичному ключу.

При вставке в таблицу создаются отдельные куски данных, каждый из которых лексикографически отсортирован по первичному ключу. Например, если первичный ключ — (CounterID, Date), то данные в куске будут лежать в порядке CounterID, а для каждого CounterID в порядке Date.

Данные, относящиеся к разным партициям, разбиваются на разные куски. В фоновом режиме ClickHouse выполняет слияния (merge) кусков данных для более эффективного хранения. Куски, относящиеся к разным партициям не объединяются. Механизм слияния не гарантирует, что все строки с одинаковым первичным ключом окажутся в одном куске.

Куски данных могут храниться в формате Wide или Compact. В формате Wide каждый столбец хранится в отдельном файле, а в формате Compact все столбцы хранятся в одном файле. Формат Compact может быть полезен для повышения производительности при частом добавлении небольших объемов данных.

Формат хранения определяется настройками движка min_bytes_for_wide_part и min_rows_for_wide_part. Если число байт или строк в куске данных меньше значения, указанного в соответствующей настройке, тогда этот кусок данных хранится в формате Compact. В противном случае кусок данных хранится в формате Wide. Если ни одна из настроек не задана, куски данных хранятся в формате Wide.

Каждый кусок данных логически делится на гранулы. Гранула — это минимальный неделимый набор данных, который ClickHouse считывает при выборке данных. ClickHouse не разбивает строки и значения и гранула всегда содержит целое число строк. Первая строка гранулы помечается значением первичного ключа для этой строки (засечка). Для каждого куска данных ClickHouse создаёт файл с засечками (индексный файл). Для каждого столбца, независимо от того, входит он в первичный ключ или нет, ClickHouse также сохраняет эти же засечки. Засечки используются для поиска данных напрямую в файлах столбцов.

Размер гранул оганичен настройками движка index_granularity и index_granularity_bytes. Количество строк в грануле лежит в диапазоне [1, index_granularity], в зависимости от размера строк. Размер гранулы может превышать index_granularity_bytes в том случае, когда размер единственной строки в грануле превышает значение настройки. В этом случае, размер гранулы равен размеру строки.

Первичные ключи и индексы в запросах​

Рассмотрим первичный ключ — (CounterID, Date). В этом случае сортировку и индекс можно проиллюстрировать следующим образом:

Whole data:     [-------------------------------------------------------------------------]
CounterID:      [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]
Date:           [1111111222222233331233211111222222333211111112122222223111112223311122333]
Marks:           |      |      |      |      |      |      |      |      |      |      |
                a,1    a,2    a,3    b,3    e,2    e,3    g,1    h,2    i,1    i,3    l,3
Marks numbers:   0      1      2      3      4      5      6      7      8      9      10

Если в запросе к данным указать:

  • CounterID IN ('a', 'h'), то сервер читает данные в диапазонах засечек [0, 3) и [6, 8).
  • CounterID IN ('a', 'h') AND Date = 3, то сервер читает данные в диапазонах засечек [1, 3) и [7, 8).
  • Date = 3, то сервер читает данные в диапазоне засечек [1, 10].

Примеры выше показывают, что использование индекса всегда эффективнее, чем full scan.

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

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

ClickHouse не требует уникального первичного ключа. Можно вставить много строк с одинаковым первичным ключом.

Ключ в PRIMARY KEY и ORDER BY может иметь тип Nullable. За поддержку этой возможности отвечает настройка allow_nullable_key.

При сортировке с использованием выражения ORDER BY для значений NULL всегда работает принцип NULLS_LAST.

Выбор первичного ключа​

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

  • Увеличить эффективность индекса.

    Пусть первичный ключ — (a, b), тогда добавление ещё одного столбца c повысит эффективность, если выполнены условия:

    • Есть запросы с условием на столбец c.
    • Часто встречаются достаточно длинные (в несколько раз больше index_granularity) диапазоны данных с одинаковыми значениями (a, b). Иначе говоря, когда добавление ещё одного столбца позволит пропускать достаточно длинные диапазоны данных.

  • Улучшить сжатие данных.

    ClickHouse сортирует данные по первичному ключу, поэтому чем выше однородность, тем лучше сжатие.

  • Обеспечить дополнительную логику при слиянии кусков данных в движках CollapsingMergeTree и SummingMergeTree.

    В этом случае имеет смысл указать отдельный ключ сортировки, отличающийся от первичного ключа.

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

Вы можете создать таблицу без первичного ключа, используя синтаксис ORDER BY tuple(). В этом случае ClickHouse хранит данные в порядке вставки. Если вы хотите сохранить порядок данных при вставке данных с помощью запросов INSERT ... SELECT, установите max_insert_threads = 1.

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

Первичный ключ, отличный от ключа сортировки​

Существует возможность задать первичный ключ (выражение, значения которого будут записаны в индексный файл для каждой засечки), отличный от ключа сортировки (выражение, по которому будут упорядочены строки в кусках данных). Кортеж выражения первичного ключа при этом должен быть префиксом кортежа выражения ключа сортировки.

Данная возможность особенно полезна при использовании движков SummingMergeTree и AggregatingMergeTree. В типичном сценарии использования этих движков таблица содержит столбцы двух типов: измерения (dimensions) и меры (measures). Типичные запросы агрегируют значения столбцов-мер с произвольной группировкой и фильтрацией по измерениям. Так как SummingMergeTree и AggregatingMergeTree производят фоновую агрегацию строк с одинаковым значением ключа сортировки, приходится добавлять в него все столбцы-измерения. В результате выражение ключа содержит большой список столбцов, который приходится постоянно расширять при добавлении новых измерений.

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

ALTER ключа сортировки — лёгкая операция, так как при одновременном добавлении нового столбца в таблицу и ключ сортировки не нужно изменять данные кусков (они остаются упорядоченными и по новому выражению ключа).

Использование индексов и партиций в запросах​

Для запросов SELECT ClickHouse анализирует возможность использования индекса. Индекс может использоваться, если в секции WHERE/PREWHERE, в качестве одного из элементов конъюнкции, или целиком, есть выражение, представляющее операции сравнения на равенства, неравенства, а также IN или LIKE с фиксированным префиксом, над столбцами или выражениями, входящими в первичный ключ или ключ партиционирования, либо над некоторыми частично монотонными функциями от этих столбцов, а также логические связки над такими выражениями.

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

Рассмотрим движок сконфигурированный следующим образом:

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate) SETTINGS index_granularity=8192

В этом случае в запросах:

SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34
SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)
SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

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

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

В примере ниже индекс не может использоваться.

SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'

Чтобы проверить, сможет ли ClickHouse использовать индекс при выполнении запроса, используйте настройки force_index_by_date и force_primary_key.

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

Использование индекса для частично-монотонных первичных ключей​

Рассмотрим, например, дни месяца. Они образуют последовательность монотонную в течение одного месяца, но не монотонную на более длительных периодах. Это частично-монотонная последовательность. Если пользователь создаёт таблицу с частично-монотонным первичным ключом, ClickHouse как обычно создаёт разреженный индекс. Когда пользователь выбирает данные из такого рода таблиц, ClickHouse анализирует условия запроса. Если пользователь хочет получить данные между двумя метками индекса, и обе эти метки находятся внутри одного месяца, ClickHouse может использовать индекс в данном конкретном случае, поскольку он может рассчитать расстояние между параметрами запроса и индексными метками.

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

ClickHouse использует эту логику не только для последовательностей дней месяца, но и для любого частично-монотонного первичного ключа.

Индексы пропуска данных​

Объявление индексов при определении столбцов в запросе CREATE.

INDEX index_name expr TYPE type(...) GRANULARITY granularity_value

Для таблиц семейства *MergeTree можно задать дополнительные индексы в секции столбцов.

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

Пример

CREATE TABLE table_name
(
    u64 UInt64,
    i32 Int32,
    s String,
    ...
    INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3,
    INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4
) ENGINE = MergeTree()
...

Эти индексы смогут использоваться для оптимизации следующих запросов

SELECT count() FROM table WHERE s < 'z'
SELECT count() FROM table WHERE u64 * i32 == 10 AND u64 * length(s) >= 1234

Доступные индексы​

  • minmax — хранит минимум и максимум выражения (если выражение - Tuple, то для каждого элемента Tuple), используя их для пропуска блоков аналогично первичному ключу.

  • set(max_rows) — хранит уникальные значения выражения на блоке в количестве не более max_rows (если max_rows = 0, то ограничений нет), используя их для пропуска блоков, оценивая выполнимость WHERE выражения на хранимых данных.

  • ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed) — хранит фильтр Блума, содержащий все N-граммы блока данных. Работает только с данными форматов String, FixedString и Map с ключами типа String или fixedString. Может быть использован для оптимизации выражений EQUALS, LIKE и IN.

    • n — размер N-граммы,
    • size_of_bloom_filter_in_bytes — размер в байтах фильтра Блума (можно использовать большие значения, например, 256 или 512, поскольку сжатие компенсирует возможные издержки).
    • number_of_hash_functions — количество хеш-функций, использующихся в фильтре Блума.
    • random_seed — состояние генератора случайных чисел для хеш-функций фильтра Блума.

  • tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed) — то же, что иngrambf_v1, но хранит токены вместо N-грамм. Токены — это последовательности символов, разделенные не буквенно-цифровыми символами.

  • bloom_filter([false_positive]) — фильтр Блума для указанных стоблцов.

    Необязательный параметр false_positive — это вероятность получения ложноположительного срабатывания. Возможные значения: (0, 1). Значение по умолчанию: 0.025.

    Поддерживаемые типы данных: Int*, UInt*, Float*, Enum, Date, DateTime, String, FixedString.

    Фильтром могут пользоваться функции: equals, notEquals, in, notIn, has, hasAny, hasAll.

Примеры

INDEX b (u64 * length(str), i32 + f64 * 100, date, str) TYPE minmax GRANULARITY 4
INDEX b (u64 * length(str), i32 + f64 * 100, date, str) TYPE set(100) GRANULARITY 4

Поддержка для функций​

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

Индекс set используется со всеми функциями. Наборы функций для остальных индексов представлены в таблице ниже.

Функция (оператор) / Индексprimary keyminmaxngrambf_v1tokenbf_v1bloom_filter
equals (=, ==)✔✔✔✔✔
notEquals(!=, <>)✔✔✔✔✔
like✔✔✔✔✗
notLike✔✔✔✔✗
startsWith✔✔✔✔✗
endsWith✗✗✔✔✗
multiSearchAny✗✗✔✗✗
in✔✔✔✔✔
notIn✔✔✔✔✔
less (\<)✔✔✗✗✗
greater (>)✔✔✗✗✗
lessOrEquals (\<=)✔✔✗✗✗
greaterOrEquals (>=)✔✔✗✗✗
empty✔✔✗✗✗
notEmpty✔✔✗✗✗
hasToken✗✗✗✔✗

Функции с постоянным агрументом, который меньше, чем размер ngram не могут использовать индекс ngrambf_v1 для оптимизации запроса.

Фильтры Блума могут иметь ложнопозитивные срабатывания, следовательно индексы ngrambf_v1, tokenbf_v1 и bloom_filter невозможно использовать для оптимизации запросов, в которых результат функции предполается false, например:

  • Можно оптимизировать:
    • s LIKE '%test%'
    • NOT s NOT LIKE '%test%'
    • s = 1
    • NOT s != 1
    • startsWith(s, 'test')
  • Нельзя оптимизировать:
    • NOT s LIKE '%test%'
    • s NOT LIKE '%test%'
    • NOT s = 1
    • s != 1
    • NOT startsWith(s, 'test')

Проекции​

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

Проекции — это экспериментальная возможность. Чтобы включить поддержку проекций, установите настройку allow_experimental_projection_optimization в значение 1. См. также настройку force_optimize_projection .

Проекции не поддерживаются для запросов SELECT с модификатором FINAL.

Запрос проекции​

Запрос проекции — это то, что определяет проекцию. Такой запрос неявно выбирает данные из родительской таблицы. Синтаксис

SELECT <column list expr> [GROUP BY] <group keys expr> [ORDER BY] <expr>

Проекции можно изменить или удалить с помощью запроса ALTER.

Хранение проекции​

Проекции хранятся в каталоге куска данных. Это похоже на хранение индексов, но используется подкаталог, в котором хранится анонимный кусок таблицы MergeTree. Таблица создается запросом определения проекции. Если присутствует секция GROUP BY, то используется движок AggregatingMergeTree, а все агрегатные функции преобразуются в AggregateFunction. Если присутствует секция ORDER BY, таблица MergeTree использует ее в качестве выражения для первичного ключа. Во время процесса слияния кусок данных проекции объединяется с помощью процедуры слияния хранилища. Контрольная сумма куска данных родительской таблицы включает кусок данных проекции. Другие процедуры аналогичны индексам пропуска данных.

Анализ запросов​

  1. Проверьте, можно ли использовать проекцию в данном запросе, то есть, что с ней получается тот же результат, что и с запросом к базовой таблице.
  2. Выберите наиболее подходящее совпадение, содержащее наименьшее количество гранул для чтения.
  3. План запроса, который использует проекции, отличается от того, который использует исходные куски данных. Если в некоторых кусках проекции отсутствуют, можно расширить план, чтобы «проецировать» на лету.

Конкурентный доступ к данным​

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

Чтения из таблицы автоматически распараллеливаются.

TTL для столбцов и таблиц​

Определяет время жизни значений.

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

Выражения должны возвращать тип Date или DateTime.

Синтаксис

Для задания времени жизни столбца:

TTL time_column
TTL time_column + interval

Чтобы задать interval, используйте операторы интервала времени, например:

TTL date_time + INTERVAL 1 MONTH
TTL date_time + INTERVAL 15 HOUR

TTL столбца​

Когда срок действия значений в столбце истечёт, ClickHouse заменит их значениями по умолчанию для типа данных столбца. Если срок действия всех значений столбцов в части данных истек, ClickHouse удаляет столбец из куска данных в файловой системе.

Секцию TTL нельзя использовать для ключевых столбцов.

Примеры

Создание таблицы с TTL:

CREATE TABLE example_table
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

Добавление TTL на колонку существующей таблицы:

ALTER TABLE example_table
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 DAY;

Изменение TTL у колонки:

ALTER TABLE example_table
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 MONTH;

TTL таблицы​

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

TTL expr
    [DELETE|RECOMPRESS codec_name1|TO DISK 'xxx'|TO VOLUME 'xxx'][, DELETE|RECOMPRESS codec_name2|TO DISK 'aaa'|TO VOLUME 'bbb'] ...
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ]

За каждым TTL выражением может следовать тип действия, которое выполняется после достижения времени, соответствующего результату TTL выражения:

  • DELETE - удалить данные (действие по умолчанию);
  • RECOMPRESS codec_name - повторно сжать данные с помощью кодека codec_name;
  • TO DISK 'aaa' - переместить данные на диск aaa;
  • TO VOLUME 'bbb' - переместить данные на том bbb;
  • GROUP BY - агрегировать данные.

В секции WHERE можно задать условие удаления или агрегирования устаревших строк (для перемещения и сжатия условие WHERE не применимо).

Колонки, по которым агрегируются данные в GROUP BY, должны являться префиксом первичного ключа таблицы.

Если колонка не является частью выражения GROUP BY и не задается напрямую в секции SET, в результирующих строках она будет содержать случайное значение, взятое из одной из сгруппированных строк (как будто к ней применяется агрегирующая функция any).

Примеры

Создание таблицы с TTL:

CREATE TABLE example_table
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH DELETE,
    d + INTERVAL 1 WEEK TO VOLUME 'aaa',
    d + INTERVAL 2 WEEK TO DISK 'bbb';

Изменение TTL:

ALTER TABLE example_table
    MODIFY TTL d + INTERVAL 1 DAY;

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

CREATE TABLE table_with_where
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH DELETE WHERE toDayOfWeek(d) = 1;

Создание таблицы, в которой куски с устаревшими данными повторно сжимаются:

CREATE TABLE table_for_recompression
(
    d DateTime,
    key UInt64,
    value String
) ENGINE MergeTree()
ORDER BY tuple()
PARTITION BY key
TTL d + INTERVAL 1 MONTH RECOMPRESS CODEC(ZSTD(17)), d + INTERVAL 1 YEAR RECOMPRESS CODEC(LZ4HC(10))
SETTINGS min_rows_for_wide_part = 0, min_bytes_for_wide_part = 0;

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

CREATE TABLE table_for_aggregation
(
    d DateTime,
    k1 Int,
    k2 Int,
    x Int,
    y Int
)
ENGINE = MergeTree
ORDER BY (k1, k2)
TTL d + INTERVAL 1 MONTH GROUP BY k1, k2 SET x = max(x), y = min(y);

Удаление устаревших данных​

Данные с истекшим TTL удаляются, когда ClickHouse мёржит куски данных.

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

Если вы выполните запрос SELECT между слияниями вы можете получить устаревшие данные. Чтобы избежать этого используйте запрос OPTIMIZE перед SELECT.

См. также

Хранение данных таблицы на нескольких блочных устройствах​

Введение​

Движки таблиц семейства MergeTree могут хранить данные на нескольких блочных устройствах. Это может оказаться полезным, например, при неявном разделении данных одной таблицы на «горячие» и «холодные». Наиболее свежая часть занимает малый объём и запрашивается регулярно, а большой хвост исторических данных запрашивается редко. При наличии в системе нескольких дисков, «горячая» часть данных может быть размещена на быстрых дисках (например, на NVMe SSD или в памяти), а холодная на более медленных (например, HDD).

Минимальной перемещаемой единицей для MergeTree является кусок данных (data part). Данные одного куска могут находится только на одном диске. Куски могут перемещаться между дисками в фоне, согласно пользовательским настройкам, а также с помощью запросов ALTER.

Термины​

  • Диск — примонтированное в файловой системе блочное устройство.
  • Диск по умолчанию — диск, на котором находится путь, указанный в конфигурационной настройке сервера path.
  • Том (Volume) — упорядоченный набор равноценных дисков (схоже с JBOD)
  • Политика хранения (StoragePolicy) — множество томов с правилами перемещения данных между ними.

У всех описанных сущностей при создании указываются имена, можно найти в системных таблицах system.storage_policies и system.disks. Имя политики хранения можно указать в настройке storage_policy движков таблиц семейства MergeTree.

Конфигурация​

Диски, тома и политики хранения задаются внутри тега <storage_configuration> в основном файле config.xml или в отдельном файле в директории config.d.

Структура конфигурации:

<storage_configuration>
    <disks>
        <disk_name_1> <!-- disk name -->
            <path>/mnt/fast_ssd/clickhouse/</path>
        </disk_name_1>
        <disk_name_2>
            <path>/mnt/hdd1/clickhouse/</path>
            <keep_free_space_bytes>10485760</keep_free_space_bytes>
        </disk_name_2>
        <disk_name_3>
            <path>/mnt/hdd2/clickhouse/</path>
            <keep_free_space_bytes>10485760</keep_free_space_bytes>
        </disk_name_3>

        ...
    </disks>

    ...
</storage_configuration>

Теги:

  • <disk_name_N> — имя диска. Имена должны быть разными для всех дисков.
  • path — путь по которому будут храниться данные сервера (каталоги data и shadow), должен быть терминирован /.
  • keep_free_space_bytes — размер зарезервированного свободного места на диске.

Порядок задания дисков не имеет значения.

Общий вид конфигурации политик хранения:

<storage_configuration>
    ...
    <policies>
        <policy_name_1>
            <volumes>
                <volume_name_1>
                    <disk>disk_name_from_disks_configuration</disk>
                    <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
                </volume_name_1>
                <volume_name_2>
                    <!-- configuration -->
                </volume_name_2>
                <!-- more volumes -->
            </volumes>
            <move_factor>0.2</move_factor>
        </policy_name_1>
        <policy_name_2>
            <!-- configuration -->
        </policy_name_2>

        <!-- more policies -->
    </policies>
    ...
</storage_configuration>

Тэги:

  • policy_name_N — название политики. Названия политик должны быть уникальны.
  • volume_name_N — название тома. Названия томов должны быть уникальны.
  • disk — диск, находящийся внутри тома.
  • max_data_part_size_bytes — максимальный размер куска данных, который может находиться на любом из дисков этого тома. Если в результате слияния размер куска ожидается больше, чем max_data_part_size_bytes, то этот кусок будет записан в следующий том. В основном эта функция позволяет хранить новые / мелкие куски на горячем (SSD) томе и перемещать их на холодный (HDD) том, когда они достигают большого размера. Не используйте этот параметр, если политика имеет только один том.
  • move_factor — доля доступного свободного места на томе, если места становится меньше, то данные начнут перемещение на следующий том, если он есть (по умолчанию 0.1). Для перемещения куски сортируются по размеру от большего к меньшему (по убыванию) и выбираются куски, совокупный размер которых достаточен для соблюдения условия move_factor, если совокупный размер всех партов недостаточен, будут перемещены все парты.
  • prefer_not_to_merge — Отключает слияние кусков данных, хранящихся на данном томе. Если данная настройка включена, то слияние данных, хранящихся на данном томе, не допускается. Это позволяет контролировать работу ClickHouse с медленными дисками.

Примеры конфигураций:

<storage_configuration>
    ...
    <policies>
        <hdd_in_order> <!-- policy name -->
            <volumes>
                <single> <!-- volume name -->
                    <disk>disk1</disk>
                    <disk>disk2</disk>
                </single>
            </volumes>
        </hdd_in_order>

        <moving_from_ssd_to_hdd>
            <volumes>
                <hot>
                    <disk>fast_ssd</disk>
                    <max_data_part_size_bytes>1073741824</max_data_part_size_bytes>
                </hot>
                <cold>
                    <disk>disk1</disk>
                </cold>
            </volumes>
            <move_factor>0.2</move_factor>
        </moving_from_ssd_to_hdd>

        <small_jbod_with_external_no_merges>
            <volumes>
                <main>
                    <disk>jbod1</disk>
                </main>
                <external>
                    <disk>external</disk>
                    <prefer_not_to_merge>true</prefer_not_to_merge>
                </external>
            </volumes>
        </small_jbod_with_external_no_merges>

    </policies>
    ...
</storage_configuration>

В приведенном примере, политика hdd_in_order реализует прицип round-robin. Так как в политике есть всего один том (single), то все записи производятся на его диски по круговому циклу. Такая политика может быть полезна при наличии в системе нескольких похожих дисков, но при этом не сконфигурирован RAID. Учтите, что каждый отдельный диск ненадёжен и чтобы не потерять важные данные это необходимо скомпенсировать за счет хранения данных в трёх копиях.

Если система содержит диски различных типов, то может пригодиться политика moving_from_ssd_to_hdd. В томе hot находится один SSD-диск (fast_ssd), а также задается ограничение на максимальный размер куска, который может храниться на этом томе (1GB). Все куски такой таблицы больше 1GB будут записываться сразу на том cold, в котором содержится один HDD-диск disk1. Также при заполнении диска fast_ssd более чем на 80% данные будут переноситься на диск disk1 фоновым процессом.

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

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

CREATE TABLE table_with_non_default_policy (
    EventDate Date,
    OrderID UInt64,
    BannerID UInt64,
    SearchPhrase String
) ENGINE = MergeTree
ORDER BY (OrderID, BannerID)
PARTITION BY toYYYYMM(EventDate)
SETTINGS storage_policy = 'moving_from_ssd_to_hdd'

По умолчанию используется политика хранения default в которой есть один том и один диск, указанный в <path>. Изменить политику хранения после создания таблицы можно при помощи запроса [ALTER TABLE ... MODIFY SETTING]. При этом необходимо учесть, что новая политика должна содержать все тома и диски предыдущей политики с теми же именами.

Количество потоков для фоновых перемещений кусков между дисками можно изменить с помощью настройки background_move_pool_size

Особенности работы​

В таблицах MergeTree данные попадают на диск несколькими способами:

  • В результате вставки (запрос INSERT).
  • В фоновых операциях слияний и мутаций.
  • При скачивании данных с другой реплики.
  • В результате заморозки партиций ALTER TABLE … FREEZE PARTITION.

Во всех случаях, кроме мутаций и заморозки партиций, при записи куска выбирается том и диск в соответствии с указанной конфигурацией хранилища:

  1. Выбирается первый по порядку том, на котором есть свободное место для записи куска (unreserved_space > current_part_size) и который позволяет записывать куски требуемого размера max_data_part_size_bytes > current_part_size.
  2. Внутри тома выбирается следующий диск после того, на который была предыдущая запись и на котором свободного места больше чем размер куска (unreserved_space - keep_free_space_bytes > current_part_size)

Мутации и запросы заморозки партиций в реализации используют жесткие ссылки. Жесткие ссылки между различными дисками не поддерживаются, поэтому в случае таких операций куски размещаются на тех же дисках, что и исходные.

В фоне куски перемещаются между томами на основе информации о занятом месте (настройка move_factor) по порядку, в котором указаны тома в конфигурации. Данные никогда не перемещаются с последнего тома и на первый том. Следить за фоновыми перемещениями можно с помощью системных таблиц system.part_log (поле type = MOVE_PART) и system.parts (поля path и disk). Также подробная информация о перемещениях доступна в логах сервера. С помощью запроса ALTER TABLE … MOVE PART|PARTITION … TO VOLUME|DISK … пользователь может принудительно перенести кусок или партицию с одного раздела на другой. При этом учитываются все ограничения, указанные для фоновых операций. Запрос самостоятельно инициирует процесс перемещения не дожидаясь фоновых операций. В случае недостатка места или неудовлетворения ограничениям пользователь получит сообщение об ошибке.

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

После выполнения фоновых слияний или мутаций старые куски не удаляются сразу, а через некоторое время (табличная настройка old_parts_lifetime). Также они не перемещаются на другие тома или диски, поэтому до момента удаления они продолжают учитываться при подсчёте занятого дискового пространства.

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

Использование сервиса S3 для хранения данных​

Таблицы семейства MergeTree могут хранить данные в сервисе S3 при использовании диска типа s3.

Конфигурация:

<storage_configuration>
    ...
    <disks>
        <s3>
            <type>s3</type>
            <endpoint>https://storage.yandexcloud.net/my-bucket/root-path/</endpoint>
            <access_key_id>your_access_key_id</access_key_id>
            <secret_access_key>your_secret_access_key</secret_access_key>
            <region></region>
            <proxy>
                <uri>http://proxy1</uri>
                <uri>http://proxy2</uri>
            </proxy>
            <connect_timeout_ms>10000</connect_timeout_ms>
            <request_timeout_ms>5000</request_timeout_ms>
            <retry_attempts>10</retry_attempts>
            <single_read_retries>4</single_read_retries>
            <min_bytes_for_seek>1000</min_bytes_for_seek>
            <metadata_path>/var/lib/clickhouse/disks/s3/</metadata_path>
            <cache_enabled>true</cache_enabled>
            <cache_path>/var/lib/clickhouse/disks/s3/cache/</cache_path>
            <skip_access_check>false</skip_access_check>
        </s3>
    </disks>
    ...
</storage_configuration>

Обязательные параметры:

  • endpoint — URL точки приема запроса на стороне S3 в форматах path или virtual hosted. URL точки должен содержать бакет и путь к корневой директории на сервере, где хранятся данные.
  • access_key_id — id ключа доступа к S3.
  • secret_access_key — секретный ключ доступа к S3.

Необязательные параметры:

  • region — название региона S3.
  • use_environment_credentials — признак, нужно ли считывать учетные данные AWS из сетевого окружения, а также из переменных окружения AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY и AWS_SESSION_TOKEN, если они есть. Значение по умолчанию: false.
  • use_insecure_imds_request — признак, нужно ли использовать менее безопасное соединение при выполнении запроса к IMDS при получении учётных данных из метаданных Amazon EC2. Значение по умолчанию: false.
  • proxy — конфигурация прокси-сервера для конечной точки S3. Каждый элемент uri внутри блока proxy должен содержать URL прокси-сервера.
  • connect_timeout_ms — таймаут подключения к сокету в миллисекундах. Значение по умолчанию: 10 секунд.
  • request_timeout_ms — таймаут выполнения запроса в миллисекундах. Значение по умолчанию: 5 секунд.
  • retry_attempts — число попыток выполнения запроса в случае возникновения ошибки. Значение по умолчанию: 10.
  • single_read_retries — число попыток выполнения запроса в случае возникновения ошибки в процессе чтения. Значение по умолчанию: 4.
  • min_bytes_for_seek — минимальное количество байтов, которые используются для операций поиска вместо последовательного чтения. Значение по умолчанию: 1 МБайт.
  • metadata_path — путь к локальному файловому хранилищу для хранения файлов с метаданными для S3. Значение по умолчанию: /var/lib/clickhouse/disks/<disk_name>/.
  • cache_enabled — признак, разрешено ли хранение кэша засечек и файлов индекса в локальной файловой системе. Значение по умолчанию: true.
  • cache_path — путь в локальной файловой системе, где будут храниться кэш засечек и файлы индекса. Значение по умолчанию: /var/lib/clickhouse/disks/<disk_name>/cache/.
  • skip_access_check — признак, выполнять ли проверку доступов при запуске диска. Если установлено значение true, то проверка не выполняется. Значение по умолчанию: false.

Диск S3 может быть сконфигурирован как main или cold:

<storage_configuration>
    ...
    <disks>
        <s3>
            <type>s3</type>
            <endpoint>https://storage.yandexcloud.net/my-bucket/root-path/</endpoint>
            <access_key_id>your_access_key_id</access_key_id>
            <secret_access_key>your_secret_access_key</secret_access_key>
        </s3>
    </disks>
    <policies>
        <s3_main>
            <volumes>
                <main>
                    <disk>s3</disk>
                </main>
            </volumes>
        </s3_main>
        <s3_cold>
            <volumes>
                <main>
                    <disk>default</disk>
                </main>
                <external>
                    <disk>s3</disk>
                </external>
            </volumes>
            <move_factor>0.2</move_factor>
        </s3_cold>
    </policies>
    ...
</storage_configuration>

Если диск сконфигурирован как cold, данные будут переноситься в S3 при срабатывании правил TTL или когда свободное место на локальном диске станет меньше порогового значения, которое определяется как move_factor * disk_size.

Виртуальные столбцы​

  • _part — Имя куска.
  • _part_index — Номер куска по порядку в результате запроса.
  • _partition_id — Имя партиции.
  • _part_uuid — Уникальный идентификатор куска (если включена MergeTree настройка assign_part_uuids).
  • _partition_value — Значения (кортеж) выражения partition by.
  • _sample_factor — Коэффициент сэмплирования (из запроса).