7

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?

Kirill Liubun
  • 1,965
  • 1
  • 17
  • 35

2 Answers2

15

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 │
└────┴──────┘
Denny Crane
  • 11,574
  • 2
  • 19
  • 30
  • By the way, you said that the first approach doesn't copy real data but when I tried to select from the original table after optimize -- the result was the same (3 records) and in the data directory there are different files (I check inodes numbers) for those tables. – Kirill Liubun Aug 10 '21 at 11:57
  • https://en.wikipedia.org/wiki/Hard_link it's possible to create several files which point to the same data . `Alter table attach` does not copy data, but creates new links on existing data. – Denny Crane Dec 06 '21 at 23:29
0

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
Kirill Markin
  • 1
  • 1