sqoop迁移oracle数据到TDH

从oracle导出数据为文本格式

代码
https://github.com/reader-sword/oracle-transform-TDH
驱动包位置&＃xff1a;
oracle-drives.rar
https://download.csdn.net/my

#!/bin/bash
touch /mnt/oracle_transport/sqoop.log
echo "" > /mnt/oracle_transport/sqoop.logi&＃61;0
cat table.txt |while read line
do#db&＃61;&＃96;echo $line | awk -F "." &＃39;{print $1}&＃39;&＃96;table&＃61;&＃96;echo $line &＃96;echo " -------------------------------------------begin hdfs dfs -rm ${table}------------------------------------------"#>> ./sqoop/${table}.loghdfs dfs -rm -r /tmp/data/${table}# --fields-terminated-by "指定分隔符"#在原有sqoop语句中添加 --hive-drop-import-delims 可以将如mysql中取到的\n, \r, and \01等特殊字符丢弃
# 在从mysql(或者别的RDBMS)导入数据到hdfs后会发现原来在mysql中字段值明明是NULL, 为什么到hive查询后 where field is null 会没有结果呢&＃xff0c;然后通过检查一看 居然NULL值都变成了字段串&＃39;null&＃39;。
# 其实你在导入的时候加上以下两个参数就可以解决了&＃xff0c;
# --null-string &＃39;\\N&＃39;
# --null-non-string &＃39;\\N&＃39;
#这里要注意一点。 在hive里面。NULL是用\N来表示的。echo " ---------------------------------------------begin sqoop import ${table} --------------------------------------------------"sqoop import --connect jdbc:oracle:thin:&＃64;10.20.1.140:1521:orcl --username *** --password **** --query " select * from ${table} where \$CONDITIONS" --target-dir /tmp/data/${table} -m 1 --fields-terminated-by "\001" --hive-drop-import-delims --null-string &＃39;\\N&＃39; --null-non-string &＃39;\\N&＃39;let i&＃43;&＃43;echo "${table} $i finished" >>/mnt/oracle_transport/sqoop.logecho " -------------------------------------- sqoop import finished ${table} $i--------------------------------------------"
done

创建外表

#!/bin/bash
cat table.txt |while read line
doecho "DROP TABLE IF EXISTS JIAXING_PRE_MEACH.${line}_EXT;CREATE EXTERNAL TABLE JIAXING_PRE_MEACH.${line}_EXT (ROWKEY STRING, NUM INT, COUNTRY INT, RD INT)row format delimited fields terminated by \"\\001\" location &＃39;/tmp/data/${line}&＃39;;" >/mnt/oracle_transport/create_ext/create_ext_${line}.sqlecho "-------------------------------------create_ext_${line}.sql finished ----------------------------------------------"
done

执行建外表命令并创建orc表

#!/bin/bash
i&＃61;0
rm /mnt/oracle_transport/create_ext_orc_all.log
touch /mnt/oracle_transport/create_ext_orc_all.log
cat table.txt |while read line
dobeeline -u jdbc:hive2://10.20.1.144:10000 -f /mnt/oracle_transport/create_ext/create_ext_${line}.sqlstatement&＃61;"DROP TABLE IF EXISTS JIAXING_PRE_MEACH.${line}_ORC;CREATE TABLE JIAXING_PRE_MEACH.${line}_ORC STORED AS ORC AS SELECT * FROM JIAXING_PRE_MEACH.${line}_EXT;select count(*) from JIAXING_PRE_MEACH.${line}_ORC;"beeline -u jdbc:hive2://10.20.1.144:10000 -e "$statement"let i&＃43;&＃43;echo "JIAXING_PRE_MEACH.${line}_ORC $i finished" >>/mnt/oracle_transport/create_ext_orc_all.logecho "--------------------------------JIAXING_PRE_MEACH.${line}_ORC $i finished---------------------------------------------"
done