# job模版:/home/work/datax/job/fn/example/fn_t_test_days
{
"job": {
"content": [{
"reader": {
"name": "mysqlreader",
"parameter": {
"columnMeta": [{
"value": "dt",
"label": "DATE_FORMAT(f_create_time,'%Y-%m-%d')",
"type": "custom"
}, {
"value": "f_seq",
"label": "f_seq",
"type": null
}, {
"value": "f_enabled",
"label": "f_enabled",
"type": null
}],
"where": "",
"connectionPattern": {
"urls": ["jdbc:mysql://*.*.*.*:1234"],
"database": "dyp_db",
"tablename": "t_test",
"arguments": null
},
"connection": [JDBCSTR],
"mandatoryEncoding": "GBK",
"username": "datax_r",
"password": "******",
"column": ["DATE_FORMAT(f_create_time,'%Y-%m-%d')", "f_seq", "f_enabled"],
"jdbc_encoding": "CP1252"
}
},
"writer": {
"name": "streamwriter",
"parameter": {
"cryptMode": "no",
"roleType": "",
"fieldDelimiter": "\t",
"fileName": "r_ext_fn_t_test_old.dat",
"groupname": "dyp_gp",
"lineDelimiter": "\n",
"path": "/home/work/dyp-datax-admin/ext/"
}
}
}],
"setting": {
"speed": {
"channel": 5,
"byte": 20971520
}
}
}
}
#!/bin/bash
# 每小时同步一次,每次拉近9天数据
# 0 */1 * * * (cd /home/work/daiyuanpei; bash t_test_days.sh 9 > /home/work/daiyuanpei/t_test_days.out 2>&1 &)
. ~/.bashrc
dateEnd=`date +"%Y%m%d"`
# 默认开始时间30天前
dateBegin=`date +%Y%m%d -d"-30 day $dateEnd"`
# 判断是否传参并校验是否为数字、是否大于100
if [ $# == 1 ]; then
if [ ! -n "$(echo $1| sed -n "/^[0-9]\+$/p")" ]; then
echo "FATAL: The parameter is not a number."
exit -1
fi
if [ $1 -gt 100 ]; then
echo "FATAL: Parameter n cannot be greater than 100."
exit -1
fi
dateBegin=`date +%Y%m%d -d"-$1 day $dateEnd"`
fi
delDateBegin=`date +%Y-%m-%d -d"+1 day $dateBegin"`
echo $dateBegin $dateEnd $delDateBegin
schema="fn"
tbname="t_test"
example="/home/work/datax/job/fn/example/${schema}_${tbname}_days"
tempdir="/home/work/datax/job/fn"
basepath=$(cd `dirname $0`; pwd)
cd $basepath
jsonfile="${tempdir}/${schema}_${tbname}_days"
if [[ "$dateBegin" > "$dateEnd" ]]; then
echo "FATAL: dateEnd must be greater than or equal to dateBegin. dateBegin: $dateBegin, dateEnd: $dateEnd"
exit -1
fi
jdbcTpl="{\"jdbcUrl\":[\"jdbc:mysql://*.*.*.*:1234/dyp_db_DBDATE\"],\"table\":[\"t_test\"]}"
jdbcStr=""
while [[ "$dateBegin" < "$dateEnd" ]] || [[ "$dateBegin" == "$dateEnd" ]]; do
dayStr="${jdbcTpl/DBDATE/$dateBegin}"
jdbcStr="$jdbcStr,$dayStr"
dateBegin=`date +%Y%m%d -d"+1 day $dateBegin"`
done
echo "while done"
# 去掉第一个字符
jdbcStr=`echo $jdbcStr | awk '{print substr($1,2)}'`
# 下面sed 所以需要转义斜线和双引号
# 单斜线替换一次 双斜线替换所有匹配
jdbcStr="${jdbcStr//\"/\\\"}"
jdbcStr="${jdbcStr//\//\/}"
echo $jdbcStr
sed "s/JDBCSTR/${jdbcStr}/g;s/CURRENTDATE/${dateEnd}/g" ${example} > ${jsonfile}
echo "${jsonfile} done"
~/datax/bin/start_datax.sh ${jsonfile} && bash /home/work/datax/sh-load/t_test_days.sh ${schema} ${tbname} ${delDateBegin}
echo "*****************************"
echo "*****************************"
echo "*****************************"
#!/bin/bash
# /home/work/datax/sh-load/t_test_days.sh
. ~/.bashrc
if [ $# -lt 3 ]
then
echo "./loaddata schema tablename delDateBegin"
exit
fi
export LANG="zh_CN.UTF-8"
export LC_ALL="zh_CN.UTF-8"
schema=$1
tablename=$2
delDateBegin=$3
data_path=" /home/work/dyp-datax-admin/ext/r_ext_${schema}_${tablename}_old.dat"
basepath=$(cd `dirname $0`; pwd)
cd ${basepath}
if [ ! -e ${data_path} ];then
echo "no exist filepath:${data_path}"
exit
fi
#DEL_SQL="delete from ${schema}.${tablename} where dt>='${delDateBegin}';"
LOAD_SQL="delete from ${schema}.${tablename} where dt>='${delDateBegin}';insert into ${schema}.${tablename} select * from pay_data_ext.r_ext_${schema}_${tablename}_old;"
#echo ${DEL_SQL}
echo ${LOAD_SQL}
tempfile="${basepath}/temp.log"
export PGPASSWORD=******
/usr/bin/psql -h *.*.*.* -p 5432 -d dyp_gp_pay -U gp_user -c "$LOAD_SQL" > ${tempfile} 2>&1
if [ $? -eq 0 ]
then
logmsg=`cat ${tempfile}`
echo "load to success ${tablename}:${logmsg}"
emailTitle="[云上][${schema}.${tablename}] load success"
emailContent=`echo "datax load to gppay success: ${schema}.${tablename}: ${logmsg}"`
else
logmsg=`cat ${tempfile}`
msg=`echo "datax load to gppay failed:${tablename}:${logmsg}"`
emailTitle="[云上][${schema}.${tablename}] load failed"
emailContent=`echo "datax load to gppay failed: ${schema}.${tablename}: ${logmsg}"`
time=$(date "+%Y-%m-%d %H:%M:%S")
echo "${time} ${schema}.${tablename} FATAL" >> /home/work/datax/sh-load/log/datax.log.wf
fi
echo ${emailContent} | mail -s "${emailTitle}" daiyuanpei@daiyuanpei.com