hive 直接访问mysql_hive 直接插入mysql

先在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); "