hive导出数据FTP和SFTP的shell脚本实现文件的传输

1.脚本入参

/home/bdp/shangeshishi/shellTest/exportTargrp.sh 10.45.47.63  dauser dauser /work/dauser/da/test/ "select * from smart_test.ed3_prd_inst_inject_label_ext0 limit 100" /home/bdp/shangeshishi/shellTest/20200212/ tar_grp_52429 20200212 "-u 'jdbc:hive2://host66:10000/default;principal=hive/host66@NBDP.COM' --hiveconf mapreduce.job.queuename=default"

2.FTP推送脚本

#!/bin/bash
if [ $# -lt 9 ];then
    echo " 参数校验失败！参数存在异常！"
    echo "retCode -1"
    exit 1
 fi
##ftp信息
ftpIP=$1
ftpUser=$2
ftpPassword=$3
ftpPath=$4

##tableName,为database.table
tableName=$5
echo ${tableName}
##集群服务器上hive导出文件的路径
filePath=$6
##导出文件的文件名
fileName=$7
##账期
acct_time=$8
##beeline命令参数信息
beelineInfo=$9

knowledgeCode=${fileName}".txt"
##knowledgeCheckCode=${fileName}"_ch.txt"
wholePath=${filePath}${fileName}".txt"
##wholeCheckPath=${filePath}${fileName}"_ch.txt"

echo ${wholePath}
##echo ${wholeCheckPath}

##判断文件夹是否存在 
if [[ ! -d $filePath ]]; then
	echo "文件夹不存在！" 
	mkdir -p $filePath
else
	echo "文件夹存在"
fi
chmod 777 $filePath
#export HADOOP_HOME=/opt/cloudera/parcels/CDH
#export HADOOP_CONF_DIR=/etc/hadoop/conf
##执行hive导出到指定TXT文件
/opt/cloudera/parcels/CDH/bin/beeline ${beelineInfo} --silent=true --delimiterForDSV=$'\005' --outputformat=dsv --incremental=true --showHeader=true --nullemptystring=true  -e "set hive.resultset.use.unique.column.names=false; ${tableName} " > ${wholePath}

##计算总行数
#totalNum=`cat ${wholePath}|wc -l`
#echo ${totalNum}
##生成check文件 不用了
##cat > ${wholeCheckPath} << END_TEXT
##total : $((totalNum-1));
##acct_time : ${acct_time}
##END_TEXT

##判断远程目录下面是否已经存在当前账期的目录
function checkfile()
{
ftp -n<<!
open $ftpIP
user $ftpUser $ftpPassword
ls $ftpPath
bye
!
}
##ftp上传到服务器如果存在对应的文件夹则不创建
if checkfile ${acct_time} | grep ${acct_time}
then
ftp -n<<!
open $ftpIP
user $ftpUser $ftpPassword
binary
cd $ftpPath${acct_time}
lcd $filePath
prompt
mput $knowledgeCode
close
bye
!
else
ftp -n<<!
open $ftpIP
user $ftpUser $ftpPassword
binary
mkdir $ftpPath${acct_time}
cd $ftpPath${acct_time}
lcd $filePath
prompt
mput $knowledgeCode
close
bye
!
fi

echo "retCode:0"
echo "retMes:执行完毕!"

exit 0;

3.SFTP推送脚本

#!/bin/bash
if [ $# -lt 9 ];then
    echo " 参数校验失败！参数存在异常！"
    echo "retCode -1"
    exit 1
 fi
##ftp信息
ftpIP=$1
ftpUser=$2
ftpPassword=$3
ftpPath=$4

##tableName,为database.table
tableName=$5
echo ${tableName}
##集群服务器上hive导出文件的路径
filePath=$6
##导出文件的文件名
fileName=$7
##账期
acct_time=$8
##beeline命令参数信息
beelineInfo=$9

knowledgeCode=${fileName}".txt"
##knowledgeCheckCode=${fileName}"_ch.txt"
wholePath=${filePath}${fileName}".txt"
##wholeCheckPath=${filePath}${fileName}"_ch.txt"

echo ${wholePath}
##echo ${wholeCheckPath}

##判断文件夹是否存在 
if [[ ! -d $filePath ]]; then
	echo "文件夹不存在！" 
	mkdir -p $filePath
else
	echo "文件夹存在"
fi
chmod 777 $filePath

##执行hive导出到指定TXT文件
/opt/cloudera/parcels/CDH/bin/beeline ${beelineInfo} --silent=true --delimiterForDSV=$'\005' --outputformat=dsv --incremental=true --showHeader=true --nullemptystring=true  -e "set hive.resultset.use.unique.column.names=false; ${tableName} " > ${wholePath}

##判断远程文件是否存在
function checkfile()
{
expect <<- EOF
spawn sftp $ftpUser@$ftpIP
expect {
    "password:" {send "$ftpPassword\r"}
}
expect "sftp>"
send "cd $ftpPath\r"
expect "sftp>"
send "ls $ftpPath\r"
expect {
    "password:" {send "cd $ftpPath\r"}
}
send "bye\r"
EOF
}

#判断帐期文件是否存在,不存在则创建
if checkfile | grep ${acct_time}
then
expect <<- EOF
set timeout 60
spawn sftp $ftpUser@$ftpIP
expect {
    "password:" {send "$3\r"}
}
expect "sftp>"
send "mkdir $ftpPath${acct_time}\r"
expect "sftp>"
send "cd $ftpPath${acct_time}\r"
set timeout -1
expect "sftp>"
send "lcd $filePath\r"
expect "sftp>"
send "put $knowledgeCode\r"
expect "sftp>"
send "bye\r"
EOF
else
expect <<- EOF
set timeout 60
spawn sftp $ftpUser@$ftpIP
expect {
    "password:" {send "$3\r"}
}
expect "sftp>"
send "mkdir $ftpPath${acct_time}\r"
expect "sftp>"
send "cd $ftpPath${acct_time}\r"
set timeout -1
expect "sftp>"
send "lcd $filePath\r"
expect "sftp>"
send "put $knowledgeCode\r"
expect "sftp>"
send "bye\r"
EOF
fi

echo "retCode:0"
echo "retMes:执行完毕!"

exit 0;