Mysql表数据导入到hive脚本——云贝教育19941464235

Mysql表数据导入到hive脚本

1.脚本1(hive服务器)

#!/bin/bash

files=/apps/sh/outdb.txt

if [ -f $files ]

then

rm -rf $files

fi

HOST=192.168.2.58

YDAY=$(date -d '-1 day' +%Y-%m-%d)

NTIME=$(date -d '-0 hour' +%H:%M:%S )

YTIME=${YDAY}" "${NTIME}

taskid=$1

echo 'HOUR:'${YTIME}

ssh -q root@${HOST} /apps/sh/mysqlIntoFile.sh"$taskid" "$YTIME"

sleep 4

scp root@${HOST}:$files /apps/sh/

chmod 755 outdb.txt

echo "load data into table yunbee"

hive --database yunbee -e "load datalocal inpath '${files}' into table dw_log_mb_push_ybadmin_hm;" -v echo"load data finished!"

2.脚本2(mysql服务器/apps/sh/mysqlIntoFile.sh)

#!/bin/bash

dbhost=192.168.2.58

dbuser=root

dbpwd=yunbee

files=/apps/sh/outdb.txt

if [ -f $files ]

then

rm -rf $files

fi

task_id=\'$1\'

ytime=\'`echo ${@:2}`\'

echo $task_id

echo $ytime

mysql -h$dbhost -u$dbuser -p$dbpwd <<EOF 2>/dev/null;

use ybadmin;

update t_marketing_task setschedule_time=${ytime} where task_id=${task_id};

update t_marketing_history setcreate_time=${ytime} where gp_task_id=${task_id};

SELECT id,target target_id,''content,gp_task_id,send_time,'' AS content2,brand_sn,STATUS,template_id,is_recomemended,task_desc,''status_str,fav_type,channel_code,action_type,serial_num,send_param,create_time,user_id,act_id,task_name,task_id,phrase_id,head_id,tail_id,brand_id,DATE_FORMAT(create_time,'%Y%m%d')dt,DATE_FORMAT(create_time,'%H') hm FROM t_marketing_history WHERE STATUS=1 andgp_task_id=${task_id} into outfile '${files}' fields terminated by ',' linesterminated by '\r\n';

 

quit

EOF

3.脚本重点:

1、日期格式如"2016-09-28 20:20:20"中间带空格作为sh脚本间传参的写法:目前是用的echo ${@:2}来做，不是很科学

2、linux中的变量传入到mysql脚本中，直接用${ytime}，记得要加sql语句的单引号

3、mysql中根据条件查询后导出到文本:select* from table where… into outfile ‘${files}’ fields terminated by ‘,’ lines terminated by ‘\r\n’; 字段之间用逗号，行之间用\r\n