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

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

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

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

Соблюдая этот принцип, мы сосредотачиваемся на основной таблице posts. Схема Postgres для этого показана ниже:

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

Это дает нам начальную не оптимизированную схему.

Без ограничения NOT NULL, столбцы Postgres могут содержать нулевые значения. Не проверяя значения строк, ClickHouse сопоставляет их с эквивалентными Nullable типами. Обратите внимание, что первичный ключ не может быть NULL, что является требованием в Postgres.

Мы можем создать таблицу ClickHouse, используя эти типы, с простой командой CREATE AS EMPTY SELECT.

Тот же подход может быть использован для загрузки данных из s3 в других форматах. См. здесь эквивалентный пример загрузки этих данных из формата Parquet.

Первоначальная загрузка

Создав таблицу, мы можем вставить строки из Postgres в ClickHouse, используя функцию таблицы Postgres.

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

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

Если использовать полный набор данных, пример должен загрузить 59 миллионов постов. Подтвердите это с помощью простого подсчета в ClickHouse:

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

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

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

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

Первичные (упорядочивающие) ключи в ClickHouse

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

В чем отличие первичных ключей ClickHouse?

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

• Первичные ключи Postgres, по определению, уникальны для каждой строки. Использование структур B-дерева позволяет эффективно искать отдельные строки по этому ключу. Хотя ClickHouse также может быть оптимизирован для поиска одного значения строки, аналитические нагрузки обычно требуют чтения нескольких столбцов, но для многих строк. Фильтры чаще всего должны идентифицировать подмножество строк, по которым будет выполняться агрегация.
• Эффективность памяти и диска имеет первостепенное значение на масштабах, на которых ClickHouse обычно используется. Данные записываются в таблицы ClickHouse с использованием блоков, которые называют parts, с правилами для объединения этих блоков в фоновом режиме. В ClickHouse каждый блок имеет свой собственный первичный индекс. Когда блоки объединяются, первичные индексы объединенного блока также объединяются. В отличие от Postgres, эти индексы не строятся для каждой строки. Вместо этого первичный индекс для блока имеет одну запись индекса на группу строк - эта техника называется разреженной индексацией.
• Разреженная индексация возможна, поскольку ClickHouse хранит строки для блока на диске, упорядоченные по заданному ключу. Вместо того чтобы напрямую находить отдельные строки (как в индексе на основе B-дерева), разреженный первичный индекс позволяет быстро (посредством бинарного поиска по записям индекса) идентифицировать группы строк, которые могут соответствовать запросу. Обнаруженные группы потенциально совпадающих строк затем обрабатываются параллельно и передаются в механизм ClickHouse для поиска совпадений. Этот дизайн индекса позволяет первичному индексу быть небольшим (он полностью помещается в основную память), при этом значительно ускоряя время выполнения запросов, особенно для диапазонных запросов, которые типичны для аналитических случаев использования данных. Для получения дополнительной информации мы рекомендуем этот подробный гид.

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

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

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

По вопросам и шагам при выборе упорядочивающего ключа, используя таблицу постов в качестве примера, см. здесь.

Сжатие

Колоночное хранилище ClickHouse означает, что сжатие часто будет значительно лучше по сравнению с Postgres. Следующее показано на сравнении требований к хранению для всех таблиц Stack Overflow в обеих базах данных:

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

Нажмите здесь для части 3.