ClickHouse DB::Exception: Unknown storage engine

The "DB::Exception: Unknown storage engine" error occurs when ClickHouse encounters a table engine name it does not recognize in a CREATE TABLE statement. Tagged with the error code UNKNOWN_STORAGE, this usually means there is a typo in the engine name, the engine is not available in your ClickHouse build, or a required plugin has not been loaded.

Impact

The CREATE TABLE statement fails completely, and no table is created. Any workflows depending on that table -- data pipelines, application migrations, replication setup -- will be blocked until the engine name is corrected or the missing engine is made available.

Common Causes

1. Typo in the engine name -- writing MergTree instead of MergeTree, or ReplicatedMegreTree instead of ReplicatedMergeTree.
2. Using an engine not compiled into your build -- some ClickHouse distributions (especially minimal Docker images or custom builds) may exclude certain engines like MaterializedMySQL or MaterializedPostgreSQL.
3. Version mismatch -- referencing an engine that was introduced in a newer version than the one you are running. For example, MaterializedPostgreSQL was added in version 21.11.
4. Case sensitivity -- engine names in ClickHouse are case-sensitive. mergetree will not be recognized; it must be MergeTree.
5. Missing external dictionary or integration plugin -- engines that depend on external libraries (e.g., HDFS, S3, RabbitMQ) may not be available if the corresponding library was not included at build time.

Troubleshooting and Resolution Steps

1. Double-check the engine name spelling and casing. ClickHouse engine names use PascalCase:

   -- Correct
   ENGINE = MergeTree()
   
   -- Wrong
   ENGINE = mergetree()
   ENGINE = Merge_Tree()
   

2. List all available engines in your ClickHouse instance. Query the system table to see what is supported:

   SELECT * FROM system.table_engines ORDER BY name;
   

3. Check your ClickHouse version. Some engines are only available in certain versions:

   SELECT version();
   

   Then compare against the ClickHouse changelog to confirm the engine you need is available.

4. Verify your build includes the engine. If running a custom or minimal build, you may need to rebuild with the appropriate flags or switch to the full ClickHouse package:

   clickhouse-server --version
   

5. For integration engines, confirm dependencies are installed. Engines like Kafka, RabbitMQ, or S3 require specific libraries. Check the ClickHouse server logs at startup for warnings about missing libraries.

6. If migrating DDL between servers, normalize engine names. Extract the DDL from the source using:

   SHOW CREATE TABLE source_table;
   

   Then verify every engine referenced is available on the target.

Best Practices

• Query system.table_engines on your target environment before writing DDL that references non-standard engines.
• Pin your ClickHouse version in deployment scripts and test DDL against that specific version.
• Use the official ClickHouse Docker images for consistent engine availability across environments.
• When upgrading ClickHouse, review the changelog for newly added or deprecated engines.
• Keep a mapping document of which engines your team uses and their minimum required ClickHouse version.

Frequently Asked Questions

Q: How do I list all available table engines in my ClickHouse instance?
A: Run SELECT name FROM system.table_engines ORDER BY name; to get a full list of supported engines.

Q: Are engine names case-sensitive?
A: Yes. ClickHouse engine names are case-sensitive and follow PascalCase convention. MergeTree works, mergetree does not.

Q: I need MaterializedPostgreSQL but it is not available. What should I do?
A: This engine requires a ClickHouse build compiled with PostgreSQL support and is available starting from version 21.11. Switch to the full ClickHouse package or a Docker image that includes it.

Q: Can I add a custom storage engine to ClickHouse?
A: Not through configuration alone. Custom storage engines require modifying the ClickHouse source code and rebuilding. However, you can often achieve custom behavior through materialized views, dictionaries, or table functions.