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

Документация по именованным коллекциям

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

Именованные коллекции можно настраивать с помощью DDL или в файлах конфигурации; они применяются при запуске ClickHouse. Они упрощают создание объектов и позволяют скрывать учетные данные от пользователей без административного доступа.

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

Параметры, заданные в именованной коллекции, можно переопределять в SQL; это показано в примерах ниже. Эту возможность можно ограничить с помощью ключевых слов [NOT] OVERRIDABLE, XML-атрибутов и/или параметра конфигурации allow_named_collection_override_by_default.

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

​

Хранение именованных коллекций в системной базе данных

​

Пример DDL

CREATE NAMED COLLECTION name AS
key_1 = 'value' OVERRIDABLE,
key_2 = 'value2' NOT OVERRIDABLE,
url = 'https://connection.url/'

В приведённом выше примере:

• key_1 всегда можно переопределить.
• key_2 нельзя переопределить никогда.
• Возможность переопределить url зависит от значения allow_named_collection_override_by_default.

​

Разрешения на создание именованных коллекций с помощью DDL

Чтобы управлять именованными коллекциями с помощью DDL, пользователь должен иметь привилегию named_collection_control. Ее можно назначить, добавив файл в /etc/clickhouse-server/users.d/. В этом примере пользователю default назначаются привилегии access_management и named_collection_control:

/etc/clickhouse-server/users.d/user_default.xml

<clickhouse>
  <users>
    <default>
      <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex replace=true>
      <access_management>1</access_management>
      <named_collection_control>1</named_collection_control>
    </default>
  </users>
</clickhouse>

В приведённом выше примере значение password_sha256_hex — это шестнадцатеричное представление SHA256-хеша пароля. В этой конфигурации для пользователя default задан атрибут replace=true, поскольку в конфигурации по умолчанию установлен password в открытом виде, а для одного пользователя нельзя одновременно задать пароль в открытом виде и пароль в формате SHA256 hex.

​

Хранение именованных коллекций

Именованные коллекции можно хранить либо на локальном диске, либо в ZooKeeper/Keeper. По умолчанию используется локальное хранилище. Их также можно хранить в зашифрованном виде, используя те же алгоритмы, что и для шифрования диска, при этом по умолчанию используется aes_128_ctr.

Чтобы настроить хранилище именованных коллекций, нужно указать type. Это может быть local или keeper/zookeeper. Для зашифрованного хранилища можно использовать local_encrypted или keeper_encrypted/zookeeper_encrypted.

Чтобы использовать ZooKeeper/Keeper, также нужно указать path (путь в ZooKeeper/Keeper, где будут храниться именованные коллекции) в разделе named_collections_storage файла конфигурации. В следующем примере используются шифрование и ZooKeeper/Keeper:

<clickhouse>
  <named_collections_storage>
    <type>zookeeper_encrypted</type>
    <key_hex>bebec0cabebec0cabebec0cabebec0ca</key_hex>
    <algorithm>aes_128_ctr</algorithm>
    <path>/named_collections_path/</path>
    <update_timeout_ms>1000</update_timeout_ms>
  </named_collections_storage>
</clickhouse>

Необязательный параметр конфигурации update_timeout_ms по умолчанию имеет значение 5000.

​

Хранение именованных коллекций в конфигурационных файлах

​

Пример XML

/etc/clickhouse-server/config.d/named_collections.xml

<clickhouse>
     <named_collections>
        <name>
            <key_1 overridable="true">value</key_1>
            <key_2 overridable="false">value_2</key_2>
            <url>https://connection.url/</url>
        </name>
     </named_collections>
</clickhouse>

В приведённом выше примере:

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

​

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

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

​

Изменить именованную DDL-коллекцию

Измените или добавьте ключи key1 и key3 в коллекции collection2 (это не изменит значение флага overridable для этих ключей):

ALTER NAMED COLLECTION collection2 SET key1=4, key3='value3'

Измените или добавьте ключ key1 и разрешите всегда его переопределять:

ALTER NAMED COLLECTION collection2 SET key1=4 OVERRIDABLE

Удалите ключ key2 из коллекции collection2:

ALTER NAMED COLLECTION collection2 DELETE key2

Измените или добавьте ключ key1, а также удалите ключ key3 в коллекции collection2:

ALTER NAMED COLLECTION collection2 SET key1=4, DELETE key3

Чтобы принудительно применить к ключу настройки по умолчанию для флага overridable, необходимо удалить ключ и добавить его заново.

ALTER NAMED COLLECTION collection2 DELETE key1;
ALTER NAMED COLLECTION collection2 SET key1=4;

​

Удалите именованную коллекцию DDL collection2:

DROP NAMED COLLECTION collection2

​

Именованные коллекции для доступа к S3

Описание параметров см. в разделе табличной функции S3.

​

Пример DDL

CREATE NAMED COLLECTION s3_mydata AS
access_key_id = 'AKIAIOSFODNN7EXAMPLE',
secret_access_key = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
format = 'CSV',
url = 'https://s3.us-east-1.amazonaws.com/yourbucket/mydata/'

​

Пример XML

<clickhouse>
    <named_collections>
        <s3_mydata>
            <access_key_id>AKIAIOSFODNN7EXAMPLE</access_key_id>
            <secret_access_key>wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY</secret_access_key>
            <format>CSV</format>
            <url>https://s3.us-east-1.amazonaws.com/yourbucket/mydata/</url>
        </s3_mydata>
    </named_collections>
</clickhouse>

​

Примеры использования именованной коллекции в функции s3() и таблице S3

В обоих приведённых ниже примерах используется одна и та же именованная коллекция s3_mydata:

​

Функция s3()

INSERT INTO FUNCTION s3(s3_mydata, filename = 'test_file.tsv.gz',
   format = 'TSV', structure = 'number UInt64', compression_method = 'gzip')
SELECT * FROM numbers(10000);

Первый аргумент функции s3(), показанной выше, — это имя коллекции s3_mydata. Без именованных коллекций идентификатор ключа доступа, секретный ключ, формат и URL пришлось бы указывать при каждом вызове функции s3().

​

Таблица S3

CREATE TABLE s3_engine_table (number Int64)
ENGINE=S3(s3_mydata, url='https://s3.us-east-1.amazonaws.com/yourbucket/mydata/test_file.tsv.gz', format = 'TSV')
SETTINGS input_format_with_names_use_header = 0;

SELECT * FROM s3_engine_table LIMIT 3;
┌─number─┐
│      0 │
│      1 │
│      2 │
└────────┘

​

Именованные коллекции для доступа к базе данных MySQL

Описание параметров см. на странице mysql.

​

Пример DDL

CREATE NAMED COLLECTION mymysql AS
user = 'myuser',
password = 'mypass',
host = '127.0.0.1',
port = 3306,
database = 'test',
connection_pool_size = 8,
replace_query = 1

​

Пример XML

<clickhouse>
    <named_collections>
        <mymysql>
            <user>myuser</user>
            <password>mypass</password>
            <host>127.0.0.1</host>
            <port>3306</port>
            <database>test</database>
            <connection_pool_size>8</connection_pool_size>
            <replace_query>1</replace_query>
        </mymysql>
    </named_collections>
</clickhouse>

​

Примеры для функции mysql(), таблицы MySQL, базы данных MySQL и именованной коллекции словарь

В следующих четырёх примерах используется одна и та же именованная коллекция mymysql:

​

Функция mysql()

SELECT count() FROM mysql(mymysql, table = 'test');

┌─count()─┐
│       3 │
└─────────┘

В именованной коллекции параметр table не указан, поэтому он задаётся при вызове функции как table = 'test'.

​

Таблица MySQL

CREATE TABLE mytable(A Int64) ENGINE = MySQL(mymysql, table = 'test', connection_pool_size=3, replace_query=0);
SELECT count() FROM mytable;

┌─count()─┐
│       3 │
└─────────┘

DDL переопределяет параметр connection_pool_size в именованной коллекции.

​

База данных MySQL

CREATE DATABASE mydatabase ENGINE = MySQL(mymysql);

SHOW TABLES FROM mydatabase;

┌─name───┐
│ source │
│ test   │
└────────┘

​

Словарь MySQL

CREATE DICTIONARY dict (A Int64, B String)
PRIMARY KEY A
SOURCE(MYSQL(NAME mymysql TABLE 'source'))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED());

SELECT dictGet('dict', 'B', 2);

┌─dictGet('dict', 'B', 2)─┐
│ two                     │
└─────────────────────────┘

​

Именованные коллекции для доступа к базе данных PostgreSQL

Описание параметров см. в postgresql. Также доступны следующие псевдонимы:

• username для user
• db для database.

Параметр addresses_expr используется в коллекции вместо host:port. Этот параметр необязателен, поскольку есть и другие необязательные параметры: host, hostname, port. Приоритет показан в следующем псевдокоде:

CASE
    WHEN collection['addresses_expr'] != '' THEN collection['addresses_expr']
    WHEN collection['host'] != ''           THEN collection['host'] || ':' || if(collection['port'] != '', collection['port'], '5432')
    WHEN collection['hostname'] != ''       THEN collection['hostname'] || ':' || if(collection['port'] != '', collection['port'], '5432')
END

Пример создания:

CREATE NAMED COLLECTION mypg AS
user = 'pguser',
password = 'jw8s0F4',
host = '127.0.0.1',
port = 5432,
database = 'test',
schema = 'test_schema'

Пример конфигурации:

<clickhouse>
    <named_collections>
        <mypg>
            <user>pguser</user>
            <password>jw8s0F4</password>
            <host>127.0.0.1</host>
            <port>5432</port>
            <database>test</database>
            <schema>test_schema</schema>
        </mypg>
    </named_collections>
</clickhouse>

​

Пример использования именованных коллекций с функцией postgresql

SELECT * FROM postgresql(mypg, table = 'test');

┌─a─┬─b───┐
│ 2 │ two │
│ 1 │ one │
└───┴─────┘
SELECT * FROM postgresql(mypg, table = 'test', schema = 'public');

┌─a─┐
│ 1 │
│ 2 │
│ 3 │
└───┘

​

Пример использования именованных коллекций с базой данных на движке PostgreSQL

CREATE TABLE mypgtable (a Int64) ENGINE = PostgreSQL(mypg, table = 'test', schema = 'public');

SELECT * FROM mypgtable;

┌─a─┐
│ 1 │
│ 2 │
│ 3 │
└───┘

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

​

Пример использования именованных коллекций с базой данных на движке PostgreSQL

CREATE DATABASE mydatabase ENGINE = PostgreSQL(mypg);

SHOW TABLES FROM mydatabase

┌─name─┐
│ test │
└──────┘

​

Пример использования именованных коллекций со словарём на основе источника POSTGRESQL

CREATE DICTIONARY dict (a Int64, b String)
PRIMARY KEY a
SOURCE(POSTGRESQL(NAME mypg TABLE test))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED());

SELECT dictGet('dict', 'b', 2);

┌─dictGet('dict', 'b', 2)─┐
│ two                     │
└─────────────────────────┘

​

Именованные коллекции для доступа к удалённой базе данных ClickHouse

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

Пример конфигурации:

CREATE NAMED COLLECTION remote1 AS
host = 'remote_host',
port = 9000,
database = 'system',
user = 'foo',
password = 'secret',
secure = 1

<clickhouse>
    <named_collections>
        <remote1>
            <host>remote_host</host>
            <port>9000</port>
            <database>system</database>
            <user>foo</user>
            <password>secret</password>
            <secure>1</secure>
        </remote1>
    </named_collections>
</clickhouse>

secure не нужен для подключения, так как используется remoteSecure, но его можно использовать для словарей.

​

Пример использования именованных коллекций в функциях remote/remoteSecure

SELECT * FROM remote(remote1, table = one);
┌─dummy─┐
│     0 │
└───────┘

SELECT * FROM remote(remote1, database = merge(system, '^one'));
┌─dummy─┐
│     0 │
└───────┘

INSERT INTO FUNCTION remote(remote1, database = default, table = test) VALUES (1,'a');

SELECT * FROM remote(remote1, database = default, table = test);
┌─a─┬─b─┐
│ 1 │ a │
└───┴───┘

​

Пример использования именованных коллекций со словарём на основе ClickHouse

CREATE DICTIONARY dict(a Int64, b String)
PRIMARY KEY a
SOURCE(CLICKHOUSE(NAME remote1 TABLE test DB default))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED());

SELECT dictGet('dict', 'b', 1);
┌─dictGet('dict', 'b', 1)─┐
│ a                       │
└─────────────────────────┘

​

Именованные коллекции для доступа к Kafka

Описание параметров приведено в разделе Kafka.

​

Пример DDL

CREATE NAMED COLLECTION my_kafka_cluster AS
kafka_broker_list = 'localhost:9092',
kafka_topic_list = 'kafka_topic',
kafka_group_name = 'consumer_group',
kafka_format = 'JSONEachRow',
kafka_max_block_size = '1048576';

​

Пример XML

<clickhouse>
    <named_collections>
        <my_kafka_cluster>
            <kafka_broker_list>localhost:9092</kafka_broker_list>
            <kafka_topic_list>kafka_topic</kafka_topic_list>
            <kafka_group_name>consumer_group</kafka_group_name>
            <kafka_format>JSONEachRow</kafka_format>
            <kafka_max_block_size>1048576</kafka_max_block_size>
        </my_kafka_cluster>
    </named_collections>
</clickhouse>

​

Пример использования именованных коллекций с таблицей Kafka

В обоих приведённых ниже примерах используется одна и та же именованная коллекция my_kafka_cluster:

CREATE TABLE queue
(
    timestamp UInt64,
    level String,
    message String
)
ENGINE = Kafka(my_kafka_cluster)

CREATE TABLE queue
(
    timestamp UInt64,
    level String,
    message String
)
ENGINE = Kafka(my_kafka_cluster)
SETTINGS kafka_num_consumers = 4,
         kafka_thread_per_consumer = 1;

​

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

Описание параметров см. в разделе Резервное копирование и восстановление.

​

Пример DDL

BACKUP TABLE default.test to S3(named_collection_s3_backups, 'directory')

​

Пример XML

<clickhouse>
    <named_collections>
        <named_collection_s3_backups>
            <url>https://my-s3-bucket.s3.amazonaws.com/backup-S3/</url>
            <access_key_id>ABC123</access_key_id>
            <secret_access_key>Abc+123</secret_access_key>
        </named_collection_s3_backups>
    </named_collections>
</clickhouse>

​

Именованные коллекции для доступа к таблице и словарю MongoDB

См. описание параметров в mongodb.

​

Пример DDL

CREATE NAMED COLLECTION mymongo AS
user = '',
password = '',
host = '127.0.0.1',
port = 27017,
database = 'test',
collection = 'my_collection',
options = 'connectTimeoutMS=10000'

​

Пример XML

<clickhouse>
    <named_collections>
        <mymongo>
            <user></user>
            <password></password>
            <host>127.0.0.1</host>
            <port>27017</port>
            <database>test</database>
            <collection>my_collection</collection>
            <options>connectTimeoutMS=10000</options>
        </mymongo>
    </named_collections>
</clickhouse>

​

Таблица MongoDB

CREATE TABLE mytable(log_type VARCHAR, host VARCHAR, command VARCHAR) ENGINE = MongoDB(mymongo, options='connectTimeoutMS=10000&compressors=zstd')
SELECT count() FROM mytable;

┌─count()─┐
│       2 │
└─────────┘

DDL переопределяет настройку options в named collection.

​

Словарь MongoDB

CREATE DICTIONARY dict
(
    `a` Int64,
    `b` String
)
PRIMARY KEY a
SOURCE(MONGODB(NAME mymongo COLLECTION my_dict))
LIFETIME(MIN 1 MAX 2)
LAYOUT(HASHED())

SELECT dictGet('dict', 'b', 2);

┌─dictGet('dict', 'b', 2)─┐
│ two                     │
└─────────────────────────┘

Именованная коллекция указывает my_collection в качестве имени коллекции. В вызове функции это значение переопределяется через collection = 'my_dict', чтобы выбрать другую коллекцию.