Change column name in a table in Clickhouse

user_3174578 0
Viewed 7117

Is there any way to ALTER a table and change the column name in clickhouse? I only found to change tha table name but not for an individual column in a straight forward way.

Thanks.

clickhouse
3 Answers

The feature has been introduced here into v20.4.

ALTER TABLE table1 RENAME COLUMN old_name TO new_name

You can also rename multiple columns at on:

ALTER TABLE table1 
    RENAME COLUMN old_name1 TO new_name1, 
    RENAME COLUMN old_name2 TO new_name2

Old answer:

ClickHouse doesn't have that feature yet.

Implementation is not trivial, because ALTERs that changing columns are processed outside of usual replication queue, and adding rename without reworking of ALTERs will introduce race conditions in replicated tables.

https://github.com/yandex/ClickHouse/issues/146#issuecomment-255631384

As @Slash said, the solution for now is to create new table and 

INSERT INTO `new_table` SELECT * FROM `old_table`

Do not forget that column aliasing won't work there (AS).

INSERT INTO `new_table` SELECT a, b AS c, c AS b FROM `old_table`

That will still insert a into first column, b into second column and c into third column. AS has no effect there.

user_5193330

You can try use CREATE TABLE new_table with another field name and run INSERT INTO new_table SELECT old_field AS new_field FROM old_table

user_12046650

If you created the table using Engine=log, it won't allow you to alter or rename the column.

connection_string = f'clickhouse://{username}:{password}@{host}:{port}/{database}' 
engine = create_engine(connection_string)      
conn = engine.connect() 
table = "table1" 
schema = 'Parameter String, Key UInt8'
engine.execute("CREATE TABLE IF NOT EXISTS {}({}) ENGINE = Log".format(table,schema))

If you created table using the mergeTree engine, it’s allowed to rename the column:

engine.execute("CREATE TABLE IF NOT EXISTS {}({}) ENGINE =MergeTree ORDER BY Key".format(table,schema))
user_151976220
Related