Hive2Mysql

#!/bin/bash
set -e

bin=`dirname "$0"`
bin=`cd $bin; pwd`

function usage() {
echo
echo "Usage:
	-f:			必选，执行文件的路径

	文件内容:
	-h			必选，mysql host
	-P			必选，mysql post
	-u			必选，mysql username
	-p			必选，mysql password
	-mysql			必选，mysql插入语句
	-hql			必选，hive查询语句"
echo
exit
}

file_path=""
mysql_host=""
mysql_port=""
username=""
password=""
mysql_insert_sql=""
hive_select_sql=""


while [ $# -gt 0 ]; do
  case "$1" in
   	-f)
      shift
	  file_path=$1
      shift
      ;;
    -help)
	  usage
	  break
      ;;
  esac
done

# 获取配置
while read line || [[ -n ${line} ]]
do
	eval "$line"
done < $file_path

# 参数验证
if [[ $mysql_host == "" || $mysql_port == "" || $username == "" || $password == "" || $mysql_insert_sql == "" || "$hive_select_sql" == "" ]]; then
	echo "必选参数没填"
	usage
fi

# 临时路径
tmp_dir=/tmp/hive2mysql-$USER
mkdir -p $tmp_dir
chmod +777 $tmp_dir

data_dir=$tmp_dir/${table_name}/data
valuse_file=$tmp_dir/${table_name}/values.txt

echo '=======================开始获取hive数据==========================='
hive -e "SET hive.exec.compress.output=false; 
insert overwrite local directory '${data_dir}' 
row format delimited fields terminated by '\001' 
STORED AS TEXTFILE 
$hive_select_sql"

sed 's/"/\\"/g' $data_dir/* | sed "s/'/\\\'/g" | awk -F '\x01' '{
	line="";
    for(i=1;i<=NF;i++) {
	    if(i==1){
	    	line=sprintf("('\''%s'\''", $i);
	    }
	    else {
	    	line=sprintf("%s,'\''%s'\''", line, $i);
	    }
	}
	if(line!="") {
        printf("%s)\n", line);
    }
}' > $valuse_file

rm -rf $data_dir/*


echo '=======================获取hive成功==========================='

echo '=======================开始插入mysql=========================='
pos=0
insert_sql=${mysql_insert_sql}

IFS_old=$IFS
IFS=$'\n'

for line in `cat $valuse_file`
do
	pos=$(($pos+1))
	if [ $pos == 1 ];
	then 
		insert_sql="$insert_sql$line"
	elif [ $pos == 1000 ];
	then
		insert_sql="$insert_sql,$line;"
        mysql -h${mysql_host} -P${mysql_port} -u${username} -p${password} -e "${insert_sql}"
        echo "insert data ${pos}"
		insert_sql=$mysql_insert_sql
		pos=0
        sleep 0.05
	else
		insert_sql="$insert_sql,$line"
	fi
done

IFS=$IFS_old

if [ pos > 0 ];
then
	insert_sql="$insert_sql;" 
	mysql -h${mysql_host} -P${mysql_port} -u${username} -p${password} -e "${insert_sql}"
    echo "insert data ${pos}"
fi


rm -rf $valuse_file
echo '=======================插入mysql成功=========================='

mysql_host='xxxx'
mysql_port='3306'
username='root'
password='1234'
mysql_insert_sql='replace into db.table(`id`,`name`) values'
hive_select_sql='select * from db.table'

hive2mysql -f config