sqoop导入oracle报错,missing in or out parameter

隔壁老登

于 2024-06-24 21:28:12 发布

阅读量163

点赞数 2

分类专栏：调优文章标签： sqoop oracle hadoop

本文链接：https://blog.csdn.net/weixin_45547818/article/details/139939099

版权

调优专栏收录该内容

14 篇文章 0 订阅

订阅专栏

报错原因: 数据导入oracle,表名字段名要一定要大写

报错如下:
2024-06-24 17:01:30,030 | ERROR | IPC Server handler 4 on default port 27102 | Task: attempt_1719212265552_0008_m_000000_0 - exited : java.io.IOException: java.sql.SQLException: Missing IN or OUT parameter at index:: 2
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:197)
	at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.close(MapTask.java:683)
	at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:806)
	at org.apache.hadoop.mapred.MapTask.run(MapTask.java:348)
	at org.apache.hadoop.mapred.YarnChild$1.run(YarnChild.java:184)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:422)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1890)
	at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:178)
Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 2
	at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1844)
	at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:10213)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.addBatch(OraclePreparedStatementWrapper.java:1362)
	at org.apache.sqoop.mapreduce.UpdateOutputFormat$UpdateRecordWriter.getPreparedStatement(UpdateOutputFormat.java:174)
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.execUpdate(AsyncSqlRecordWriter.java:153)
	at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:194)
	... 8 more
 | TaskAttemptListenerImpl.java:304


错误的抽数脚本:
/tpdata/sqoop_tool/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop export \
-Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:oracle:thin:@10.22.33.44:1521/DEV \
--username TEST \
--password sW22bb4ApNtjQvmq \
--table test.test0621 \
--columns id \
--input-fields-terminated-by '|' \
--input-lines-terminated-by '\n' \
--input-null-string '\\N' \
--input-null-non-string '\\N' \
--outdir /tpdata/data/sqoopcode \
--update-key id \
--update-mode allowinsert \
--export-dir /user/hive/warehouse/dlk_tpprty_ods_bak.db/test0621/etl_date=20200920 \
-m 1

修改后正确的抽数脚本:
/tpdata/sqoop_tool/sqoop-1.4.7.bin__hadoop-2.6.0/bin/sqoop \
export -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
--connect jdbc:oracle:thin:@10.22.33.44:1521/DEV \
--username TEST \
--password sW22bb4ApNtjQvmq \
--table TEST.TEST0621 \
--columns ID,NAME \
--input-fields-terminated-by '|' \
--input-lines-terminated-by '\n' \
--input-null-string '\\N' \
--input-null-non-string '\\N' \
--outdir /tpdata/data/sqoopcode \
--update-key ID \
--update-mode allowinsert \
--export-dir /user/hive/warehouse/dlk_tpprty_ods_bak.db/test0621/etl_date=20200920 \
-m 1