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，但是需要重启生效
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权限的，不一定会有问题，没试过。
规范点好。
再次删除就不会报错了。
标签：force,50G,drop,flags,clickhouse,table,size,ClickHouse,大表 From： https://www.cnblogs.com/PiscesCanon/p/17040952.html