GBase 8a巡检脚本--distribution_gbase_table_segment.sh

#!/bin/bash
source /home/gbase/.gbase_profile
gccli_command='gccli -ugbase -pxxxxxxxxxxxxxx'
#sql="select vc_id,dbname,tbname from gbase.table_distribution where dbname  in ('datumdb') order by dbname,tbname" 
sql="select t1.name,t2.dbname,t2.tbname from information_schema.vc t1 inner join gbase.table_distribution t2 on t1.id=t2.vc_id  where t1.name in ('vc_dkcx','vc_xxx','vc_sjxt','vc_sjjs','vc_east','vc_sjfx','vc_sssj') and t2.dbname  not in ('information_schema','performance_schema','gbase','gctmpdb','gclusterdb') order by t1.name,t2.dbname,t2.tbname"
${gccli_command} -Ns -e"$sql" > db_table.lst
Mytime=$(date +%Y%m%d%H%M%S)
echo "${Mytime} start"

distribution(){
   > /home/gbase/work_space/distribution.log
   > /home/gbase/work_space/table_size.log
   while read vcname dbname tbname
   do
    sql_size="use information_schema;select table_vc,table_schema,table_name,round(TABLE_DATA_SIZE/1024/1024/1024) size,$Mytime from CLUSTER_TABLES where table_vc='${vcname}' and table_schema='${dbname}' and table_name='${tbname}'"
     $gccli_command -Ns -e "$sql_size" >> table_size.log
     sql1="select * from information_schema.CLUSTER_TABLE_SEGMENTS where table_vc = '${vcname}' and table_schema='${dbname}' and table_name='${tbname}'"
     sql2="use information_schema; select round(TABLE_DATA_SIZE/1024/1024/1024) from CLUSTER_TABLES  where table_vc = '${vcname}' and table_schema='${dbname}' and table_name='${tbname}'"
     sql3="use gbase; select t.isReplicate from table_distribution t inner join information_schema.vc t1 on t.vc_id = t1.id  where t1.name = '${vcname}' and t.dbName='${dbname}' and t.tbName='${tbname}';"
     df=`gccli -ugbase -pxxxxxxxxxxxxxx -Ns -e"$sql2"`
     isReplicate=`gccli -ugbase -pxxxxxxxxxxxxxx -Ns -e"$sql3"`
     gccli -ugbase -pxxxxxxxxxxxxxx -Ns -e"$sql1" > gbase_tmp.log
     min=`cat gbase_tmp.log | awk 'BEGIN {min = 100} {if ($8+0 < min+0) min=$8} END {print min}' | awk -F '%' '{print $1}'`
     max=`cat gbase_tmp.log | awk 'BEGIN {max = 0} {if ($8+0 > max+0) max=$8} END {print max}' | awk -F '%' '{print $1}'`
     value=$(echo $max-$min|bc)
     d_value=`expr .5 \> $value`
     if [ $isReplicate = 'NO' ]; then
         if [[ $df -ge 2 ]]; then 
           if [[ $d_value -eq 0 ]];then
             echo $vcname,$dbname,$tbname,$df,$min,$max,$Mytime >> distribution.log
           fi
         fi
     fi
   done <db_table.lst

   sql_load_distribution="use vc vc_xxx;load data infile 'sftp://gbase:xxxxxxxxxxxxxx@172.16.9.181/home/gbase/work_space/distribution.log' into table test.distributions fields terminated by ',';"
$gccli_command -Ns -e "$sql_load_distribution"
   sql_load_size="use vc vc_xxx;load data infile 'sftp://gbase:xxxxxxxxxxxxxx@172.16.9.181/home/gbase/work_space/table_size.log' into table test.testsize fields terminated by '\t';"
   $gccli_command -Ns -e "$sql_load_size"
   distribution_Endtime=$(date +%Y%m%d%H%M%S)
   echo "${distribution_Endtime}  end"
}

distribution

注意：
1、
d_value=`expr .1 \> $value`
shell脚本里 0.1 写成 .1
验证： 
[root@vm1 ~]# expr 0.1 \> 0.1
0
[root@vm1 ~]# expr 0.1 \> 0.09
1
倾斜大于0.1给统计出

2、$df -ge 2
大于2G的表，其实是大于1.5G的表

建表：
distributions

use vc_xxx.test;

create table distributions(
vcname varchar(64) DEFAULT NULL,
dbname varchar(64) DEFAULT NULL,
tbname varchar(64) DEFAULT NULL,
df int(21) DEFAULT NULL,
min double DEFAULT NULL,
max double DEFAULT NULL,
Mytime varchar(64) DEFAULT NULL);

create table testsize(
table_vc varchar(64) DEFAULT NULL,
TABLE_SCHEMA varchar(64) DEFAULT NULL,
TABLE_NAME varchar(64) DEFAULT NULL,
size  int(21) DEFAULT NULL,
Mytime varchar(64) DEFAULT NULL);

-gt   ：    (greater than) 大于
-ge  ：    (greater than or equal) 大于或等于
-lt    ：    (less than) 小于
-le   ：    (less than or equal)小于或等于
-ne  ：    （not equal） 不相等
-eq  ：    （equal） 相等

less gbase_tmp.log 
vc_xxx test    testsize        n1      172.16.9.182    775     3015    46.6586%
vc_xxx test    testsize        n2      172.16.9.181    886     3126    53.3414%

awk 'NR == 1 {next} {print $1}' file.txt > file_col1.txt 
#NR==1指的是不打印第一行，print $1指的是打印第一列

求最大值：
awk 'BEGIN {max = 0} {if ($1+0 > max+0) max=$1} END {print "Max=", max}' test.txt