Проектирование схемы

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

Набор данных Stack Overflow

Для примеров в этом руководстве мы используем подсет данных Stack Overflow. Он содержит каждый пост, голос, пользователя, комментарий и значок, которые имели место на Stack Overflow с 2008 года по апрель 2024 года. Эти данные доступны в формате Parquet с использованием схем, указанных ниже в S3 бакете s3://datasets-documentation/stackoverflow/parquet/:

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

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

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

Установите начальную схему

Поскольку таблица posts будет целевой для большинства аналитических запросов, мы сосредотачиваемся на установлении схемы для этой таблицы. Эти данные доступны в общедоступном S3 бакете s3://datasets-documentation/stackoverflow/parquet/posts/*.parquet с файлом за каждый год.

Загрузка данных из S3 в формате Parquet представляет собой самый распространенный и предпочтительный способ загрузки данных в ClickHouse. ClickHouse оптимизирован для обработки Parquet и может потенциально читать и вставлять десятки миллионов строк из S3 в секунду.

ClickHouse предоставляет функцию автоматического определения схемы для автоматического определения типов для набора данных. Это поддерживается для всех форматов данных, включая Parquet. Мы можем использовать эту функцию для определения типов ClickHouse для данных с помощью функции таблицы s3 и команды DESCRIBE. Обратите внимание, что мы используем шаблон glob *.parquet, чтобы прочитать все файлы в папке stackoverflow/parquet/posts.

Функция s3 table function позволяет выполнять запросы к данным в S3 на месте из ClickHouse. Эта функция совместима со всеми форматами файлов, которые поддерживает ClickHouse.

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

Несколько важных моментов:

Наша таблица posts пуста после выполнения этой команды. Данные не были загружены. Мы указали MergeTree в качестве движка таблицы. MergeTree является наиболее распространенным движком таблицы в ClickHouse, который вы, вероятно, будете использовать. Это многофункциональный инструмент в вашей коробке ClickHouse, способный обрабатывать PB данных и обслуживать большинство аналитических случаев использования. Существуют другие движки таблиц для случая использования, такого как CDC, которым необходимо поддерживать эффективные обновления.

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

Чтобы подтвердить, что таблица была создана:

С нашим начальным определением схемы мы можем заполнять данные с использованием INSERT INTO SELECT, читая данные с помощью функции таблицы s3. Следующий запрос загружает данные posts за примерно 2 минуты на 8-ядерном экземпляре ClickHouse Cloud.

Приведенный выше запрос загружает 60 миллионов строк. Хотя это небольшое количество для ClickHouse, пользователи с более медленными интернет-соединениями могут пожелать загрузить подмножество данных. Это можно сделать, просто указав годы, которые они хотят загрузить, с помощью шаблона glob, например, https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2008.parquet или https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/{2008, 2009}.parquet. Увидеть здесь, как шаблоны glob можно использовать для таргетинга подмножеств файлов.

Оптимизация типов

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

Меньше данных на диске означает меньше ввода-вывода (I/O) и, следовательно, более быстрые запросы и вставки. Нагрузка любого алгоритма сжатия по отношению к ЦП в большинстве случаев будет компенсирована сокращением ввода-вывода. Улучшение сжатия данных должно, следовательно, быть первым приоритетом при работе над обеспечением быстроты запросов ClickHouse.

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

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

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

• Используйте строгие типы - Наша исходная схема использовала строки для многих колонок, которые, очевидно, являются числами. Использование правильных типов обеспечит ожидаемую семантику при фильтрации и агрегации. То же самое применимо к типам дат, которые были правильно предоставлены в файлах Parquet.
• Избегайте Nullable колонок - По умолчанию вышеуказанные колонки предполагались как Null. Тип Nullable позволяет запросам определять разницу между пустым и Null значением. Это создает отдельную колонку типа UInt8. Эта дополнительная колонка должна обрабатываться каждый раз, когда пользователь работает с nullable колонкой. Это приводит к дополнительному использованию пространства для хранения и почти всегда негативно сказывается на производительности запросов. Используйте Nullable только тогда, когда есть разница между значением по умолчанию для типа и Null. Например, значение 0 для пустых значений в колонке ViewCount будет, вероятно, достаточным для большинства запросов и не повлияет на результаты. Если пустые значения должны обрабатываться отдельно, их часто также можно исключить из запросов с помощью фильтра. Используйте минимальную точность для числовых типов - ClickHouse имеет несколько числовых типов, предназначенных для различных числовых диапазонов и точности. Всегда ставьте цель минимизировать количество бит, используемых для представления колонки. Кроме целых чисел различного размера, например, Int16, ClickHouse предлагает беззнаковые варианты, у которых минимальное значение равно 0. Эти варианты могут позволить использовать меньше бит для колонки, например, UInt16 имеет максимальное значение 65535, в два раза большее, чем у Int16. Предпочитайте эти типы более крупным знаковым вариантам, если это возможно.
• Минимальная точность для типов дат - ClickHouse поддерживает несколько типов дат и даты/времени. Date и Date32 могут использоваться для хранения чистых дат, при этом последний поддерживает более широкий диапазон дат за счет большего количества бит. DateTime и DateTime64 поддерживают дату и время. DateTime ограничен секундной гранулярностью и использует 32 бита. DateTime64, как и следует из названия, использует 64 бита, но поддерживает гранулярность вплоть до наносекунд. Как всегда, выбирайте более грубую версию, приемлемую для запросов, минимизируя количество необходимых бит.
• Используйте LowCardinality - Числовые, строковые, Date или DateTime колонки с небольшим количеством уникальных значений могут быть потенциально закодированы с использованием типа LowCardinality. Этот словарь кодирует значения, уменьшая размер на диске. Рассмотрите возможность использования этого для колонок с менее чем 10k уникальными значениями. FixedString для особых случаев - Строки, которые имеют фиксированную длину, могут быть закодированы с использованием типа FixedString, например, коды языка и валюты. Это эффективно, когда данные имеют длину точно N байт. В остальных случаях это, вероятно, снизит эффективность, и предпочтительнее использовать LowCardinality.
• Enums для валидации данных - Тип Enum может использоваться для эффективного кодирования перечисляемых типов. Enums могут быть либо 8, либо 16 бит в зависимости от количества уникальных значений, которые они должны хранить. Рассмотрите возможность использования этого, если вам нужна либо соответствующая валидация в момент вставки (недекларированные значения будут отклоняться), либо вы хотите выполнять запросы, которые используют естественный порядок значений Enum, например, представьте себе колонку отзывов, содержащую ответы пользователей Enum(':(' = 1, ':|' = 2, ':)' = 3).

Советы: Чтобы найти диапазон всех колонок и количество различных значений, пользователи могут использовать простой запрос SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical. Рекомендуем выполнять это на меньшем подмножестве данных, так как это может быть затратно. Этот запрос требует, чтобы числовые данные по крайней мере определялись как таковые для точного результата, т.е. не как строка.

Применяя эти простые правила к нашей таблице posts, мы можем определить оптимальный тип для каждой колонки:

Это дает нам следующую схему:

Мы можем заполнить эту таблицу с помощью простой команды INSERT INTO SELECT, читая данные из нашей предыдущей таблицы и вставляя в эту:

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

Выбор упорядочивающего ключа

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

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

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

Некоторые простые правила могут быть применены для выбора упорядочивающего ключа. Следующие моменты могут иногда конфликтовать, поэтому рассматривайте их в порядке значимости. Пользователи могут идентифицировать несколько ключей из этого процесса; обычно 4-5 ключей достаточно:

• Выбирайте колонки, которые соответствуют вашим общим фильтрам. Если колонка часто используется в клаузах WHERE, приоритизируйте включение этих колонок в ваш ключ, а не тех, которые используются реже. Предпочитайте колонки, которые помогают исключить большой процент от общего числа строк при фильтрации, тем самым уменьшая количество данных, которые необходимо прочитать.
• Предпочитайте колонки, которые, вероятно, будут высоко коррелировать с другими колонками в таблице. Это поможет обеспечить, чтобы эти значения также хранились непрерывно, улучшая сжатие. Операции GROUP BY и ORDER BY для колонок в упорядочивающем ключе могут быть сделаны более эффективными по памяти.

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

Пример

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

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

Запрос для этого вопроса, используя нашу предыдущую таблицу posts_v2 с оптимизированными типами, но без упорядочивающего ключа:

Запрос здесь очень быстрый, даже несмотря на то, что все 60 миллионов строк были линейно просканированы - ClickHouse просто быстрый :) Вы должны нам доверять, что упорядочивающие ключи имеют значение на уровне TB и PB!

Давайте выберем колонки PostTypeId и CreationDate в качестве наших упорядочивающих ключей.

Может быть, в нашем случае мы ожидаем, что пользователи всегда будут фильтровать по PostTypeId. Это значение имеет кардинальность 8 и представляет собой логический выбор для первой записи в нашем упорядочивающем ключе. Признавая, что фильтрация по гранулярности даты, вероятно, будет достаточной (это все еще будет полезно для фильтров времени), мы используем toDate(CreationDate) в качестве второго компонента нашего ключа. Это также даст меньший индекс, так как дату можно представить 16, что ускорит фильтрацию. Наша окончательная запись ключа - CommentCount, чтобы помочь найти посты с наибольшим количеством комментариев (финальное сортировка).

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

Далее: Техники моделирования данных

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

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

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

Следующие подходы все нацелены на минимизацию необходимости использования JOIN для оптимизации чтения и улучшения производительности запросов. Хотя JOIN полностью поддерживаются в ClickHouse, мы рекомендуем использовать их экономно (2-3 таблицы в запросе JOIN - это нормально) для достижения оптимальной производительности.

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

Чтобы минимизировать использование JOIN во время выполнения запросов, пользователи имеют несколько инструментов/подходов:

• Денормализация данных - Денормализуйте данные, объединяя таблицы и используя сложные типы для не 1:1 отношений. Это часто включает в себя перемещение любых JOIN из времени выполнения запросов в время вставки.
• Словари - Особая функция ClickHouse для обработки прямых объединений и поисков значений по ключам.
• Инкрементные материализованные представления - Функция ClickHouse для переноса затрат на вычисления из времени выполнения запросов в время вставки, включая возможность инкрементально вычислять агрегированные значения.
• Обновляемые материализованные представления - Похожие на материализованные представления, используемые в других продуктах баз данных, это позволяет периодически вычислять и кэшировать результаты запроса.

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