How Do I Delete the Backup Table of the ZooKeeper Node in the ClickHouse Cluster?

Symptom

When you connect a ClickHouse cluster and create a table, the ZooKeeper node does not get fully removed after you delete the table.

Figure 1 Execution results

Possible Causes

The command for creating a table contains ON CLUSTER default_cluster. Therefore, a local table is created on each node.

Solutions

Delete the tables and stored data on other nodes.

1. Create a database.

   create database demo ON CLUSTER default_cluster;

2. Create tables in the database.

   use demo;

   Create the test table.

   CREATE TABLE demo.test ON CLUSTER default_cluster(`EventDate` DateTime, `id` UInt64)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}') PARTITION BY toYYYYMM(EventDate) ORDER BY id;

3. Delete the table.

   drop table test SYNC;

4. The following error message is displayed if a table with the same name as the deleted one is created:

   ┌─host──────────┬─port─┬─status─┬─error──────────────────────────────────────────────────────────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
   │ 192.168.2.185 │ 9000 │     57 │ Code: 57. DB::Exception: Table demo.test already exists. (TABLE_ALREADY_EXISTS) (version 22.3.2.1) │                   1 │                1 │
   └───────────────┴──────┴────────┴────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────┴──────────────────┘
   ┌─host─────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
   │ 192.168.2.16 │ 9000 │      0 │       │                   0 │                0 │
   └──────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

5. Method 1: Run the following command to delete the tables and related data on each node:

   drop table test ON CLUSTER default_cluster SYNC;

   No error occurs if a table with the same name as the deleted one is created.

6. Method 2: Delete the table on other nodes.

   drop table test SYNC;