Mastering DDL in ClickHouse: Create, Rename, Truncate, and Drop Explained

  

ClickHouse is a high-performance columnar database well suited for real-time analytics. While much attention is often given to its query performance and architecture, understanding its Data Definition Language (DDL) capabilities is very important for managing schemas efficiently.

 

This post talks about the essential DDL operations in ClickHouse like CREATE, RENAME, TRUNCATE, and DROP. It covers how to create and manage databases, tables, users, views, and dictionaries. You'll also learn how to rename and truncate tables safely, and how to properly drop database objects when cleaning up or redesigning your schema.

 

In ClickHouse, DDL (Data Definition Language) statements primarily fall into four categories:

·      CREATE,

·      RENAME,

·      TRUNCATE, and

·      DROP.

These operations allow users to define, modify, and remove database objects such as databases, tables, views, users, and dictionaries.

 

1. CREATE Databases, Tables, and Views

The CREATE statement in ClickHouse is used to define new database objects. These include databases, tables, users, and views. Let’s go through each one in detail.

 

1.1 Create a Database

Syntax

CREATE DATABASE [IF NOT EXISTS] database_name;

Example

CREATE DATABASE IF NOT EXISTS analytics_db;

   

This creates a database named analytics_db if it doesn't already exist.

 

krishna :) CREATE DATABASE IF NOT EXISTS analytics_db;

CREATE DATABASE IF NOT EXISTS analytics_db

Query id: 550502e2-1bca-488a-b2a2-befbdc7dbc2c

Ok.

0 rows in set. Elapsed: 0.003 sec.

   

1.2 CREATE TABLE

To create a new table, you must specify column names, data types, and optionally, the engine (e.g., MergeTree, ReplacingMergeTree).

 

Syntax

 

CREATE TABLE [IF NOT EXISTS] database_name.table_name
(
    column1_name column1_type,
    column2_name column2_type,
    ...
)
ENGINE = engine_name
[ORDER BY expression];  -- Required for MergeTree family engines

Example

CREATE TABLE IF NOT EXISTS analytics_db.page_views
(
    `user_id` UInt32,
    `page_url` String,
    `view_time` DateTime
)
ENGINE = MergeTree
ORDER BY (user_id, view_time);

This creates a table page_views in the analytics_db database using the MergeTree engine.

krishna :) SHOW TABLES FROM analytics_db;

SHOW TABLES FROM analytics_db

Query id: bb133f2f-0f64-4e12-a79a-7b2ce8bdc22e

   ┌─name───────┐
1. │ page_views │
   └────────────┘

1 row in set. Elapsed: 0.003 sec.

   

1.3 Create View

ClickHouse supports both standard views and materialized views.

 

Syntax for Standard View

CREATE VIEW [IF NOT EXISTS] view_name AS
SELECT ...

  

Example

 

CREATE VIEW IF NOT EXISTS analytics_db.daily_active_users AS
SELECT user_id, toDate(view_time) AS activity_date
FROM analytics_db.page_views
GROUP BY user_id, activity_date;

  

This creates a view that groups user activity by day.

krishna :) CREATE VIEW IF NOT EXISTS analytics_db.daily_active_users AS
SELECT user_id, toDate(view_time) AS activity_date
FROM analytics_db.page_views
GROUP BY user_id, activity_date;
:-] SELECT user_id, toDate(view_time) AS activity_date
FROM analytics_db.page_views
GROUP BY user_id, activity_date;
:-] FROM analytics_db.page_views
GROUP BY user_id, activity_date;
:-] GROUP BY user_id, activity_date;

CREATE VIEW IF NOT EXISTS analytics_db.daily_active_users
AS SELECT
    user_id,
    toDate(view_time) AS activity_date
FROM analytics_db.page_views
GROUP BY
    user_id,
    activity_date

Query id: c9702e64-2db6-4aad-a04b-cae8ea6df74a

Ok.

0 rows in set. Elapsed: 0.004 sec.

You can confirm the view creation using SHOW TABLES command.

 

krishna :) SHOW TABLES FROM analytics_db;

SHOW TABLES FROM analytics_db

Query id: 33e25069-3c30-4377-8532-301a2348aee0

   ┌─name───────────────┐
1. │ daily_active_users │
2. │ page_views         │
   └────────────────────┘

2 rows in set. Elapsed: 0.002 sec. 

   

Syntax for materialized view

 

CREATE MATERIALIZED VIEW view_name
TO target_table
AS SELECT ...

   

Materialized views store results physically in a target table and automatically refresh when source data changes.

 

2. Working with RENAME statement

The RENAME statement in ClickHouse allows you to rename existing objects such as:

 

·      Tables (both ordinary and distributed)

·      Views (standard and materialized)

·      Dictionaries

 

This operation is fast because it's essentially a metadata change, not a data copy.

 

Syntax

 

RENAME TABLE [db1.]old_name TO [db2.]new_name
[, [db3.]old_name2 TO [db4.]new_name2, ...];

   

In Clickhouse:

·      You can rename multiple tables at once (in an atomic batch).

·      You can move a table to a different database in the same operation.

·      You can rename a view just like a table.

·      Dictionary renaming is supported but less commonly used.

 

Example

RENAME TABLE analytics_db.page_views TO analytics_db.user_activity;

 

Above statement renames the table page_views to user_activity in the same database

 

krishna :) RENAME TABLE analytics_db.page_views TO analytics_db.user_activity;

RENAME TABLE analytics_db.page_views TO analytics_db.user_activity

Query id: 849d587c-19d2-4698-a47a-917260279633

Ok.

0 rows in set. Elapsed: 0.006 sec. 

krishna :) 
krishna :) SHOW TABLES FROM analytics_db;

SHOW TABLES FROM analytics_db

Query id: 88423d39-4173-47e9-abe9-f1607ab756c1

   ┌─name───────────────┐
1. │ daily_active_users │
2. │ user_activity      │
   └────────────────────┘

2 rows in set. Elapsed: 0.002 sec.

   

Let’s rename two tables daily_active_users, user_activity with the same command.

 

RENAME TABLE analytics_db.daily_active_users TO analytics_db.daily_active_users_new, analytics_db.user_activity TO analytics_db.user_activity_new;

   

Above command rename

a.   daily_active_users to daily_active_users_new

b.   user_activity to user_activity_new

 

2.1 Move and Rename a Table Between Databases

RENAME TABLE analytics_db.user_activity_new TO archive_db.archived_activity;

 

This moves and renames the table across databases in a single operation. To demo this, I created archive_db.

 

m-krishna :) RENAME TABLE analytics_db.user_activity_new TO archive_db.archived_activity;

RENAME TABLE analytics_db.user_activity_new TO archive_db.archived_activity

Query id: d8df8aea-81cf-436a-825d-cb628b7efe78

Ok.

0 rows in set. Elapsed: 0.005 sec. 

m-krishna :) 
m-krishna :) 
m-krishna :) SHOW TABLES FROM analytics_db;

SHOW TABLES FROM analytics_db

Query id: fb3f2aff-899b-4f78-b2aa-85ed411093d4

   ┌─name───────────────────┐
1. │ daily_active_users_new │
   └────────────────────────┘

1 row in set. Elapsed: 0.003 sec. 

m-krishna :) 
m-krishna :) 
m-krishna :) SHOW TABLES FROM archive_db;

SHOW TABLES FROM archive_db

Query id: 4a3d998a-f503-4c1b-af36-6ec9edf1eb96

   ┌─name──────────────┐
1. │ archived_activity │
   └───────────────────┘

1 row in set. Elapsed: 0.003 sec.

   

2.2 Rename a View

Let’s create an employees table and derive a view from it.

CREATE TABLE IF NOT EXISTS analytics_db.employees
(
    `user_id` UInt32,
    `name` String,
    `registration_date` DateTime
)
ENGINE = MergeTree
ORDER BY (user_id, registration_date);


CREATE VIEW IF NOT EXISTS analytics_db.employees_name_view AS
SELECT name FROM analytics_db.employees;

krishna :) CREATE TABLE IF NOT EXISTS analytics_db.employees
(
    `user_id` UInt32,
    `name` String,
    `registration_date` DateTime
)
ENGINE = MergeTree
ORDER BY (user_id, registration_date);

CREATE TABLE IF NOT EXISTS analytics_db.employees
(
    `user_id` UInt32,
    `name` String,
    `registration_date` DateTime
)
ENGINE = MergeTree
ORDER BY (user_id, registration_date)

Query id: e8500eb8-62c4-461e-bbe4-f968f21fbd71

Ok.

0 rows in set. Elapsed: 0.009 sec. 
krishna :)
krishna :)
krishna :) CREATE VIEW IF NOT EXISTS analytics_db.employees_name_view AS
SELECT name FROM analytics_db.employees;

CREATE VIEW IF NOT EXISTS analytics_db.employees_name_view
AS SELECT name
FROM analytics_db.employees

Query id: aa96ee0e-c1b2-42df-b04e-69e08a806025

Ok.

0 rows in set. Elapsed: 0.004 sec.

   

Execute the statement SHOW TABLES FROM analytics_db; to list all the tables, views in analytics_db database.

 

krishna :) SHOW TABLES FROM analytics_db;

SHOW TABLES FROM analytics_db

Query id: 086b3c55-ffc9-4312-ba3b-bb6fbd29282a

   ┌─name───────────────────┐
1. │ daily_active_users_new │
2. │ employees              │
3. │ employees_name_view    │
   └────────────────────────┘

3 rows in set. Elapsed: 0.003 sec.

   

Let’s rename the view employees_name_view  to emp_name_view.

 

RENAME TABLE analytics_db.employees_name_view  TO analytics_db.emp_name_view;

   

Even though it’s a view, ClickHouse uses TABLE keyword for RENAME.

 

krishna :) RENAME TABLE analytics_db.employees_name_view  TO analytics_db.emp_name_view;

RENAME TABLE analytics_db.employees_name_view TO analytics_db.emp_name_view

Query id: 8c1b3f60-c225-4e0c-93d4-4caf544a4b63

Ok.

0 rows in set. Elapsed: 0.003 sec. 

krishna :) 
krishna :) 
krishna :) SHOW TABLES FROM analytics_db;

SHOW TABLES FROM analytics_db

Query id: 567232b6-cea7-493f-af69-ee9f783f1fe6

   ┌─name───────────────────┐
1. │ daily_active_users_new │
2. │ emp_name_view          │
3. │ employees              │
   └────────────────────────┘

3 rows in set. Elapsed: 0.003 sec.

   

3. TRUNCATE: to remove all records from a table

The TRUNCATE TABLE statement in ClickHouse removes all rows from a table quickly and efficiently, without removing the table structure itself.

 

Syntax

 

TRUNCATE TABLE [IF EXISTS] [db.]table_name;

   

Let’s go through a practical example step by step.

 

Step 1: Create page_visits table.

 

CREATE TABLE IF NOT EXISTS analytics_db.page_visits
(
    user_id UInt32,
    page_url String,
    view_time DateTime
)
ENGINE = MergeTree
ORDER BY user_id;

krishna :) CREATE TABLE IF NOT EXISTS analytics_db.page_visits
(
    user_id UInt32,
    page_url String,
    view_time DateTime
)
ENGINE = MergeTree
ORDER BY user_id;

CREATE TABLE IF NOT EXISTS analytics_db.page_visits
(
    `user_id` UInt32,
    `page_url` String,
    `view_time` DateTime
)
ENGINE = MergeTree
ORDER BY user_id

Query id: 40ab25e7-6b80-4a19-a4d2-3fc8c735120f

Ok.

0 rows in set. Elapsed: 0.007 sec.

   

Step 2: Insert Some Data.

 

INSERT INTO analytics_db.page_visits (user_id, page_url, view_time) VALUES
(1, 'home', now()),
(2, 'about', now()),
(3, 'contact', now());

 

krishna :) INSERT INTO analytics_db.page_visits (user_id, page_url, view_time) VALUES
(1, 'home', now()),
(2, 'about', now()),
(3, 'contact', now());

INSERT INTO analytics_db.page_visits (user_id, page_url, view_time) FORMAT Values

Query id: 7567d384-c8ab-4d49-bba9-ed96ef7f9000

Ok.

3 rows in set. Elapsed: 0.015 sec.

   

Step 3: View the Data.

 

SELECT * FROM analytics_db.page_visits;

krishna :) SELECT * FROM analytics_db.page_visits;

SELECT *
FROM analytics_db.page_visits

Query id: d9c794af-341f-49c0-be34-dd1bc3f0702e

   ┌─user_id─┬─page_url─┬───────────view_time─┐
1. │       1 │ home     │ 2025-05-07 18:45:51 │
2. │       2 │ about    │ 2025-05-07 18:45:51 │
3. │       3 │ contact  │ 2025-05-07 18:45:51 │
   └─────────┴──────────┴─────────────────────┘

3 rows in set. Elapsed: 0.004 sec.

   

Step 4: Truncate the Table.

 

TRUNCATE TABLE analytics_db.page_visits;

krishna :) TRUNCATE TABLE analytics_db.page_visits;

TRUNCATE TABLE analytics_db.page_visits

Query id: d3823645-956b-48d6-ae1b-6f1124d8e003

Ok.

0 rows in set. Elapsed: 0.019 sec. 

krishna :)
krishna :)
krishna :) SELECT * FROM analytics_db.page_visits;

SELECT *
FROM analytics_db.page_visits

Query id: 7cced52e-0f22-4cc6-97cd-a469ead1a896

Ok.

0 rows in set. Elapsed: 0.001 sec. 

krishna :)

   

Why TRUNCATE is DDL?

Even though TRUNCATE removes all data from a table, it is considered a DDL command because it operates on the table structure rather than the data itself.

·      Structural Operation: TRUNCATE effectively resets the table to its original empty state by deallocating the data pages used by the table.

·      Table Identity: The table structure, including its columns, indexes, and constraints, remains unchanged.

·      Performance: TRUNCATE is often faster than deleting all rows with DELETE because it doesn't log individual row deletions.

·      Rollback: TRUNCATE operations cannot be rolled back in the same way as DML operations.

 

4. DROP: Drop databases, table, views etc.,

The DROP statement in ClickHouse is used to permanently remove objects such as:

 

·      Databases

·      Tables

·      Views (materialized or standard)

·      Dictionaries

·      Users

 

Syntax

 

DROP [TEMPORARY] TABLE [IF EXISTS] [db.]table_name;
DROP DATABASE [IF EXISTS] db_name;
DROP VIEW [IF EXISTS] [db.]view_name;
DROP DICTIONARY [IF EXISTS] [db.]dict_name;

   

4.1 Drop a Table

Syntax

 

DROP TABLE IF EXISTS db_name.table_name;

   

Example

 

DROP TABLE IF EXISTS analytics_db.page_visits;

   

Above statement removes the table page_visits from analytics_db. All data and metadata are permanently deleted.

 

krishna :) SHOW TABLES FROM analytics_db;

SHOW TABLES FROM analytics_db

Query id: 447c8f0b-a1ad-435b-8f6b-015e784d99db

   ┌─name───────────────────┐
1. │ daily_active_users_new │
2. │ emp_name_view          │
3. │ employees              │
4. │ page_visits            │
   └────────────────────────┘

4 rows in set. Elapsed: 0.001 sec. 

krishna :) 
krishna :) DROP TABLE IF EXISTS analytics_db.page_visits;

DROP TABLE IF EXISTS analytics_db.page_visits

Query id: 08f0ebaf-efc8-44b7-b06c-f808948107c5

Ok.

0 rows in set. Elapsed: 0.004 sec. 

krishna :) 
krishna :) SHOW TABLES FROM analytics_db;

SHOW TABLES FROM analytics_db

Query id: 3810d52c-116e-4eff-bea5-4d1a96d43834

   ┌─name───────────────────┐
1. │ daily_active_users_new │
2. │ emp_name_view          │
3. │ employees              │
   └────────────────────────┘

3 rows in set. Elapsed: 0.002 sec. 

krishna :)

   

4.2 Drop a view

 

DROP TABLE IF EXISTS database_name.view_name;

   

4.3 Drop a Database

 

DROP DATABASE IF EXISTS database_name;

   

In summary, ClickHouse provides a powerful set of Data Definition Language (DDL) commands that allow you to define, modify, and manage your database schema. These operations directly affect the structure and metadata of your databases and tables, not just the data within.

 

Command	Purpose	Key Actions Supported
CREATE	Defines new database objects	Create databases, tables, views, users, dictionaries
RENAME	Renames existing objects	Rename tables, databases, dictionaries
TRUNCATE	Deletes all records but keeps the structure	Clears a table quickly without dropping it
DROP	Permanently removes schema objects	Drop databases, tables, views, dictionaries, users

 

Previous                                                    Next                                                    Home