dolphinscheduler实现(oracle-hdfs-doris)数据ETL

完整脚本(自行替换相关变量)

user_ol=`sh conf/getInfo.sh 'Oracle' 'user' conf/databases.conf`
password_ol=`sh conf/getInfo.sh 'Oracle' 'password' conf/databases.conf`
dblink_ol=`sh conf/getInfo.sh 'Oracle' 'dblink' conf/databases.conf`
current_date=$(date +"%Y%m%d%H%M%S")
table_name=""
hdfs_path="/user/yarn/"${table_name}${current_date}echo "参数成功"${current_date}
#where $CONDITIONS 必须有
sql='
where $CONDITIONS
'
echo "开始执行sqoop"sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect ${dblink_ol} \
--username ${user_ol} \
--password ${password_ol} \
--query "${sql}" \
--target-dir ${hdfs_path} \
--delete-target-dir \
--split-by card_no \
-m 2if [ $? -ne 0 ]; thenecho "导出到hdfs失败----------------------"exit -1 
fiecho "开始导入到doris"
#broker_name
user=`sh conf/getInfo.sh 'Doris-Pro' 'user' conf/databases.conf`
password=`sh conf/getInfo.sh 'Doris-Pro' 'password' conf/databases.conf`
dblink=`sh conf/getInfo.sh 'Doris-Pro' 'dblink' conf/databases.conf`
target_table_name="ods_cme_"${table_name}
echo "加载路径:"${hdfs_path}
lable_name=${target_table_name}"_"${current_date}#nameservice1 分布式hdfs集群名称,单机选IP形式
sql="
LOAD LABEL ${lable_name}
(DATA INFILE('hdfs://nameservice1${hdfs_path}/part*')INTO TABLE ${target_table_name}COLUMNS TERMINATED BY ','            
) 
with BROKER worker3
PROPERTIES
('timeout'='1200','max_filter_ratio'='0.1'
)
"
mysql -h fe_ip -P9030 -u${user} -p${password} -D offline -e "truncate table ${target_table_name}"
echo "清空表成功"
mysql -h fe_id -P9030 -u${user} -p${password} -D offline -e "${sql}"if [ $? -ne 0 ]; thenecho "创建broker任务失败--------------------"exit -1  # 可以选择退出脚本或执行其他操作
fi# broker 任务检查
sh conf/broker_state.sh ${label} ${user} ${password}if [ $? -ne 0 ]; thenecho "任务失败--------------------"exit -1  # 可以选择退出脚本或执行其他操作
fi

配置文件conf

# 文本输出为base64的形式
[Doris]
user_name=_=用户名
password=_=密码
dblink=_=jdbcurl

配置文件解析脚本

#!/bin/bash
param=`awk -F '=_=' '/\['"$1"'\]/{a=1}a==1&&$1~/'"$2"'/{gsub(/[[:blank:]]*/,"",$2);printf("%s\t",$2);exit;}' $3`
echo $(echo -n ${param} | base64 --decode)

转base64脚本

#!/bin/bash
# 要编码的文本
text=$1
# 编码文本
encoded_text=$(echo -n "${text}" | base64 -w 0)
# 输出编码结果
echo "编码结果:${encoded_text}"
# 输出编码欠的结果
echo "解码结果:"$(echo -n ${encoded_text} | base64 --decode)