Именованные коллекции

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

Именованные коллекции (named collections) в ADQM/ClickHouse — предопределенные наборы параметров в виде пар ключ/значение, которые можно неоднократно использовать в SQL-запросах при подключении к внешним источникам данных (таким как PostgreSQL, Kafka, S3, MySQL и другим) через табличные функции, словари, интеграционные движки таблиц и баз данных.

Эта функциональность позволяет:

  • делать SQL-запросы более короткими и понятными, избегая повторного определения одних и тех же параметров — значения параметров можно указать один раз в рамках именованной коллекции, а затем обращаться к этой коллекции в различных запросах по мере необходимости;

  • централизованно управлять наборами параметров — конфигурация для интеграции с внешней системой хранится в одном месте, где можно, например, легко проанализировать ошибки подключения в случае их возникновения и обновить необходимые параметры;

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

ПРИМЕЧАНИЕ

В данной статье способы создания и использования именованных коллекций описаны на примере интеграции ADQM и ADPG. Для воспроизведения приведенных примеров подготовьте ADPG, как описано в статье Интеграция ADQM и ADPG: создайте пользователя PostgreSQL, от имени которого будет выполняться подключение к ADPG из ADQM, и две базы данных PostgreSQL с таблицами для тестирования.

Управление именованными коллекциями

Для управления именованными коллекциями (создания/изменения/удаления) можно использовать:

  • DDL-запросы. Чтобы у пользователя было разрешение конфигурировать именованные коллекции через DDL-запросы, ему должна быть предоставлена привилегия named_collection_control — для этого в конфигурационном файле /etc/clickhouse-server/users.xml (или в другом XML-файле директории /etc/clickhouse-server/users.d/) установите значение параметра named_collection_control равным 1 в настройках пользователя.

    Пример

     
    Назначение привилегии named_collection_control пользователю default в файле users.xml:

    <users>
    <default>
        ...
        <named_collection_control>1</named_collection_control>
        ...
    </default>
</users>

  • XML-файлы. Именованные коллекции можно определять в конфигурационном файле /etc/clickhouse-server/config.xml или в других XML-файлах директории /etc/clickhouse-server/config.d/. Созданную или измененную в конфигурационном файле именованную коллекцию можно применять после перезапуска сервера ClickHouse.

Список всех существующих именованных коллекций (созданных с помощью DDL-запросов и описанных в XML-файлах) можно посмотреть в системной таблице named_collections.

Переопределение параметров

Параметры, указанные в именованной коллекции, могут быть переопределены в использующем эту коллекцию SQL-запросе. Для каждого параметра в составе именованной коллекции можно ограничить возможность изменения его значения с помощью специального флага:

  • OVERRIDABLE (или overridable="true" в XML-конфигурации) — значение параметра можно переопределять;

  • NOT OVERRIDABLE (или overridable="false" в XML-конфигурации) — значение параметра запрещено переопределять;

  • флаг не указан — возможность переопределения значения параметра определяется настройкой allow_named_collection_override_by_default.

ВНИМАНИЕ
Если вы используете именованные коллекции с целью скрыть учетные данные для подключения к внешним системам от пользователей без доступа администратора, следует ограничить возможность переопределения соответствующих параметров.

Создание именованных коллекций

DDL-запросы

Создать именованную коллекцию можно с помощью запроса CREATE NAMED COLLECTION:

CREATE NAMED COLLECTION [IF NOT EXISTS] <collection_name> [ON CLUSTER <cluster_name>] AS
key1 = 'value1' [[NOT] OVERRIDABLE],
key2 = 'value2' [[NOT] OVERRIDABLE],
...;

Ключи в именованной коллекции должны совпадать с названиями параметров соответствующей функции, источника данных для словаря, движка таблицы или базы данных. Посмотреть точные названия параметров можно в документации ClickHouse — например, в статьях postgresql, mysql, s3, remote для соответствующих табличных функций или PostgreSQL, MySQL, Kafka для табличных движков.

При обращении к именованной коллекции, в которой указан недопустимый ключ, консольный клиент clickhouse-client выведет сообщение о соответствующей ошибке с указанием списка допустимых ключей (обязательных и опциональных). Например, если в именованной коллекции указан ключ user_password, при вызове функции postgresql со ссылкой на эту коллекцию clickhouse-client сообщит, что функция не поддерживает такой ключ:

DB::Exception: Unexpected key user_password in named collection. Required keys: database, db, password, table, user, username,
optional keys: addresses_expr, host, hostname, on_conflict, port, schema, use_table_cache. (BAD_ARGUMENTS)

Пример

Следующий запрос создает именованную коллекцию my_adpg с параметрами для подключения ADQM к ADPG:

CREATE NAMED COLLECTION my_adpg AS
host = '<adpg_host>',
port = 5432,
database = 'adpg_db1' NOT OVERRIDABLE,
user = 'adqm_user',
password = '<adqm_user_secret_password>';

Значения параметров (база данных adpg_db1 и пользователь PostgreSQL adqm_user) здесь указаны в соответствии с примерами, описанными в статье Интеграция ADQM и ADPG — см. раздел Подготовка ADPG для тестовых примеров.

XML-файлы

Чтобы определять именованные коллекции в XML-конфигурации сервера ClickHouse, используйте секцию named_collections:

<clickhouse>
    <named_collections>
        <named_collection1>
            <key1 overridable="true">value1</key1>
            <key2 overridable="false">value2</key2>
            <key3>value3</key3>
            ...
        </named_collection1>
        <named_collection2>
            ...
        </named_collection2>
        ...
    </named_collections>
</clickhouse>

Пример

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

<clickhouse>
    <named_collections>
        <my_adpg_xml>
            <host>adpg_host</host>
            <port>5432</port>
            <database>adpg_db2</database>
            <user>adqm_user</user>
            <password>adqm_user_secret_password</password>
        </my_adpg_xml>
    </named_collections>
</clickhouse>

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

После того как именованная коллекция создана, ее можно использовать в SQL-запросах, чтобы ссылаться на предопределенные параметры подключения к внешнему источнику данных при работе с табличной функцией, интеграционным табличным движком или движком баз данных, словарем.

Следующие примеры демонстрируют различные способы получить доступ к данным PostgreSQL из ADQM с использованием созданных выше именованных коллекций с параметрами подключения к ADPG.

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

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

    SELECT id, name FROM postgresql(my_adpg, table = 'table1');

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

    INSERT INTO TABLE FUNCTION postgresql(my_adpg, table = 'table1') (id, name) VALUES (3, 'three');

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

    Создание таблицы ADQM на базе табличного движка PostgreSQL:

    CREATE TABLE psql_table (id Int32, name String) ENGINE = PostgreSQL(my_adpg, table = 'table1');

    Движок копирует данные из именованной коллекции при создании таблицы. Последующее изменение коллекции не будет влиять на существующие таблицы.

  • База данных PostgreSQL

    Создание в ADQM базы данных на основе движка баз данных PostgreSQL (в эту базу данных будут автоматически добавлены таблицы из базы данных PostgreSQL adpg_db1 — в данном примере одна таблица table1):

    CREATE DATABASE psql_database ENGINE = PostgreSQL(my_adpg);

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

    Создание таблицы MaterializedPostgreSQL для репликации данных таблицы ADPG adpg_db2.table1 с указанием параметров подключения к ADQG через именованную коллекцию my_adpg_xml:

    SET allow_experimental_materialized_postgresql_table=1;
    CREATE TABLE psql_table_replica (id Int32, name String)
ENGINE = MaterializedPostgreSQL(my_adpg_xml, table = 'table1');

  • База данных MaterializedPostgreSQL

    Создание базы данных MaterializedPostgreSQL, в которую будут реплицироваться таблицы table1 и table2 из базы данных ADPG adpg_db2, с использованием именованной коллекции my_adpg_xml для указания параметров подключения для этой репликации:

    SET allow_experimental_database_materialized_postgresql=1;
    CREATE DATABASE psql_db_replica
ENGINE = MaterializedPostgreSQL(my_adpg_xml)
SETTINGS materialized_postgresql_tables_list = 'table1,table2';

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

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

    CREATE DICTIONARY roles_dict_psql (id Int32, role String DEFAULT 'no role assigned')
PRIMARY KEY id
SOURCE(POSTGRESQL(name my_adpg_xml table roles_dict))
LIFETIME(MIN 10 MAX 20)
LAYOUT(FLAT());

Изменение и удаление именованных коллекций

Именованные коллекции, созданные через DDL-запросы, можно изменять или удалять также с помощью DDL-запросов. Именованные коллекции, определенные в XML-файлах, можно изменять и удалять, редактируя или удаляя соответствующие им XML-описания.

Изменение именованных коллекций DDL

Изменить существующую именованную коллекцию можно с помощью запроса ALTER NAMED COLLECTION, используя ключевое слово SET для добавления/изменения параметров и DELETE — для удаления существующих параметров. Синтаксис запроса в общем виде:

ALTER NAMED COLLECTION [IF EXISTS] <collection_name> [ON CLUSTER <cluster_name>]
[SET key1 = 'value1' [[NOT] OVERRIDABLE], key2 = 'value2' [[NOT] OVERRIDABLE], ... ] |
[DELETE key3, key4, ... ];

Ниже приведены примеры использования запроса ALTER NAMED COLLECTION для изменения именованной коллекции my_adpg.

  • Добавление/изменение параметров

    Изменение значения параметра database и добавление параметра table (при этом изначально установленный для параметра database флаг NOT OVERRIDABLE не меняется, то есть базу данных по-прежнему нельзя переопределить в SQL-запросе):

    ALTER NAMED COLLECTION my_adpg SET database = 'adpg_db2', table = 'table1';

    Изменение значения параметра database с разрешением его переопределять:

    ALTER NAMED COLLECTION my_adpg SET database = 'adpg_db1' OVERRIDABLE;

  • Удаление параметров

    Удаление параметра table из коллекции my_adpg:

    ALTER NAMED COLLECTION my_adpg DELETE table;

    Если параметр необходимо перенастроить так, чтобы в качестве флага на разрешение/ограничение переопределения его значения применялась настройка по умолчанию (allow_named_collection_override_by_default), этот параметр нужно удалить из именованной коллекции и снова добавить без указания флага:

    ALTER NAMED COLLECTION my_adpg DELETE database;
    ALTER NAMED COLLECTION my_adpg SET database = 'adpg_db1';

    Изменение значения параметра password и удаление параметра database в одном запросе:

    ALTER NAMED COLLECTION my_adpg SET password = '<new_password>' DELETE database;

Удаление именованных коллекций DDL

Чтобы удалить именованную коллекцию, созданную через DDL-запрос, используйте запрос DROP NAMED COLLECTION:

DROP NAMED COLLECTION <collection_name>;
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней