Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Sqoop export --direct option

Sqoop export --direct option

Explorer

Trying sqoop export using --direct option with --update-key to Oracle DB but getting error. Sqoop process 100 percent of records but creates new table in Oracle DB as temp table. Any idea on this?

 

6 REPLIES 6

Re: Sqoop export --direct option

Guru

Hi,

 

Are you able to provide a bit more detail on what the error is? Can you add "--verbose" so that more information can be printed out on the sqoop command output? This can help with debugging your issue.

 

Cheers

Re: Sqoop export --direct option

Explorer

sqoop export --connect "jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521/orcl" --username xxx --password xxx --table t1 --direct --update-key "a,b,c,d,e" --update-mode updateonly --export-dir '/xxxxxxx/xxxx/xxxx/output/' --verbose

Above is the sqoop export command.

 

Below is the error.

 

--------------

 

16/10/18 16:58:45 INFO mapreduce.Job: map 49% reduce 0%
16/10/18 16:58:47 INFO mapreduce.Job: map 50% reduce 0%
16/10/18 16:58:52 INFO mapreduce.Job: map 51% reduce 0%
16/10/18 16:58:53 INFO mapreduce.Job: Task Id : attempt_1476580359784_0073_m_000001_0, Status : FAILED
Error: java.io.IOException: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:211)
at org.apache.sqoop.manager.oracle.OraOopOutputFormatBase$OraOopDBRecordWriterBase.close(OraOopOutputFormatBase.java:578)
at org.apache.sqoop.manager.oracle.OraOopOutputFormatUpdate$OraOopDBRecordWriterUpdate.close(OraOopOutputFormatUpdate.java:113)
at org.apache.hadoop.mapred.MapTask$NewDirectOutputCollector.close(MapTask.java:670)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:793)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:341)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:163)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1671)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158)
Caused by: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1845)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1810)
at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:294)
at org.apache.sqoop.manager.oracle.OraOopOracleQueries.updateTable(OraOopOracleQueries.java:1282)
at org.apache.sqoop.manager.oracle.OraOopOutputFormatUpdate$OraOopDBRecordWriterUpdate.updateMainExportTableFromUniqueMapperTable(OraOopOutputFormatUpdate.java:322)
at org.apache.sqoop.manager.oracle.OraOopOutputFormatUpdate$OraOopDBRecordWriterUpdate.closeConnection(OraOopOutputFormatUpdate.java:188)
at org.apache.sqoop.mapreduce.AsyncSqlRecordWriter.close(AsyncSqlRecordWriter.java:209)
... 10 more

Re: Sqoop export --direct option

Guru

Hi,

 

I can see that you used "--verbose" in the sqoop command, is there any clue before the error message showing what kind of query ran in Oracle caused the ORA-01427 error? What is the schema for table "t1"?

Re: Sqoop export --direct option

Explorer

Thanks for your reply. It is a simple update query (constructed by Sqoop) and schema. --Direct option is throwing error and if I remove --Direct it is taking hours together to update the table. I also noticed that using --Direct Sqoop MR fails but temporary tables are created in Oracle which is equal to number of map tasks. Currently I have created staging table in Oracle to insert record on a daily basis and update the actual table. Is this the right apporach or is there anything which I'm missing?

Re: Sqoop export --direct option

Champion

As per log, it seems like this is an issue with your update logic but not with --direct

 

Status : FAILED
Error: java.io.IOException: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

Pls try with different column combination in the below step and make sure it doesn't return any duplicate 
--update-key

 

Thanks

Kumar

Re: Sqoop export --direct option

Champion

Your update key which should be a primary key of your table , by inspecting your errorl log and ora error , it is clear that the update key that you are using is returning more than one row which is a duplicate row. The reason you are getting this error is beacuse the  update key will be used in where caluse and the rest of the coloumn will be used in SET clause of the update statement. Please make sure that you are using a primary key or unique key for update key parameter . Also if you are trying to export new row using update mode it will not work. The fix for your error is to inspect your table data to see if there any duplicates row returing using ur update key coloumn and ignoring that update key .