> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-postgresql-tls-support.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for Named collections

# Named collections

export const CloudNotSupportedBadge = () => {
  return <div className="cloudNotSupportedBadge">
            <div className="cloudNotSupportedIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.5" d="M6.33366 12.6666L12.3739 12.6667C13.6593 12.6667 14.7073 11.6187 14.7073 10.3334C14.7073 9.04804 13.6593 8.00003 12.3739 8.00003C12.3739 8.00003 12.3337 7.66659 12.0003 7.33325M10.667 5.33322C8.00033 2.33325 4.45395 4.78537 4.14195 6.68203C2.55728 6.7627 1.29395 8.06203 1.29395 9.6667C1.29395 11.3234 2.66699 12.6666 4.00033 12.6666" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.5" d="M2.66699 14L12.0003 4.66663" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>

        </div>
            Not supported in ClickHouse Cloud
        </div>;
};

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

<Warning>
  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).
</Warning>

<h2 id="storing-named-collections-in-the-system-database">
  Storing named collections in the system database
</h2>

<h3 id="ddl-example">
  DDL example
</h3>

```sql theme={null}
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`.

<h3 id="permissions-to-create-named-collections-with-ddl">
  Permissions to create named collections with DDL
</h3>

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:

```xml title='/etc/clickhouse-server/users.d/user_default.xml' highlight={6} theme={null}
<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>
```

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

<h3 id="storage-for-named-collections">
  Storage for named collections
</h3>

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](/concepts/features/configuration/server-config/storing-data#encrypted-virtual-file-system),
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:

```xml theme={null}
<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`.

<h2 id="storing-named-collections-in-configuration-files">
  Storing named collections in configuration files
</h2>

<h3 id="xml-example">
  XML example
</h3>

```xml title='/etc/clickhouse-server/config.d/named_collections.xml' theme={null}
<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`.

<h2 id="modifying-named-collections">
  Modifying named collections
</h2>

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.

<h3 id="alter-a-ddl-named-collection">
  Alter a DDL named collection
</h3>

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

```sql theme={null}
ALTER NAMED COLLECTION collection2 SET key1=4, key3='value3'
```

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

```sql theme={null}
ALTER NAMED COLLECTION collection2 SET key1=4 OVERRIDABLE
```

Remove the key `key2` from `collection2`:

```sql theme={null}
ALTER NAMED COLLECTION collection2 DELETE key2
```

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

```sql theme={null}
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.

```sql theme={null}
ALTER NAMED COLLECTION collection2 DELETE key1;
ALTER NAMED COLLECTION collection2 SET key1=4;
```

<h3 id="drop-the-ddl-named-collection-collection2">
  Drop the DDL named collection `collection2`:
</h3>

```sql theme={null}
DROP NAMED COLLECTION collection2
```

<h2 id="named-collections-for-accessing-s3">
  Named collections for accessing S3
</h2>

The description of parameters see [s3 Table Function](/reference/functions/table-functions/s3).

<h3 id="ddl-example-1">
  DDL example
</h3>

```sql theme={null}
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/'
```

<h3 id="xml-example-1">
  XML example
</h3>

```xml theme={null}
<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>
```

<h3 id="s3-function-and-s3-table-named-collection-examples">
  s3() function and S3 Table named collection examples
</h3>

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

<h4 id="s3-function">
  s3() function
</h4>

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

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

<h4 id="s3-table">
  S3 table
</h4>

```sql theme={null}
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 │
└────────┘
```

<h2 id="named-collections-for-accessing-mysql-database">
  Named collections for accessing MySQL database
</h2>

The description of parameters see [mysql](/reference/functions/table-functions/mysql).

<h3 id="ddl-example-2">
  DDL example
</h3>

```sql theme={null}
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
```

<h3 id="xml-example-2">
  XML example
</h3>

```xml theme={null}
<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>
```

<h3 id="mysql-function-mysql-table-mysql-database-and-dictionary-named-collection-examples">
  mysql() function, MySQL table, MySQL database, and Dictionary named collection examples
</h3>

The four following examples use the same named collection `mymysql`:

<h4 id="mysql-function">
  mysql() function
</h4>

```sql theme={null}
SELECT count() FROM mysql(mymysql, table = 'test');

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

<Note>
  The named collection does not specify the `table` parameter, so it is specified in the function call as `table = 'test'`.
</Note>

<h4 id="mysql-table">
  MySQL table
</h4>

```sql theme={null}
CREATE TABLE mytable(A Int64) ENGINE = MySQL(mymysql, table = 'test', connection_pool_size=3, replace_query=0);
SELECT count() FROM mytable;

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

<Note>
  The DDL overrides the named collection setting for connection\_pool\_size.
</Note>

<h4 id="mysql-database">
  MySQL database
</h4>

```sql theme={null}
CREATE DATABASE mydatabase ENGINE = MySQL(mymysql);

SHOW TABLES FROM mydatabase;

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

<h4 id="mysql-dictionary">
  MySQL Dictionary
</h4>

```sql theme={null}
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                     │
└─────────────────────────┘
```

<h2 id="named-collections-for-accessing-postgresql-database">
  Named collections for accessing PostgreSQL database
</h2>

The description of parameters see [postgresql](/reference/functions/table-functions/postgresql). Additionally, there are aliases:

* `username` for `user`
* `db` for `database`.

The connection-pool settings of the [PostgreSQL table engine](/reference/engines/table-engines/integrations/postgresql) (`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:

```sql theme={null}
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:

```sql theme={null}
CREATE NAMED COLLECTION mypg AS
user = 'pguser',
password = 'jw8s0F4',
host = '127.0.0.1',
port = 5432,
database = 'test',
schema = 'test_schema'
```

Example of configuration:

```xml theme={null}
<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>
```

<h3 id="example-of-using-named-collections-with-the-postgresql-function">
  Example of using named collections with the postgresql function
</h3>

```sql theme={null}
SELECT * FROM postgresql(mypg, table = 'test');

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

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

<h3 id="example-of-using-named-collections-with-database-with-engine-postgresql">
  Example of using named collections with database with engine PostgreSQL
</h3>

```sql theme={null}
CREATE TABLE mypgtable (a Int64) ENGINE = PostgreSQL(mypg, table = 'test', schema = 'public');

SELECT * FROM mypgtable;

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

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

<h3 id="example-of-using-named-collections-with-database-with-engine-postgresql-1">
  Example of using named collections with database with engine PostgreSQL
</h3>

```sql theme={null}
CREATE DATABASE mydatabase ENGINE = PostgreSQL(mypg);

SHOW TABLES FROM mydatabase

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

<h3 id="example-of-using-named-collections-with-a-dictionary-with-source-postgresql">
  Example of using named collections with a dictionary with source POSTGRESQL
</h3>

```sql theme={null}
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                     │
└─────────────────────────┘
```

<h2 id="named-collections-for-accessing-a-remote-clickhouse-database">
  Named collections for accessing a remote ClickHouse database
</h2>

The description of parameters see [remote](/reference/functions/table-functions/remote#parameters).

Example of configuration:

```sql theme={null}
CREATE NAMED COLLECTION remote1 AS
host = 'remote_host',
port = 9000,
database = 'system',
user = 'foo',
password = 'secret',
secure = 1
```

```xml theme={null}
<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.

<h3 id="example-of-using-named-collections-with-the-remoteremotesecure-functions">
  Example of using named collections with the `remote`/`remoteSecure` functions
</h3>

```sql theme={null}
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 │
└───┴───┘
```

<h3 id="example-of-using-named-collections-with-a-dictionary-with-source-clickhouse">
  Example of using named collections with a dictionary with source ClickHouse
</h3>

```sql theme={null}
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                       │
└─────────────────────────┘
```

<h2 id="named-collections-for-accessing-kafka">
  Named collections for accessing Kafka
</h2>

The description of parameters see [Kafka](/reference/engines/table-engines/integrations/kafka).

<h3 id="ddl-example-3">
  DDL example
</h3>

```sql theme={null}
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';

```

<h3 id="xml-example-3">
  XML example
</h3>

```xml theme={null}
<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>
```

<h3 id="example-of-using-named-collections-with-a-kafka-table">
  Example of using named collections with a Kafka table
</h3>

Both of the following examples use the same named collection `my_kafka_cluster`:

```sql theme={null}
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;
```

<h2 id="named-collections-for-backups">
  Named collections for backups
</h2>

For the description of parameters see [Backup and Restore](/concepts/features/backup-restore/overview).

<h3 id="ddl-example-4">
  DDL example
</h3>

```sql theme={null}
BACKUP TABLE default.test to S3(named_collection_s3_backups, 'directory')
```

<h3 id="xml-example-4">
  XML example
</h3>

```xml theme={null}
<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>
```

<h2 id="named-collections-for-accessing-mongodb-table-and-dictionary">
  Named collections for accessing MongoDB Table and Dictionary
</h2>

For the description of parameters see [mongodb](/reference/functions/table-functions/mongodb).

<h3 id="ddl-example-5">
  DDL example
</h3>

```sql theme={null}
CREATE NAMED COLLECTION mymongo AS
user = '',
password = '',
host = '127.0.0.1',
port = 27017,
database = 'test',
collection = 'my_collection',
options = 'connectTimeoutMS=10000'
```

<h3 id="xml-example-5">
  XML example
</h3>

```xml theme={null}
<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>
```

<h4 id="mongodb-table">
  MongoDB table
</h4>

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

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

<Note>
  The DDL overrides the named collection setting for options.
</Note>

<h4 id="mongodb-dictionary">
  MongoDB Dictionary
</h4>

```sql theme={null}
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                     │
└─────────────────────────┘
```

<Note>
  The named collection specifies `my_collection` for the collection name. In the function call it is overwritten by `collection = 'my_dict'` to select another collection.
</Note>