Интеграция ADQM и ADPG

Елена Дворядкина

Обзор

ClickHouse предоставляет несколько способов интеграции с PostgreSQL:

  • табличная функция postgresql | табличный движок PostgreSQL | движок баз данных PostgreSQL — обеспечивают возможность отправлять запросы из ClickHouse к таблицам/базам данных на сервере PostgreSQL для обмена данными в реальном времени. Поддерживаются операции чтения и записи данных PostgreSQL. При выполнении запроса SELECT или INSERT ClickHouse подключается к таблице/базе данных PostgreSQL и получает актуальные данные PostgreSQL или экспортирует новые данные. Иными словами, данные хранятся в PostgreSQL, а из ClickHouse можно к ним обращаться.

  • табличный движок MaterializedPostgreSQL | движок баз данных MaterializedPostgreSQL — позволяют использовать ClickHouse в качестве реплики PostgreSQL. При создании таблицы/базы данных MaterializedPostgreSQL в ClickHouse создается исходный дамп данных таблицы или таблиц базы данных PostgreSQL и запускается процесс репликации — в фоновом режиме к таблице/базе данных MaterializedPostgreSQL применяются изменения данных, выполненные на стороне PostgreSQL. Таким образом, при использовании движков MaterializedPostgreSQL в отличие от движков PostgreSQL данные хранятся и в PostgreSQL, и в ClickHouse.

  • тип источника данных POSTGRESQL для словарей — позволяет использовать таблицы PostgreSQL в качестве источника данных для словарей в ClickHouse.

В этой статье описываются особенности и способы использования этих инструментов на примере ADQM и ADPG (Arenadata Postgres — реляционная система управления базами данных, основанная на PostgreSQL). Приводятся запросы для вызова табличной функции и создания таблиц/баз данных на основе перечисленных интеграционных движков, а также примеры запросов для чтения/записи данных ADPG из ADQM.

Подготовка ADPG для тестовых примеров

 
На хосте ADPG запустите терминальный клиент psql от имени пользователя по умолчанию postgres с помощью команд:

$ sudo su - postgres
$ psql

Теперь в psql можно вводить команды для работы с базами данных и таблицами PostgreSQL:

psql (14.1)
Type "help" for help.

postgres=#
ПРИМЕЧАНИЕ

Обзор по работе с PostgreSQL-таблицами в psql можно посмотреть в статье документации ADPG Пример работы с таблицами через psql.

Создание пользователя PostgreSQL

Создайте пользователя (с правами суперпользователя для демонстрационных целей), от имени которого будет выполняться подключение к ADPG из ADQM:

CREATE ROLE adqm_user SUPERUSER LOGIN PASSWORD '<adqm_user_secret_password>';

Создание базы данных и таблицы ADPG для примеров с табличной функцией и движками PostgreSQL

  1. Создайте в ADPG базу данных adpg_db1:

    CREATE DATABASE adpg_db1;

    Используйте psql-команду \c для подключения к этой базе данных:

    \c adpg_db1

    Результат выполнения команды:

    You are now connected to database "adpg_db1" as user "postgres".
adpg_db1=#

  2. Создайте таблицу:

    CREATE TABLE table1 (id INTEGER PRIMARY KEY, name VARCHAR(10));

  3. Добавьте в таблицу тестовые данные:

    INSERT INTO table1 (id, name) VALUES (1, 'one'), (2, 'two');

Создание базы данных с таблицами ADPG для примеров с движками MaterializedPostgreSQL

  1. Создайте в ADPG новую базу данных adpg_db2 и подключитесь к ней:

    CREATE DATABASE adpg_db2;
    \c adpg_db2

  2. Создайте в базе данных adpg_db2 три таблицы с одинаковой структурой:

    CREATE TABLE table1 (id INTEGER PRIMARY KEY, name VARCHAR(10));
    CREATE TABLE table2 (id INTEGER PRIMARY KEY, name VARCHAR(10));
    CREATE TABLE table3 (id INTEGER PRIMARY KEY, name VARCHAR(10));

  3. Добавьте в эти таблицы тестовые данные:

    INSERT INTO table1 (id, name) VALUES (1, 'one'), (2, 'two');
    INSERT INTO table2 (id, name) VALUES (3, 'three'), (4, 'four');
    INSERT INTO table3 (id, name) VALUES (5, 'five'), (6, 'six');

Создание таблицы ADPG, источника данных для словаря

Создайте в базе данных adpg_db2 таблицу roles_dict, которая будет источником данных для словаря:

CREATE TABLE roles_dict (id INTEGER PRIMARY KEY, role VARCHAR(10));
INSERT INTO roles_dict (id, role) VALUES (10, 'admin'), (20, 'owner'), (30, 'author'), (40, 'reviewer'), (50, 'editor'), (60, 'view only');

Настройка аутентификации для соединения с ADPG

Чтобы разрешить подключение к базам данных adpg_db1 и adpg_db2 с хоста ADQM от имени пользователя adqm_user и настроить аутентификацию по паролю, необходимо добавить следующие записи в файл pg_hba.conf (для ADPG это можно сделать через интерфейс ADCM — подробнее в статье Аутентификация по паролю):

host adpg_db1 adqm_user <adqm_host_address> password
host adpg_db2 adqm_user <adqm_host_address> password

где <adqm_host_address> — адрес хоста ADQM (например, если IPv4-адрес хоста ADQM — 10.92.40.25, замените <adqm_host_address> на 10.92.40.25/32). Подробное описание поля address и других полей записей файла pg_hba.conf приведены в статье The pg_hba.conf file.

Основные параметры интеграции

При вызове табличной функции postgresql и создании таблиц/баз данных на основе движков для интеграции с PostgreSQL (базовый синтаксис запросов приводится в соответствующих разделах ниже) необходимо указывать следующие параметры.

<postgres_host>

Адрес сервера PostgreSQL (IP или имя хоста)

<postgres_database>

Имя базы данных PostgreSQL

<postgres_table>

Имя таблицы в базе данных PostgreSQL (указывается для табличной функции postgresql, а также для табличных движков PostgreSQL и MaterializedPostgreSQL)

<postgres_user>

Имя пользователя PostgreSQL, от имени которого ClickHouse подключается к PostgreSQL

<user_password>

Пароль пользователя PostgreSQL

<schema>

Схема PostgreSQL (опционально может быть указана для табличной функции postgresql, табличного движка PostgreSQL, движка баз данных PostgreSQL)
РЕКОМЕНДАЦИЯ
В реальных системах рекомендуется передавать параметры через именованные коллекции (named collections).

Табличная функция postgresql

Табличная функция postgresql позволяет направлять запросы SELECT и INSERT к таблицам в базах данных PostgreSQL.

Синтаксис функции:

postgresql('<postgres_host>:5432', '<postgres_database>', '<postgres_table>', '<postgres_user>', '<user_password>'[, '<schema>'])

Описание параметров приведено выше.

Пример

Чтение данных из ADPG

Выполните следующий запрос, чтобы получить в ADQM данные из таблицы ADPG adpg_db1.table1 с помощью функции postgresql:

SELECT id, name FROM postgresql('<adpg_host>:5432', 'adpg_db1', 'table1', 'adqm_user', '<adqm_user_secret_password>');

где <adpg_host> — адрес хоста ADPG (IP или имя хоста).

Результат:

┌─id─┬─name──┐
│  1 │ one   │
│  2 │ two   │
└────┴───────┘

Запись данных в ADPG

Выполните следующий запрос, чтобы добавить новую запись в таблицу ADPG:

INSERT INTO TABLE FUNCTION postgresql('<adpg_host>:5432', 'adpg_db1', 'table1', 'adqm_user', '<adqm_user_secret_password>') (id, name)
VALUES (3, 'three');

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

SELECT * FROM table1;
 id | name
----+-------
  1 | one
  2 | two
  3 | three
(3 rows)

Табличный движок PostgreSQL

Табличный движок PostgreSQL позволяет направлять запросы SELECT и INSERT к таблицам на удаленном сервере PostgreSQL.

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

Базовый синтаксис запроса для создания таблицы PostgreSQL (описание параметров приведено выше):

CREATE TABLE <table_name> (<column_name> <column_type> [DEFAULT|MATERIALIZED|ALIAS <expr>] [TTL <expr>], ...)
ENGINE = PostgreSQL('<postgres_host>:5432', '<postgres_database>', '<postgres_table>', '<postgres_user>', '<user_password>'[, '<schema>']);

Структура таблицы ADQM может отличаться от структуры таблицы PostgreSQL, к которой осуществляется подключение:

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

  • Типы столбцов в таблице ADQM могут отличаться от типов в исходной таблице PostgreSQL — ADQM пытается приводить значения к типам данных ClickHouse.

  • Настройка external_table_functions_use_nulls определяет, как обрабатываются Nullable-столбцы. Значение по умолчанию: 1. Если значение 0, столбцы не преобразуются в Nullable — вместо NULL в столбце выставляются значения по умолчанию для соответствующего типа данных. Это также применимо для значений NULL в массивах.

Пример

В ADQM создайте таблицу на базе движка PostgreSQL и запросите из нее данные:

CREATE TABLE psql_table (id Int32, name String)
ENGINE = PostgreSQL('<adpg_host>:5432', 'adpg_db1', 'table1', 'adqm_user', '<adqm_user_secret_password>');
SELECT * FROM psql_table;
┌─id─┬─name──┐
│  1 │ one   │
│  2 │ two   │
│  3 │ three │
└────┴───────┘

Вставьте данные в таблицу ADQM:

INSERT INTO psql_table VALUES (4, 'four');

На хосте ADPG запросите данные из таблицы adpg_db1.table1, чтобы проверить, что в нее успешно были импортированы данные из ADQM:

SELECT * FROM table1;
 id | name
----+-------
  1 | one
  2 | two
  3 | three
  4 | four
(4 rows)

Движок баз данных PostgreSQL

Движок баз данных PostgreSQL позволяет подключаться к базе данных на сервере PostgreSQL и выполнять операции чтения и записи (запросы SELECT и INSERT) для обмена данными между ClickHouse и PostgreSQL, а также в реальном времени получать от сервера PostgreSQL информацию о таблицах базы данных и их структуре с помощью запросов SHOW TABLES и DESCRIBE TABLE.

Создание базы данных

Базовый синтаксис запроса для создания базы данных PostgreSQL в ADQM:

CREATE DATABASE <database_name>
ENGINE = PostgreSQL('<postgres_host>:5432', '<postgres_database>', '<postgres_user>', '<user_password>'[, `<schema>`, `use_table_cache`]);

Описание основных параметров приведено выше. Необязательный параметр use_table_cache определяет, кешируются ли структуры таблиц. Если значение параметра 1, структура таблицы кешируется и изменения в ней не отслеживаются. Чтобы обновить структуру таблицы, нужно последовательно выполнить команды DETACH TABLE и ATTACH TABLE.

Пример

Создайте в ADQM базу данных psql_database на основе движка PostgreSQL:

CREATE DATABASE psql_database
ENGINE = PostgreSQL('<adpg_host>:5432', 'adpg_db1', 'adqm_user', '<adqm_user_secret_password>', 1);

Проверьте, что новая база данных добавлена в список баз данных ADQM:

SHOW DATABASES;
┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ information_schema │
│ psql_database      │
│ system             │
└────────────────────┘

Посмотрите, какие таблицы есть в базе данных psql_database:

SHOW TABLES FROM psql_database;
┌─name───┐
│ table1 │
└────────┘

Запросите данные из таблицы:

SELECT * FROM psql_database.table1;
┌─id─┬─name──┐
│  1 │ one   │
│  2 │ two   │
│  3 │ three │
│  4 │ four  │
└────┴───────┘

Добавьте новые данные в таблицу на стороне ADPG:

INSERT INTO table1 (id, name) VALUES (5, 'five');

Проверьте, что эти данные доступны через таблицу ADQM:

SELECT * FROM psql_database.table1;
┌─id─┬─name──┐
│  1 │ one   │
│  2 │ two   │
│  3 │ three │
│  4 │ four  │
│  5 │ five  │
└────┴───────┘

На хосте ADPG измените структуру таблицы adpg_db1.table1, добавив в нее новый столбец:

ALTER TABLE table1 ADD COLUMN value SMALLINT;

Структура таблицы на стороне ADQM не поменяется, так как при создании базы данных параметр use_table_cache был установлен в 1 (значит, структура таблицы в ADQM была закеширована):

DESCRIBE TABLE psql_database.table1;
┌─name─┬─type─────────────┬─
│ id   │ Int32            │
│ name │ Nullable(String) │
└──────┴──────────────────┴─

Если таблицу в ADQM отключить, а затем снова подключить, структура таблицы обновится:

DETACH TABLE psql_database.table1;
ATTACH TABLE psql_database.table1;
DESCRIBE TABLE psql_database.table1;
┌─name──┬─type─────────────┬─
│ id    │ Int32            │
│ name  │ Nullable(String) │
│ value │ Nullable(Int16)  │
└───────┴──────────────────┴─

Табличный движок MaterializedPostgreSQL

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

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

ВНИМАНИЕ
Репликация TOAST-значений не поддерживается. Для типа данных используется значение по умолчанию.

Требования

Для использования табличного движка MaterializedPostgreSQL необходимо выполнение следующих условий:

  • В конфигурационном файле PostgreSQL настройка wal_level должна иметь значение logical, значение параметра max_replication_slots должно быть не меньше 2 (подробнее об этих параметрах и о том, как их настроить в ADPG, написано в статье Логическая репликация).

  • Таблица, созданная на основе движка MaterializedPostgreSQL, должна иметь такой же первичный ключ, как репликационный идентификатор (обычно это первичный ключ) таблицы PostgreSQL.

  • Таблица MaterializedPostgreSQL может быть создана только в базе данных Atomic.

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

Базовый синтаксис запроса для создания таблицы на основе движка MaterializedPostgreSQL:

CREATE TABLE <table_name> (<key_column_name> <key_column_type>, <column_name> <column_type>, ...)
ENGINE = MaterializedPostgreSQL('<postgres_host>:5432', '<postgres_database>', '<postgres_table>', '<postgres_user>', '<user_password>')
PRIMARY KEY <key_column_name>;

Описание параметров приведено выше.

Виртуальные столбцы

В таблицу MaterializedPostgreSQL автоматически добавляются следующие виртуальные столбцы, которые доступны в запросах SELECT:

  • _version (UInt64) — счетчик транзакций (значение LSN в WAL);

  • _sign (Int8) — метка удаления. Возможные значения:

    • 1 — строка не удалена;

    • -1 — строка удалена.

Пример

Настройка ADPG

В ADPG настройте следующие параметры конфигурации, необходимые для логической репликации, с помощью которой реализуется репликация через табличный движок MaterializedPostgreSQL:

  • значение параметра wal_level — logical;

  • значение параметра max_replication_slots — не меньше 2.

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

В ADQM создайте таблицу MaterializedPostgreSQL:

CREATE TABLE psql_table_replica (id Int32, name String)
ENGINE = MaterializedPostgreSQL('<adpg_host>:5432', 'adpg_db2', 'table1', 'adqm_user', '<adqm_user_secret_password>')
PRIMARY KEY id;

В созданную таблицу ADQM автоматически вставятся данные из исходной таблицы ADPG:

SELECT * FROM psql_table_replica;
┌─id─┬─name──┐
│  1 │ one   │
│  2 │ two   │
└────┴───────┘

Тестирование репликации

В ADPG добавьте новые строки в таблицу adpg_db2.table1:

INSERT INTO table1 (id, name) VALUES (11, 'eleven'), (22, 'twenty two');

Убедитесь, что новые строки реплицированы в таблицу ADQM:

SELECT * FROM psql_table_replica;
┌─id─┬─name──┐
│  1 │ one   │
│  2 │ two   │
└────┴───────┘
┌─id─┬─name───────┐
│ 11 │ eleven     │
│ 22 │ twenty two │
└────┴────────────┘

Движок баз данных MaterializedPostgreSQL

Движок баз данных MaterializedPostgreSQL позволяет создать базу данных ADQM с таблицами из базы данных PostgreSQL. При создании базы данных на основе движка MaterializedPostgreSQL создается снепшот базы данных PostgreSQL и в базу данных MaterializedPostgreSQL загружаются необходимые таблицы (это может быть любой набор таблиц из любого набора схем базы данных PostgreSQL). База данных MaterializedPostgreSQL получает исходный дамп данных таблиц PostgreSQL и запускает процесс репликации — читает WAL и выполняет запросы DML, применяя изменения данных, которые были произведены в базе данных PostgreSQL.

Репликация реализована с помощью протокола логической репликации PostgreSQL, который не поддерживает репликацию запросов DDL, но позволяет определять изменения, нарушающие репликацию (например, изменение типа столбца, добавление/удаление столбцов). При таких изменениях соответствующие таблицы перестают получать обновления. В этом случае следует выполнить запросы ATTACH TABLE/DETACH TABLE для перезагрузки таблицы. Если DDL-запрос не нарушает репликацию (например, переименование столбца), таблица будет продолжать получать обновления (вставка производится по позиции, а не по имени столбца).

ВАЖНО

  • В настоящее время движок баз данных MaterializedPostgreSQL находится в статусе экспериментального. Чтобы его использовать, необходимо перед созданием базы данных включить опцию: SET allow_experimental_database_materialized_postgresql = 1.

  • Репликация TOAST-значений не поддерживается. Для типа данных будет использоваться значение по умолчанию.

Требования

Чтобы создавать и использовать базы данных на движке MaterializedPostgreSQL необходимо выполнение следующих условий:

  • В конфигурационном файле в PostgreSQL настройка wal_level должна иметь значение logical, значение параметра max_replication_slots должно быть не меньше 2 (подробнее об этих параметрах и о том, как их настроить в ADPG, написано в статье Логическая репликация).

  • Каждая реплицируемая таблица должна иметь репликационный идентификатор — первичный ключ (по умолчанию) или индекс, определенный как REPLICA IDENTITY.

Создание базы данных

Синтаксис запроса для создания базы данных на основе движка MaterializedPostgreSQL в общем виде:

CREATE DATABASE <database_name>
ENGINE = MaterializedPostgreSQL('<postgres_host>:5432', '<postgres_database>', '<postgres_user>', '<user_password>')
[SETTINGS ...];

Описание параметров приведено выше.

Настройки

Через выражение SETTINGS для базы данных MaterializedPostgreSQL можно указать следующие настройки.

materialized_postgresql_tables_list

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

  • В списке можно указать таблицы из разных схем базы данных. Для этого нужно указать имя каждой таблицы вместе с именем схемы (через точку), а также установить настройку materialized_postgresql_tables_list_with_schema = 1.

    В этом случае при обращении к таблице базы данных MaterializedPostgreSQL в ADQM также необходимо указывать имя схемы вместе с именем таблицы. Например:

    SELECT * FROM <database_name>.`<schema_name>.<table_name>`;

  • Если в базу данных MaterializedPostgreSQL реплицируются таблицы из одной схемы (схемы, назначенной через параметр materialized_postgresql_schema, или схемы по умолчанию), имена таблиц в списке materialized_postgresql_tables_list указываются без имени схемы. Обращение к таблице базы данных MaterializedPostgreSQL в этом случае также осуществляется только по имени таблицы. Например:

    SELECT * FROM <database_name>.<table_name>;

  • Если список таблиц не указан, реплицируются все таблицы из схемы materialized_postgresql_schema или схемы по умолчанию.

materialized_postgresql_schema

Схема базы данных PostgreSQL, таблицы которой будут реплицироваться

materialized_postgresql_schema_list

Список схем базы данных PostgreSQL (разделяются запятыми), все таблицы которых будут реплицироваться.

Если для базы данных MaterializedPostgreSQL через параметр materialized_postgresql_schema_list указано несколько схем PostgreSQL, таблицы которых будут реплицироваться, для обращения к таблице в ADQM нужно указывать имя схемы и имя таблицы через точку. Например:

SELECT * FROM <database_name>.`<schema_name>.<table_name>`;

materialized_postgresql_max_block_size

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

materialized_postgresql_replication_slot

materialized_postgresql_snapshot

Идентификатор созданного пользователем слота репликации и идентификатор снепшота, из которого будет выполняться исходный дамп таблиц PostgreSQL.

Обе эти настройки должны быть указаны вместе. Использовать их рекомендуется только в случае реальной необходимости — например, если нужно определить постоянный слот репликации, чтобы избежать нарушения репликации данных PostgreSQL в случае сбоя основного сервера (за более подробной информацией и примером можно обратиться к разделу Failover of the logical replication slot документации ClickHouse). Обычно самостоятельно конфигурировать слот репликации не нужно — он создается таблицей

Динамическое добавление и удаление таблиц из репликации

После того, как база данных MaterializedPostgreSQL создана, новые таблицы, добавляемые в базу данных PostgreSQL, не попадают автоматически в репликацию — их необходимо добавить вручную с помощью запроса ATTACH TABLE. Этот запрос можно также использовать, чтобы добавить таблицу в список materialized_postgresql_tables_list, указанный при создании базы данных.

Удалить из репликации отдельные таблицы можно с помощью запроса DETACH TABLE.

Пример

Настройка ADPG

Движок баз данных MaterializedPostgreSQL требует те же настройки логической репликации в ADPG, что и табличный движок MaterializedPostgreSQL. В ADPG убедитесь, что:

  • параметр wal_level установлен в logical;

  • значение параметра max_replication_slots не меньше 2.

Создание базы данных MaterializedPostgreSQL

В ADQM включите возможность работы с движком баз данных MaterializedPostgreSQL и создайте базу данных:

SET allow_experimental_database_materialized_postgresql=1;
CREATE DATABASE psql_db_replica
ENGINE = MaterializedPostgreSQL('<adpg_host>:5432', 'adpg_db2', 'adqm_user', '<adqm_user_secret_password>')
SETTINGS materialized_postgresql_tables_list = 'table1,table2';

Посмотрите, какие таблицы содержатся в базе данных:

SHOW TABLES FROM psql_db_replica;
┌─name───┐
│ table1 │
│ table2 │
└────────┘

Получите данные одной из таблиц (например, table2):

SELECT * FROM psql_db_replica.table2;
┌─id─┬─name──┐
│  3 │ three │
│  4 │ four  │
└────┴───────┘

Тестирование репликации

В ADPG добавьте новую запись в таблицу adpg_db2.table2:

INSERT INTO table2 (id, name) VALUES (30, 'thirty');

Убедитесь, что эта запись попала в таблицу-реплику в ADQM:

SELECT * FROM psql_db_replica.table2;
┌─id─┬─name───┐
│  3 │ three  │
│  4 │ four   │
│ 30 │ thirty │
└────┴────────┘

Добавление новой таблицы в репликацию

Чтобы указать дополнительную таблицу базы данных ADPG для репликации в базу данных ADQM (иными словами, изменить список materialized_postgresql_tables_list базы данных MaterializedPostgreSQL), выполните команду ATTACH TABLE:

ATTACH TABLE psql_db_replica.table3;

Теперь таблица table3 также реплицируется в базу данных psql_db_replica:

SHOW TABLES FROM psql_db_replica;
┌─name───┐
│ table1 │
│ table2 │
│ table3 │
└────────┘
SELECT * FROM psql_db_replica.table3;
┌─id─┬─name─┐
│  5 │ five │
│  6 │ six  │
└────┴──────┘

Источник данных для словаря типа POSTGRESQL

Таблицу PostgreSQL можно назначить источником данных для словаря в ClickHouse/ADQM. Для этого при создании словаря в запросе CREATE DICTIONARY укажите тип источника POSTGRESQL в выражении SOURCE и настройте параметры подключения к таблице PostgreSQL следующим образом:

CREATE DICTIONARY <dictionary_name> (...)
...
SOURCE(POSTGRESQL(
    port 5432
    host '<postgres_host>'
    user '<postgres_user>'
    password '<postgres_user_password>'
    replica(host 'postgres_host_1' port 5432 priority 1)
    replica(host 'postgres_host_2' port 5432 priority 2)
    db '<postgres_database>'
    table '<postgres_table>'
    where '<filter_expr>'
    invalidate_query '<query_to_check_dictionary_status>'
    query '<custom_query>'))
...

Конфигурация источника данных словаря типа POSTGRESQL включает следующие поля:

  • port — порт PostgreSQL.

  • host — хост PostgreSQL.

  • user — имя пользователя PostgreSQL, от имени которого ClickHouse будет подключаться к PostgreSQL.

  • password — пароль пользователя PostgreSQL.

  • replica — настройки подключения к реплике PostgreSQL.

    При определении источника данных для словаря можно сконфигурировать общие настройки подключения к PostgreSQL (host, port, user и password) или указать несколько реплик и каждую настроить отдельно — в поле replica указать хост и порт для подключения к реплике, а также назначить реплике приоритет, который ClickHouse будет учитывать при попытке соединения с PostgreSQL (чем меньше значение priority, тем выше приоритет реплики).

  • db — имя базы данных PostgreSQL.

  • table — имя таблицы в базе данных PostgreSQL.

  • where — условие выборки (необязательный параметр).

  • invalidate_query — запрос для проверки условия загрузки словаря (необязательный параметр).

  • query — пользовательский запрос (необязательный параметр).

Источник данных обязательно должен быть объявлен через одно из полей: table или query. Поля table и where не могут быть использованы одновременно с query.

Пример

В ADQM создайте словарь, который будет считывать данные из таблицы ADPG adpg_db2.roles_dict:

CREATE DICTIONARY roles_dict_psql (id Int32, role String DEFAULT 'no role assigned')
PRIMARY KEY id
SOURCE(POSTGRESQL(port 5432 host '<adpg_host_name>' user 'adqm_user' password '<adqm_user_secret_password>' db 'adpg_db2' table 'roles_dict'))
LIFETIME(MIN 10 MAX 20)
LAYOUT(FLAT());

Создайте таблицу users с именами пользователей и идентификаторами назначенных им ролей:

CREATE TABLE users (user_id Int32, name String, role_id Int32) ENGINE = MergeTree ORDER BY user_id;
INSERT INTO users VALUES (1, 'john', 10), (2, 'mary', 30), (3, 'andrew', 40), (4, 'harry', 70), (5, 'ann', 50);
--user_id---name-----role_id--
│       1 │ john   │      10 │
│       2 │ mary   │      30 │
│       3 │ andrew │      40 │
│       4 │ harry  │      70 │
│       5 │ ann    │      50 │
------------------------------

Выполните следующий запрос, чтобы считать имена пользователей из таблицы users и найти названия их ролей в словаре roles_dict_psql по идентификаторам role_id:

SELECT  name AS user, dictGet('roles_dict_psql', 'role', toUInt64(role_id)) AS role FROM users;

Результат:

┌─user───┬─role─────────────┐
│ john   │ admin            │
│ mary   │ author           │
│ andrew │ reviewer         │
│ harry  │ no role assigned │
│ ann    │ editor           │
└────────┴──────────────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней