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

Движок таблиц PostgreSQL

Движок PostgreSQL позволяет выполнять запросы SELECT и INSERT к данным, хранящимся на удалённом сервере PostgreSQL.

примечание

В настоящее время поддерживаются только версии PostgreSQL 12 и выше.

Репликация или миграция данных Postgres с помощью PeerDB

В дополнение к движку таблиц Postgres вы можете использовать PeerDB от ClickHouse для настройки непрерывного конвейера данных из Postgres в ClickHouse. PeerDB — это инструмент, специально предназначенный для репликации данных из Postgres в ClickHouse с использованием захвата изменений данных (CDC).

Создание таблицы​

Смотрите подробное описание запроса CREATE TABLE.

Структура таблицы может отличаться от оригинальной структуры таблицы PostgreSQL:

  • Имена колонок должны совпадать с именами в оригинальной таблице PostgreSQL, но вы можете использовать только некоторые из этих колонок и в любом порядке.
  • Типы колонок могут отличаться от тех, что в оригинальной таблице PostgreSQL. ClickHouse пытается привести значения к типам данных ClickHouse.
  • Параметр external_table_functions_use_nulls определяет, как обрабатывать Nullable колонки. Значение по умолчанию: 1. Если 0, табличная функция не создаёт Nullable колонки и вставляет значения по умолчанию вместо null. Это также применимо к значениям NULL внутри массивов.

Параметры движка

  • host:port — адрес сервера PostgreSQL.
  • database — имя удалённой базы данных.
  • table — имя удалённой таблицы.
  • user — пользователь PostgreSQL.
  • password — пароль пользователя.
  • schema — нестандартная схема таблицы. Опционально.
  • on_conflict — стратегия разрешения конфликтов. Пример: ON CONFLICT DO NOTHING. Опционально. Примечание: добавление этого параметра сделает вставку менее эффективной.

Именованные коллекции (доступны с версии 21.11) рекомендуются для производственной среды. Вот пример:

Некоторые параметры могут быть переопределены аргументами в формате "ключ-значение":

Детали реализации​

Запросы SELECT на стороне PostgreSQL выполняются как COPY (SELECT ...) TO STDOUT внутри транзакции PostgreSQL только для чтения с коммитом после каждого запроса SELECT.

Простые условия WHERE, такие как =, !=, >, >=, <, <= и IN, выполняются на сервере PostgreSQL.

Все соединения, агрегации, сортировка, условия IN [ array ] и ограничение выборки LIMIT выполняются в ClickHouse только после завершения запроса к PostgreSQL.

Запросы INSERT на стороне PostgreSQL выполняются как COPY "table_name" (field1, field2, ... fieldN) FROM STDIN внутри транзакции PostgreSQL с автоматическим коммитом после каждого оператора INSERT.

Типы Array в PostgreSQL преобразуются в массивы ClickHouse.

примечание

Будьте осторожны - в PostgreSQL массив данных, созданный как type_name[], может содержать многомерные массивы разных размерностей в разных строках таблицы в одном и том же столбце. Но в ClickHouse разрешается иметь многомерные массивы только одинакового количества размерностей во всех строках таблицы в одном и том же столбце.

Поддерживает несколько реплик, которые должны быть перечислены через |. Например:

Приоритет реплик для источника словаря PostgreSQL поддерживается. Чем больше число в карте, тем меньше приоритет. Самый высокий приоритет — 0.

В приведённом ниже примере реплика example01-1 имеет самый высокий приоритет:

Пример использования​

Таблица в PostgreSQL​

Создание таблицы в ClickHouse и подключение к таблице PostgreSQL, созданной выше​

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

Вставка начальных данных из таблицы PostgreSQL в таблицу ClickHouse с помощью запроса SELECT​

Табличная функция postgresql копирует данные из PostgreSQL в ClickHouse, что часто используется для улучшения производительности запросов к данным, выполняя запросы или аналитические операции в ClickHouse, а не в PostgreSQL, или также может использоваться для миграции данных из PostgreSQL в ClickHouse. Поскольку мы будем копировать данные из PostgreSQL в ClickHouse, мы используем движок таблиц MergeTree в ClickHouse и называем его postgresql_copy:

Вставка инкрементальных данных из таблицы PostgreSQL в таблицу ClickHouse​

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

Это потребует ведения учёта максимального идентификатора или метки времени, ранее добавленного, например:

Затем вставляя значения из таблицы PostgreSQL, большие чем максимальный

Выбор данных из результирующей таблицы ClickHouse​

Использование нестандартной схемы​

Смотрите также

Связанный контент​