clickhouse_slow_query.sh

gistfile1.txt

# ClickHouse的top工具，实时查看运行的SQL，运行了多久，来自哪里

# 注意事项：查询SQL的时候，要把客户端里的空格去掉，要判断是否为空，否则影响while read的读取位置

# TODO：清屏很暴力，鼠标滚动就不见了   limit限制

password="xxxx"

ls /usr/bin/clickhouse-client >/dev/null 2>&1

if [[ $? -ne 0 ]]
then
    echo "clickhouse-client command not found, exit"
    exit 1
fi


ck="clickhouse-client    --password $password -udefault"

cat << EOF
输入你想要登陆的ClickHouse节点
`echo "select host_name from system.clusters where host_name like '10.64.58.%' "  | $ck `
请输入
EOF

read -p "Enter the values: " ck_domain


# 清屏
clear

    # 进入循环加载
    while((1))
    do
    # color print
    printf '\e[1;32m%-6s\e[0m \e[1;32m%-10s\e[0m \e[1;32m%-15s\e[0m \e[1;32m%-25s\e[0m \e[1;32m%-8s\e[0m \e[1;32m%-100s\e[0m\n'   "id" "user" "address" "client" "elapsed" "query(Top 30)"
        while read id initial_user initial_address client_name elapsed query
        do
            # echo $initial_user $initial_address $client_name $elapsed $query

            # 根据响应时间，高亮慢查询
            [[ `echo "$elapsed > 3" | bc` -eq 1 ]]  \
                && printf '%-6s %-10s %-15s %-25s \e[1;31m%-8s\e[0m %-100s\n'   "$id" "$initial_user" "$initial_address" "$client_name" "$elapsed" "$query" \
                           || printf '%-6s %-10s %-15s %-25s \e[1;32m%-8s\e[0m %-100s\n' "$id"  "$initial_user" "$initial_address" "$client_name" "$elapsed" "$query"

        done<<EOF
`$ck -h ${ck_domain}  -d system -q  \
"select substring(query_id, 1, 5) AS id, if(initial_user = '', 'NULL', initial_user),  if(http_user_agent = '', 'NULL', http_user_agent) , replaceAll(if(client_name='','NULL', client_name), ' ', '_') as client_name, \
round(elapsed, 2) as elapsed, substring(query, 1, 250) as query from processes order by elapsed desc limit 30"`
EOF
    sleep 1
    clear
    done

echo