达梦数据库linux装载数据文件和控制文件模板

#!/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

)