ClickHouse DB::Exception: Cannot get CREATE TABLE query

The "DB::Exception: Cannot get CREATE TABLE query" error means ClickHouse is unable to retrieve or reconstruct the DDL statement that defines a table. Identified by the error code CANNOT_GET_CREATE_TABLE_QUERY, this usually points to corrupted or missing metadata files. It can surface when you run SHOW CREATE TABLE, when ClickHouse tries to load the table during startup, or when internal operations need to read the table definition.

Impact

A table with corrupted metadata may become completely inaccessible. You cannot query it, alter it, or even drop it cleanly. In severe cases, the ClickHouse server may fail to start if it encounters this error while loading tables on boot.

Common Causes

Corrupted metadata files on disk -- hardware failures, unexpected shutdowns, or filesystem errors can damage the .sql metadata files in /var/lib/clickhouse/metadata/.
Manual editing of metadata files -- modifying .sql files by hand can introduce syntax errors.
Incomplete table creation or migration -- a crash during CREATE TABLE may leave partial metadata.
Disk space exhaustion during DDL -- running out of space while writing metadata can produce truncated files.
Filesystem permission changes -- the ClickHouse process can no longer read its own metadata files.
Backup restoration issues -- restoring metadata files from an incompatible version or incomplete backup.

Troubleshooting and Resolution Steps

Check the metadata file on disk. The file is located at:
```
cat /var/lib/clickhouse/metadata/my_database/my_table.sql
```
Look for truncation, syntax errors, or garbage characters.
Check filesystem integrity:
```
ls -la /var/lib/clickhouse/metadata/my_database/my_table.sql
```
Verify the file exists, has a reasonable size, and is readable by the ClickHouse user.

If the metadata file is corrupt and you have a backup, restore it:

cp /backup/metadata/my_database/my_table.sql /var/lib/clickhouse/metadata/my_database/my_table.sql
chown clickhouse:clickhouse /var/lib/clickhouse/metadata/my_database/my_table.sql

Then restart ClickHouse or run SYSTEM RELOAD DICTIONARY if applicable.

If you have the CREATE TABLE statement saved elsewhere (version control, documentation, another replica), recreate the metadata file manually:
```
echo "ATTACH TABLE my_table (...) ENGINE = MergeTree() ORDER BY id;" > /var/lib/clickhouse/metadata/my_database/my_table.sql
```
Note: metadata files use ATTACH TABLE syntax, not CREATE TABLE.

For replicated tables, get the schema from ZooKeeper:

SELECT value FROM system.zookeeper
WHERE path = '/clickhouse/tables/shard1/my_table' AND name = 'metadata';

If the table data is intact but metadata is lost, reconstruct the table definition by examining the data parts:

ls /var/lib/clickhouse/data/my_database/my_table/
cat /var/lib/clickhouse/data/my_database/my_table/all_1_1_0/columns.txt

Check ClickHouse server logs for more context:

grep "Cannot get CREATE TABLE" /var/log/clickhouse-server/clickhouse-server.err.log

Best Practices

Enable regular backups of the ClickHouse metadata directory, not just data.
Use a filesystem with journaling (ext4, XFS) to reduce corruption risk from unexpected shutdowns.
Never manually edit metadata files in /var/lib/clickhouse/metadata/ -- always use DDL statements.
Monitor disk space on the metadata volume to prevent truncation during writes.
For replicated tables, ZooKeeper serves as an additional metadata backup -- leverage it for recovery.
Store your CREATE TABLE statements in version control as a recovery reference.

Frequently Asked Questions

Q: Can I recover a table if the metadata file is completely gone but the data files are intact?
A: Yes, if you know the original table schema. Recreate the metadata file with the correct ATTACH TABLE statement and restart ClickHouse. The server will attach the existing data parts to the table.

Q: What is the difference between ATTACH TABLE and CREATE TABLE in metadata files?
A: Metadata files on disk use ATTACH TABLE syntax, which tells ClickHouse to load an existing table from its data directory. CREATE TABLE is the user-facing command that creates both metadata and data structures.

Q: Will ClickHouse skip a corrupted table and start normally?
A: It depends on the database engine. With the Atomic engine, ClickHouse tries to be resilient and may log warnings but continue starting. With Ordinary, a corrupted table can block the entire database from loading.

Q: How do I prevent metadata corruption?
A: Use reliable storage with journaling, ensure clean shutdowns, monitor disk health, and maintain regular backups. UPS systems or battery-backed write caches help protect against power loss.