Hive2Mysql

标签：insert mysql Hive2Mysql pos hive sql line

#!/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

标签：insert,mysql,Hive2Mysql,pos,hive,sql,line
From： https://blog.51cto.com/u_15879559/5868560