How to EXPORT SCHEMA in ClickHouse

How do I export only the schema (DDL) in ClickHouse?

EXPORT SCHEMA writes the DDL of one or more ClickHouse databases or tables to disk so you can version-control or migrate the structures without data.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problem does EXPORT SCHEMA solve?

EXPORT SCHEMA lets you dump only the database objects—databases, tables, views, materialized views—so you can move structures between clusters or keep them in Git without transferring terabytes of data.

How do I export an entire schema?

Run EXPORT SCHEMA myshop_db TO '/var/ck_dumps/myshop_ddl/' FORMAT SQL;. ClickHouse creates one .sql file per object, each containing a CREATE statement that can be replayed on another server.

Can I export selected tables?

Yes.Separate multiple tables with commas: EXPORT SCHEMA myshop_db.Customers, myshop_db.Orders TO '/tmp/ecom_schema/' FORMAT SQL;.

How do I include table engines and TTLs?

Engine, partition keys, ordering, TTL, comments, and settings are always present in the generated DDL, guaranteeing a byte-for-byte recreation.

What formats are supported?

SQL is default; Native and JSONEachRow are available when you need programmatic parsing.Specify with FORMAT keyword.

Where does ClickHouse write the files?

ClickHouse must have write permission on the target path, which must reside on the same server where the query runs. Use an NFS mount for remote storage.

How do I re-import the schema?

Pipe the .sql files into clickhouse-client --multiquery or use psql-style \i commands inside the client. For Native/JSONEachRow, use IMPORT SCHEMA.

Best practice: version-control your DDL

Commit the generated .sql files to Git after every migration.Pair with CI to apply the DDL automatically in staging.

Best practice: automate with cron

Schedule nightly EXPORT SCHEMA jobs. Keep only the last N dumps to save disk space.

Common mistakes

Wrong path permissions: EXPORT fails silently when ClickHouse lacks write rights. Fix with chown/chmod.

Missing FORMAT keyword: Omitting FORMAT defaults to SQL. Explicitly set FORMAT Native when you need high-speed IMPORT.

Related commands

Use SHOW CREATE TABLE for a quick single-table DDL.Use BACKUP for both schema and data.

Why How to EXPORT SCHEMA in ClickHouse is important

How to EXPORT SCHEMA in ClickHouse Example Usage


-- Dump only the structural definitions of the order pipeline
EXPORT SCHEMA myshop_db.Customers,
              myshop_db.Orders,
              myshop_db.OrderItems
TO '/var/exports/order_pipeline/' FORMAT SQL;

How to EXPORT SCHEMA in ClickHouse Syntax


EXPORT SCHEMA [db_name | db_name.table_list]
TO 'destination_path/'
[FORMAT SQL | Native | JSONEachRow]
[SETTINGS output_format_write_statistics = 0];

-- Export the entire e-commerce schema
EXPORT SCHEMA myshop_db TO '/var/exports/myshop_schema/' FORMAT SQL;

-- Export only structural objects for three tables
EXPORT SCHEMA myshop_db.Customers, myshop_db.Orders, myshop_db.OrderItems 
TO '/var/exports/order_core/' FORMAT SQL;

Common Mistakes

Providing a relative path ("./dump") instead of an absolute one. ClickHouse resolves paths relative to its working directory, causing dumps to appear elsewhere or fail. Always pass an absolute path like '/var/exports/'.
Running EXPORT SCHEMA on a distributed node. Schema export happens on the server that executes the query; distributed nodes may lack local metadata, resulting in partial dumps. Run on the initiator of the underlying shards.