Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

Named collections

View as MarkdownEdit this pageReport an issueSet up docs MCP

Named collections provide a way to store collections of key-value pairs to be used to configure integrations with external sources. You can use named collections with dictionaries, tables, table functions, and object storage.

Named collections can be configured with DDL or in configuration files and are applied when ClickHouse starts. They simplify the creation of objects and the hiding of credentials from users without administrative access.

The keys in a named collection must match the parameter names of the corresponding function, table engine, database, etc. In the examples below the parameter list is linked to for each type.

Parameters set in a named collection can be overridden in SQL, this is shown in the examples below. This ability can be limited using [NOT] OVERRIDABLE keywords and XML attributes and/or the configuration option allow_named_collection_override_by_default.

Storing named collections in the system database

DDL example

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

In the above example:

  • key_1 can always be overridden.
  • key_2 can never be overridden.
  • url can be overridden or not depending on the value of allow_named_collection_override_by_default.

Permissions to create named collections with DDL

To manage named collections with DDL a user must have the named_collection_control privilege. This can be assigned by adding a file to /etc/clickhouse-server/users.d/. The example gives the user default both the access_management and named_collection_control privileges:

/etc/clickhouse-server/users.d/user_default.xmlxml
<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>

Storage for named collections

Named collections can either be stored on local disk or in ZooKeeper/Keeper. By default local storage is used. They can also be stored using encryption with the same algorithms used for disk encryption, where aes_128_ctr is used by default.

To configure named collections storage you need to specify a type. This can be either local or keeper/zookeeper. For encrypted storage, you can use local_encrypted or keeper_encrypted/zookeeper_encrypted.

To use ZooKeeper/Keeper we also need to set up a path (path in ZooKeeper/Keeper, where named collections will be stored) to named_collections_storage section in configuration file. The following example uses encryption and 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>

An optional configuration parameter update_timeout_ms by default is equal to 5000.

You can inspect the active storage type through system.server_settings and getServerSetting:

SELECT value, getServerSetting('named_collections_storage_type')
FROM system.server_settings
WHERE name = 'named_collections_storage.type';

Changing the storage type requires a server restart; SYSTEM RELOAD CONFIG does not change the active backend.

Storing named collections in configuration files

XML example

/etc/clickhouse-server/config.d/named_collections.xmlxml
<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>

In the above example:

  • key_1 can always be overridden.
  • key_2 can never be overridden.
  • url can be overridden or not depending on the value of allow_named_collection_override_by_default.

Modifying named collections

Named collections that are created with DDL queries can be altered or dropped with DDL. Named collections created with XML files can be managed by editing or deleting the corresponding XML.

Alter a DDL named collection

Change or add the keys key1 and key3 of the collection collection2 (this will not change the value of the overridable flag for those keys):

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

Change or add the key key1 and allow it to be always overridden:

ALTER NAMED COLLECTION collection2 SET key1=4 OVERRIDABLE

Remove the key key2 from collection2:

ALTER NAMED COLLECTION collection2 DELETE key2

Change or add the key key1 and delete the key key3 of the collection collection2:

ALTER NAMED COLLECTION collection2 SET key1=4, DELETE key3

To force a key to use the default settings for the overridable flag, you have to remove and re-add the key.

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

Drop the DDL named collection collection2:

DROP NAMED COLLECTION collection2

Named collections for accessing S3

The description of parameters see s3 Table Function.

DDL example

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 example

<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() function and S3 Table named collection examples

Both of the following examples use the same named collection s3_mydata:

s3() function

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

S3 table

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;
┌

Named collections for accessing MySQL database

The description of parameters see mysql.

DDL example

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 example

<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() function, MySQL table, MySQL database, and Dictionary named collection examples

The four following examples use the same named collection mymysql:

mysql() function

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

┌

MySQL table

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

┌

MySQL database

CREATE DATABASE mydatabase ENGINE = MySQL(mymysql);

SHOW TABLES FROM mydatabase;

┌

MySQL Dictionary

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);

┌

Named collections for accessing PostgreSQL database

The description of parameters see postgresql. Additionally, there are aliases:

  • username for user
  • db for database.

The connection-pool settings of the PostgreSQL table engine (postgresql_connection_pool_size and the other postgresql_* settings) can also be stored in the collection or passed as key = value overrides. They apply to the PostgreSQL table engine, the postgresql table function, and the PostgreSQL database engine; an explicit SETTINGS clause on a table takes precedence over the values from the collection.

Parameter addresses_expr is used in a collection instead of host:port. The parameter is optional, because there are other optional ones: host, hostname, port. The following pseudo code explains the priority:

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

Example of creation:

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

Example of configuration:

<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>

Example of using named collections with the postgresql function

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

┌

Example of using named collections with database with engine PostgreSQL

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

SELECT * FROM mypgtable;

┌

Example of using named collections with database with engine PostgreSQL

CREATE DATABASE mydatabase ENGINE = PostgreSQL(mypg);

SHOW TABLES FROM mydatabase

┌

Example of using named collections with a dictionary with source 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);

┌

Named collections for accessing a remote ClickHouse database

The description of parameters see remote.

Example of configuration:

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 is not needed for connection because of remoteSecure, but it can be used for dictionaries.

Example of using named collections with the remote/remoteSecure functions

SELECT * FROM remote(remote1, table = one);
┌

Example of using named collections with a dictionary with source 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);
┌

Named collections for accessing Kafka

The description of parameters see Kafka.

DDL example

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 example

<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>

Example of using named collections with a Kafka table

Both of the following examples use the same named collection 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;

Named collections for backups

For the description of parameters see Backup and Restore.

DDL example

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

XML example

<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>

Named collections for accessing MongoDB Table and Dictionary

For the description of parameters see mongodb.

DDL example

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

XML example

<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 table

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

┌

MongoDB Dictionary

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);

┌
Navigation