Drop & UnDrop Table In ClickHouse

Starting from ClickHouse version 23.3, It is now feasible to recover a dropped table in an Atomic database, provided that it is within the duration specified in the database_atomic_delay_before_drop_table_sec parameter (which is set to 8 minutes by default) since the DROP TABLE statement was executed.

First, this is an experimental feature of ClickHouse (v23.3). So, you have to enable this feature with the command below.

set allow_experimental_undrop_table_query = 1;

To change database_atomic_delay_before_drop_table_sec parameter, you can add a new config file into clickhouse-server config.d file. To do that, please follow the steps given below.

Create a database_atomic_delay_before_drop_table.xml file.

vi /etc/clickhouse-server/config.d/database_atomic_delay_before_drop_table.xml

Add a parameter to it.

Note: As a default this is 8 minutes.

<clickhouse>
    <database_atomic_delay_before_drop_table_sec>600</database_atomic_delay_before_drop_table_sec>
</clickhouse>

We set 600 seconds (10 minutes). It means you can recover your dropped table in 10 minutes.

Let’s give an example

Create a sample table.

CREATE TABLE testTable
(
    `id` UInt8,
    `name` String
)
ENGINE = MergeTree
ORDER BY id

Drop Table

drop table testTable;

Check the table status on the system.dropped_tables

SELECT *
FROM system.dropped_tables
FORMAT Vertical

Row 1:
──────
index:                 0
database:              default
table:                 testTable
uuid:                  d14dca2f-57c8-4586-b682-74574ea551bc
engine:                MergeTree
metadata_dropped_path: /var/lib/clickhouse/metadata_dropped/default.testTable.d14dca2f-57c8-4586-b682-74574ea551bc.sql
table_dropped_time:    2023-05-21 12:03:11

Undrop Table

UNDROP TABLE testTable

Check system.dropped_tables

SELECT *
FROM system.dropped_tables
FORMAT Vertical

Ok.

0 rows in set. Elapsed: 0.001 sec.

You will see the following empty result. Because you already undrop the table.