先在hive中计算,将计算结果保存到hdfs文件,读取到本地使用mysql 的 load data local infile 语句写入到mysql
#!/bin/bash
#BEG :----------------------------- define function-----------
log(){
echo "`date +%Y%m%d-%H%M%S` : $@" >> ${RUNLOG}
}
CheckIfError(){
if [ $? != 0 ];then
#log " Error : $1"
echo " Error : $1" | mail -s "${FILENAME} ${DAY} ${HOUR} fail" spider.zhcl@qq.com
exit -1
fi
}
#END :----------------------------- define function-----------
DAY=${1}
if [ -z "${DAY}" ];then
DAY=`date -d "-1day" +%Y%m%d`
fi
echo ${DAY}
ROOTDIR="/data/dmap/script/mediareport"
FILENAME=$(basename $0)
RUNLOG="${ROOTDIR}/${FILENAME}.runlog"
export HADOOP_HOME=/dmp/hadoop/hadoop-1.0.3
export JAVA_HOME=/usr/java/jdk1.7.0_06
HADOOP=/dmp/hadoop/hadoop-1.0.3/bin/hadoop
HIVE=/dmp/hive/hive-0.9.0/bin/hive
Yesterday=`date -d "-1day ${DAY}" +%Y%m%d`
Tomorrow=`date -d "+1day ${DAY}" +%Y%m%d`
echo ${DAY}
echo ${HOUR}
$HIVE -S -e "insert overwrite directory '/user/dmap/mediareport/adxreq/${DAY}' select day_id,orig_plat_type, f_get_domain(url),count(request_id) from x_s_adx_bidreq where day_id=${DAY} group by day_id,orig_plat_type, f_get_domain(url);"
localPath=/data/dmap/file/mediareport
$HADOOP fs -getmerge /user/dmap/mediareport/adxreq/${DAY} ${localPath}/adxreq_${DAY}.dat
MYSQL="mysql -h xxx -uxxx -pxxx database --local-infile=1 -e "
$MYSQL "truncate media_adxreq_${DAY};"
$MYSQL "LOAD DATA LOCAL INFILE '${localPath}/adxreq_${DAY}.dat' into table media_adxreq_${DAY} FIELDS TERMINATED BY '' (datatime,channel,media, adxreq); "