How to Create a Table in ClickHouse

Basic Example

This example demonstrates how to create a events table in ClickHouse:

CREATE TABLE events (
    user_id UInt32,
    timestamp DateTime,
    message String
) ENGINE = MergeTree()
PRIMARY KEY (user_id, message);

The resulting table has three columns:

• user_id, which is a UInt32 (unsigned integer of 32 bits)
• timestamp, which is of type DateTime
• message, which is of type String

And the primary key is (user_id, message). Keep in mind that, contrary to many how primary keys work in many other database systems, primary key values are not required to be unique in ClickHouse.

How to Create Non-Nullable Columns

In ClickHouse, columns are non-nullable by default. To create nullable columns, define them with the Nullable type:

CREATE TABLE events (
    user_id UInt32,
    timestamp DateTime,
    message Nullable(String)
) ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp);

How to Create Columns With Default Values

CREATE TABLE events (
    user_id UInt32,
    timestamp DateTime,
    message String DEFAULT 'a default value'
) ENGINE = MergeTree()
PRIMARY KEY (user_id, message);