Clickhouse 批量删除表分区脚本

#输入的分区只会删除分区之前的数据 当天的分区数据不会被删除 partition<分区
#支持执行脚本时遇到执行失败的sql 会终止脚本执行 exit 遇到报错会停止执行 no_exit 遇到报错还会继续执行下去
#支持单个表批量分区删除

查询表大小

select hostname(), database, table,formatReadableSize(sum(bytes_on_disk)) as on_disk,sum(bytes_on_disk),sum(rows) from system.parts_all where database!='system' group by database,table  order by sum(bytes_on_disk)  desc SETTINGS distributed_group_by_no_merge = 1;

查询表分区

select partition from system.parts_all where database='xxx' and  table='xxx' group by partition order by partition desc;

执行删除分区脚本

sh drop_partition.sh 127.0.0.1  exit user passwrod database table partition;

#!/bin/bash
#输入的分区只会删除分区之前的数据 当天的分区数据不会被删除 partition<分区
#支持执行脚本时遇到执行失败的sql 会终止脚本执行 exit 遇到报错会停止执行 no_exit 遇到报错还会继续执行下去
#支持自定义库和自定义表分区删除

#当前目录
dir=$(dirname $(readlink -f $0))
#登录ck_client_ip
host_ip=$1
#ck 账号
#退出标识
exit_code=$2
user=$3
#ck 密码
pass=$4
#库名
database=$5
#表名
table=$6
#partition
partition=$7
#ck 端口
http_port="29010"







#判断一下传入参数
if [[ $# -ne 7 ]];then
  echo "$0 host  exit_code user passwrod database table partition"
  echo "$0 127.0.0.1  exit user passwrod database table partition"
  exit 1
fi


if [[ "$exit_code" -ne "exit" ]] && [[ "$exit_code" -ne "no_exit" ]]; then
    echo "exit_code: $exit_code"
    echo "Please input first parameter exit or no_exit"
    exit 1
fi


function exit_method(){
   if [[ $exit_code == "exit" ]]; then  
      exit 1
   fi
}
 
function drop_partition(){

  select_partition="select concat('alter table ${database}.${table}  on cluster xhraptor drop partition ', partition ,';') from system.parts where  partition<'${partition}' and database='${database}' and table='${table}' group by partition order by partition"

  ${dir}/clickhouse-client -h ${host_ip} --port ${http_port} -u ${user} --password ${pass} -q "${select_partition}" > /tmp/1.txt
  
  
  IFS=$'\n'
  for line in `cat /tmp/1.txt`
  do
    echo "执行 $line"
        ${dir}/clickhouse-client -h ${host_ip} --port ${http_port} -u ${user} --password ${pass} -q "${line}"
        if [[ $? -ne 0 ]];
        then
     echo "失败----------------"
         exit_method
     
    else
     echo "成功----------------"
         sleep 2s
         
  
  fi

  done

  

}

drop_partition