sqoop（二）用shell脚本导mysql数据到hive中

小鱼儿 2024-04-19 10:11 60阅读 0赞

首先说一下遇到的问题  
1.**shell脚本执行错误 $’\\r’:command not found**  
[https://blog.csdn.net/lxxc11/article/details/79386005][https_blog.csdn.net_lxxc11_article_details_79386005]

2 .**syntax error near unexpected token \`$’do\\r”**  
[https://blog.csdn.net/xiaobing\_122613/article/details/85032333][https_blog.csdn.net_xiaobing_122613_article_details_85032333]  
3. **"java.lang.IllegalArgumentException: HOUR\_OF\_DAY: 2 -> 3"**  
jdbc加serverTimezone=Asia/Shanghai

主要参考这篇 [https://blog.csdn.net/yesha\_baci/article/details/88918603][https_blog.csdn.net_yesha_baci_article_details_88918603] 加了点日志  
新建import.sh 文件，如下文，sh import.sh 执行就可以了，注意下面路径的修改

#!/bin/bash
    #sqoop的路径
    export sqoop_path=/opt/sqoop/sqoop147/bin
    #hive的路径
    export hive_path=/opt/hive_data/apache-hive-2.3.5-bin/bin
    #自定义的日志输出文件
    export sqoop_logs=/opt/sqoop/sqoop147/log/sqoop.log  
    #如果上面两个路径不存在的时候报错
    if [ !  -e ${sqoop_path} ] || [ ! -e ${hive_path} ]
    then
       echo "---- path not right ----  !" >> $sqoop_logs
        exit
    else
       echo "import start $(date +%F%n%T)!" >> $sqoop_logs
    fi
    #
    #要导入的表 空格分开
    array_default=(aaa bbb ccc)
    #
    #连接配置  
    oracle_driver=jdbc:mysql://1.1.11.1111:3306/database?serverTimezone=Asia/Shanghai
    username=username
    password=password
    #
    #在hive查如果有这张表了就删掉
    for tables in ${array_default[@]}
    do
    ${hive_path}/hive -e  "use database;drop table if exists ${tables}"; 
    ${sqoop_path}/sqoop import --connect ${oracle_driver}   --username ${username}  --password ${password} --table ${tables}   \
    --fields-terminated-by "\t" --lines-terminated-by "\n"   --hive-drop-import-delims  --hive-import --hive-overwrite     \
    --hive-database database--create-hive-table --hive-table  ${tables}  --null-string '\\N' --null-non-string '\\N'  --delete-target-dir  -m 3;  
    #判断sqoop执行结果,失败退出 也可以不退出 继续执行下一张表 把exit去掉
    if [ $? -ne 0 ]; then  
    	echo "error----${tables} import error--exit---$(date +%F%n%T)!" >> $sqoop_logs
    	exit
    else
    	echo "${tables} import Successfully $(date +%F%n%T)!" >> $sqoop_logs
    fi
    done
    #

[https_blog.csdn.net_lxxc11_article_details_79386005]: https://blog.csdn.net/lxxc11/article/details/79386005
[https_blog.csdn.net_xiaobing_122613_article_details_85032333]: https://blog.csdn.net/xiaobing_122613/article/details/85032333
[https_blog.csdn.net_yesha_baci_article_details_88918603]: https://blog.csdn.net/yesha_baci/article/details/88918603