筛选需要删除的分区，此处指定时间

    1. #!/bin/bash
    2. #author:robin
    3. #why: clean clickhouse data
    5. DATE=$(date -d"15 day ago" +%Y-%m-%d)
    6. QUERY="select table,partition,database from parts where active and max_date < '$DATE';"
    8. echo "$QUERY"
    10. /opt/dtstack/clickhouse/bin/clickhouse-client -h 0.0.0.0 -d system -q "$QUERY" > /tmp/clean_ck_data.list 
    12. if [ $? -eq 0 ]
    13. then 
    14.     echo "[ok]"
    15. else
    16.     echo "[failed]"
    17. fi

    清理筛选出来的分区

    1. #!/bin/bash
    2. #author: robin
    3. #why: clean clickhouse data
    6. QUERY="ALTER TABLE $TABLE DROP PARTITION '$PARTITION'"
    8. for((i=1;i<3000;i++))
    9. do
    10.   TABLE=`sed -n "${i},1p" /tmp/clean_ck_data.list | awk '{print $1}'`
    11.   PARTITION=`sed -n "$i,1p" /tmp/clean_ck_data.list | awk '{print $2}'`
    12.   DATABASE=`sed -n "${i},1p" /tmp/clean_ck_data.list | awk '{print $3}'`
    13.       if [ -z "$TABLE" ]
    14.       then
    15.          break
    16.        fi
    17.          sudo touch '/data/clickhouse/data/flags/force_drop_table' && sudo chmod 666 '/data/clickhouse/data/flags/force_drop_table'
    18.          ssh xx.xx.xx.xx "sudo touch '/data/clickhouse/data/flags/force_drop_table' && sudo chmod 666 '/data/clickhouse/data/flags/force_drop_table'"
    19.          /opt/dtstack/clickhouse/bin/clickhouse-client -h 0.0.0.0 -d $DATABASE -q "ALTER TABLE \"${TABLE}\" DROP PARTITION '${PARTITION}';" >> /tmp/clean_ck_data.log
    20.       if [ $? -eq 0 ]
    21.       then
    22.           echo "[ok]"
    23.       else
    24.           echo "[failed] clickhouse-client -h 0.0.0.0  -q "ALTER TABLE "${DATABASE}.${TABLE}" DROP PARTITION "'${PARTITION}'";""
    25.       fi
    26. done

    Tips
    ClickHouse client version 19.14.6.12版本，如果分区过大，清理时会限制删除操作，需要每次执行前创建一个临时文件，如果集群中有多个节点，多个节点都需要创建此文件

    Reason:

    1. Size (53.95 GB) is greater than max_[table/partition]_size_to_drop (50.00 GB)
    2. File ‘/data/clickhouse/data/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/data/flags/force_drop_table and make sure that ClickHouse has write permission for it.
      Example:
      sudo touch ‘/data/clickhouse/data/flags/force_drop_table’ && sudo chmod 666 ‘/data/clickhouse/data/flags/force_drop_table’.
      [failed] clickhouse-client -h 0.0.0.0 -q ALTER TABLE local_3.im DROP PARTITION ‘2020-02-25180d’