shell脚本读取clikhouse表数据到mysql

shell脚本读取clikhouse表数据到mysql

注意：其中数据库地址表名字账号密码需要改正自己的，包括sql逻辑等

#!/bin/bash
        echo "start..."

#this is ck changliang
ckhost="localhost"
ckport="9191"
ckreadSql="SELECT * FROM tb_table limit 1,1"
clickhouse-client --host ${ckhost} --port ${ckport} --multiquery -q"$ckreadSql">./a.txt

#this is mysql changliang
mysqlhost="localhost"
mysqlDB="db_test"
mysql_test_DB="tb_table"
mysqltable="tb_table_test"
mysqluser="root"
mysqlpwd="root"

select_sql="select * from group_info limit 1,1"
create_sql="CREATE table if not exists ${mysqltable} (
 record_insert_time DateTime DEFAULT now(),
 table_name varchar(255),
 field_name varchar(255),
 field_value varchar(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8"

        echo "创表语句为:${create_sql}"
#create test_table
mysql -u${mysqluser} -p${mysqlpwd} -h${mysqlhost} ${mysql_test_DB} -s  -e "${create_sql}"
#create table
mysql -u${mysqluser} -p${mysqlpwd} -h${mysqlhost} ${mysqlDB} -s  -e "${create_sql}"
base_path=$(cd "$(dirname "$0")"; pwd)
        echo ${base_path}  
        
#read txt to writeMysql
        echo "读取文件内容"
      
while read line
do
record_insert_time=`echo ${line}|awk -F" " '{print $1" "$2}'`
table_name=`echo ${line}|awk -F" " '{print $3}'`
field_name=`echo ${line}|awk -F" " '{print $4}'`
field_value=`echo ${line}|awk -F" " '{print $5}'`
        echo record_insert_time===${record_insert_time}
        echo table_name===${table_name}
        echo field_name===${field_name}
        echo field_value==${field_value}
        insert_sql="insert into ${mysqltable} (record_insert_time,table_name,field_name,field_value) values('${record_insert_time}','${table_name}','${field_name}','${field_value}')"
        echo ${insert_sql}
#insert data to msyqltable
        mysql -u${mysqluser} -p${mysqlpwd} -h${mysqlhost} ${mysqlDB} -s  -e "${insert_sql}"
        mysql -u${mysqluser} -p${mysqlpwd} -h${mysqlhost} ${mysql_test_DB} -s  -e "${insert_sql}"
done < ./a.txt