# !/bin/sh
# 1463922300,http://aliv.weipai.cn,电信,上海,82793933,155,153,82793300,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,633,0,0,0,0,0,0,0,0,0,0,0,0,1,0
MYSQL_HOST='127.0.0.1'
MYSQL_USER='root'
MYSQL_PASSWD='root'
MYSQL_DB='db'
MYSQL_TABLE='table'
csv_file='/tmp/file.csv'
touch $csv_file
> $csv_file
while read line;do
#echo $line
t_flow=0
t_pv=0
buckettime=`echo $line | cut -d "," -f1`
channel=`echo $line | cut -d "," -f2`
area=`echo $line | cut -d "," -f3`
country=`echo $area | cut -d "-" -f1`
province=`echo $area | cut -d "-" -f2`
city=`echo $area | cut -d "-" -f3`
isp=`echo $area | cut -d "-" -f4`
httpcode=`echo $line | cut -d "," -f4`
rs1=$buckettime","$channel","$isp","$province
rs2=""
for (( i=1; i<=16; i++)); do
tmp=`echo $httpcode | cut -d "|" -f$i`
num=`echo $tmp | cut -d "_" -f1`
flow=`echo $tmp | cut -d "_" -f2`
t_flow=`expr $t_flow + $flow`
t_pv=`expr $t_pv + $num`
if test -z "$rs2"
then
rs2=$num","$flow
else
rs2=$rs2","$num","$flow
fi
done
rst=$rs1","$t_flow","$t_pv","$rs2
echo $rst
echo $rst >> $csv_file
done < /root/data.txt
import_table_sql="load data infile '/tmp/file.csv' into table pin.flowfenbu_by5m fields terminated by ',' optionally enclosed by '' escaped by '' lines terminated by '\n'; "
mysql -h${MYSQL_HOST} -P3306 -u${MYSQL_USER} -p${MYSQL_PASSWD} ${MYSQL_DB} -e"${import_table_sql}"
#mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST -P3306 --default-character-set=utf8 < db.all.sql