Clickhouse: FR: REPLACE TABLE statement

[ramazanpolat]

If users need to reject(delete) some data from a table, they can create a new table and fill it with a select statement which doesn't retrieve unwanted data, then rename it to old table name.

Example:

CREATE TABLE myNewTable AS myOldTable;

Then load data with:

INSERT INTO myNewTable SELECT * FROM myOldTable WHERE CounterID <12345

Drop old table and rename:

DROP TABLE myOldTable;
RENAME TABLE myNewTable TO myOldTable

Instead of above, being able to use the following would be nice:

REPLACE TABLE myOldTable SELECT * FROM myOldTable WHERE CounterID <12345;

Behind the scenes, CH can do the creating, dropping and renaming.

[alexey-milovidov]

We are planning to implement a set of related operations:

TRUNCATE TABLE
CREATE OR REPLACE TABLE
REPLACE TABLE

The main downside is that in first implementation they will be supported for all table engines except Replicated- tables. This is because for Replicated tables, CREATE and DROP just add or remove single replica for table.

[ramazanpolat]

Looking forward to it.
I would like to contribute to this project, unfortunately I'm far from being good at C++. I'll do my best if there is any other way to help.

[henyihanwobushi]

GOOD Suggestion!!!
Let the rename statement support multi renames, and support transaction for the renames, like mysql, will solve my problem.

[filimonov]

Let the rename statement support multi renames, and support transaction for the renames

Rename support renaming multiple tables at once. Syntax is usual.

RENAME TABLE a1 TO b1, a2 TO b2, a3 TO b3

RENAME operation can be considered atomic but "but only in cases when there was no exceptions during this process and server does not fall" (see comment).

[tetafro]

Waiting for REPLACE. The usecase is switching application to another table by re-creating view like this

CREATE VIEW my_table AS SELECT * FROM my_table_v1
...
REPLACE TABLE my_table WITH
CREATE VIEW AS SELECT * FROM my_table_v2

and application (which queries my_table) seamlessly switches from one table to another.

[alexey-milovidov]

Now this feature can be easily implemented on top of DatabaseAtomic.
@tavplubix said that he is ready to take.