Is it possible to change a table engine of an existed clickhouse table?

Question

Is it possible to alter a table engine in clickhouse table like in MySQL, something like this:

CREATE TABLE example_table (id UInt32, data String) ENGINE=MergeTree() ORDER BY id;
ALTER example_table ENGINE=SummingMergeTree();

Because I didn't find such capability in the documentation.

If it is not possible, are there any plans to implement it in near future, or what architecture limitations prevent from doing this?

Answer 1

It's possible to change an Engine by several ways.

But it's impossible to change PARTITION BY / ORDER BY. That's why it's not documented explicitly. So in 99.99999% cases it does not make any sense. SummingMergeTree uses table's ORDER BY as a collapsing rule and the existing ORDER BY usually does not suit.

Here is an example of one the ways (less hacky one), (you can copy partitions from one table to another, it's almost free operation, it exploits FS hardlinks and does not copy real data). (COW -- copy on write).

CREATE TABLE example_table (id UInt32, data Float64) 
ENGINE=MergeTree() ORDER BY id;

Insert into example_table values(1,1), (1,1), (2,1);


CREATE TABLE example_table1 (id UInt32, data Float64) 
ENGINE=SummingMergeTree() ORDER BY id;

-- this does not copy any data (instant & almost free command)
alter table example_table1 attach partition tuple() from example_table;

SELECT * FROM example_table1;
┌─id─┬─data─┐
│  1 │    1 │
│  1 │    1 │
│  2 │    1 │
└────┴──────┘

optimize table example_table1 final;

select * from example_table1;
┌─id─┬─data─┐
│  1 │    2 │
│  2 │    1 │
└────┴──────┘

One more way (edit metadata file, also ZK records if a table Replicated)

detach table example_table;

vi /var/lib/clickhouse/metadata/default/example_table.sql
replace MergeTree with SummingMergeTree

attach table example_table;

SHOW CREATE TABLE example_table

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.example_table
(
    `id` UInt32,
    `data` Float64
)
ENGINE = SummingMergeTree
ORDER BY id
SETTINGS index_granularity = 8192 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

SELECT * FROM example_table;

┌─id─┬─data─┐
│  1 │    1 │
│  1 │    1 │
│  2 │    1 │
└────┴──────┘

optimize table example_table final;

SELECT * FROM example_table;
┌─id─┬─data─┐
│  1 │    2 │
│  2 │    1 │
└────┴──────┘