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

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

Понимание эффективного проектирования схемы является ключом к оптимизации производительности 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. Обратите внимание, что ниже мы используем паттерн *.parquet для чтения всех файлов в папке stackoverflow/parquet/posts.

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

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

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

  • Наша таблица постов пуста после выполнения этой команды. Данные не были загружены.
  • Мы указали 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 и, следовательно, более быстрые запросы и вставки. Нагрузка любого алгоритма сжатия по отношению к ЦП в большинстве случаев будет компенсирована уменьшением 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. Мы рекомендуем выполнять это над меньшим подмножеством данных, так как это может быть дорого. Этот запрос требует, чтобы числовые значения были как минимум определены как таковые для точного результата, то есть не строкой.

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


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

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

Наш предыдущий запрос улучшает время отклика запроса более чем в 3 раза:

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

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

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

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

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

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

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

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

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

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