Named collections
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.
If override is allowed, it may be possible for users without administrative access to
figure out the credentials that you are trying to hide.
If you are using named collections with that purpose, you should disable
allow_named_collection_override_by_default (which is enabled by default).
Storing named collections in the system database
DDL example
In the above example:
key_1can always be overridden.key_2can never be overridden.urlcan be overridden or not depending on the value ofallow_named_collection_override_by_default.
Permissions to create named collections with DDL
To manage named collections with DDL a user must have the named_control_collection 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:
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.
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:
An optional configuration parameter update_timeout_ms by default is equal to 5000.
Storing named collections in configuration files
XML example
In the above example:
key_1can always be overridden.key_2can never be overridden.urlcan be overridden or not depending on the value ofallow_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):
Change or add the key key1 and allow it to be always overridden:
Remove the key key2 from collection2:
Change or add the key key1 and delete the key key3 of the collection collection2:
To force a key to use the default settings for the overridable flag, you have to
remove and re-add the key.
Drop the DDL named collection collection2:
Named collections for accessing S3
The description of parameters see s3 Table Function.
DDL example
XML example
s3() function and S3 Table named collection examples
Both of the following examples use the same named collection s3_mydata:
s3() function
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
Named collections for accessing MySQL database
The description of parameters see mysql.
DDL example
XML example
mysql() function, MySQL table, MySQL database, and Dictionary named collection examples
The four following examples use the same named collection mymysql:
mysql() function
The named collection does not specify the table parameter, so it is specified in the function call as table = 'test'.
MySQL table
The DDL overrides the named collection setting for connection_pool_size.
MySQL database
MySQL Dictionary
Named collections for accessing PostgreSQL database
The description of parameters see postgresql. Additionally, there are aliases:
usernameforuserdbfordatabase.
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:
Example of creation:
Example of configuration:
Example of using named collections with the postgresql function
Example of using named collections with database with engine PostgreSQL
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
Example of using named collections with a dictionary with source POSTGRESQL
Named collections for accessing a remote ClickHouse database
The description of parameters see remote.
Example of configuration:
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
Example of using named collections with a dictionary with source ClickHouse
Named collections for accessing Kafka
The description of parameters see Kafka.
DDL example
XML example
Example of using named collections with a Kafka table
Both of the following examples use the same named collection my_kafka_cluster:
Named collections for backups
For the description of parameters see Backup and Restore.