How to CREATE TABLE in ClickHouse

Why use CREATE TABLE in ClickHouse?

CREATE TABLE lets you design storage for fast analytics—choosing column data types, primary key, partition keys, and an engine such as MergeTree for high-speed inserts and queries.

What is the basic CREATE TABLE syntax?

Start with the table name, list columns with types, pick an engine, then add ORDER BY and PARTITION BY clauses for optimal performance.

How do I create a simple table?

CREATE TABLE Customers
(
id UInt64,
name String,
email String,
created_at DateTime
) ENGINE = MergeTree()
ORDER BY id;


How do I add partitions for time-based pruning?

CREATE TABLE Orders
(
id UInt64,
customer_id UInt64,
order_date Date,
total_amount Decimal(10,2)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (customer_id, id);


Can I prevent errors if a table already exists?

Yes—append IF NOT EXISTS to skip creation when the table is present.

How do I set column compression or TTL?

CREATE TABLE OrderItems IF NOT EXISTS
(
id UInt64,
order_id UInt64,
product_id UInt64,
quantity UInt8
) ENGINE = MergeTree()
ORDER BY id
SETTINGS compression = 'ZSTD', ttl = toDateTime(order_id) + INTERVAL 30 DAY;


Which engine should I choose?

Use MergeTree for most workloads. ReplicatedMergeTree adds high availability, while ReplacingMergeTree deduplicates rows by a version column.

How do I list tables after creation?

SHOW TABLES FROM default;


Best practices for CREATE TABLE

• Pick ORDER BY keys that match your most common filtering columns.
• Partition by a low-cardinality date key for balanced shard sizes.
• Store decimals as Decimal or Int64 cents—avoid Float for money.

What are common mistakes?

See below for troubleshooting tips and fixes.