ClickHouse is a powerful columnar database built for lightning-fast analytics. While it’s widely praised for its performance, understanding how to manage schema changes effectively is crucial for long-term maintainability and flexibility of your data models. This post helps you to understand the ClickHouse's DDL (Data Definition Language) capabilities, with a focus on modifying table columns using ALTER operations, including how to add, drop, rename, and clear columns efficiently and safely.
Introducing DDLs in Clickhouse
DDL (Data Definition Language) in ClickHouse refers to SQL commands that define or modify table structures. This includes operations like creating tables, adding or removing columns, changing column names, or clearing data.
Let’s try to understand these commands one by one.
1. Creating a table
CREATE TABLE statement is used to create a table. For example, following statements create events table in demo_db database.
CREATE DATABASE IF NOT EXISTS demo_db; CREATE TABLE demo_db.events ( event_date Date, event_type String, user_id UInt32 ) ENGINE = MergeTree() ORDER BY event_date;
krishna :) DESCRIBE TABLE demo_db.events; DESCRIBE TABLE demo_db.events Query id: dbdef71b-46ca-4ef4-8b96-5da6778fbc02 ┌─name───────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ 1. │ event_date │ Date │ │ │ │ │ │ 2. │ event_type │ String │ │ │ │ │ │ 3. │ user_id │ UInt32 │ │ │ │ │ │ └────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ 3 rows in set. Elapsed: 0.002 sec.
2. ADD COLUMN (Add New Columns)
Adding new columns is straightforward and non-blocking in ClickHouse.
Syntax
ALTER TABLE table_name ADD COLUMN column_name type;
Example
ALTER TABLE demo_db.events ADD COLUMN location String;
For example, above statement create new column location to the events table.
krishna :) ALTER TABLE demo_db.events ADD COLUMN location String; ALTER TABLE demo_db.events (ADD COLUMN `location` String) Query id: d75dd793-8387-4ee3-a900-2db83edc2932 Ok. 0 rows in set. Elapsed: 0.019 sec. krishna :) ; Empty query krishna :) DESCRIBE TABLE demo_db.events; DESCRIBE TABLE demo_db.events Query id: 9434e2d9-3a27-4061-a872-b8a2c740e96e ┌─name───────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ 1. │ event_date │ Date │ │ │ │ │ │ 2. │ event_type │ String │ │ │ │ │ │ 3. │ user_id │ UInt32 │ │ │ │ │ │ 4. │ location │ String │ │ │ │ │ │ └────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ 4 rows in set. Elapsed: 0.001 sec.
3. DROP COLUMN (Remove Unused Columns)
You can remove columns that are no longer needed.
Syntax
ALTER TABLE database_name.table_name DROP COLUMN column_name;
Example
ALTER TABLE demo_db.events DROP COLUMN location;
krishna :) ALTER TABLE demo_db.events DROP COLUMN location; ALTER TABLE demo_db.events (DROP COLUMN location) Query id: 1e723d4a-7ffd-42df-97e7-e9e6b306de51 Ok. 0 rows in set. Elapsed: 0.014 sec. krishna :) ; Empty query krishna :) DESCRIBE TABLE demo_db.events; DESCRIBE TABLE demo_db.events Query id: ce05fbf0-101e-4b13-b995-db7b8d9f6985 ┌─name───────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ 1. │ event_date │ Date │ │ │ │ │ │ 2. │ event_type │ String │ │ │ │ │ │ 3. │ user_id │ UInt32 │ │ │ │ │ │ └────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ 3 rows in set. Elapsed: 0.001 sec.
4. RENAME COLUMN (Rename Columns Safely)
Renaming columns is useful during refactoring or schema evolution.
Syntax
ALTER TABLE database_name.table_name RENAME COLUMN column_name TO new_column_name;
Example
ALTER TABLE demo_db.events RENAME COLUMN user_id TO customer_id;
Above statement rename the column user_id to customer_id.
krishna :) ALTER TABLE demo_db.events RENAME COLUMN user_id TO customer_id; ALTER TABLE demo_db.events (RENAME COLUMN user_id TO customer_id) Query id: 58a9a897-058e-47a4-b976-0b6404acb3cd Ok. 0 rows in set. Elapsed: 0.013 sec. krishna :) ; Empty query krishna :) DESCRIBE TABLE demo_db.events; DESCRIBE TABLE demo_db.events Query id: da05e4d5-9aa9-4bfc-a6f8-40332e24401b ┌─name────────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ 1. │ event_date │ Date │ │ │ │ │ │ 2. │ event_type │ String │ │ │ │ │ │ 3. │ customer_id │ UInt32 │ │ │ │ │ │ └─────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘ 3 rows in set. Elapsed: 0.001 sec.
5. CLEAR COLUMN (Remove Data, Keep Column)
If you want to remove all data in a column but retain the schema, use CLEAR.
Syntax
ALTER TABLE database_name.table_name CLEAR COLUMN column_name;
Example
ALTER TABLE demo_db.events CLEAR COLUMN event_type;
In summary, ClickHouse offers a robust set of DDL operations for evolving your table schemas with minimal downtime. Whether you're adding a new metric, retiring old data, or refactoring for clarity, ALTER TABLE is your go-to tool. With careful planning and understanding of ADD, DROP, RENAME, and CLEAR, you can maintain both performance and schema flexibility.
Previous Next Home
No comments:
Post a Comment