clickhouse如何删除大表
![](https://filescdn.proginn.com/1497276a147ed73f39044ccc0c17fce9/80697d4ffcb4d19016d275e484ac3f61.webp)
clickhouse 在单表或单分区超过50GB时,将无法直接删除
1. 案例
模拟删除单表或单分区超过50GB时,删除报错的情况
1.1 一个表中超过50GB的分区
-- 删除分区的脚本如下
ALTER TABLE testdb.test DROP PARTITION (20220606,'12');
Code: 359. DB::Exception: Received from localhost:9000. DB::Exception: Table or Partition in testdb.test was not dropped.
Reason:
1. Size (56.20 GB) is greater than max_[table/partition]_size_to_drop (50.00 GB)
2. File '/data/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 /data/clickhouse/flags/force_drop_table and make sure that ClickHouse has write permission for it.
Example:
sudo touch '/data/clickhouse/flags/force_drop_table' && sudo chmod 666 '/data/clickhouse/flags/force_drop_table'.
1.3 解决方法
报错信息中已给出解决方法:
1) 增大单表或单分区的可删除的大小
2) 通过执行脚本,强制删除
1.4 我们选择强制删除来解决
执行如下脚本
sudo touch '/data/clickhouse/flags/force_drop_table' && sudo chmod 666 '/data/clickhouse/flags/force_drop_table'
![](https://filescdn.proginn.com/3448bf6191dc2fb8cba6a4e989d6246d/d26950df89056e253ff91a11d9035cf2.webp)
2. mysql8.0新增用户及加密规则修改的那些事
3. 比hive快10倍的大数据查询利器-- presto
4. 监控利器出鞘:Prometheus+Grafana监控MySQL、Redis数据库
5. PostgreSQL主从复制--物理复制
6. MySQL传统点位复制在线转为GTID模式复制
![](https://filescdn.proginn.com/c7fdfb7ccc700939b5ab617708b491b7/2e1025449bbe571795b9e0bfb1de0adf.webp)
![](https://filescdn.proginn.com/9ef094072a9566bef82ccaa1b17b5094/e143b0254adbe4acd7f611afc3a0b935.webp)
![](https://filescdn.proginn.com/33120a91fd9196a3ca1275ecabc4e62d/0984c10f36b62dc81d0d5d183af4b732.webp)
评论