Managing ClickHouse Named Collections with Atlas

Named collections in ClickHouse provide a centralized way to store key-value pairs — typically connection credentials and configuration for integrations such as S3, PostgreSQL, Kafka, or other ClickHouse instances. Instead of embedding credentials directly in table definitions or dictionaries, you reference a named collection, making it easier to rotate secrets and share configuration across multiple objects.

Atlas lets you define named collections as code and automatically plans the CREATE, ALTER, and DROP statements needed to bring your database in sync with the desired state.

Prerequisites

1. Docker
2. Atlas installed on your machine (installation guide)
3. An Atlas Pro account (run atlas login to authenticate)
4. ClickHouse 23.10 or later (named collections require this minimum version)

Enabling Named Collections

Named collections require the ClickHouse server to have named_collection_control enabled for the connecting user. This is a server-level XML setting and cannot be configured via SQL CREATE SETTINGS PROFILE.

Create a file called users.xml in your project directory:

users.xml

<clickhouse>
  <users>
    <default>
      <named_collection_control>1</named_collection_control>
      <show_named_collections>1</show_named_collections>
    </default>
  </users>
</clickhouse>

This grants the default user permission to create, modify, and list named collections.

Next, create an atlas.hcl project file. Use the volumes attribute to mount the configuration into the dev database container:

Atlas DDL (HCL)

atlas.hcl

docker "clickhouse" "dev" {
  image   = "clickhouse/clickhouse-server:24.10"
  volumes = ["./users.xml:/etc/clickhouse-server/users.d/named_collections.xml:ro"]
}

env "local" {
  dev = docker.clickhouse.dev.url
  schema {
    src = "file://schema.ch.hcl"
  }
}

SQL

atlas.hcl

docker "clickhouse" "dev" {
  image   = "clickhouse/clickhouse-server:24.10"
  volumes = ["./users.xml:/etc/clickhouse-server/users.d/named_collections.xml:ro"]
}

env "local" {
  dev = docker.clickhouse.dev.url
  schema {
    src = "file://schema.sql"
  }
}

Defining a Named Collection

A named_collection block defines a named collection with one or more item blocks, each specifying a key-value pair. Let's create one that stores S3 connection credentials:

note

ClickHouse supports the OVERRIDABLE keyword on individual keys (allowing them to be overridden at query time). This is not yet supported by Atlas and will be ignored during inspection and planning.

Atlas DDL (HCL)

schema.ch.hcl

schema "default" {
  engine = Atomic
}

named_collection "s3_data" {
  item "access_key_id" {
    value = getenv("AWS_ACCESS_KEY_ID")
  }
  item "secret_access_key" {
    value = getenv("AWS_SECRET_ACCESS_KEY")
  }
}

SQL

schema.sql

CREATE NAMED COLLECTION s3_data AS
  access_key_id = 'AKIA...',
  secret_access_key = 'wJalr...';

Versioned Migrations

Named collections work with the versioned migrations workflow. Use atlas migrate diff to generate migration files:

atlas migrate diff add_named_collection --env local

This generates a migration file:

migrations/20250525120000_add_named_collection.sql

-- Create named collection "s3_data"
CREATE NAMED COLLECTION `s3_data` AS `access_key_id` = '', `secret_access_key` = '';

Values are omitted

Notice that the generated migration contains the keys of the named collection but not the actual values. Atlas intentionally omits sensitive values from versioned migration files to prevent credentials from being committed to version control. You are expected to populate the values manually or through a secrets manager before applying the migration.

Adding and Removing Keys

Atlas can detect structural changes — adding new keys or removing existing ones — and will generate the appropriate ALTER NAMED COLLECTION ... SET or ALTER NAMED COLLECTION ... DELETE statements:

-- Add "region" key to named collection "s3_data"
ALTER NAMED COLLECTION `s3_data` SET `region` = '';

-- Remove "region" key from named collection "s3_data"
ALTER NAMED COLLECTION `s3_data` DELETE `region`;

Again, values are omitted in the generated statements — only the key structure is managed.

Value Changes Are Not Tracked

warning

In the versioned workflow, Atlas does not generate migrations for value-only changes. If you modify a value (e.g., changing access_key_id from one key to another) without adding or removing keys, atlas migrate diff will report "no changes to be made".

This is by design — named collection values often contain sensitive credentials, and embedding changing secrets in version-controlled migration files is a security risk.

To modify values, use the declarative workflow described below.

Declarative Workflow with Value Management

The declarative workflow (atlas schema apply) can manage the full lifecycle of named collections, including value changes. To enable Atlas to diff values, configure sensitive = ALLOW in the schema mode:

atlas.hcl

docker "clickhouse" "dev" {
  image   = "clickhouse/clickhouse-server:24.10"
  volumes = ["./users.xml:/etc/clickhouse-server/users.d/named_collections.xml:ro"]
}

env "local" {
  url = getenv("DATABASE_URL")
  dev = docker.clickhouse.dev.url
  schema {
    src = "file://schema.sql"
    mode {
      sensitive = ALLOW
    }
  }
}

With sensitive = ALLOW, Atlas will:

1. Compare the actual values in the database against your schema definition.
2. Generate ALTER NAMED COLLECTION ... SET statements for any changed values.
3. Mask sensitive values in the plan output (the execution still uses real values).

Example: Rotating S3 Credentials

Update your schema with new credentials:

Atlas DDL (HCL)

schema.ch.hcl

schema "default" {
  engine = Atomic
}

named_collection "s3_data" {
  item "access_key_id" {
    value = getenv("AWS_ACCESS_KEY_ID")
  }
  item "secret_access_key" {
    value = getenv("AWS_SECRET_ACCESS_KEY")
  }
  item "region" {
    value = "us-east-1"
  }
}

SQL

schema.sql

CREATE NAMED COLLECTION s3_data AS
  access_key_id = 'AKIA_NEW...',
  secret_access_key = 'wJalr_NEW...',
  region = 'us-east-1';

Run the declarative apply:

atlas schema apply --env local

Atlas plans and applies the change (values are masked in the output):

-- modify named collection "s3_data":
-- atlas:sensitive:
   -> ALTER NAMED COLLECTION (sensitive) SET (sensitive) = (sensitive), (sensitive) = (sensitive), (sensitive) = (sensitive);

Enabling Secret Visibility

note

By default, ClickHouse hides named collection values from inspection (returning [HIDDEN] for sensitive keys). Without secret visibility, Atlas cannot detect actual value changes and will always report the collection as modified (since the inspected [HIDDEN] values never match your schema).

To allow Atlas to read the real values and produce accurate diffs, enable show_named_collections_secrets in your users.xml:

danger

Enabling show_named_collections_secrets exposes all named collection values (including passwords and tokens) to any user or tool that connects with this configuration. This is not recommended for production environments. Consider using a dedicated Atlas user with restricted access, and only enable this setting for that specific user rather than the default profile.

users.xml

<clickhouse>
  <users>
    <default>
      <named_collection_control>1</named_collection_control>
      <show_named_collections>1</show_named_collections>
      <show_named_collections_secrets>1</show_named_collections_secrets>
    </default>
  </users>
</clickhouse>

With this enabled, Atlas reads the actual stored values and only generates changes when they truly differ from the desired state.

Best Practices

Keep Secrets Out of Version Control

Even though Atlas can manage named collection values, avoid committing real credentials to your schema files. Use environment variables or secret managers:

schema.ch.hcl

named_collection "my_source" {
  item "host" {
    value = getenv("DB_HOST")
  }
  item "password" {
    value = getenv("DB_PASSWORD")
  }
}

Enable Secret Visibility Only Where Needed

The show_named_collections_secrets setting should only be enabled for the user Atlas connects with. Do not enable it globally for all users. In production, configure a dedicated XML profile for the Atlas user:

atlas_user.xml

<clickhouse>
  <users>
    <atlas_admin>
      <named_collection_control>1</named_collection_control>
      <show_named_collections>1</show_named_collections>
      <show_named_collections_secrets>1</show_named_collections_secrets>
    </atlas_admin>
  </users>
</clickhouse>

Additionally, grant the user permission to manage named collections:

GRANT NAMED COLLECTION ON *.* TO atlas_admin;

Manage Named Collections with Roles and Users in a Dedicated Environment

Named collections contain sensitive credentials that are tightly coupled with access control — the same users and roles that access them also need GRANT NAMED COLLECTION privileges. Manage them together in a dedicated environment, separate from your schema objects (tables, views, etc.):

atlas.hcl

// Environment for schema objects (tables, views, dictionaries)
env "schema" {
  url = getenv("DATABASE_URL")
  dev = docker.clickhouse.dev.url
  schema {
    src = "file://schema.sql"
  }
}

// Environment for credentials + access control
env "security" {
  url = getenv("DATABASE_URL")
  dev = docker.clickhouse.dev.url
  schema {
    src = "file://security.sql"
    mode {
      sensitive   = ALLOW
      roles       = true
      permissions = true
    }
  }
}

This separation allows you to:

• Deploy schema changes (tables, indexes) without touching credentials or access control.
• Rotate secrets and update permissions together — when a credential changes, the corresponding GRANT NAMED COLLECTION stays in sync.
• Restrict CI/CD permissions — the schema pipeline doesn't need show_named_collections_secrets or GRANT privileges.