sqoop1实战 常见问题

🧙 Questions

☄️ Ideas

sqoop oracle导入hive中,数据不一致

--fields-terminated-by ',' 
--lines-terminated-by '\n'
--hive-drop-import-delims

Could not load db driver class: oracle.jdbc.OracleDriver

21/08/27 11:16:09 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: oracle.jdbc.OracleDriver
java.lang.RuntimeException: Could not load db driver class: oracle.jdbc.OracleDriver
at org.apache.sqoop.manager.OracleManager.makeConnection(OracleManager.java:289)
at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:59)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:762)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:785)
at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:288)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:259)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.java:252)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:342)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1879)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1672)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:106)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:515)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:633)
at org.apache.sqoop.Sqoop.run(Sqoop.java:146)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:182)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:233)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:242)
at org.apache.sqoop.Sqoop.main(Sqoop.java:251)

解决方案

cp ojdbc8-19.7.0.0.jar /data/cdh/cloudera/parcels/CDH/lib/sqoop/lib/
sudo chmod 777 /data/cdh/cloudera/parcels/CDH/lib/sqoop/lib/ojdbc8-19.7.0.0.jar

🔗 Links

• sqoop website

• org.apache.commons.lang.StringUtils
• java.lang.ClassNotFoundException: ${tableName}
• sqoop oracle缺少driver

☄️ Ideas

org.apache.commons.lang.StringUtils

Caused by: java.lang.ClassNotFoundException: org.apache.commons.lang.StringUtils
        at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
        ... 20 more

解决方案

缺少commons-lang-2的包

wget https://mirrors.tuna.tsinghua.edu.cn/apache/commons/lang/binaries/commons-lang-2.6-bin.tar.gz
tar -zvxf commons-lang-2.6-bin.tar.gz
mv commons-lang-2.6/commons-lang-2.6.jar sqoop/lib

java.lang.ClassNotFoundException: ${tableName}

Caused by: java.lang.ClassNotFoundException: table_demo
        at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:355)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:348)
        at org.apache.sqoop.mapreduce.TextExportMapper.setup(TextExportMapper.java:70)
        ... 10 more

解决方案

因为存在sqoop没有找到自动生成的对象文件 ${tableName}.jar 文件没有在 /data/sqoop/sqoop1/lib/路径
提前生成文件

sqoop codegen \
    --hadoop-mapred-home /data/hadoop-3.3.0/share/hadoop/mapreduce \
    --connect jdbc:mysql://isxcode:30002/data_assets \
    --username ispong_data \
    --password define2021 \
    --driver com.mysql.cj.jdbc.Driver \
    --table ispong_demo \
    --outdir /data/sqoop/sqoop1/lib/compile	\
    --bindir /data/sqoop/sqoop1/lib/

sqoop codegen \
    --connect jdbc:mysql://isxcode:30002/data_assets \
    --username ispong_data \
    --password define2021 \
    --driver com.mysql.cj.jdbc.Driver \
    --table ispong_demo \
    --outdir /data/sqoop/sqoop1/lib/compile	\
    --bindir /data/sqoop/sqoop1/lib/ 
&&
sqoop import \
    -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
    --connect jdbc:mysql://isxcode:30002/data_assets \
    --username ispong_data \
    --password define2021 \
    --table ispong_demo \
    --driver com.mysql.cj.jdbc.Driver \
    --target-dir /data/hive-data/warehouse/ispong_demo \
    --split-by id 

#  自定义sql同步

sqoop import \
    --driver com.mysql.cj.jdbc.Driver \
    --connect jdbc:mysql://isxcode:30002/data_assets \
    --username ispong_data \
    --password define2021 \
    --target-dir /data/hive-data/warehouse/ispong_demo \
    --query 'select id,username,age from ispong_demo where $CONDITIONS' \
    --split-by id \
    --outdir /data/sqoop/sqoop1/lib/ \
    --bindir /data/sqoop/sqoop1/lib/ \
|| \
sqoop import \
    --connect jdbc:mysql://isxcode:30002/data_assets \
    --query 'select id,username,age from ispong_demo where $CONDITIONS' \
    --username ispong_data \
    --password define2021 \
    --driver com.mysql.cj.jdbc.Driver \
    --target-dir /data/hive-data/warehouse/ispong_demo \
    --delete-target-dir \
    --split-by id \
&& \
rm -rf /data/sqoop/sqoop1/lib/QueryResult*

###

21/06/07 16:30:04 ERROR tool.ExportTool: Error during export: 
Mixed update/insert is not supported against the target database yet
        at org.apache.sqoop.manager.ConnManager.upsertTable(ConnManager.java:690)
        at org.apache.sqoop.tool.ExportTool.exportTable(ExportTool.java:86)
        at org.apache.sqoop.tool.ExportTool.run(ExportTool.java:112)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:146)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:182)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:233)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:242)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:251)

解决方案

sqoop 命令中取消driver字段

sqoop oracle缺少driver

解决方案

/opt/cloudera/parcels/CDH/lib/sqoop/lib/ojdbc6.jar
# 并且修改权限为777
sudo chmod -R 777 /opt/cloudera/parcels/CDH/lib/sqoop/lib/ojdbc6.jar

🔗 Links

• sqoop website