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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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