ClickHouse如何删除超过50G的大表

 

ClickHouse如何删除超过50G的大表

 

默认ck不给删除超过50G的表

文件/etc/clickhouse-server/config.xml里边有参数max_table_size_to_drop控制“50GB”这个阈值，并给了说明处理：

    <!-- Protection from accidental DROP.
         If size of a MergeTree table is greater than max_table_size_to_drop (in bytes) than table could not be dropped with any DROP query.
         If you want do delete one table and don't want to change clickhouse-server config, you could create special file <clickhouse-path>/flags/force_drop_table and make DROP once.
         By default max_table_size_to_drop is 50GB; max_table_size_to_drop=0 allows to DROP any tables.
         The same for max_partition_size_to_drop.
         Uncomment to disable protection.
    -->
    <!-- <max_table_size_to_drop>0</max_table_size_to_drop> -->
    <!-- <max_partition_size_to_drop>0</max_partition_size_to_drop> -->

 

 因此两种处理方式，

1.设置max_table_size_to_drop为0，对所有表生效，无需重启

重新设置后通过查看数据目录下preprocessed_configs下的config.xml验证参数是否实时生效：

[root@ck02 ~]# grep \<max_table_size_to_drop\> /clickhouse/preprocessed_configs/config.xml 
    <max_table_size_to_drop>0</max_table_size_to_drop>

 

PS:在ck版本23.9.2.56下存在视图system.server_settings，通过视图查看该参数会发现还是旧值，重启才会重置为最新值（实际设置为0立刻生效，以/clickhouse/preprocessed_configs/config.xml为准）。

2.创建<clickhouse-path>/flags/force_drop_table并授权，只针对一次表生效

 

第2种报错的时候实际上也给解决方式了。

下边结合我的案例就明白了，有需要注意的地方。

删表报错：

sztest-ck01 :) drop table ceb.test1030 ON CLUSTER ceb_cluster;

DROP TABLE ceb.test1030 ON CLUSTER ceb_cluster

Query id: 462a7590-0741-4b26-814b-7994e111ab8a

┌─host───────┬─port─┬─status─┬─error──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─num_hosts_remaining─┬─num_hosts_active─┐
│ sztest-ck04 │ 9000 │    359 │ Code: 359. DB::Exception: Table or Partition in ceb.test1030 was not dropped.
Reason:
1. Size (135.51 GB) is greater than max_[table/partition]_size_to_drop (50.00 GB)
2. File '/clickhouse/flags/force_drop_table' intended to force DROP doesn't exist
How to fix this:
1. Either increase (or set to zero) max_[table/partition]_size_to_drop in server config
2. Either create forcing file /clickhouse/flags/force_drop_table and make sure that ClickHouse has write permission for it.
Example:
sudo touch '/clickhouse/flags/force_drop_table' && sudo chmod 666 '/clickhouse/flags/force_drop_table'. (TABLE_SIZE_EXCEEDS_MAX_DROP_SIZE_LIMIT) (version 22.3.2.1) │                   3 │                0 │
│ sztest-ck01 │ 9000 │    359 │ Code: 359. DB::Exception: Table or Partition in ceb.test1030 was not dropped.
Reason:
1. Size (135.44 GB) is greater than max_[table/partition]_size_to_drop (50.00 GB)
2. File '/clickhouse/flags/force_drop_table' intended to force DROP doesn't exist
How to fix this:
1. Either increase (or set to zero) max_[table/partition]_size_to_drop in server config
2. Either create forcing file /clickhouse/flags/force_drop_table and make sure that ClickHouse has write permission for it.
Example:
sudo touch '/clickhouse/flags/force_drop_table' && sudo chmod 666 '/clickhouse/flags/force_drop_table'. (TABLE_SIZE_EXCEEDS_MAX_DROP_SIZE_LIMIT) (version 22.3.2.1) │                   2 │                0 │
│ sztest-ck03 │ 9000 │    359 │ Code: 359. DB::Exception: Table or Partition in ceb.test1030 was not dropped.
Reason:
1. Size (135.51 GB) is greater than max_[table/partition]_size_to_drop (50.00 GB)
2. File '/clickhouse/flags/force_drop_table' intended to force DROP doesn't exist
How to fix this:
1. Either increase (or set to zero) max_[table/partition]_size_to_drop in server config
2. Either create forcing file /clickhouse/flags/force_drop_table and make sure that ClickHouse has write permission for it.
Example:
sudo touch '/clickhouse/flags/force_drop_table' && sudo chmod 666 '/clickhouse/flags/force_drop_table'. (TABLE_SIZE_EXCEEDS_MAX_DROP_SIZE_LIMIT) (version 22.3.2.1) │                   1 │                0 │
│ sztest-ck02 │ 9000 │    359 │ Code: 359. DB::Exception: Table or Partition in ceb.test1030 was not dropped.
Reason:
1. Size (135.44 GB) is greater than max_[table/partition]_size_to_drop (50.00 GB)
2. File '/clickhouse/flags/force_drop_table' intended to force DROP doesn't exist
How to fix this:
1. Either increase (or set to zero) max_[table/partition]_size_to_drop in server config
2. Either create forcing file /clickhouse/flags/force_drop_table and make sure that ClickHouse has write permission for it.
Example:
sudo touch '/clickhouse/flags/force_drop_table' && sudo chmod 666 '/clickhouse/flags/force_drop_table'. (TABLE_SIZE_EXCEEDS_MAX_DROP_SIZE_LIMIT) (version 22.3.2.1) │                   0 │                0 │
└────────────┴──────┴────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────┴──────────────────┘
← Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.)  0%
4 rows in set. Elapsed: 0.121 sec. 

Received exception from server (version 22.3.2):
Code: 359. DB::Exception: Received from localhost:9000. DB::Exception: There was an error on [sztest-ck04:9000]: Code: 359. DB::Exception: Table or Partition in ceb.test1030 was not dropped.
Reason:
1. Size (135.51 GB) is greater than max_[table/partition]_size_to_drop (50.00 GB)
2. File '/clickhouse/flags/force_drop_table' intended to force DROP doesn't exist
How to fix this:
1. Either increase (or set to zero) max_[table/partition]_size_to_drop in server config
2. Either create forcing file /clickhouse/flags/force_drop_table and make sure that ClickHouse has write permission for it.
Example:
sudo touch '/clickhouse/flags/force_drop_table' && sudo chmod 666 '/clickhouse/flags/force_drop_table'. (TABLE_SIZE_EXCEEDS_MAX_DROP_SIZE_LIMIT) (version 22.3.2.1). (TABLE_SIZE_EXCEEDS_MAX_DROP_SIZE_LIMIT)

 

信息最下边就有解决办法了。

处理：

所有节点执行如下：

touch /clickhouse/flags/force_drop_table
chown clickhouse:clickhouse /clickhouse/flags/force_drop_table
chmod 666 /clickhouse/flags/force_drop_table

 

注意的点是，我多了一步chown授权用户和组的操作，原因是通过rpm安装ck的方式，OS用户clickhouse是无法登录的。

但是启动ck的服务以及脚本是指定了clickhouse用户启动ck服务器的，因此需要注意这个权限问题。

当然后边的666权限是有w权限的，不一定会有问题，没试过。

规范点好。

 

再次删除就不会报错了。

删除表后/clickhouse/flags/force_drop_table这个文件同时会被删除。