Проектирование схемы
Понимание эффективного проектирования схемы является ключом к оптимизации производительности 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 минуты на экземпляре ClickHouse Cloud с 8 ядрами.
Указанный выше запрос загружает 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 в большинстве случаев будет компенсирована уменьшением ввода-вывода. Улучшение сжатия данных должно быть поэтому первым приоритетом при работе над обеспечением быстроты запросов ClickHouse.
Для того, почему ClickHouse так хорошо сжимает данные, мы рекомендуем эту статью. Вкратце, как столбцовая база данных, значения будут записываться в порядке столбцов. Если эти значения отсортированы, одни и те же значения будут находиться рядом друг с другом. Алгоритмы сжатия используют непрерывные паттерны данных. Дополнительно, ClickHouse имеет кодеки и гранулярные типы данных, которые позволяют пользователям дополнительно настраивать техники сжатия.
Сжатие в ClickHouse будет зависеть от 3 основных факторов: ключа сортировки, типов данных и любых используемых кодеков. Все это настраивается через схему.
Наибольшее первоначальное улучшение в сжатии и производительности запросов может быть достигнуто простым процессом оптимизации типов. Несколько простых правил могут быть применены для оптимизации схемы:
- Используйте строгие типы - Наша начальная схема использовала строки для многих столбцов, которые явно являются числами. Использование правильных типов обеспечит ожидаемую семантику при фильтрации и агрегации. Это также относится к типам даты, которые были правильно указаны в файлах Parquet.
- Избегайте Nullable колонок - По умолчанию вышеуказанные столбцы предполагаются как Null. Тип Nullable позволяет запросам определять разницу между пустым и Null значением. Это создает отдельный столбец типа UInt8. Этот дополнительный столбец нужно обрабатывать каждый раз, когда пользователь работает с nullable-столбцом. Это приводит к дополнительному использованию пространства хранения и почти всегда негативно сказывается на производительности запросов. Используйте Nullable только в том случае, если есть разница между значением по умолчанию для типа и Null. Например, значение 0 для пустых значений в колонке
ViewCountбудет вероятно достаточным для большинства запросов и не повлияет на результаты. Если пустые значения должны обрабатываться иначе, их нередко также можно исключить из запросов с помощью фильтра. - Используйте минимальную точность для числовых типов - ClickHouse имеет ряд числовых типов, разработанных для различных числовых диапазонов и точности. Всегда старайтесь минимизировать количество битов, использованных для представления столбца. Кроме целых чисел разных размеры e.g. 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 может быть использован для эффективного кодирования перечисляемых типов. Элементы Enum могут быть либо 8, либо 16 бит, в зависимости от необходимого количества уникальных значений, которые они должны хранить. Рассмотрите возможность использования этого типа, если вам нужна соответствующая валидация во время вставки (необъявленные значения будут отвергнуты) или если вы хотите выполнять запросы, которые используют естественный порядок значений Enum e.g. представьте колонку отзывов, содержащую пользовательские ответы
Enum(':(' = 1, ':|' = 2, ':)' = 3).
Советуем: чтобы найти диапазон всех столбцов и количество различных значений, пользователи могут использовать простой запрос
SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical. Мы рекомендуем выполнять это на меньшем подмножестве данных, так как это может быть дорого. Этот запрос требует, чтобы числовые значения были как минимум определены как такие, т.е. не строками.
Применяя эти простые правила к нашей таблице posts, мы можем определить оптимальный тип для каждого столбца:
| Column | Is Numeric | Min, Max | Unique Values | Nulls | Comment | Optimized Type |
|---|---|---|---|---|---|---|
PostTypeId | Yes | 1, 8 | 8 | No | Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8) | |
AcceptedAnswerId | Yes | 0, 78285170 | 12282094 | Yes | Отделение Null от 0 значения | UInt32 |
CreationDate | No | 2008-07-31 21:42:52.667000000, 2024-03-31 23:59:17.697000000 | - | No | Милисекундная гранулярность не требуется, используйте DateTime | DateTime |
Score | Yes | -217, 34970 | 3236 | No | Int32 | |
ViewCount | Yes | 2, 13962748 | 170867 | No | UInt32 | |
Body | No | - | - | No | String | |
OwnerUserId | Yes | -1, 4056915 | 6256237 | Yes | Int32 | |
OwnerDisplayName | No | - | 181251 | Yes | Рассмотрите возможность рассмотрения Null как пустую строку | String |
LastEditorUserId | Yes | -1, 9999993 | 1104694 | Yes | 0 как unused value может быть использован для Nulls | Int32 |
LastEditorDisplayName | No | - | 70952 | Yes | Рассмотрите возможность рассмотрения Null как пустую строку. Тестировался LowCardinality, преимуществ не было | String |
LastEditDate | No | 2008-08-01 13:24:35.051000000, 2024-04-06 21:01:22.697000000 | - | No | Милисекундная гранулярность не требуется, используйте DateTime | DateTime |
LastActivityDate | No | 2008-08-01 12:19:17.417000000, 2024-04-06 21:01:22.697000000 | - | No | Милисекундная гранулярность не требуется, используйте DateTime | DateTime |
Title | No | - | - | No | Рассмотрите возможность рассмотрения Null как пустую строку | String |
Tags | No | - | - | No | Рассмотрите возможность рассмотрения Null как пустую строку | String |
AnswerCount | Yes | 0, 518 | 216 | No | Рассмотрите возможность обсуждения Null и 0 как одно и то же | UInt16 |
CommentCount | Yes | 0, 135 | 100 | No | Рассмотрите возможность обсуждения Null и 0 как одно и то же | UInt8 |
FavoriteCount | Yes | 0, 225 | 6 | Yes | Рассмотрите возможность обсуждения Null и 0 как одно и то же | UInt8 |
ContentLicense | No | - | 3 | No | LowCardinality превосходит FixedString | LowCardinality(String) |
ParentId | No | - | 20696028 | Yes | Рассмотрите возможность рассмотрения Null как пустую строку | String |
CommunityOwnedDate | No | 2008-08-12 04:59:35.017000000, 2024-04-01 05:36:41.380000000 | - | Yes | Рассмотрите возможность указать 1970-01-01 как значение по умолчанию для Null. Милисекундная гранулярность не требуется, используйте DateTime | DateTime |
ClosedDate | No | 2008-09-04 20:56:44, 2024-04-06 18:49:25.393000000 | - | Yes | Рассмотрите возможность указать 1970-01-01 как значение по умолчанию для Null. Милисекундная гранулярность не требуется, используйте DateTime | DateTime |
Это дает нам следующую схему:
Мы можем заполнить это с помощью простой команды 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, предположим, что наши пользователи хотят выполнять аналитику с фильтрацией по дате и типу публикации e.g.:
"Какие вопросы получили наибольшее количество комментариев за последние 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 нет понятия внешних ключей. Это не запрещает использование joins, но означает, что ссылочная целостность остается под управлением пользователя на уровне приложения. В системах OLAP, таких как ClickHouse, целостность данных часто управляется на уровне приложения или в процессе приема данных, а не поддерживается самой базой данных, где это влечет за собой значительные накладные расходы. Этот подход обеспечивает большую гибкость и более быструю вставку данных. Это соответствует фокусу ClickHouse на скорости и масштабируемости запросов на чтение и вставку с очень большими наборами данных.
Чтобы минимизировать использование JOIN-ов во время выполнения запросов, пользователи имеют несколько инструментов/подходов:
- Денормализация данных - Денормализуйте данные, комбинируя таблицы и используя сложные типы для отношений, не являющихся 1:1. Это часто включает перемещение любых соединений с времени выполнения запроса на время вставки.
- Словари - Специфическая для ClickHouse функция для обработки прямых соединений и поиска по ключевым значениям.
- Инкрементные материализованные представления - Функция ClickHouse, позволяющая перенести стоимость вычисления с времени выполнения запроса на время вставки, включая возможность инкрементального вычисления агрегированных значений.
- Обновляемые материализованные представления - Похожие на материализованные представления, используемые в других продуктах базы данных, это позволяет периодически вычислять результаты запроса и кэшировать результат.
Мы исследуем каждый из этих подходов в каждом руководстве, подчеркивая, когда каждый из них уместен, с примером, показывающим, как он может быть применен для решения вопросов по набору данных Stack Overflow.
