PostgreSQL

Позволяет подключаться к базам данных на удалённом PostgreSQL сервере. Поддерживает операции чтения и записи (запросы SELECT и INSERT) для обмена данными между ClickHouse и PostgreSQL.

Обеспечивает доступ в реальном времени к списку таблиц и структуре таблиц на удалённом сервере PostgreSQL с помощью запросов SHOW TABLES и DESCRIBE TABLE.

Поддерживает изменение структуры таблиц (ALTER TABLE ... ADD|DROP COLUMN). Если параметр use_table_cache (см. раздел «Параметры движка» ниже) установлен в 1, структура таблиц кэшируется и не проверяется на изменения, но может быть обновлена запросами DETACH и ATTACH.

CREATE DATABASE test_database
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `schema`, `use_table_cache`]);

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

• host:port — Адрес сервера PostgreSQL.
• database — Имя удалённой базы данных.
• user — Пользователь PostgreSQL.
• password — Пароль пользователя.
• schema — Схема PostgreSQL.
• use_table_cache — Определяет, кэшируется ли структура таблицы базы данных. Необязательный параметр. Значение по умолчанию: 0.

Параметры TLS/SSL передаются в libpq и могут быть заданы через именованную коллекцию или в качестве завершающих аргументов ключ-значение: sslmode (disable, allow, prefer, require, verify-ca или verify-full), а также сертификаты и ключ в одном из двух вариантов. sslrootcert (CA‑сертификат), sslcert (клиентский сертификат) и sslkey (закрытый ключ клиента) — это пути к локальным файлам сервера; они принимаются только из именованной коллекции, определённой в файле конфигурации сервера. Вместо этого параметры sslrootcert_pem, sslcert_pem и sslkey_pem принимают литеральное содержимое соответствующих файлов; их можно указывать в SQL (например, ENGINE = PostgreSQL('host:port', 'database', 'user', 'password', sslmode = 'verify-full', sslrootcert_pem = '...')), а в журналах и запросах SHOW они маскируются так же, как пароль. Если они не заданы, применяются значения libpq по умолчанию (sslmode=prefer).

База данных в ClickHouse, которая обменивается данными с сервером PostgreSQL:

CREATE DATABASE test_database
ENGINE = PostgreSQL('postgres1:5432', 'test_database', 'postgres', 'mysecretpassword', 'schema_name',1);

SHOW DATABASES;

┌─name──────────┐
│ default       │
│ test_database │
│ system        │
└───────────────┘

SHOW TABLES FROM test_database;

┌─name───────┐
│ test_table │
└────────────┘

Чтение данных из таблицы PostgreSQL:

SELECT * FROM test_database.test_table;

┌─id─┬─value─┐
│  1 │     2 │
└────┴───────┘

Запись данных в таблицу PostgreSQL:

INSERT INTO test_database.test_table VALUES (3,4);
SELECT * FROM test_database.test_table;

┌─int_id─┬─value─┐
│      1 │     2 │
│      3 │     4 │
└────────┴───────┘

Предположим, что структура таблицы в PostgreSQL была изменена:

postgre> ALTER TABLE test_table ADD COLUMN data Text

Поскольку при создании базы данных параметр use_table_cache был установлен в значение 1, структура таблицы в ClickHouse была кэширована и поэтому не была изменена:

DESCRIBE TABLE test_database.test_table;

┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
└────────┴───────────────────┘

После отсоединения таблицы и её повторного подключения структура была обновлена:

DETACH TABLE test_database.test_table;
ATTACH TABLE test_database.test_table;
DESCRIBE TABLE test_database.test_table;

┌─name───┬─type──────────────┐
│ id     │ Nullable(Integer) │
│ value  │ Nullable(Integer) │
│ data   │ Nullable(String)  │
└────────┴───────────────────┘

• Блог: ClickHouse и PostgreSQL — идеальное сочетание в мире данных — часть 1
• Блог: ClickHouse и PostgreSQL — идеальное сочетание в мире данных — часть 2