Перейти к основному содержимому
Перейти к основному содержимому

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

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

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

Для примеров в этом гидe мы используем подмножество набора данных 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 и, следовательно, более быстрые запросы и вставки. Нагрузка любого алгоритма сжатия по отношению к CPU в большинстве случаев будет перевешена сокращением I/O. Поэтому улучшение сжатия данных должно быть первым приоритетом при работе над обеспечением быстроты запросов ClickHouse.

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

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

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

  • Используйте строгие типы - Наша начальная схема использовала строки для многих колонок, которые явно являются числовыми. Использование правильных типов обеспечит ожидаемую семантику при фильтрации и агрегации. То же самое относится к типам даты, которые были корректно заданы в файлах Parquet.
  • Избегайте Nullable Columns - По умолчанию вышеуказанные колонки были предположительно Null. Тип Nullable позволяет запросам определять разницу между пустым и Null-значением. Это создает отдельный столбец типа UInt8. Этот дополнительный столбец должен обрабатываться каждый раз, когда пользователь работает с nullable-колонкой. Это приводит к дополнительному использованию пространства для хранения и почти всегда отрицательно сказывается на производительности запросов. Используйте Nullable только если есть разница между значением по умолчанию для типа и Null. Например, значение 0 для пустых значений в колонке ViewCount будет достаточно для большинства запросов и не повлияет на результаты. Если пустые значения следует обрабатывать по-другому, их также можно часто исключить из запросов с помощью фильтра. Используйте минимальную точность для числовых типов - ClickHouse имеет ряд числовых типов, предназначенных для различных числовых диапазонов и точности. Всегда стремитесь минимизировать количество битов, используемых для представления колонки. Кроме целых чисел разных размеров, например Int16, ClickHouse предлагает беззнаковые варианты, чье минимальное значение равно 0. Они могут позволить использовать меньше бит для колонки, например UInt16 имеет максимальное значение 65535, что в два раза больше, чем Int16. Предпочитайте эти типы более крупным знаковым вариантам, если это возможно.
  • Минимальная точность для типов даты - ClickHouse поддерживает ряд типов даты и datetime. Типы 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 частями, известными как части, с правилами, применяемыми для объединения частей в фоновом режиме. В 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, и это логичный выбор для первой записи в нашем упорядочивающем ключе. Осознавая, что фильтрация по детализации даты может быть достаточной (она все равно будет выгодной для фильтров datetime), мы используем toDate(CreationDate) в качестве второго компонента нашего ключа. Это также создаст меньший индекс, так как дата может быть представлена 16, что ускорит фильтрацию. Наша финальная запись ключа — это CommentCount, чтобы помочь найти посты с наибольшим количеством комментариев (финальная сортировка).

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

Следующий шаг: Техники моделирования данных​

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

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

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

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

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

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

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

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