实战：shell调用datax从HDFS导出到关系数据库（如MySQL、Oracle、PostgreSQL）

jzy3711
173 阅读2分钟

shell调用datax从HDFS导出到关系数据库（如MySQL、Oracle、PostgreSQL）

脚本非常详细地展示了如何将数据从HDFS导出到关系数据库（如MySQL、Oracle、PostgreSQL）中。

操作步骤

定义参数和变量

bash
sql=$1                  # 导出数据的SQL语句
s_tablename=$2          # 源表名
ds_name=$3              # 目标数据库名称
t_tablename=$4          # 目标表名
temptable="h2o_"`date +%s%N | md5sum | head -c 16`  # 生成一个基于时间戳的临时表名
filename=${s_tablename}_${temptable}  # 文件名
path="hdfs://prdhdfs/tmp/hdfs_to_rdb/$filename/"   # HDFS路径
local_path="/data02/dcadmin/scripts/dataos_scripts/data_exp"  # 本地脚本路径
flag=$5 # 标志，用来确定是否TRUNCATE表

2. 构造SQL查询并提交给Hive

bash
sql1=`echo "$sql"|cut -d ";" -f2`  # 截取分号后的部分
sql0=`echo "$sql"|cut -d ";" -f1`  # 截取分号前的部分
sql0="$sql0;insert overwrite directory '${path}' stored as ORC $sql1"  # 构建最终的SQL
echo "$sql0"

通过上面的脚本片段，我们构造了一个Hive SQL，它会将查询结果写入到指定的HDFS路径。

kinit -kt /data02/dcadmin/keytab_shengchan/dcadmin.keytab dcadmin@SC.COM  # 鉴权
beeline -u "jdbc:hive2://prdnn1.yxbdprd.sc.ctc.com:2181, ... ,prddb1.yxbdprd.sc.ctc.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2" -e "set tez.queue.name=offline;$sql0 distribute by rand()"

3. 获取目标数据库连接信息

从PostgreSQL数据库中获取目标数据库的连接信息，并解析结果为变量。

re=$(PGPASSWORD=... psql -h 10.251.110.104 -p 18921 -U dacp -d dacp  -t <<EOF
SELECT CASE WHEN ds_type = 'mysql'  THEN ...
            WHEN ds_type = 'oracle' THEN ...
            WHEN ds_type = 'pg'     THEN ... END jdbc_url
      ,ds_acct
      ,ds_auth
      ,CASE WHEN ds_type = 'mysql'  THEN 'mysqlwriter' 
            WHEN ds_type = 'oracle' THEN 'oraclewriter' 
            WHEN ds_type = 'pg'     THEN 'postgresqlwriter' END ds_type
FROM dacp_dev.dacp_meta_datasource
WHERE ds_type IN ('mysql', 'oracle', 'pg')
  AND upper(trim(ds_name)) = upper(trim('$ds_name'))
EOF
)

通过eval $(echo $re | awk '...')解析查询结果并将其分配给变量。

4. 获取目标数据库密码

通过执行Java程序解密数据库密码：

pw=`java -Dpwd=${ds_auth} -jar $local_path/AesCipher-1.0.jar`

5. 预处理SQL语句

根据标志变量flag，确定是否执行TRUNCATE语句：

preSQL="select * from $t_tablename where 1=-1"
if [ "$flag" = "T" ];then
 preSQL="truncate table $t_tablename"
fi

6. 数据导出并导入目标数据库

使用datax执行从HDFS导入到关系数据库：

python $local_path/datax/bin/datax.py -p "-Dpath=$path -Dwriter=$ds_type -Drdb_user=$ds_acct -Drdb_pass="$pw" -Drdb_jdbc="$jdbc_url" -Drdb_table=$t_tablename -DpreSql="$preSQL"" $local_path/hdfs_to_rdb.json

完整脚本

#!/bin/bash
sql=$1                  #导出数据sql
s_tablename=$2  #源表
ds_name=$3              #目标库
t_tablename=$4  #目标表
temptable="h2o_"`date +%s%N | md5sum | head -c 16`      #构造一个时间戳
filename=${s_tablename}_${temptable}    #文件名
path="hdfs://prdhdfs/tmp/hdfs_to_rdb/$filename/"
local_path="/data02/dcadmin/scripts/dataos_scripts/data_exp"
flag=$5 #t TRUNCATE 
#hadoop fs -mkdir $path
# 参数sql0为 待执行SQL
echo "$sql"
sql1=`echo "$sql"|cut -d ";" -f2`
sql0=`echo "$sql"|cut -d ";" -f1`
sql0="$sql0;insert overwrite directory '${path}' stored as ORC $sql1"
echo "$sql0"
# 向Hive提交HQL
# 向Hive提交HQL
kinit -kt /data02/dcadmin/keytab_shengchan/dcadmin.keytab dcadmin@SC.COM
#beeline <<EOF
#!connect jdbc:hive2://devdataosambari:2181,devdataosnn1:2181,devdataosnn2:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2


#$sql0
#;
#EOF
beeline -u "jdbc:hive2://prdnn1.yxbdprd.sc.ctc.com:2181,prdnn2.yxbdprd.sc.ctc.com:2181,prdrm1.yxbdprd.sc.ctc.com:2181,prddb2.yxbdprd.sc.ctc.com:2181,prddb1.yxbdprd.sc.ctc.com:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2" -e "set tez.queue.name=offline;$sql0 distribute by rand()"
# 获取目标数据源地址
#eval $(mysql -h 10.251.88.71 -udacp -pdacp123456 dacp_dev -e "select case when ds_type = 'mysql' then concat('jdbc:mysql://', ds_inst_loc, '/',json_unquote(json_extract(ds_conf,'$.physicalDbName')),'?characterEncoding=UTF-8')
#when ds_type = 'oracle' then concat('jdbc:oracle:thin:@', ds_inst_loc, '/',json_unquote(json_extract(ds_conf,'$.physicalDbName')))
#when ds_type = 'pg' then concat('jdbc:postgresql://', ds_inst_loc, '/',json_unquote(json_extract(ds_conf,'$.physicalDbName'))) end jdbc_url,
#ds_acct, ds_auth, case when ds_type = 'mysql' then 'mysqlwriter' when ds_type = 'oracle' then 'oraclewriter' when ds_type = 'pg' then 'postgresqlwriter' end ds_type
#from dacp_meta_datasource 
#where ds_type in ('mysql','oracle','pg') 
#and ds_name = '$ds_name'" | awk 'NR== 2 {printf("jdbc_url=%s; ds_acct=%s; ds_auth=%s; ds_type=%s",$1,$2,$3,$4)}')

re=$(PGPASSWORD=jxFgCKv9GJw2ohS3 psql -h 10.251.110.104 -p 18921 -U dacp -d dacp  -t <<EOF
SELECT CASE WHEN ds_type = 'mysql'  THEN CONCAT ('jdbc:mysql://'     ,ds_inst_loc,'/',(ds_conf::json ->>'physicalDbName'),'?characterEncoding=UTF-8') 
            WHEN ds_type = 'oracle' THEN ds_conf::json ->> 'url' 
            WHEN ds_type = 'pg'     THEN CONCAT ('jdbc:postgresql://',ds_inst_loc,'/',(ds_conf::json ->>'physicalDbName')) END jdbc_url
      ,ds_acct
      ,ds_auth
      ,CASE WHEN ds_type = 'mysql'  THEN 'mysqlwriter' 
            WHEN ds_type = 'oracle' THEN 'oraclewriter' 
            WHEN ds_type = 'pg'     THEN 'postgresqlwriter' END ds_type
FROM dacp_dev.dacp_meta_datasource
WHERE ds_type IN ('mysql', 'oracle', 'pg')
  AND upper(trim(ds_name)) = upper(trim('$ds_name'))
EOF
)
eval $(echo $re| awk '{printf("jdbc_url=%s; ds_acct=%s; ds_auth=%s; ds_type=%s",$1,$3,$5,$7)}')

#eval $(java -jar /data01/etl/scripts/exec_aes.jar $ds_auth | awk ' {printf("pw=%s;",$1)}')
#pw=`java -jar $local_path/exec_aes.jar $ds_auth`
pw=`java -Dpwd=${ds_auth} -jar $local_path/AesCipher-1.0.jar`
preSQL="select * from $t_tablename where 1=-1"
if [ "$flag" = "T" ];then
 preSQL="truncate table $t_tablename"
fi
echo "preSQL=$preSQL"

python $local_path/datax/bin/datax.py -p "-Dpath=$path -Dwriter=$ds_type -Drdb_user=$ds_acct -Drdb_pass=\"$pw\" -Drdb_jdbc=\"$jdbc_url\" -Drdb_table=$t_tablename -DpreSql=\"$preSQL\"" $local_path/hdfs_to_rdb.json

总结

整个脚本实现的主要功能是：

  1. 通过传入的SQL语句，从Hive中将数据导出到HDFS。
  2. 获取目标数据库连接信息。
  3. 预处理目标数据库表（如TRUNCATE操作）。
  4. 使用DataX工具，将数据从HDFS导入到目标关系数据库中。

这个过程确保了数据的完整性和准确性，适用于大数据处理和数据仓库的迁移与同步操作。希望这段解释能帮助你更好地理解脚本的执行过程！

avatar
文章
阅读
粉丝