从mysql导入hive脚本

#!/bin/bash
#全量更新kn1_base_conf_group

#环境设置:u 不存在的变量报错;e 发生错误退出;pipefail 管道有错退出
set -euo pipefail

#########要更改变的变量#######
#mysql数据库信息
MYSQL_HOST="192.168.1.138"
MYSQL_PORT="63751"
MYSQL_USER='user'
MYSQL_PASS='MYSQL_PASSWORD'
MYSQL_DB='dw'
MYSQL_TABLE='kn1_base_conf_group'
################


# 临时文件目录
TMP_DIR="/var/tmp"
md5=`echo -n "${MYSQL_TABLE}"|md5sum|cut -d ' ' -f1`

echo "=======全量更新${MYSQL_TABLE}到${HIVE_DB_PATH},========"
cd ${TMP_DIR}
# MySQL导出数据到文件的路径
TMP_SAVE_NAME="${MYSQL_TABLE}-$md5.txt"
TMP_SAVE_PATH="${TMP_DIR}/${TMP_SAVE_NAME}"

echo "=======从mysql导出数据文件========"
# mysql查询命令
mysqlQuery="select day_key,\
group_id,\
group_name,\
realgroup_id,\
realgroup_name,\
app_service_id,\
app_service_name,\
service_state,\
node_id,\
node_name,\
area,\
province,\
isp,\
isp_cnname,\
province_name,\
area_name from ${MYSQL_DB}.${MYSQL_TABLE};"


rm -f ${TMP_SAVE_NAME}
echo "mysql -N -u${MYSQL_USER} -p\"${MYSQL_PASS}\" -h${MYSQL_HOST} -P${MYSQL_PORT} -D ${MYSQL_DB} --default-character-set=utf8   -e \"$mysqlQuery\"  | sed 's/\t/|/g;' > ${TMP_SAVE_PATH}"

mysql -N -u${MYSQL_USER} -p"${MYSQL_PASS}" -h${MYSQL_HOST} -P${MYSQL_PORT} -D ${MYSQL_DB} --default-character-set=utf8   -e "$mysqlQuery"  | sed 's/\t/|/g;' > ${TMP_SAVE_PATH}

#echo "=======压缩数据文件========"
#echo "tar -zcf ${HIVE_FILE_NAME}  ${TMP_SAVE_NAME}"
#tar -zcvf ${HIVE_FILE_NAME}  ${TMP_SAVE_NAME} 

echo "=======导入hadoop========"

# hive数据库信息
HIVE_DB="dw"
HIVE_TABLE="kn1_base_conf_group"

#hive的hdfs路径
HIVE_DIR="/user/hive/warehouse"
HIVE_DB_PATH="${HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}"
HIVE_FILE_NAME="${HIVE_TABLE}.txt"
HIVE_TABLE_PATH="${HIVE_DB_PATH}/${HIVE_FILE_NAME}"

echo "=======hadoop 路径========"
echo "$HIVE_DB_PATH"

#hadoop命令前辍
CMD_PRE="sudo -u hdfs "

#hadoop命令
CDM_HADOOP=${CMD_PRE}" hadoop fs "


set +e
echo "${CDM_HADOOP} -rm -r ${HIVE_DB_PATH}"
echo "${CDM_HADOOP} -mkdir -p ${HIVE_DB_PATH}"
echo "${CDM_HADOOP} -copyFromLocal ${TMP_SAVE_NAME} ${HIVE_TABLE_PATH}"
${CDM_HADOOP} -rm -r ${HIVE_DB_PATH}
${CDM_HADOOP} -mkdir -p ${HIVE_DB_PATH}
${CDM_HADOOP} -copyFromLocal ${TMP_SAVE_NAME} ${HIVE_TABLE_PATH}
set -e

echo "=======hive显示========"
#hive sql片断
HIVE_LIMIT=10

#hive SQL
hiveSelectSql="use ${HIVE_DB};
select * from ${HIVE_TABLE} limit ${HIVE_LIMIT};
"

CDM_HIVE=${CMD_PRE}" hive "

echo "${CDM_HIVE} -e \"$hiveSelectSql\""
${CDM_HIVE} -e "$hiveSelectSql"
sudo -u hdfs impala-shell --impalad=121.226.240.$res:21000 -q "REFRESH ${HIVE_DB}.${HIVE_TABLE};"
#sudo -u hdfs impala-shell --impalad=121.226.240.$res:21000 -q "compute stats ${HIVE_DB}.${HIVE_TABLE};"
rm -f ${TMP_SAVE_PATH}