Just want to add one point to this, there are " in the nclob column, is this causing this? How to deal with " inside.
Thanks. Best Regards, John Huang -----Original Message----- From: John Huang Sent: November 17, 2015 02:33 PM To: [email protected] Subject: RE: Oracle Import Error Thank you, Venkat. I use ojdbc7.jar. Thanks. -----Original Message----- From: Venkat Ranganathan [mailto:[email protected]] Sent: November 17, 2015 02:09 PM To: [email protected] Subject: Re: Oracle Import Error Most likely a JDBC driver issue. What version of JDBC driver are you using? Venkat On 11/17/15, 10:51 AM, "John Huang" <[email protected]> wrote: >Work with hortonworks hdp 2.2.4 > > >sqoop import -D oraoop.disabled=true -verbose --connect >jdbc:oracle:thin:@xxx.xxx.xxx.xxx:xxxx/xxxxx --username xxx --password xxxx >--table xxx --map-column-java TEXTBODY=String --map-column-hive >TEXTBODY=String -m 1 --hive-import --create-hive-table --null-string '' >--null-non-string 0 --warehouse-dir xxxx --hive-table xxxx >--fields-terminated-by \\b --optionally-enclosed-by \" >--hive-drop-import-delims > > >TEXTBODY is NCLOB > >Get error: >15/11/17 15:52:35 INFO mapreduce.Job: Task Id : >attempt_1447773191704_0004_m_000000_0, Status : FAILED >Error: java.io.IOException: SQLException in nextKeyValue > at > org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:277) > at > org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:553) > at > org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80) > at > org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91) > at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:144) > at > org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64) > at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:784) > 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:1628) > at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:158) >Caused by: java.sql.SQLException: Protocol violation: [ 14, 101, ] > at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:669) > at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249) > at oracle.jdbc.driver.T4C8TTIClob.read(T4C8TTIClob.java:245) > at oracle.jdbc.driver.T4CConnection.getChars(T4CConnection.java:3901) > at oracle.sql.CLOB.getChars(CLOB.java:517) > at oracle.sql.CLOB.getSubString(CLOB.java:354) > > > >Any idea? > >Thanks. > > > > >-----Original Message----- >From: Deepak Vohra [mailto:[email protected]] >Sent: November 17, 2015 11:19 AM >To: [email protected] >Subject: RE: Oracle Import Error Using --direct Option > >Does the --direct provide significant performance gain? >-------------------------------------------- >On Mon, 11/16/15, David Robson <[email protected]> wrote: > > Subject: RE: Oracle Import Error Using --direct Option > To: "[email protected]" <[email protected]> > Date: Monday, November 16, 2015, 2:45 PM > > Sqoop 1.4.5 and above includes the > Data Connector for Oracle and Hadoop via the "--direct" > flag. This was previously known as OraOop. > > -----Original Message----- > From: Deepak Vohra [mailto:[email protected]] > > Sent: Tuesday, 17 November 2015 8:45 AM > To: [email protected] > Cc: [email protected] > Subject: Re: Oracle Import Error Using --direct Option > > Oracle Database does not support the --direct mode. > > Database version > --direct support? connect string matches Oracle 10.2.0+ No > jdbc:oracle:*// > > https://sqoop.apache.org/docs/1.4.0-incubating/SqoopUserGuide.html#id1773570 > > > -------------------------------------------- > On Mon, 11/16/15, Ted Dong <[email protected]> > wrote: > > Subject: Oracle Import Error Using --direct Option > To: [email protected] > Date: Monday, November 16, 2015, 1:14 PM > > Hi, > I was able to use the --direct > option to import data from an Oracle database to HDFS. > The Oracle database was a development instance and the credentials I was > using had full read/write privileges. > I then switched to another Oracle > database which is a QA instance. The credentials I was given contains only > read-only access. When I run Sqoop with the --direct option I get this > error: > 15/11/16 > 13:06:09 WARN oracle.OraOopManagerFactory: Unable to determine whether the > Oracle table "IAPPS"."MF_PROFILE" is an index-organized > table.Error:ORA-00942: table or view does not > exist15/11/16 > 13:06:09 ERROR sqoop.Sqoop: Got exception running Sqoop: > java.lang.RuntimeException: > java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist > java.lang.RuntimeException: > java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist > > at > > org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:133) > at > > org.apache.sqoop.manager.oracle.OraOopConnManager.getSelectedColumnNamesInOracleTable(OraOopConnManager.java:144) > at > > org.apache.sqoop.manager.oracle.OraOopConnManager.getColTypesQuery(OraOopConnManager.java:216) > at > org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:225) > at > > org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnTypes(OraOopConnManager.java:504) > at > org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295) > at > org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1833) > at > org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645) > at > org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96) > at > org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478) > at > org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605) > at > org.apache.sqoop.Sqoop.run(Sqoop.java:143) at > org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) > at > org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:179) > at > org.apache.sqoop.Sqoop.runTool(Sqoop.java:218) > at > org.apache.sqoop.Sqoop.runTool(Sqoop.java:227) > at org.apache.sqoop.Sqoop.main(Sqoop.java:236)Caused > by: > java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist > > 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.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208) > at > > oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886) > at > > oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175) > at > > oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296) > at > > oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613) > at > > oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657) > at > > oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495) > at > > org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumns(OraOopOracleQueries.java:658) > at > > org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumns(OraOopOracleQueries.java:767) > at > > org.apache.sqoop.manager.oracle.OraOopOracleQueries.getTableColumnNames(OraOopOracleQueries.java:624) > at > > org.apache.sqoop.manager.oracle.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:124) > ... > 16 more > I confirmed that the > credentials I am using to access the QA database can see and read from the > table I am trying to import to HDFS (confirmed using toad). > Are there some other > user account settings that is required on the Oracle side to get the > --direct option working from Sqoop? > Thanks! >
