ClickHouse DB::Exception: Dictionary already exists

The "DB::Exception: Dictionary already exists" error in ClickHouse occurs when you run a CREATE DICTIONARY statement for a dictionary name that is already registered on the server. This is the DICTIONARY_ALREADY_EXISTS error code, and it prevents accidental overwriting of existing dictionary definitions.

Impact

The CREATE DICTIONARY statement fails, and the existing dictionary remains unchanged. This is a metadata-level error with no impact on data or running queries. It is commonly encountered in deployment scripts or migration tools that attempt to create dictionaries without checking for their existence first.

Common Causes

1. Running CREATE DICTIONARY without IF NOT EXISTS -- the most common cause is simply omitting the IF NOT EXISTS clause.
2. Deployment scripts run multiple times -- CI/CD pipelines or migration scripts that re-execute without idempotency checks.
3. Dictionary defined in both DDL and XML -- a dictionary may already exist from an XML configuration file, and a DDL CREATE DICTIONARY statement attempts to create the same name.
4. Name collision across databases -- attempting to create a dictionary with a name that already exists in the target database.
5. Residual metadata after incomplete drop -- in rare cases, dictionary metadata may persist after a failed DROP operation.

Troubleshooting and Resolution Steps

1. Use IF NOT EXISTS to make the statement idempotent:

   CREATE DICTIONARY IF NOT EXISTS my_dictionary (
       id UInt64,
       name String
   )
   PRIMARY KEY id
   SOURCE(CLICKHOUSE(TABLE 'source_table' DB 'default'))
   LAYOUT(HASHED())
   LIFETIME(MIN 300 MAX 600);
   

2. Check if the dictionary already exists:

   SELECT name, database, status FROM system.dictionaries WHERE name = 'my_dictionary';
   

3. Drop the existing dictionary if you need to recreate it:

   DROP DICTIONARY IF EXISTS my_dictionary;
   CREATE DICTIONARY my_dictionary (...) ...;
   

4. Use CREATE OR REPLACE to update the definition:

   CREATE OR REPLACE DICTIONARY my_dictionary (
       id UInt64,
       name String,
       new_column Float64
   )
   PRIMARY KEY id
   SOURCE(CLICKHOUSE(TABLE 'source_table' DB 'default'))
   LAYOUT(HASHED())
   LIFETIME(MIN 300 MAX 600);
   

5. Check for XML-defined dictionaries that may conflict:

   grep -r 'my_dictionary' /etc/clickhouse-server/
   

   If the dictionary is defined in XML, either remove the XML definition or use a different name for the DDL version.

Best Practices

• Always use CREATE DICTIONARY IF NOT EXISTS in migration scripts and deployment pipelines for idempotency.
• Prefer DDL-based dictionary management over XML configuration for easier version control and reproducibility.
• If you need to update a dictionary definition, use CREATE OR REPLACE DICTIONARY rather than DROP followed by CREATE.
• Maintain a clear inventory of dictionary names to avoid conflicts between teams or services.
• Include database names explicitly in dictionary definitions to avoid cross-database collisions.

Frequently Asked Questions

Q: Can I have a dictionary and a table with the same name?
A: Yes, dictionaries and tables occupy separate namespaces in ClickHouse. A dictionary named geo_lookup can coexist with a table named geo_lookup in the same database.

Q: Does CREATE OR REPLACE DICTIONARY cause downtime?
A: The replacement is atomic from the perspective of queries. Running queries that reference the dictionary will either see the old or new definition, with no intermediate state where the dictionary is missing.

Q: What if the dictionary exists in XML config and I want to manage it via DDL?
A: Remove the XML definition first, reload the configuration with SYSTEM RELOAD CONFIG, and then create the dictionary via DDL. Having both can cause conflicts and unexpected behavior.

Q: Does dropping and recreating a dictionary affect data?
A: Dictionaries do not store data permanently -- they load data from their configured source. Dropping and recreating a dictionary only removes the definition. Data will be reloaded from the source when the new dictionary is created.