Подключиться
Практические руководства

Миграция базы данных из PostgreSQL в ClickHouse с помощью Yandex Data Transfer

Статья создана

Из этого руководства вы узнаете, как перенести базу данных из PostgreSQL в ClickHouse с помощью сервиса Yandex Data Transfer. Также будут рассмотрены особенности такого переноса и работы с данными на источнике и приемнике во время него. Для упрощения все нужные ресурсы будут созданы в Yandex Cloud.

Чтобы перенести базу данных из PostgreSQL в ClickHouse:

  1. Создайте эндпоинт-источник.
  2. Создайте эндпоинт-приемник.
  3. Создайте трансфер.
  4. Активируйте трансфер.
  5. Проверьте работу репликации.
  6. Выполните выборку данных в приемнике.
  7. Выполните очистку удаленных данных в приемнике.

Если созданные ресурсы вам больше не нужны, удалите их.

Перед началом работы

  1. Создайте кластер-источник Yandex Managed Service for PostgreSQL любой подходящей вам конфигурации со следующими настройками:

    • Имя базы — db1.
    • Имя пользователя — user1.
    • Пароль — <пароль источника>.

  2. Создайте в базе данных db1 кластера Yandex Managed Service for PostgreSQL таблицу x_tab и заполните ее данными:

    CREATE TABLE x_tab
(
    id NUMERIC,
    name CHAR
);
CREATE INDEX ON x_tab (id);
INSERT INTO x_tab (id, name) VALUES
  (40, 'User1'),
  (41, 'User2'),
  (42, 'User3'),
  (43, 'User4'),
  (44, 'User5');

  3. Создайте кластер-приемник Yandex Managed Service for ClickHouse любой подходящей вам конфигурации со следующими настройками:

    • Количество хостов ClickHouse — не меньше 2 (для включения репликации в кластере).
    • Имя базы — db1.
    • Имя пользователя — user1.
    • Пароль — <пароль приемника>.

  4. Убедитесь, что группы безопасности кластеров настроены правильно и допускают подключение к ним:

Создайте эндпоинт-источник

Создайте эндпоинт-источник типа PostgreSQL и укажите в нем параметры подключения к базе данных db1.

Создайте эндпоинт-приемник

Создайте эндпоинт-приемник типа ClickHouse.

Создайте трансфер

Создайте трансфер типа Копировать и реплицировать, использующий созданные эндпоинты.

Активируйте трансфер

  1. Активируйте трансфер и дождитесь его перехода в статус Реплицируется.

  2. Чтобы проверить, что трансфер перенес данные с учетом репликации в приемнике, подключитесь к кластеру-приемнику Yandex Managed Service for ClickHouse и посмотрите, что таблица x_tab в базе db1 содержит те же колонки, что таблица x_tab в базе-источнике, а также колонки с временными метками __data_transfer_commit_time и __data_transfer_delete_time:

    SELECT * FROM db1.x_tab WHERE id = 41;

    ┌─id─┬──name──┬─── __data-transfer_commit_time─┬───__data-transfer-delete_time─┐
│ 41 │  User2 │   1633417594957267000          │ 0                             │
└────┴────────┴────────────────────────────────┴───────────────────────────────┘

Проверьте работу репликации

  1. Подключитесь к кластеру-источнику.

  2. Удалите одну строку и измените другую в таблице x_tab базы-источника PostgreSQL:

    DELETE FROM x_tab WHERE id = 41;
UPDATE x_tab SET name = 'Key3' WHERE id = 42;

  3. Убедитесь, что в таблице x_tab на приемнике ClickHouse отобразились изменения:

    SELECT * FROM db1.x_tab WHERE (id >= 41) AND (id <= 42);

    ┌─id─┬──name──┬─── __data-transfer_commit_time─┬───__data-transfer-delete_time─┐
│ 41 │  User2 │   1633417594957267000          │ 1675417594957267000           │
│ 42 │  Key3  │   1675417594957267000          │ 0                             │
│ 42 │  User3 │   1633417594957268000          │ 1675417594957267000           │
└────┴────────┴────────────────────────────────┴───────────────────────────────┘

Выполните выборку данных в ClickHouse

На приемнике ClickHouse с включенной репликацией для воссоздания таблиц используются движки ReplicatedReplacingMergeTree и ReplacingMergeTree. В каждую таблицу автоматически добавляются колонки:

  • __data_transfer_commit_time — время изменения строки на это значение, в формате TIMESTAMP;

  • __data_transfer_delete_time — время удаления строки в формате TIMESTAMP, если строка удалена в источнике. Если строка не удалялась, то колонка заполняется значением 0.

    Колонка __data_transfer_commit_time необходима для работы движка ReplicatedReplacedMergeTree. Если запись удаляется или изменяется, в таблицу вставляется новая строка со значением в этой колонке. Запрос по одному первичному ключу возвращает несколько записей с разными значениями в колонке __data_transfer_commit_time.

В статусе трансфера Реплицируется данные в источнике могут добавляться или удаляться. Чтобы обеспечить стандартное поведение команд SQL, когда первичный ключ указывает на единственную запись, дополните запросы к перенесенным таблицам в ClickHouse конструкцией с фильтром по колонке __data_transfer_delete_time. Например, для таблицы x_tab:

SELECT name FROM x_tab FINAL WHERE name LIKE 'User%' AND __data_transfer_delete_time = 0;

Важно

Использование ключевого слова FINAL сильно снижает эффективность запросов. По возможности избегайте его применения при работе с большими таблицами.

Выполните очистку удаленных данных в ClickHouse

Если трансфер перешел в статус Реплицируется, изменения данных на источнике переносятся на приемник ClickHouse следующим образом:

  • в реплицируемые таблицы будут добавлены новые строки, в колонках __data_transfer_commit_time и __data_transfer_delete_time которых будут указаны дата и время удаления или изменения этих строк в источнике;
  • существующие строки с предыдущими значениями удалены не будут.

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

  1. Выполните запрос для удаления записей:

    DELETE FROM db1.x_tab WHERE id IN (
    SELECT id FROM db1.x_tab FINAL WHERE __data_transfer_delete_time != 0
);

  2. Запустите внеплановое объединение строк таблицы:

    OPTIMIZE TABLE db1.x_tab FINAL;

Повторите процедуру очистки после завершения трансфера.

Примечание

Выполнять очистку необходимо только для трансферов типа Копировать и реплицировать и Реплицировать.

Удалите созданные ресурсы

Если созданные ресурсы вам больше не нужны, удалите их:

Если кластер-источник или кластер-приемник размещены в одном из сервисов управляемых баз данных, удалите их:

В этой статье: