title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/named-collections.md)

Not supported in ClickHouse Cloud

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

Note

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[​](#storing-named-collections-in-the-system-database "Direct link to Storing named collections in the system database")

### DDL example[​](#ddl-example "Direct link to 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[​](#permissions-to-create-named-collections-with-ddl "Direct link to 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:

```
<clickhouse>
  <users>
    <default>
      <password_sha256_hex>65e84be33532fb784c48129675f9eff3a682b27168c0ea744b2cf58ee02337c5</password_sha256_hex replace=true>
      <access_management>1</access_management>
      <!-- highlight-start -->
      <named_collection_control>1</named_collection_control>
      <!-- highlight-end -->
    </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.

### Storage for named collections[​](#storage-for-named-collections "Direct link to 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](/docs/operations/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:

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

## Storing named collections in configuration files[​](#storing-named-collections-in-configuration-files "Direct link to Storing named collections in configuration files")

### XML example[​](#xml-example "Direct link to XML example")

```
<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[​](#modifying-named-collections "Direct link to 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[​](#alter-a-ddl-named-collection "Direct link to 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-the-ddl-named-collection-collection2 "Direct link to drop-the-ddl-named-collection-collection2")

```
DROP NAMED COLLECTION collection2
```

## Named collections for accessing S3[​](#named-collections-for-accessing-s3 "Direct link to Named collections for accessing S3")

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

### DDL example[​](#ddl-example-1 "Direct link to 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[​](#xml-example-1 "Direct link to 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[​](#s3-function-and-s3-table-named-collection-examples "Direct link to s3() function and S3 Table named collection examples")

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

#### s3() function[​](#s3-function "Direct link to 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);
```

Tip