#!/bin/bash
set -euo pipefail # 严格模式:错误退出、未定义变量报错
# 配置参数
dm_path="/opt/DM8/bin" # 达梦安装路径
user="user" # 数据库用户
password="tutu@455" # 密码(建议从环境变量读取)
ip="10.1.1.1:5236"
datadate="${1}"
ctltmplpath="/app/etlfile/ctltemplate"
ctlpath="/app/etlfile/ctlfile"
datapath="/app/loaddata"
logfile="/app/etlfile/logs/load_${datadate}.log" # 按日期区分日志
badfile="/app/etlfile/error/error_${datadate}.log"
# 检查参数
if [ $# -ne 1 ]; then
echo "Usage: $0 <日期格式: 20250505>"
exit 1
fi
# 创建目录(如果不存在)
mkdir -p "${ctlpath}" "${datapath}" "/app/etlfile/logs" "/app/etlfile/error"
# 初始化日志文件
: > "${logfile}" # 清空日志
: > "${badfile}"
#删除 表名列表
deltablenames=(
"branch_code"
"loan_busi"
)
for delname in "${deltablenames[@]}"; do
echo "[INFO] 开始清空表: ${delname}" | tee -a "${logfile}"
# 清空表
if ! echo "TRUNCATE TABLE ${delname};" | "${dm_path}/disql" "${user}/\"${password}\"@${ip}" >> "${logfile}" 2>&1; then
echo "[ERROR] 清空表 ${delname} 失败!" | tee -a "${logfile}"
exit 1
fi
done
tablenames=(
"file_name1"
"file_name2"
"file_name3"
)
rm -rf $ctlpath/*.ctl
# 数据加载
for tname in "${tablenames[@]}"; do
echo "[INFO] 开始处理表: ${tname}" | tee -a "${logfile}"
# 生成控制文件`
ctl_template="${ctltmplpath}/${tname}.ctl"
ctl_output="${ctlpath}/${tname}_${datadate}.ctl"
if ! sed "s/\${v_date}/${datadate}/g" "${ctl_template}" > "${ctl_output}"; then
echo "[ERROR] 生成控制文件 ${ctl_output} 失败!" | tee -a "${logfile}"
exit 1
fi
# 数据加载
if ! ${dm_path}/dmfldr USERID=${user}/\"${password}\"@${ip} CONTROL=\'${ctl_output}\' LOG=\'${logfile}\' BADFILE=\'${badfile}\' ; then
echo "[ERROR] 加载表 ${tname} 失败!" | tee -a "${logfile}"
exit 1
fi
#判断文件实际条数与ok文件条数是否一致
fileokcount=`cat ${datapath}/${datadate}/t_kjzz${tname:4}_${datadate}.del.ok |awk -F ' ' '{print $1}'`
#filedelcount=`cat ${data_path}/${data_date}/${tablename}_${data_date}.del | wc -l`
#echo "$fileokcount"
#echo "$filedelcount"
count=$($dm_path/disql $user/\"$password\"@$ip -e "SELECT COUNT(*) FROM $tname;"| awk -F'|' '/^[0-9]+/{print $1}' | tr -d ' ')
#echo "记录条数:$count"
if [ $? -ne 0 ]; then
echo "Database query failed! Error:"
# cat "$tmpfile"
exit 1
fi
# -------------------- 数据比对 --------------------
if [ "$fileokcount" -eq "$count" ]; then
echo "${tname}数据一致,条数${count}"
else
echo "${tname}数据不一致,数据库${count}条,ok文件${fileokcount}条"
exit 1 # 直接退出,无需额外变量
fi
done
echo "[SUCCESS] 数据加载与校验全部完成!"
==============控制文件模板,字符集可以不设置,或者修改,参看官方文档。少列时大部分是字符集问题。
OPTIONS(
character_code='utf-8'
skip =0
)
LOAD DATA INFILE '/app/loaddata/${v_date}/org_code_${v_date}.del'
BADFILE '/app/etlfile/error/load.bad'
INTO TABLE org_code
FIELDS TERMINATED BY '|$|'
(DATA_DATE
,ORG_CODE
)