clickhouse删除数据常见报错

clickhouse 删除数据常见异常

• Expected one of: DATABASE, TEMPORARY, TABLE

truncate login_log_struct;

Syntax error: failed at position 10:

truncate login_log_struct;

Expected one of: DATABASE, TEMPORARY, TABLE

解决方案

drop table login_log_struct;

• Code: 359. DB::Exception: Received from localhost:9000. DB::Exception: Table or Partition in 库.表 was not dropped.

drop table login_log_struct;

# 分区删除，不超过50GB, 表删除，不超过200GB
Received exception from server (version 19.15.3):
Code: 359. DB::Exception: Received from localhost:9000. DB::Exception: Table or Partition in flash.flash_login_log_struct was not dropped.
Reason:
1. Size (1.09 TB) is greater than max_[table/partition]_size_to_drop (200.00 GB)
2. File '/data/clickhouse/flags/force_drop_table' intended to force DROP doesn't exist
How to fix this:
3. Either increase (or set to zero) max_[table/partition]_size_to_drop in server config and restart ClickHouse
4. 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. 修改200G的限制，这个功能主要是怕操作不当，一次性直接删除了全表数据，建议不要修改；
2. 删除占用磁盘较大的字段，当数据量小于该限制，就可以使用删除表了

操作一：
alter table login_log_struct drop partition (‘202012’,‘20201231’);
操作二：
alter table login_log_struct drop column field1;