ClickHouse DB::Exception: Dictionary is empty

The "DB::Exception: Dictionary is empty" error in ClickHouse indicates that a dictionary you are trying to query contains no data. This can happen because the dictionary source is genuinely empty, the initial load failed silently, or a reload encountered an error and the dictionary reverted to an empty state. The error code is DICTIONARY_IS_EMPTY.

Impact

Any query that calls dictGet, dictHas, or other dictionary functions against the affected dictionary will fail. If the dictionary is used in materialized views, INSERT operations into the source table of those views may also fail. The impact can be broad if many queries depend on the dictionary for lookups.

Common Causes

1. Dictionary source returns no data -- the source table, file, or external service is empty or returns zero rows due to a misconfigured query.
2. Source connection failure during load -- the dictionary attempted to load but could not connect to the source (e.g., MySQL, PostgreSQL, HTTP endpoint), resulting in an empty dictionary.
3. Source query has a restrictive WHERE clause -- the query configured in the dictionary source filters out all rows.
4. Dictionary lifetime expired and reload failed -- the dictionary had data but its lifetime expired, and the subsequent reload attempt failed, leaving it empty.
5. Permissions issue on the source -- the user configured in the dictionary source lacks SELECT permissions on the source table.

Troubleshooting and Resolution Steps

1. Check the dictionary status in system tables:

   SELECT name, status, last_exception, element_count, loading_start_time, last_successful_update_time
   FROM system.dictionaries
   WHERE name = 'your_dictionary';
   

   The last_exception field often reveals the root cause.

2. Manually reload the dictionary and watch for errors:

   SYSTEM RELOAD DICTIONARY your_dictionary;
   

   Then check system.dictionaries again for updated status and exception information.

3. Verify the source query returns data. Run the dictionary's source query directly:

   -- If the source is a ClickHouse table
   SELECT count() FROM source_database.source_table;
   
   -- If using a custom query, run it directly on the source database
   

4. Check source connectivity. For external sources, verify network access, credentials, and permissions:

   -- For MySQL source, test the connection
   SELECT * FROM mysql('host:port', 'database', 'table', 'user', 'password') LIMIT 1;
   

5. Review the dictionary definition for errors:

   SHOW CREATE DICTIONARY your_dictionary;
   

   Look for typos in the source configuration, incorrect table names, or mismatched column definitions.

6. Check ClickHouse server logs for detailed error messages:

   grep -i 'dictionary' /var/log/clickhouse-server/clickhouse-server.log | tail -50
   

Best Practices

• Monitor dictionary load status using system.dictionaries and set up alerts on last_exception being non-empty or element_count dropping to zero.
• Use LIFETIME(MIN 300 MAX 600) to control reload intervals and avoid constant reload storms that could mask transient source issues.
• Configure dictionary load_after_creation and allow_read_expired_keys settings to control behavior when data cannot be loaded.
• Keep dictionary source queries simple and fast to minimize the chance of timeout-related load failures.
• Test dictionary source connectivity and permissions during initial setup before relying on the dictionary in production queries.

Frequently Asked Questions

Q: Will ClickHouse serve stale data if a dictionary reload fails?
A: It depends on the dictionary layout and configuration. For flat, hashed, and complex_key_hashed layouts, ClickHouse keeps the previous version of the dictionary if a reload fails, unless it was never successfully loaded. For cache layout, expired keys will return defaults unless allow_read_expired_keys is enabled.

Q: How can I tell if the dictionary never loaded or if the source is genuinely empty?
A: Check system.dictionaries. If last_successful_update_time is zero (epoch), the dictionary never loaded successfully. If it has a timestamp but element_count is zero, the source may be empty. The last_exception field will indicate connection or query errors.

Q: Can I set a default value instead of getting an error for an empty dictionary?
A: You can use dictGetOrDefault which returns a specified default value if the key is not found. However, if the dictionary itself is completely empty and this error is raised, even dictGetOrDefault may fail. Fixing the underlying load issue is the correct solution.

Q: Does this error apply to DDL-created dictionaries or only XML-configured ones?
A: This error applies to both. Regardless of how the dictionary is defined, if it contains no data when queried, the error can occur.