ClickHouse DB::Exception: Engine required

The "DB::Exception: Engine required" error appears when you execute a CREATE TABLE statement without specifying a table engine. ClickHouse identifies this with the error code ENGINE_REQUIRED. Unlike some SQL databases that have a default storage engine, ClickHouse requires you to explicitly declare which engine to use for every table.

Impact

The CREATE TABLE statement fails and no table is created. This is a straightforward DDL error with no risk to existing data. It just needs a quick fix to the table definition.

Common Causes

Omitting the ENGINE clause entirely -- especially common when copying DDL from other database systems like PostgreSQL or MySQL.
Syntax error that hides the ENGINE clause -- a missing closing parenthesis in the column definitions can cause the parser to miss the ENGINE line.
Using an ORM or migration tool that does not generate ClickHouse-compatible DDL -- tools designed for other databases may not include ENGINE.
Incomplete copy-paste -- the ENGINE portion was cut off when copying from documentation or another source.
Using CREATE TABLE AS SELECT without an engine -- the AS SELECT shorthand still requires an engine specification in ClickHouse.

Troubleshooting and Resolution Steps

Add the ENGINE clause to your CREATE TABLE statement:

CREATE TABLE my_database.my_table (
    id UInt64,
    name String,
    created_at DateTime
) ENGINE = MergeTree()
ORDER BY id;

Choose the right engine for your use case. The most common engines are:
- MergeTree() -- the workhorse engine for most analytical workloads
- ReplicatedMergeTree() -- MergeTree with built-in replication
- Memory -- in-memory storage for temporary data
- Log / TinyLog -- simple engines for small datasets
- Distributed -- for distributed query routing across shards

For CREATE TABLE AS SELECT, include the engine before the AS clause:

CREATE TABLE my_database.my_table
ENGINE = MergeTree()
ORDER BY id
AS SELECT * FROM source_table;

If using an ORM or migration tool, configure it for ClickHouse. Most ClickHouse-aware tools have engine configuration options. For example, with SQLAlchemy and clickhouse-sqlalchemy:
```
from sqlalchemy import Table, Column, MetaData
table = Table('my_table', metadata,
    Column('id', types.UInt64),
    engines.MergeTree(order_by='id')
)
```

Check for syntax issues above the ENGINE line. A missing parenthesis or comma in the column definitions can confuse the parser:

-- This will fail with ENGINE_REQUIRED because of the missing closing paren
CREATE TABLE my_table (
    id UInt64,
    name String
ENGINE = MergeTree() ORDER BY id;

-- Correct
CREATE TABLE my_table (
    id UInt64,
    name String
) ENGINE = MergeTree() ORDER BY id;

Best Practices

Always specify the ENGINE clause explicitly, even in quick prototyping scripts.
Default to MergeTree() for analytical tables unless you have a specific reason to use another engine.
Use clickhouse-format to validate your SQL before executing it -- it will catch missing ENGINE clauses.
When migrating DDL from other database systems, create a checklist of ClickHouse-specific requirements (ENGINE, ORDER BY, etc.).
Include ENGINE in your DDL templates and snippets to avoid repeatedly hitting this error.

Frequently Asked Questions

Q: Does ClickHouse have a default table engine?
A: By default, no. You must specify the engine. However, you can set default_table_engine in your server or user configuration to provide a fallback. For example, setting default_table_engine = MergeTree allows you to omit the ENGINE clause (but you still need ORDER BY for MergeTree).

Q: What is the simplest engine I can use for a quick test?
A: ENGINE = Memory is the simplest -- no parameters, no ORDER BY required. Data is lost when the server restarts, but it is perfect for temporary testing.

Q: Can I change a table's engine after creation?
A: Not directly. You would need to create a new table with the desired engine, copy the data with INSERT INTO ... SELECT, and then swap the tables using EXCHANGE TABLES or RENAME TABLE.

Q: Do I need ORDER BY for every engine?
A: No. ORDER BY is required for MergeTree-family engines but not for simpler engines like Memory, Log, or TinyLog. Each engine has its own set of required and optional clauses.