2021-07-25 写一个bash脚本根据选项执行不同函数调用sql

#!/bin/bash

## set ck connect info

HOST=172.18.1.6
PORT=9000
USER=default
PASS=123456
DB=sample_test

## set sqls to do

SQL_TODO=(
"insert into n_result select toStartOfFiveMinute(toDateTime64(sample_ts, 3)) as time, sum(sample_data*sample_data)/count(*) as en_bhn from GS_NXT_00_BHN group by time order by time;"

"insert into e_result select toStartOfFiveMinute(toDateTime64(sample_ts, 3)) as time, sum(sample_data*sample_data)/count(*) as en_bne from GS_NXT_00_BHE group by time order by time;"

"insert into z_result select toStartOfFiveMinute(toDateTime64(sample_ts, 3)) as time, sum(sample_data*sample_data)/count(*) as en_bnz from GS_NXT_00_BHZ group by time order by time;"

"select a.time, a.en1, b.en2, c.en3 from n_result a, e_result b, z_result c where a.time = b.time and b.time = c.time;"

)

## set tbales to truncate

TABLES_TO_TRUNCATE=(
n
e
z
)

## get sql to do length and index
sql_length=${#SQL_TODO[@]}
sql_last_index=$(echo "${sql_length}-1"|bc)

## set functions

function call_ck_sql(){
        echo "$*"|clickhouse-client -h $HOST --port $PORT -u $USER --password $PASS -d $DB
}

function echo_sqls(){
for SQL_Index in $(seq 0 ${sql_last_index})
do
echo "${SQL_TODO[${SQL_Index}]}"
done
}

function call_sqls(){
for SQL_Index in $(seq 0 ${sql_last_index})
do
echo "## Preparing to call below sql:"
sql="${SQL_TODO[${SQL_Index}]}"
echo "${sql}"
echo " "
call_ck_sql "${sql}"
echo " "
echo "## Above sql executed!!!"
done
}


function truncate_tables(){
for table_prefix in ${TABLES_TO_TRUNCATE[@]}
do
call_ck_sql "truncate table ${table_prefix}_result"
done
}

function check_result(){
call_ck_sql "select a.time, a.en1, b.en2, c.en3 from n_result a, e_result b, z_result c where a.time = b.time and b.time = c.time;"
}

function main(){
echo_sqls
call_sqls
truncate_tables
echo "## check result after truncating tables:"
check_result
}

function show_help(){
echo "Usage: `basename $0` -[e|c|x|t|d|s|R] value"
}

## if no option or arg, call main
if [ "$#" -lt 1 ]; then
  echo "## no option/arguments, will call main function"
  ACTION=main
fi

while getopts 'escxtdR:h' OPT;do
    case $OPT in
        e)
            ACTION="echo_sqls";;
        c|x)
            ACTION='call_sqls';;
        t|d)
            ACTION="truncate_tables";;
        s)
            ACTION="check_result";;
        R)
            sql="$OPTARG"
            ACTION="call_ck_sql ${sql}";;
        h|?|*)
            ACTION="show_help";;
    esac
done

shift $(($OPTIND - 1))

#eval main
echo "## Prepare to call the function $ACTION"
echo $ACTION
$ACTION