##
## @Title : to_omp_quota_config
## @Kind : shell
##
## @Author:
##
## @Tips : This is a UNCHOSEN TEMPLATE
## AND you could write down your code below comment
## OR getting rid of the comment before that
## modify by Bob Wu in 2022-06-23
export TZ='CST-8'
#供数的表
src_database=cn_dw_dev_dl_tables
src_tab_name=dws_omp_common_upc_info_d
src_database1=cn_dw_dev_dl_tables
src_tab_name1=dws_omp_common_quota_config_d
src_database2=cn_dw_dev_dl_tables
src_tab_name2=dws_omp_common_upc_info_check
#退出函数
exit_script()
{
exit 1
}
#验证是否表无数据
sql_count_result=`hive -e "set tez.queue.name=smartops;select count(1) as cnt from $src_database.$src_tab_name a "`
sql_count=`echo $sql_count_result | tr -cd "[0-9]"`
if [ $sql_count -eq 0 ]
then
echo "表 $src_database.$src_tab_name 数据为 $sql_count ，数据有异常，请跟进。" | mail -s "上游表 $src_database.$src_tab_name 数据异常，请跟进。" [email protected]
echo "由于表数据异常sqooop任务中止。"
exit_script
fi
echo "表的数据量为:"$sql_count
##获取数据提取时间
current_time_result=`hive -e "set tez.queue.name=smartops;select created_time as cnt from $src_database1.$src_tab_name1 limit 1"`
current_time=`echo $current_time_result | awk -F "|" '{printf $4}'`
#校验：如数据量和上次推送数据量相差超过30%，则发邮件报警，并中止sqoop导数,并修改OMP数据create_time为当前日期
sql_count_result2=`hive -e "set tez.queue.name=smartops;select count(1) as cnt from $src_database2.$src_tab_name2 a "`
sql_count2=`echo $sql_count_result2 | tr -cd "[0-9]"`
sql_cnt_result=`echo "scale=5;(($sql_count2-$sql_count)/$sql_count2)*100" | bc | awk '{printf "%.2f", $0}' | awk '{print int($0)}'`
echo "表2的数据量为："$sql_count2
echo "校验结果为：" $sql_cnt_result
if [ $sql_cnt_result -lt 30 ]
then
export sqoop_path=/usr/bin
hostname="PCNNT57020CNSQL\ni1:14481"
user=username
password='password'
database='promotion-e2e'
table=common_category_info
columns=dept_nbr,dept_name_cn,dept_name_en,category_nbr,category_name_ch,category_name_en,sku_store_total,created_time,create_user
export_dir=hdfs://cnprod1ha/warehouse/tablespace/external/hive/$src_database1.db/$src_tab_name1
#清空sqlserver表中的数据 再执行灌入数据操作
${sqoop_path}/sqoop eval \
#从HDFS每日全量插入SQL server目标表中
${sqoop_path}/sqoop export -Dmapred.job.queue.name=bdpprod \
--connect "jdbc:sqlserver://$hostname;DatabaseName=$database" \
--username $user \
--password $password \
--table $table \
--columns $columns \
--export-dir $export_dir \
--fields-terminated-by '\001' \
--input-null-string '\\N' \
--input-null-non-string '\\N' \
--num-mappers 10 \
--batch
echo "删除历史数据开始"
${sqoop_path}/sqoop eval \
--connect "jdbc:sqlserver://$hostname;DatabaseName=$database" --username $user --password $password --query "DELETE from $table where created_time<dateadd(day,0,'${current_time}');"
echo "删除历史数据结束"$current_time
fi
execute_result=$?
if [ $execute_result -ne 0 ]
then
echo "$database.dbo.$table表数据湖同步数据运行出错，请通知下游并关注跟进问题" | mail -s "OMP的$database.dbo.$table表数据湖同步数据运行出错，请通知下游并关注跟进问题" [email protected]
fi