Заметки про ClickHouse. Tutorial 101 — Большая подборка информации

Данная статья — это подборка заметок про ClickHouse, которые я формировал в процессе прохождения курсов с сайта learn.clickhouse.com, чтения официальной документации, а также разных статей в интернете.

Contents

Первичные ключи в ClickHouse

  • Первичные ключи в ClickHouse не уникальны для каждой строки таблицы.
  • Первичный ключ таблицы ClickHouse определяет, как сортируются данные при записи на диск.
  • Индекс, созданный с помощью первичного ключа, не содержит записи для каждой строки, а вместо этого содержит запись для каждых 8192 строк (так называемый index granularity).

Эта концепция granularity создает разреженный индекс (sparse index), который легко помещается в памяти. Granules (гранулы) представляют собой полосу наименьшего объема данных столбца, который обрабатывается во время запросов SELECT.

ПРИМЕЧАНИЕ. Другими словами, если вы запустите SELECT для одной строки, поток (thread), обрабатывающий этот запрос, обработает 8192 строки. Но имейте в виду, что при анализе больших наборов данных извлечение отдельных строк встречается нечасто. И даже если вам нужна одна строка, ClickHouse работает настолько быстро, что компромисс в производительности незаметен.

Определение первичного ключа

Давайте посмотрим на простой пример. Предположим, у вас есть таблица с двумя столбцами — целым числом и строкой фиксированной длины 1 (другими словами, символом):

ПРИМЕЧАНИЕ. Столбец x является первичным ключом, а также порядком сортировки. Фактически, вам не нужно указывать первичный ключ в ClickHouse, если в вашей таблице есть предложение ORDER BY. (Первичным ключом по умолчанию будет предложение ORDER BY , если вы не укажете предложение PRIMARY KEY). Следующее определение helloworld идентично приведенному выше:

Давайте заполним таблицу с помощью функции numbers, которая генерирует последовательность чисел, начиная с 0 и заканчивая любым аргументом, который вы передаете. Следующий оператор вставляет 10 000 000 строк в таблицу helloworld:

Значения x — это просто целые числа от 0 до 10 миллионов, а значения y — случайные символы между «a» и «z».

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

Понимание granules (гранул)

Таблица helloworld имеет 10 миллионов строк, поэтому таблица логически разбита на 10 000 000/8 192 = 1 221 гранулу, причем каждая гранула состоит из 8 192 строк — за исключением последней гранулы, в которой всего 5 760 строк (не очень часто такое количество строки будут состоять из чисел, кратных 8192).

Гранула — это наименьший неделимый набор данных, который ClickHouse считывает при поиске по строкам. Это означает, что вместо чтения отдельных строк ClickHouse всегда читает (потоково и параллельно) целую группу (гранулу) строк.

Значения столбцов физически не хранятся внутри гранул: гранулы — это просто логическая организация значений столбцов для обработки запросов.

Первичный индекс имеет одну запись на гранулу.

Первичный индекс создается на основе гранул. Этот индекс представляет собой несжатый файл плоского массива (primary.idx), содержащий так называемые числовые индексные метки, начинающиеся с 0.

Индекс первичного ключа для этой таблицы содержит только первую строку каждой гранулы, поэтому он будет содержать запись, указывающую на первую строку (0, ‘u’), 8193-ю строку (8192, ‘d’), 16385 -я строка (16384,’n’) и так далее.

В чем преимущество этого редкого первичного индекса? Предположим, вы ищете определенную строку:

В зависимости от того, как сортируются ваши данные, ClickHouse знает, что результат вышеуказанного запроса SELECT должен находиться в грануле 3, и, используя индекс первичного ключа, ClickHouse знает, как быстро получить строки из гранулы 3, так что это единственная гранула, которая обрабатывается для вашего запроса. В ответе видно, что была обработана только 1 гранула:

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

Поскольку вы фильтруете только по y, вам необходимо обработать все 10 миллионов строк:

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

ПРИМЕЧАНИЕ. Если вы запускаете больше запросов с фильтрацией по y , чем по любому другому столбцу, то y должен быть первой записью в кортеже первичного ключа.

Дизайн индекса для больших объемов

Предположим у нас есть таблица с 8,87 миллионами записей.

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

Поиск записи в B(+)-Tree структуре данных имеет среднюю временную сложность O(log n); точнее, log_b n = log_2 n / log_2 b где b– коэффициент ветвления B(+)-Tree и n – количество индексированных строк. Поскольку bобычно их размер составляет от нескольких сотен до нескольких тысяч, B(+)-Trees это очень мелкие структуры, и для поиска записей требуется несколько операций поиска по диску. При 8,87 миллионах строк и коэффициенте ветвления 1000 в среднем требуется 2,3 обращения к диску. За эту возможность приходится платить: дополнительные накладные расходы на диск и память, более высокие затраты на вставку при добавлении новых строк в таблицу и записей в индекс, а иногда и на перебалансировку B-дерева.

Учитывая проблемы, связанные с индексами B-Tree, табличные движки в ClickHouse используют другой подход. Семейство движков ClickHouse MergeTree было разработано и оптимизировано для обработки огромных объемов данных. Эти таблицы предназначены для приема миллионов вставок строк в секунду и хранения очень больших (сотни петабайт) объемов данных. Данные быстро записываются в таблицу по частям, при этом правила объединения частей применяются в фоновом режиме. В ClickHouse каждая часть имеет свой первичный индекс. Когда части объединяются, первичные индексы объединенной части также объединяются. В очень больших масштабах, для которых предназначен ClickHouse, крайне важно максимально эффективно использовать диск и память. Таким образом, вместо индексации каждой строки первичный индекс для части имеет одну запись индекса (известную как «mark» или «метка») на группу строк (называемую «granule» или «гранула») — этот метод называется разреженным индексом .

Разреженная индексация возможна, поскольку ClickHouse хранит строки для части на диске, упорядоченные по столбцам первичного ключа. Вместо непосредственного поиска отдельных строк (например, индекса на основе B-дерева) разреженный первичный индекс позволяет быстро (посредством двоичного поиска по записям индекса) идентифицировать группы строк, которые могут соответствовать запросу. Найденные группы потенциально совпадающих строк (гранул) затем параллельно передаются в механизм ClickHouse для поиска совпадений. Такая конструкция индекса позволяет сделать основной индекс небольшим (он может и должен полностью помещаться в основную память), в то же время значительно ускоряя время выполнения запроса: особенно для запросов диапазона, которые типичны для сценариев использования анализа данных.

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

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

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

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

На втором этапе (чтение данных) ClickHouse находит выбранные гранулы, чтобы передать все их строки в механизм ClickHouse, чтобы найти строки, которые действительно соответствуют запросу.

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

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

Пример определения Primary Key:

Если определяется одновременно PRIMARY KEY и ORDER BY, то PRIMARY KEY должен обязательно являться префиксом для ORDER BY tuple:

PRIMARY KEY не может содержать меньше полей, чем ORDER BY.

Primary indexes должны помещаться в память, иначе ClickHouse не сможет запуститься или сервис может упасть.

Кейсы, когда может понадобиться создание дополнительных Primary Indexes

  • Создание двух таблиц для одинаковых данных.
  • Использование Projection — вы используете одну таблицу, но ClickHouse создает скрытую таблицу, которая хранит данные сортированные другим способом.
  • Использование materialized view:
    • Данные хранятся в отдельной таблице на основе SELECT запроса.
    • Сортировка данных выполняется на основе SELECT запроса.
  • Определяется skipping index

Системные базы данных

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

База данных INFORMATION_SCHEMA (с псевдонимом information_schema) содержит набор представлений стандарта ANSI, содержащих информацию о метаданных объектов базы данных (system.columns, system.databases и system.tables).

Low cardinality колонки (Enum и LowCardinality типы данных)

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

  • Используйте тип данных Enum для определения перечисления.
  • Оберните столбец в тип LowCardinality.

Enumerations

Начнем с примера перечисления. Значения Enum имеют формат string=integer. Целочисленное значение сохраняется, но в запросах вы используете строковые значения. Вот простой пример столбца device_type, где возможными типами устройств являются только «сервер», «контейнер» или «маршрутизатор»:

Значения device_type вставляются в виде строк:

Ошибка возникает, если вы попытаетесь вставить строку, которая не является частью перечисления:

Используйте столбец Enum как строку в запросах:

Примечание.

Enumerations — это здорово, но у них есть один недостаток — в них нельзя добавлять значения без ALTER TABLE. Если вы точно знаете, какие значения может принимать столбец, то Enum — хорошее решение. Если у вас есть столбец, значения которого различаются, но общая уникальность значений низкая, вместо этого рассмотрите вариант LowCardinality.

LowCardinality

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

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

Пример

Как ClickHouse хранит данные используя parts и primary keys

Еще раз про primary keys

  • Table Engine определяет как и где данные хранятся в ClickHouse
  • Если Primary Key не определен явно, то он будет сформирован на основе условия Order By
  • Primary Key не уникальный для каждой строки
  • Используйте для Primary Key те колонки, по которым чаще всего выполняется поиск

Как вставляются данные в таблицу

  • Вставки выполняются в виде bulk
  • Каждый bulk INSERT создает part (part сохраняется в его собственную директорию)
  • Part может содержать десятки тысяч или даже миллионов строк

Part, part folder, bulk insert, merging of parts

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

Каждый столбец в «части» имеет неизменяемый файл, содержащий только данные столбца.

  • granule — логическое разбиение строк внутри несжатого блока (по умолчанию 8192 строки);
  • primary key — порядок сортировки таблицы;
  • primary index — in-memory index, состоящий из значений primary keys первой строки каждой гранулы;
  • part — директория с файлами, состоящая из column files и index file подмножества данных таблицы.

Каждый bulk insert создает part

Со временем части можно объединить в фоновом режиме. Вот почему она называется таблицей MergeTree.

После мержинга, неиспользуемые parts удаляются:

Смерженные parts продолжают мержиться:

Partitions (Партиции) в ClickHouse

Partition (партиция) — это логическое объединение записей таблицы по заданному критерию. При доступе к данным ClickHouse использует минимально возможное подмножество разделов.

Пример определения партиции при создании таблицы (партиция по YYYYMM):

  • В большинстве случаев, вам не нужен ключ партицирования. Лучше используйте вместо партиций primary key.
  • В большинстве других случаев, вам не нужен partition key с большей гранулярностью, чем месяц.
    • Никогда не используйте большое число партиций.
    • Параметр max_partitions_per_insert_block по умолчанию равен 100, самое большое число партиций во вставляемом блоке, которое вы можете иметь.

Для чего используются в таком случае partitions (партиции) в ClickHouse?

Data Management:

  • Если partition key также является первым ключом в primary key, это потенциально улучшает сжатие.
  • Существует целый набор операций, которые можно выполнять над партициями (Detach Partition, Drop, Replace, Move, Freeze, Update, Attach и т.д.). То есть мы можем обновлять данные в таблице на основе партиций.

Merging partitions

  • Partitions доступны только для семейства MergeTree engines, а также для Materialized Views, которые основаны на MergeTree Family Tables.
  • Объединяются только части, имеющие одинаковый partition key:
    • Вы увидите это, когда посмотрите на папки, созданные для каждой part.
    • Значение partition key отображается в именах папок parts.

Хранение данных в parts с учетом partition key

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

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

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

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

Parts, относящиеся к разным партициям не объединяются. Механизм слияния не гарантирует, что все строки с одинаковым первичным ключом окажутся в одном куске.

Parts данных могут храниться в формате Wide или Compact.

  • В формате Wide каждый столбец хранится в отдельном файле,
  • а в формате Compact все столбцы хранятся в одном файле.

Формат Compact может быть полезен для повышения производительности при частом добавлении небольших объемов данных.

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

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

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

Логика хранения данных MergeTree Table в ClickHouse

На следующем рисунке показана логика структуры хранения таблицы MergeTree:

Файлы данных: action_id.bin , avatar_id.bin
Файлы с идентификаторами меток: action_id.mrk2 , avatar_id.mrk2
Индекс первичного ключа: primary.idx
Индекс ключа раздела: minmax_regionidx и minmax_region_name.idx
Индекс пропуска: skp_idx_avatar_id_minmax.idx

Взаимосвязь между объектами выглядит примерно следующим образом:

Deduplication (Дедупликация)

В этом разделе описываются варианты дедупликации данных в ClickHouse:

  • как реализовать дедупликацию в ClickHouse с помощью механизма таблиц ReplacingMergeTree, и как использовать этот механизм таблиц для реализации решения upsert.
  • как реализовать дедупликацию в ClickHouse с помощью механизма таблиц CollapsingMergeTree, который удаляет строки, которые «отменяют» друг друга, с помощью специального sign column.
  • как реализовать дедупликацию в ClickHouse с помощью механизмов таблиц VersionedCollapsingMergeTree.

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

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

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

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

1. ReplacingMergeTree table engine

Благодаря этому table engine во время слияний удаляются повторяющиеся строки с одинаковым ключом сортировки (sorting key). ReplacingMergeTree — хороший вариант для эмуляции поведения upsert (когда вы хотите, чтобы запросы возвращали последнюю вставленную строку).

2. Collapsing rows (Свертывание строк)

Engines таблиц CollapsingMergeTree и VersionedCollapsingMergeTree используют логику, при которой существующая строка «отменяется» и вставляется новая строка.

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

  • Для таблицы ReplacingMergeTree последняя вставленная строка — это строка, которая не удалена.
  • Для таблицы CollapsingMergeTree используется столбец sign типа Int8, которому присвоено значение 1 или -1. Чтобы отменить строку, вы вставляете строку с тем же ключом сортировки, но меняете столбец знака на -1.

ClickHouse Server Architecture

ClickHouse single node query model

Обработка запросов ClickHouse

Высокая скорость запросов в ClickHouse обычно достигается за счет правильного использования (разреженного) первичного индекса таблицы, чтобы радикально ограничить объем данных, которые ClickHouse необходимо прочитать с диска, и предотвратить повторное использование данных во время запроса, что также может позволить сократить время выполнения запроса.

Конструкция первичного индекса ClickHouse основана на алгоритме двоичного поиска , который эффективно ( временная сложность O(log2 n)) находит положение целевого значения в отсортированном массиве.

На диаграмме показано, как ClickHouse обычно выполняет запрос:

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

Для ускорения рабочего процесса выполнения запросов в ClickHouse есть три основных параметра настройки:

Чем меньше данных ClickHouse необходимо передать с диска в основную память, тем быстрее будет выполняться запрос. Объем данных, необходимых для потоковой передачи с диска, можно свести к минимуму путем (1) правильного использования первичных индексов и (2) предварительного вычисления агрегатов.
Потоковую передачу и фактическую обработку данных можно ускорить за счет (3) увеличения уровня параллелизма, используемого внутри механизма обработки запросов ClickHouse.

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

Еще раз про хранение данных и выполнение запроса внутри ClickHouse

Clickhouse хранит данные на диске, отсортированные по первичному ключу PRIMARY KEY, а затем по элементам ключа сортировки ORDER BY. Если первичный ключ не указан, он будет состоять из элементов ключа сортировки.

Вот как данные сохраняются во время вставки:

Для обработки данных ClickHouse делит столбцы на «гранулы» (сжатые), это наименьший неделимый набор данных для обработки данных:

При создании таблицы свойство SETTINGS index_granularity = 8192; (значение по умолчанию) позволяет указать размер гранул. Очевидно, что увеличение или уменьшение этого значения может повлиять на производительность запроса.

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

Clickhouse монтирует основной индексный файл в память (если памяти недостаточно, Clickhouse сообщит об ошибке)

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

Log

Explain

Как только гранула будет идентифицирована, Clickhouse должен знать физическое местоположение гранулы 1.

В файле markхранятся все физические местоположения «гранул»; один файл отметок на столбец:

Благодаря этим файлам mrk Clickhouse может найти блок в сжатом файле (.bin), следовательно, он может распаковать его в памяти, а затем, благодаря , granule_offset найти гранулу(ы), которые соответствуют:

Уровень параллелизма, используемый в конвейере запросов ClickHouse, задается с помощью параметра max_threads = 2. По умолчанию max_threads равно количеству ядер ЦП, которое ClickHouse видит на машине, на которой он работает.

Как работать с Parts и Partitions в ClickHouse. Что это такое?

Part — это физический файл на диске, в котором хранится часть данных таблицы.
Partition (партиция) — это логическое разделение данных таблицы, созданное с использованием Partition Key.

Партицирование данных (Partitioning) используется для повышения производительности и управляемости большими таблицами, позволяя пользователям более эффективно осуществлять операции хранения, обращения к подмножеству данных и их манипулированием.

Parts — Части таблиц ClickHouse

Parts — это физические файлы на диске. По умолчанию все файлы, связанные с данными, находятся в каталоге /var/lib/clickhouse. Каждая таблица Merge Tree в ClickHouse имеет уникальный путь к каталогу для хранения частей. Вы можете получить доступ к фактическому расположению частей, именам частей, информации о разделах (если таковые имеются) и некоторой другой ценной информации из таблицы system.parts.

Ниже показан пример результата запроса из таблицы system.parts. Здесь в part_type Wide означает, что каждый столбец хранится в отдельном файле в файловой системе. С другой стороны, Compact означает, что все столбцы хранятся в одном файле файловой системы. Также в partition столбце tuple() — означает, что эта таблица не партицирована.

Также можно получить доступ к parts таблицы в папке /var/lib/clickhouse/data/<DBNAME>/<TABLENAME> в виде символической ссылки.

Таблица opensky в database «sampleDatasets» состоит из четырех parts. Каждая part имеет свою собственную директорию, а их имена начинаются с all_. Если говорить о части all_3_3_0_5, то:

  • 3 — минимальный номер блока данных (data block).
  • 3 — максимальный номер блока данных (data block).
  • 0 — chunk level (глубина дерева слияния, из которого он сформирован).
  • 5 — версия мутации (если часть мутирована).

Другой вариант получения этой информации: из таблицы system.parts

Partitions — Партиции таблиц ClickHouse

Получить доступ к информации о Partitions таблиц Merge Tree можно из таблицы system.parts. Однако столбец Partition теперь представлен как нечто иное, чем tuple().

Для создания партицированной таблицы (partitioned table), прежде всего, вам необходимо использовать PARTITION BY expr при создании таблицы.

Например, предложение PARTITION BY toYYYYMMDD(start_time) создает ежедневную партицию на основе столбца start_time.

В следующем примере имена партиций и частей различаются. Кроме того, имена частей не начинаются со слова all, а начинаются с индикатора partition.
Partitioning — это что-то вроде логического разделения, но части располагаются в виде физических файлов. Одна партиция может содержать одну или несколько частей.

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

Чтобы обратиться к определенной партиции, необходимо указать условия в предложении where или использовать скрытый столбец _partition_id.
Лучшим способом является обращение к партиции в условии WHERE. Но в некоторых случаях нам нужно использовать _partition_id.

Пример запроса к партицированной таблице.
Предположим, наша таблица (recoDB.opensky_partitioned) партицирована по столбцу lastseen. Мы можем получить доступ к определенным партициям, как с помощью столбца partition key, так и скрытого столбца _partition_id. Также мы можем запросить top 10 partition с помощью _partition_id.

еще пример

Как партицирование данных может улучшить или снизить производительность

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

Операции манипулирования частями и партициями Таблицы (Manipulating Partitions and Parts)

Под манипулированием подразумеваются операции создания, изменения или удаления Parts/Partitions в таблице ClickHouse.

Примечание: Далее будет рассмотрен пример с таблицей opensky_partitioned, которая партицирована с помощью выражения PARTITION BY toYYYYMMDD(lastseen) в базе данных sampleDatasets.

DETACH/ATTACH PARTITION|PART — Операции отсоединения и присоединения партиции или части

С помощью операции DETACH указанные Parts или Partitions можно переместить в папку detached. Пользователь не может получить доступ к связанным Parts или Partitions, пока они присоеденены (attached). По умолчанию detached папка находится в каталоге /var/lib/clickhouse/data/<DATABASE_NAME>/<TABLE_NAME>.

С другой стороны, операция ATTACH — это присоединение частей или разделов из detached папки. Синтаксис операций DETACH и ATTACH в ClickHouse показан ниже:

Здесь partition expression должно быть имя PARTITION, идентификатор PARTITION или само выражение.

Предположим, мы хотим отсоединить партиции с указанными днями (в нашем примере мы будем использовать данные за 15.04.2019) из таблицы opensky_partitioned.

Сначала мы обнаружили партицию и имя части на данную дату. Как видите, эта дата находится в партиции «20190415», которая состоит из двух parts.

Затем DETACH партицию «20190415» и выполним тот же тест с одной из parts.

Как ранее уже описывалось, партиции представляют собой логическое разделение таблиц и включают в себя хотя бы одну часть. В нашем примере партиция 20190415 состоит из двух parts. Пока партиции отсоеденены (detached), партиции все эти части перемещаются в папку /var/lib/clickhouse/data/<DATABASE_NAME>/<TABLE_NAME>/detached.

Теперь мы ATTACH партицию 20190415 следующим образом:

Давайте проведем тот же тест для part.

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

Для запуска этой команды таблицы источника и назначения имеют одно и то же:

  • Одинаковая структура таблиц.
  • Partition key, тот же order by kye и тот же primary key.
  • Политика хранения (Storage policy).

DROP PARTITION/PART

DROP удаляет указанную Part или Partition из таблицы. Удаленная part или partition помечается как неактивный в таблице system.parts.

Эта часть/партиция остается в папке /var/lib/clickhouse/data/<имя_базы_данных>/<имя_таблицы>/ еще примерно в течение 10 минут после операции удаления.

Вы можете найти синтаксис команды drop и пример операции удаления, как показано ниже:

MOVE PARTITION/PART

Вы можете перемещать партиции в другую таблицу в Clickhouse. В этом случае исходная и целевая таблицы имеют одинаковую структуру, partition by key, order by key, primary key, политику хранения и engine family.

Другой вариант перемещения — перемещение part или partition на другой диск или том для таблиц merge tree engine.

Пример перемещения партиции в другую таблицу:

Для перемещения part или partition на другой диск или том нам необходимо установить политику хранения и создать новую таблицу с этой политикой.

Использование JOIN в ClickHouse. Типы объединений, оптимизация

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

Синтаксис

Выражения из секции ON и столбцы из секции USING называются «ключами соединения».

Поддерживаемые типы соединения

Все типы из стандартного SQL JOIN поддерживаются:

  • INNER JOIN, возвращает только строки с совпадающими значениями в обеих таблицах.
  • LEFT OUTER JOIN, возвращает все строки из левой таблицы и соответствующие строки из правой таблицы. Если в правой таблице нет соответствующих строк, результат будет иметь значения NULL для столбцов из правой таблицы.
  • RIGHT OUTER JOIN, возвращает все строки из правой таблицы и соответствующие строки из левой таблицы. Если в левой таблице нет соответствующих строк, результат будет иметь значения NULL для столбцов из левой таблицы.
  • FULL OUTER JOIN, возвращает все строки из обеих таблиц. Если в одной из таблиц нет соответствующих строк, результат будет иметь значения NULL для столбцов из этой таблицы.
  • CROSS JOIN, производит декартово произведение таблиц целиком, ключи соединения не указываются.

Без указания типа JOIN подразумевается INNER. Ключевое слово OUTER можно опускать. Альтернативным синтаксисом для CROSS JOIN является указание нескольких таблиц, разделённых запятыми, в секции FROM.

Обычно на практике (в аналитических задачах) используется Left Join и Inner Join. Их легче всего понять и проще делать отладку.

Дополнительные типы соединений, доступные в ClickHouse:

  • LEFT SEMI JOIN и RIGHT SEMI JOIN, белый список по ключам соединения, не производит декартово произведение.
  • LEFT ANTI JOIN и RIGHT ANTI JOIN, черный список по ключам соединения, не производит декартово произведение.
  • LEFT ANY JOIN, RIGHT ANY JOIN и INNER ANY JOIN, Частично (для противоположных сторон LEFT и RIGHT) или полностью (для INNER и FULL) отключает декартово произведение для стандартных видов JOIN.
  • ASOF JOIN и LEFT ASOF JOIN, Для соединения последовательностей по нечеткому совпадению.

Если настройка join_algorithm установлена в значение partial_merge, то для RIGHT JOIN и FULL JOIN поддерживается только уровень строгости ALL (SEMI, ANTI, ANY и ASOF не поддерживаются).

Ограничения по памяти при использовании JOIN

По умолчанию ClickHouse использует алгоритм hash join. ClickHouse берет правую таблицу и создает для нее хеш-таблицу в оперативной памяти. При включённой настройке join_algorithm = 'auto', после некоторого порога потребления памяти ClickHouse переходит к алгоритму merge join.

Если вы хотите ограничить потребление памяти во время выполнения операции JOIN, используйте настройки:

  • max_rows_in_join — ограничивает количество строк в хеш-таблице.
  • max_bytes_in_join — ограничивает размер хеш-таблицы.

По достижении любого из этих ограничений ClickHouse действует в соответствии с настройкой join_overflow_mode.

CROSS JOIN

CROSS JOIN создает полное декартово произведение двух таблиц без учета ключей соединения. Каждая строка левой таблицы объединяется с каждой строкой правой таблицы.

LEFT / RIGHT SEMI JOIN

Запрос LEFT SEMI JOIN возвращает значения столбцов для каждой строки из левой таблицы, которая имеет хотя бы одно совпадение ключа соединения в правой таблице. Возвращается только первое найденное совпадение (декартово произведение отключено).

Запрос RIGHT SEMI JOIN аналогичен и возвращает значения для всех строк из правой таблицы, имеющих хотя бы одно совпадение в левой таблице, но возвращается только первое найденное совпадение.

LEFT / RIGHT ANTI JOIN

LEFT ANTI JOIN возвращает значения столбцов для всех несовпадающих строк из левой таблицы.

Аналогично, RIGHT ANTI JOIN возвращает значения столбцов для всех несовпадающих правых строк таблицы.

LEFT / RIGHT / INNER ANY JOIN

LEFT ANY JOIN — это комбинация LEFT OUTER JOIN + LEFT SEMI JOIN, что означает, что ClickHouse возвращает значения столбца для каждой строки из левой таблицы либо в сочетании со значениями столбца соответствующей строки из правой таблицы, либо в сочетании со значениями по умолчанию. значения столбцов для правой таблицы, если совпадений не существует. Если строка из левой таблицы имеет более одного совпадения в правой таблице, ClickHouse возвращает только объединенные значения столбца из первого найденного совпадения (декартово произведение отключено).

Аналогично, RIGHT ANY JOIN представляет собой комбинацию RIGHT OUTER JOIN + RIGHT SEMI JOIN.

А INNER ANY JOIN — это INNER JOIN с отключенным декартовым произведением.

ASOF JOIN

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

Предложение ON в предложении ASOF JOIN является обязательным и определяет условие точного соответствия рядом с условием неточного соответствия в предложении AND.

ClickHouse в настоящее время (пока) не поддерживает соединения без строгого соответствия какой-либо части ключей соединения.

Что необходимо учитывать при работе с JOIN в ClickHouse

При работе с объединениями в ClickHouse следует помнить о нескольких вещах, чтобы обеспечить хорошую производительность:

  • Выберите правильный тип соединения. Различные типы соединений могут иметь разные характеристики производительности в зависимости от данных и запроса. В общем, inner joins (внутренние соединения), как правило, выполняются быстрее, чем outer joins (внешние соединения), а соединения подзапросов (subquery joins) иногда могут быть быстрее, чем обычные соединения (regular joins).
  • Используйте соответствующие ключи соединения. Ключи соединения — это столбцы, которые используются для сопоставления строк между двумя таблицами. Выбор правильных ключей соединения важен для хорошей производительности. В идеале ключи соединения должны быть индексированы и иметь низкую мощность (т.е. небольшое количество различных значений).
  • Рассмотрите возможность денормализации. В некоторых случаях может оказаться полезным денормализовать данные путем объединения нескольких таблиц в одну. Это может полностью исключить необходимость в соединениях и повысить производительность запросов.
  • Используйте соответствующие типы данных. Типы данных ключей соединения также могут влиять на производительность. Например, использование строкового типа данных для ключа соединения может быть медленнее, чем использование целочисленного типа данных.
  • Оптимизируйте свои запросы. Наконец, важно оптимизировать ваши запросы для повышения производительности. Это может включать использование соответствующих индексов, минимизацию объема данных, которые необходимо прочитать, и избежание ненужных вычислений.

Рекомендации по использованию индексов при выполнении JOIN

Вот несколько рекомендаций по использованию индексов с соединениями в ClickHouse:

  • Используйте индексы первичного ключа. Если у вас есть первичный ключ в столбцах ключей соединения, ClickHouse автоматически будет использовать этот индекс для выполнения соединения. Это может быть намного быстрее, чем полное сканирование таблицы.
  • Рассмотрите возможность использования вторичных индексов. Если у вас нет первичного ключа в столбцах ключей соединения, вы можете создать вторичный индекс, чтобы повысить производительность соединения. Однако имейте в виду, что вторичные индексы могут снизить производительность записи и увеличить требования к хранилищу.
  • Используйте растровые индексы. Растровые индексы могут быть полезны для оптимизации объединений столбцов с низкой мощностью (т.е. с небольшим количеством различных значений). Битовые индексы могут работать намного быстрее, чем обычные индексы для столбцов этих типов.
  • Избегайте ненужных столбцов. При выполнении объединения лучше выбирать только те столбцы, которые вам действительно нужны. Это может уменьшить объем данных, которые необходимо прочитать, и повысить производительность.

Что такое алгоритмы Join? Как работают алгоритмы Join «под капотом»

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

ClickHouse позволяет пользователям самостоятельно указать желаемый алгоритм соединения.

На диаграмме представлен обзор алгоритмов на основе их относительного потребления памяти и времени выполнения:

На следующей диаграмме показано потребление памяти и время выполнения всех запросов на join (с max_threads, установленным на 30, и большой таблицей справа):

Дерево решений для выбора правильного алгоритма соединения, когда основным критерием является максимально быстрое выполнение соединений:

Алгоритм Direct Join

Алгоритм прямого соединения может применяться, когда базовое хранилище для правой таблицы поддерживает запросы значений ключа с низкой задержкой. ClickHouse имеет три движка таблиц , обеспечивающих это: Join (по сути, это заранее рассчитанная хэш-таблица ), EmbeddedRocksDB и Dictionary.

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

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

Алгоритм Hash join (Хэш-соединение)

Является быстрым и наиболее универсальным. Алгоритм хеш-соединения привязан к памяти.

Хеш -таблица в памяти может обслуживать 250 миллионов совершенно случайных запросов в секунду (и более миллиарда, если она помещается в кэше ЦП). Эта очень быстрая возможность поиска делает хеш-таблицу в памяти естественным общим выбором в ClickHouse для реализации соединений, когда невозможно или невозможно воспользоваться преимуществами сортировки таблиц.

Алгоритм хеш-соединения является наиболее универсальным из доступных реализаций соединения в ClickHouse.

  1. Все данные из правой таблицы передаются (параллельно двумя потоками, поскольку max_threads = 2) в память, а затем ClickHouse заполняет этими данными хеш-таблицу в памяти.
  2. Данные из левой таблицы передаются в потоковом режиме (параллельно двумя потоками, поскольку max_threads = 2)
  3. и объединяются путем поиска в хеш-таблице.

Обратите внимание: поскольку ClickHouse берет правую таблицу и создает для нее хеш-таблицу в оперативной памяти, более эффективно использовать память, размещая меньшую таблицу в правой части JOIN.

Алгоритм Parallel hash join (Параллельное хеш-соединение)

Алгоритм параллельного хеш-соединения может работать быстрее с большими таблицами с правой стороны, но требует больше памяти.

Алгоритм параллельного хэш-соединения — это вариант хеш-соединения, который разделяет входные данные для одновременного построения нескольких хеш-таблиц, чтобы ускорить соединение за счет увеличения затрат памяти.

  1. Все данные из правой таблицы передаются (параллельно двумя потоками, потому что max_threads = 2) в память. Данные передаются поблочно. Строки из каждого потокового блока разбиваются на 2 сегмента (max_threads = 2) путем применения хэш-функции к ключам соединения каждой строки. Параллельно для каждого сегмента заполняется одна хеш-таблица в памяти с использованием одного потока.Обратите внимание, что хэш-функция для разделения строк на сегменты отличается от той, которая используется внутри хеш-таблиц.
  2. Данные из левой таблицы передаются в потоковом режиме (параллельно двумя потоками, поскольку max_threads = 2), и та же самая «хеш-функция сегмента» из шага ① применяется к ключам соединения каждой строки для определения соответствующей хеш-таблицы,
  3. и строки соединяются через выполнение поиска в соответствующей хеш-таблице.

Обратите внимание, что этот max_threads параметр определяет количество одновременных хеш-таблиц.

Алгоритм Grace hash join (Грейс-хеш-соединение)

Алгоритм не привязан к памяти, при выполнении которого данные временно переносятся на диск. Грейс-хеш-соединение не требует какой-либо сортировки данных и, следовательно, преодолевает некоторые проблемы с производительностью, присущие другим алгоритмам соединения, которые передают данные на диск, например алгоритму (частичного) соединения слиянием (partial merge join algorithm).

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

Что такое мутации в ClickHouse? Почему их следует избегать? Как они работают?

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

  • Операции вставки новых данных в таблицу.
  • Операции обновления существующих данных в таблице.
  • Операции удаления данных из таблицы.

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

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

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

Почему нужно избегать мутаций в ClickHouse?

Мутации относятся к запросам ALTER , которые манипулируют данными таблицы путем удаления или обновления. В частности, это такие запросы, как ALTER TABLEDELETE, UPDATE и т.д.

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

При обновлении вы можете избежать большого количества запросов на запись, используя специализированные обработчики таблиц, такие как ReplacingMergeTree или CollapsingMergeTree, вместо обработчика таблиц MergeTree по умолчанию.

Подходы к обновлению и удалению данных в ClickHouse

Lightweight Deletes (Облегченное удаление)

Облегченное удаление представляет собой предпочтительный и наиболее эффективный способ удаления данных из ClickHouse. Используя DELETE FROM синтаксис таблицы, пользователи могут указать условие для удаления определенных строк, как показано ниже:

По умолчанию эта операция является асинхронной, если для параметра mutations_sync не установлено значение 1.

Lightweight Deletes появились в версии 22.8 и на момент написания статьи фича является экспериментальной, облегченные удаления должны быть готовы к production в одном из следующих выпусков.

Mutations

Обновление данных с помощью мутаций

Самый простой способ обновить данные в таблице ClickHouse — использовать оператор ALTER…UPDATE:

Этот запрос будет обновлять col1 таблицу table с использованием заданного фильтра. Оператор ClickHouse ALTER UPDATE по умолчанию асинхронен. Это означает, что обновление происходит в фоновом режиме, и вы не получите мгновенного эффекта в таблице.

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

Удаление данных с помощью мутаций

Удаления по умолчанию также выполняются асинхронно. Её можно сделать синхронным, используя ту же mutations_sync настройку.

Проверка прогресса мутации

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

Если значение is_done = 0 относится к конкретной мутации, она все равно выполняется. Мутации выполняются для каждой части таблицы, и мутировавшие части становятся доступными мгновенно:

Синхронные обновления

Для пользователей, которым требуются синхронные обновления, параметр mutations_sync может быть установлен в значение 1 (или 2, если мы также хотим подождать, пока все реплики будут обновлены):

Теперь наш запрос на обновление будет ждать завершения мутации:

Полное обновление 1 столбца таблицы

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

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

Эффективное удаление больших блоков (Large Blocks)

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

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

Удаление и обновление с помощью CollapsingMergeTree

Если вам приходится часто обновлять отдельные строки, то вы можете попробовать использовать механизм CollapsingMergeTree для эффективного управления обновлениями данных.

Специальный sign столбец используется для CollapsingMergeTree как способ сообщить ClickHouse, что мы хотим обновить конкретную строку. Если мы вставим -1 столбец sign, вся строка будет удалена. Если мы вставим строку с помощью sign = 1, ClickHouse сохранит ее. Строки, подлежащие обновлению, идентифицируются на основе ключа сортировки, используемого в ORDER BY () операторе DDL при создании таблицы:

Движок CollapsingMergeTree заботится об эффективном удалении отмененных строк из хранилища в фоновом режиме.

Upserts с использованием управления версиями и ReplacingMergeTree

В более сложных случаях мы можем использовать управление версиями на основе механизма ReplacingMergeTree. Этот механизм реализует эффективный способ выполнения того, что известно UPSERT в других СУБД, путем использования специального столбца версии для отслеживания того, какие строки следует удалить. Если присутствует несколько строк с одним и тем же ключом сортировки, в хранилище сохраняется только строка с самой большой версией, а остальные удаляются:

Использованные материалы для подготовки статьи

  1. learn.clickhouse.com
  2. Parts and Partitions in ClickHouse – Part I
  3. Parts and Partitions in ClickHouse – Part II: Manipulation Operations
  4. Join Types supported in ClickHouse
  5. ClickHouse Joins Under the Hood — Hash Join, Parallel Hash Join, Grace Hash Join
  6. La maison du clic ou ClickHouse, la base qui venait de l’est.
  7. Handling Updates and Deletes in ClickHouse
0
Оставьте комментарий! Напишите, что думаете по поводу статьи.x