postgresql - ClickHouse Documentation

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

Синтаксис

postgresql({host:port, database, table, user, password[, schema, [, on_conflict]] | named_collection[, option=value [,..]]} [, SETTINGS name=value, ...])

Аргументы

Аргумент	Описание
`host:port`	Адрес сервера PostgreSQL.
`database`	Имя удалённой базы данных.
`table`	Имя удалённой таблицы или запрос, передаваемый в PostgreSQL как есть (см. Передача запроса вместо имени таблицы).
`user`	Пользователь PostgreSQL.
`password`	Пароль пользователя.
`schema`	Схема таблицы, отличная от используемой по умолчанию. Необязательно.
`on_conflict`	Стратегия разрешения конфликтов. Пример: `ON CONFLICT DO NOTHING`. Необязательно.

Аргументы также можно передавать с помощью именованных коллекций. В этом случае host и port нужно указывать отдельно. Такой подход рекомендуется для производственной среды.

Возвращаемое значение

Объект таблицы с теми же столбцами, что и исходная таблица PostgreSQL.

В запросе INSERT, чтобы отличить табличную функцию postgresql(...) от имени таблицы со списком имён столбцов, необходимо использовать ключевые слова FUNCTION или TABLE FUNCTION. См. примеры ниже.

Настройки

Пул соединений, используемый табличной функцией postgresql (и движком таблицы PostgreSQL), можно настроить с помощью завершающей секции SETTINGS. Если параметр не указан, по умолчанию используется значение соответствующего параметра уровня запроса postgresql_*. Полный список параметров postgresql_connection_pool_* и postgresql_connection_attempt_timeout, а также их значения по умолчанию см. в разделе Настройки для этого движка таблицы. Пример:

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password', SETTINGS postgresql_connection_pool_size = 32);

Подробности реализации

SELECT-запросы на стороне PostgreSQL выполняются как COPY (SELECT ...) TO STDOUT внутри PostgreSQL-транзакции в режиме только для чтения с фиксацией после каждого SELECT-запроса. Простые предложения WHERE, такие как =, !=, >, >=, <, <= и IN, выполняются на сервере PostgreSQL. Все JOIN, агрегации, сортировка, условия IN [ array ] и ограничение сэмплирования LIMIT выполняются в ClickHouse только после завершения запроса к PostgreSQL.

Передача запроса вместо имени таблицы

Вместо имени таблицы третьим аргументом может быть SELECT-запрос, который передается в PostgreSQL как есть. Структура результирующей таблицы определяется по результату запроса. Запрос можно записать либо как подзапрос, либо обернуть в функцию query:

SELECT * FROM postgresql('localhost:5432', 'test', (SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0), 'user', 'password');
SELECT * FROM postgresql('localhost:5432', 'test', query('SELECT a, b FROM t1 JOIN t2 USING (id) WHERE a > 0'), 'user', 'password');

Это полезно, чтобы проталкивать JOIN, агрегации и любую другую обработку в PostgreSQL. Такая таблица доступна только в режиме только для чтения: INSERT в неё не поддерживается. Тот же синтаксис поддерживается движком таблицы PostgreSQL.

Форма подзапроса (SELECT ...) разбирается ClickHouse и повторно сериализуется в диалект PostgreSQL (с экранированием идентификаторов PostgreSQL и строковых литералов) перед отправкой на сервер. Поэтому она должна быть корректным ClickHouse SQL. Чтобы передать синтаксис, специфичный для PostgreSQL и не разбираемый ClickHouse, используйте форму query('...'), текст которой отправляется в PostgreSQL дословно.Любой внешний WHERE, LIMIT, агрегация и т. д. из окружающего запроса ClickHouse не проталкиваются в переданный запрос — они применяются в ClickHouse после получения полного результата запроса. Чтобы ограничить объём данных, читаемых из PostgreSQL, поместите фильтр внутрь переданного запроса. При external_table_strict_query = 1 внешний фильтр, который нельзя протолкнуть, отклоняется с исключением вместо локального применения.

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

Будьте внимательны: в PostgreSQL столбец с типом массива, например Integer[], может содержать массивы разной размерности в разных строках, но в ClickHouse допускаются только многомерные массивы одинаковой размерности во всех строках.

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

SELECT name FROM postgresql(`postgres{1|2|3}:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

или

SELECT name FROM postgresql(`postgres1:5431|postgres2:5432`, 'postgres_database', 'postgres_table', 'user', 'password');

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

Примеры

Таблица в PostgreSQL:

postgres=# CREATE TABLE "public"."test" (
"int_id" SERIAL,
"int_nullable" INT NULL DEFAULT NULL,
"float" FLOAT NOT NULL,
"str" VARCHAR(100) NOT NULL DEFAULT '',
"float_nullable" FLOAT NULL DEFAULT NULL,
PRIMARY KEY (int_id));

CREATE TABLE

postgres=# INSERT INTO test (int_id, str, "float") VALUES (1,'test',2);
INSERT 0 1

postgresql> SELECT * FROM test;
  int_id | int_nullable | float | str  | float_nullable
 --------+--------------+-------+------+----------------
       1 |              |     2 | test |
(1 row)

Выбор данных из ClickHouse с помощью обычных аргументов:

SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password') WHERE str IN ('test');

Или с помощью именованных коллекций:

CREATE NAMED COLLECTION mypg AS
        host = 'localhost',
        port = 5432,
        database = 'test',
        user = 'postgresql_user',
        password = 'password';
SELECT * FROM postgresql(mypg, table='test') WHERE str IN ('test');

┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

Вставка:

INSERT INTO TABLE FUNCTION postgresql('localhost:5432', 'test', 'test', 'postgrsql_user', 'password') (int_id, float) VALUES (2, 3);
SELECT * FROM postgresql('localhost:5432', 'test', 'test', 'postgresql_user', 'password');

┌─int_id─┬─int_nullable─┬─float─┬─str──┬─float_nullable─┐
│      1 │         ᴺᵁᴸᴸ │     2 │ test │           ᴺᵁᴸᴸ │
│      2 │         ᴺᵁᴸᴸ │     3 │      │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴──────┴────────────────┘

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

postgres=# CREATE SCHEMA "nice.schema";

postgres=# CREATE TABLE "nice.schema"."nice.table" (a integer);

postgres=# INSERT INTO "nice.schema"."nice.table" SELECT i FROM generate_series(0, 99) as t(i)

CREATE TABLE pg_table_schema_with_dots (a UInt32)
        ENGINE PostgreSQL('localhost:5432', 'clickhouse', 'nice.table', 'postgrsql_user', 'password', 'nice.schema');

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

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

​Синтаксис

​Аргументы

​Возвращаемое значение

​Настройки

​Подробности реализации

​Передача запроса вместо имени таблицы

​Примеры

​См. также

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

Синтаксис

Аргументы

Возвращаемое значение

Настройки

Подробности реализации

Передача запроса вместо имени таблицы

Примеры

См. также

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