Named collections - ClickHouse Documentation

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.

DDL-created named collections can be enabled on select ClickHouse Cloud services. Contact Support to confirm availability. Named collections defined in configuration files aren’t available because users can’t modify server configuration files in ClickHouse Cloud.

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. Overriding a parameter stored in a named collection requires the SHOW NAMED COLLECTIONS SECRETS privilege on that collection, including when the parameter is marked OVERRIDABLE. Keys marked NOT OVERRIDABLE in SQL or overridable="false" in XML cannot be overridden. Adding a parameter that the collection does not store does not require this additional privilege. Aliases, such as addresses_expr for host, count as overrides of the stored parameter.

GRANT SHOW NAMED COLLECTIONS SECRETS ON my_collection TO my_user;

This check requires the privilege alone; it does not depend on the settings that control displaying secrets. The allow_named_collection_override_by_default setting is obsolete and has no effect. Dictionary sources follow the same rule. The privilege is checked when the dictionary is created, attached, or restored. When the dictionary is loaded, only keys marked NOT OVERRIDABLE are enforced.

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 be overridden by users with SHOW NAMED COLLECTIONS SECRETS on the collection.
key_2 can never be overridden.
url can be overridden by users with SHOW NAMED COLLECTIONS SECRETS on the collection.

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.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>
      <!-- Uncomment to also let this user see the values stored in named collections. -->
      <!-- <show_named_collections_secrets>1</show_named_collections_secrets> -->
    </default>
  </users>
</clickhouse>

In the above example the password_sha256_hex value is the hexadecimal representation of the SHA256 hash of the password. This configuration for the user default has the attribute replace=true as in the default configuration has a plain text password set, and it is not possible to have both plain text and sha256 hex passwords set for a user.

named_collection_control allows the user to create, alter and drop named collections, but not to read back the values that are already stored in them. That is a separate privilege, SHOW NAMED COLLECTIONS SECRETS, enabled by the show_named_collections_secrets setting. A user can only grant the privileges that they have themselves, so while show_named_collections_secrets is disabled, the user does not have the complete set of privileges and GRANT ALL ON *.* TO another_user WITH GRANT OPTION is rejected with (Missing permissions: SHOW NAMED COLLECTIONS SECRETS ON *).

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

In the above example:

key_1 can be overridden by users with SHOW NAMED COLLECTIONS SECRETS on the collection.
key_2 can never be overridden.
url can be overridden by users with SHOW NAMED COLLECTIONS SECRETS on the collection.

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

The first argument to the s3() function above is the name of the collection, s3_mydata. Without named collections, the access key ID, secret, format, and URL would all be passed in every call to the s3() function.

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;
┌─number─┐
│      0 │
│      1 │
│      2 │
└────────┘

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

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

The named collection does not specify the table parameter, so it is specified in the function call as 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;

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

The DDL overrides the named collection setting for connection_pool_size.

MySQL database

CREATE DATABASE mydatabase ENGINE = MySQL(mymysql);

SHOW TABLES FROM mydatabase;

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

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

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

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

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

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

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;

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

PostgreSQL copies data from the named collection when the table is being created. A change in the collection does not affect the existing tables.

Example of using named collections with database with engine PostgreSQL

CREATE DATABASE mydatabase ENGINE = PostgreSQL(mypg);

SHOW TABLES FROM mydatabase

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

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

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

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);
┌─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 │
└───┴───┘

Example of using named collections with a dictionary with source ClickHouse

CREATE NAMED COLLECTION remote_dict AS
host = 'localhost',
port = 9000,
database = 'default',
user = 'default',
password = '';

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

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

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>

OAUTHBEARER/OIDC authentication

OAUTHBEARER options are extended librdkafka settings, so they must be nested under <kafka> inside each named collection. Top-level kafka_* keys configure the Kafka table engine. ClickHouse converts underscores in the nested XML element names to periods before passing them to librdkafka. The following example configures OAuth client credentials for an Azure Event Hubs namespace:

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

<clickhouse>
    <named_collections>
        <eventhub_one>
            <!-- Kafka table-engine settings -->
            <kafka_broker_list>domain.one.servicebus.windows.net:9093</kafka_broker_list>
            <kafka_topic_list>my-topic</kafka_topic_list>
            <kafka_group_name>my-group</kafka_group_name>
            <kafka_format>JSONEachRow</kafka_format>
            <kafka_security_protocol>sasl_ssl</kafka_security_protocol>
            <kafka_sasl_mechanism>OAUTHBEARER</kafka_sasl_mechanism>

            <!-- Extended librdkafka settings -->
            <kafka>
                <sasl_oauthbearer_method>oidc</sasl_oauthbearer_method>
                <sasl_oauthbearer_client_id from_env="EVENTHUB_CLIENT_ID"/>
                <sasl_oauthbearer_client_secret from_env="EVENTHUB_CLIENT_SECRET"/>
                <sasl_oauthbearer_token_endpoint_url>https://login.microsoftonline.com/TENANT_ID/oauth2/v2.0/token</sasl_oauthbearer_token_endpoint_url>
                <sasl_oauthbearer_scope>https://domain.one.servicebus.windows.net/.default</sasl_oauthbearer_scope>
            </kafka>
        </eventhub_one>
    </named_collections>
</clickhouse>

When ENGINE = Kafka(eventhub_one) uses a named collection, ClickHouse does not merge the global server <kafka> configuration. Create one collection per Event Hubs namespace when the broker address or OAuth scope differs, and include every required OIDC property in that collection. The from_env attributes in this example apply to XML-defined named collections and read their values from the ClickHouse server process environment. See substitution by environment variables for deployment and default-value details, and encrypting and hiding configuration to protect secrets in preprocessed configuration files.

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;

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

The DDL overrides the named collection setting for options.

MongoDB Dictionary

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

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

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

The dictionary uses my_collection, as specified in the named collection. To use another MongoDB collection, define a separate named collection with that value.

​Storing named collections in the system database

​DDL example

​Permissions to create named collections with DDL

​Storage for named collections

​Storing named collections in configuration files

​XML example

​Modifying named collections

​Alter a DDL named collection

​Drop the DDL named collection collection2:

​Named collections for accessing S3

​DDL example

​XML example

​s3() function and S3 Table named collection examples

​s3() function

​S3 table

​Named collections for accessing MySQL database

​DDL example

​XML example

​mysql() function, MySQL table, MySQL database, and Dictionary named collection examples

​mysql() function

​MySQL table

​MySQL database

​MySQL Dictionary

​Named collections for accessing PostgreSQL database

​Example of using named collections with the postgresql function

​Example of using named collections with database with engine PostgreSQL

​Example of using named collections with database with engine PostgreSQL

​Example of using named collections with a dictionary with source POSTGRESQL

​Named collections for accessing a remote ClickHouse database

​Example of using named collections with the remote/remoteSecure functions

​Example of using named collections with a dictionary with source ClickHouse

​Named collections for accessing Kafka

​DDL example

​XML example

​OAUTHBEARER/OIDC authentication

​Example of using named collections with a Kafka table

​Named collections for backups

​DDL example

​XML example

​Named collections for accessing MongoDB Table and Dictionary

​DDL example

​XML example

​MongoDB table

​MongoDB Dictionary

Storing named collections in the system database

DDL example

Permissions to create named collections with DDL

Storage for named collections

Storing named collections in configuration files

XML example

Modifying named collections

Alter a DDL named collection

Drop the DDL named collection `collection2`:

Named collections for accessing S3

DDL example

XML example

s3() function and S3 Table named collection examples

s3() function

S3 table

Named collections for accessing MySQL database

DDL example

XML example

mysql() function, MySQL table, MySQL database, and Dictionary named collection examples

mysql() function

MySQL table

MySQL database

MySQL Dictionary

Named collections for accessing PostgreSQL database

Example of using named collections with the postgresql function

Example of using named collections with database with engine PostgreSQL

Example of using named collections with database with engine PostgreSQL

Example of using named collections with a dictionary with source POSTGRESQL

Named collections for accessing a remote ClickHouse database

Example of using named collections with the `remote`/`remoteSecure` functions

Example of using named collections with a dictionary with source ClickHouse

Named collections for accessing Kafka

DDL example

XML example

OAUTHBEARER/OIDC authentication

Example of using named collections with a Kafka table

Named collections for backups

DDL example

XML example

Named collections for accessing MongoDB Table and Dictionary

DDL example

XML example

MongoDB table

MongoDB Dictionary