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

Загрузка данных из PostgreSQL в ClickHouse

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

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

В качестве примера набора данных, чтобы показать типичную миграцию из Postgres в ClickHouse, мы используем набор данных Stack Overflow, задокументированный здесь. Он содержит каждый post, vote, user, comment и badge, которые произошли на Stack Overflow с 2008 года по апрель 2024 года. Схема PostgreSQL для этих данных показана ниже:


Схема PostgreSQL Stack Overflow

DDL-команды для создания таблиц в PostgreSQL доступны здесь.

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

Мы мигрируем каждую из этих концепций в их эквиваленты ClickHouse.

Для тех пользователей, кто хочет заполнить этот набор данных в экземпляр PostgreSQL, чтобы протестировать шаги миграции, мы предоставили данные в формате pg_dump для загрузки, при этом DDL и последующие команды загрузки данных показаны ниже:

Хотя этот набор данных мал для ClickHouse, он значителен для Postgres. Выше представлен подмножество, охватывающее первые три месяца 2024 года.

Хотя наши примеры результатов используют полный набор данных для демонстрации различий в производительности между Postgres и Clickhouse, все шаги, задокументированные ниже, функционально идентичны с меньшим подмножеством. Пользователи, желающие загрузить полный набор данных в Postgres, смотрите здесь. Из-за внешних ограничений, накладываемых вышеупомянутой схемой, полный набор данных для PostgreSQL содержит только строки, которые удовлетворяют ссылочной целостности. Версию Parquet, без таких ограничений, можно легко загрузить непосредственно в ClickHouse, если это необходимо.

Миграция данных​

Миграция данных между ClickHouse и Postgres делится на два основных типа рабочей нагрузки:

  • Первоначальная массовая загрузка с периодическими обновлениями - Необходимо мигрировать начальный набор данных вместе с периодическими обновлениями через установленные интервалы, например, ежедневно. Обновления здесь обрабатываются путем повторной отправки измененных строк - определяемых либо по колонке, которую можно использовать для сравнений (например, дата), либо по значению XMIN. Удаления обрабатываются с помощью полной периодической перезагрузки набора данных.
  • Реальная репликация или CDC - Необходимо мигрировать начальный набор данных. Изменения в этом наборе данных должны отражаться в ClickHouse в реальном времени с допустимой задержкой всего в несколько секунд. Это фактически процесс фиксирования изменений данных (CDC), где таблицы в Postgres должны быть синхронизированы с ClickHouse, т.е. вставки, обновления и удаления в таблице Postgres должны применяться к эквивалентной таблице в ClickHouse.

Первоначальная массовая загрузка с периодическими обновлениями​

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

  • Функции таблицы - Используя функцию таблицы Postgres в ClickHouse для SELECT данных из Postgres и INSERT их в таблицу ClickHouse. Это актуально для массовых загрузок до наборов данных в несколько сотен ГБ.
  • Экспорты - Экспорт в промежуточные форматы, такие как CSV или SQL-скрипт. Эти файлы затем могут быть загружены в ClickHouse либо с клиента с использованием INSERT FROM INFILE, либо с использованием объектного хранилища и их связанных функций, т.е. s3, gcs.

Инкрементальные загрузки могут, в свою очередь, быть запланированы. Если таблица Postgres получает только вставки и существует увеличивающийся id или временная метка, пользователи могут использовать вышеупомянутый подход с функцией таблицы для загрузки инкрементов, т.е. можно применить условие WHERE к SELECT. Этот подход также может использоваться для поддержки обновлений, если гарантируется обновление одной и той же колонки. Однако поддержка удалений потребует полной перезагрузки, что может быть сложно достичь, поскольку таблица растет.

Мы демонстрируем первоначальную загрузку и инкрементальную загрузку, используя CreationDate (предполагаем, что это обновляется, если строки обновляются).

ClickHouse будет перенаправлять простые условия WHERE, такие как =, !=, >,>=, <, <=, и IN на сервер PostgreSQL. Инкрементальные загрузки могут быть сделаны более эффективными за счет обеспечения существования индекса на колонках, используемых для идентификации набора изменений.

Возможным методом для обнаружения операций UPDATE при использовании репликации запросов является использование XMIN системного столбца (идентификаторы транзакций) в качестве контрольной отметки - изменение в этом столбце указывает на изменение и, следовательно, может быть применено к целевой таблице. Пользователи, использующие этот подход, должны быть осведомлены о том, что значения XMIN могут оборачиваться, и сравнения требуют полного сканирования таблицы, что делает отслеживание изменений более сложным. Для получения дополнительной информации об этом подходе смотрите "Фиксация изменений данных (CDC)".

Реальная репликация или CDC​

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

  1. PeerDB от ClickHouse - PeerDB предлагает решение Postgres CDC с открытым кодом, которое пользователи могут запускать в режиме self-managed или через SaaS-решение, которое продемонстрировало высокую производительность в масштабах с Postgres и ClickHouse. Решение фокусируется на низкоуровневых оптимизациях для достижения высокой производительности передачи данных и гарантий надежности между Postgres и ClickHouse. Оно поддерживает как онлайн, так и офлайн загрузки.
к сведению

PeerDB теперь доступен в ClickHouse Cloud - Ультрашвидкая CDC Postgres в ClickHouse с нашим новым коннектором ClickPipe - сейчас в публичной бета-версии.

  1. Создайте свое собственное - Это можно осуществить с помощью Debezium + Kafka - Debezium предоставляет возможность захватывать все изменения в таблице Postgres, пересылая их как события в очередь Kafka. Эти события затем могут быть обработаны либо коннектором Kafka ClickHouse, либо ClickPipes в ClickHouse Cloud, для вставки в ClickHouse. Это представляет собой регистрацию изменений данных (CDC), поскольку Debezium не только выполнит первоначальную копию таблиц, но также обеспечит обнаружение всех последующих обновлений, удалений и вставок в Postgres, что приведет к потоковым событиям. Это требует тщательной настройки как Postgres, так и Debezium и ClickHouse. Примеры можно найти здесь.

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

Нажмите здесь для Часть 2.