CODEWITHSUNDEEP
clickhouse
Kyrylo Liubun
Kyrylo Liubun

Reputation: 2135

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

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?

Upvotes: 11

Views: 9946

Answers (2)

Kirill Markin
Kirill Markin

Reputation: 31

Thanks to @denny-crane for the answer — it works.

One more thing. If you want to "edit metadata file, also ZK records if a table Replicated". You can do these steps:

  1. After detach table example_table
  2. run in zookeeper for all shards: deleteall /clickhouse/tables/[shard_number]/[db]/[table]
  3. run attach table soax.node_replicated
  4. run SYSTEM RESTORE REPLICA [db].[table]; to remove read-only mode

Upvotes: 3

Denny Crane
Denny Crane

Reputation: 13310

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 │
└────┴──────┘

Upvotes: 20

Related Questions