ClickHouse DB::Exception: Named collection doesn't exist

The NAMED_COLLECTION_DOESNT_EXIST error occurs when a query references a named collection that ClickHouse cannot find. Named collections are predefined sets of key-value parameters (such as connection strings, credentials, or S3 bucket details) that simplify integration with external systems. When the referenced collection name does not match any defined collection, this error is raised.

Impact

The query that references the missing named collection fails immediately. This blocks any workflow that relies on the collection for connecting to external data sources such as S3, PostgreSQL, MySQL, or other integrations. If the collection was recently deleted or renamed, multiple queries and materialized views may break simultaneously.

Common Causes

1. Typo in the collection name -- A misspelled name in the query does not match any existing collection.
2. Collection not yet created -- The collection was expected to be defined in configuration or via DDL, but the setup step was missed.
3. Collection defined in config but not loaded -- Named collections defined in XML configuration files require a server reload or restart to take effect. If the config was added but the server was not reloaded, the collection will not be available.
4. Collection dropped or renamed -- Someone removed or renamed the collection, breaking queries that still reference the old name.
5. Cluster-wide inconsistency -- In a cluster, the named collection may be defined on some nodes but not others. Distributed queries may fail when they hit a node without the collection.
6. Wrong database or access scope -- Named collections created via SQL DDL are stored centrally, but permissions may restrict visibility.

Troubleshooting and Resolution Steps

1. List all available named collections:

   SHOW NAMED COLLECTIONS;
   

   Verify that the collection name you are referencing appears in the output.

2. Check for typos: Compare the collection name in your query against the list of available collections. Names are case-sensitive.

3. Create the missing named collection via DDL: If the collection does not exist, create it:

   CREATE NAMED COLLECTION my_s3_collection AS
       url = 'https://my-bucket.s3.amazonaws.com/data/',
       access_key_id = 'AKIA...',
       secret_access_key = '...',
       format = 'Parquet';
   

4. Or define it in the server configuration: Add the collection to a config file (e.g., /etc/clickhouse-server/config.d/named_collections.xml):

   <clickhouse>
       <named_collections>
           <my_s3_collection>
               <url>https://my-bucket.s3.amazonaws.com/data/</url>
               <access_key_id>AKIA...</access_key_id>
               <secret_access_key>...</secret_access_key>
               <format>Parquet</format>
           </my_s3_collection>
       </named_collections>
   </clickhouse>
   

   Then reload the configuration:

   SYSTEM RELOAD CONFIG;
   

5. Ensure consistency across cluster nodes: If using a cluster, verify the collection exists on every node. For config-based collections, distribute the config file to all nodes. For DDL-based collections, they should propagate automatically in recent versions.

6. Check permissions: Ensure the user running the query has the NAMED COLLECTION privilege:

   GRANT NAMED COLLECTION ON my_s3_collection TO my_user;
   

Best Practices

• Use DDL-based named collections (CREATE NAMED COLLECTION) for easier management and cluster-wide consistency.
• Keep named collection definitions under version control, whether they are in XML config files or in migration scripts.
• Standardize a naming convention for collections to reduce the risk of typos (e.g., source_type_environment like s3_analytics_prod).
• Audit named collections periodically and remove unused ones to keep the namespace clean.
• Avoid hardcoding credentials in queries -- use named collections as the single source of truth for external connection details.

Frequently Asked Questions

Q: What is the difference between config-based and DDL-based named collections?
A: Config-based collections are defined in XML files and require a config reload or server restart. DDL-based collections are created with SQL statements and take effect immediately. DDL-based collections are generally more convenient and are the recommended approach in newer ClickHouse versions.

Q: Can I modify a named collection after creating it?
A: Yes, if the collection was created via DDL. Use ALTER NAMED COLLECTION to update its parameters. Config-based collections must be edited in the configuration file and then reloaded.

Q: Do named collections persist across server restarts?
A: Yes. DDL-based collections are stored persistently by ClickHouse. Config-based collections persist as long as the configuration file is present.

Q: Can I use named collections with any ClickHouse integration?
A: Named collections are supported by most integrations including S3, PostgreSQL, MySQL, MongoDB, and others. Check the documentation for your specific integration to confirm support.