Sqoop Error:java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

[#1]

I am trying to import the data from oracle database.

CDH5.2,java 1.7,Oracle jar file in /var/lib/sqoop/ojdbc7.jar

The command is :-

sqoop import --connect "jdbc:oracle:thin:@host:1684:servicename" --driver "oracle.jdbc.driver.OracleDriver" --username "xxxx" --password "xxxx" --table "testdta.F41021" --hive-import

error i am getting is :-

Warning: /apps/opt/cloudera/parcels/CDH-5.2.0-1.cdh5.2.0.p0.36/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
14/12/03 16:52:06 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5-cdh5.2.0
14/12/03 16:52:06 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
14/12/03 16:52:06 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
14/12/03 16:52:06 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
14/12/03 16:52:06 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
14/12/03 16:52:06 INFO manager.SqlManager: Using default fetchSize of 1000
14/12/03 16:52:06 INFO tool.CodeGenTool: Beginning code generation
14/12/03 16:52:12 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM testdta.F41021 AS t WHERE 1=0
14/12/03 16:52:12 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:750)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:759)
at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:269)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:240)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:226)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1829)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1641)
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)
14/12/03 16:52:12 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1647)
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)

[#2]

You shouldn't need to provide '--driver'. Try removing it from your sqoop command.

[#3]

Yes it worked but gave an error related to table name not correct.

Also i changed the driver from ojdbc 6 to ojdbc 7 because java version is 1.7 ,with 1.6 when i removed driver it gave null pointer exception.

Thanks,That was easy. 🙂

[#4]

Glad it worked. Does that mean your job is running now?

[#5]

Yes 🙂

[#6]

Hi

I am targeting ojdbc7.jar.  I have both ojdbc6 and ojdbc7 in my /opt/cloudera/parcels/CDH/lib/sqoop/lib/

I ran

sqoop import --libjars ...../sqoop/lib/ojdbc7.jar --driver "oracle.jdbc.driver.OracleDriver" --connect "jdbc:oracle:thin:@(description=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXX.XXXX.com)(PORT=XXXX))(CONNECT_DATA=(SERVER=DEDICATED)(service_name=XXXXX.XXXXX.com)))" --username XXXXXX --password XXXXXX --query 'select * from XXXXXXX where rownum < 100 and $CONDITIONS' --fields-terminated-by  "\001" --target-dir /XXX/XXXX/XXXXX/ --num-mappers 1 --verbose --delete-target-dir

The job worked but I can't determine if it used driver ojdbc7 or odbc6 as i see both in log

 DEBUG mapreduce.JobBase: Adding to job classpath: file:.....

..../sqoop/lib/ojdbc6.jar

 DEBUG mapreduce.JobBase: Adding to job classpath: file:.....

..../sqoop/lib/ojdbc7.jar

is there a way to target only to use the ojdbc7.jar and not any other driver? trying to test this new ojdbc7 and I am not sure if it is still using old 6? also I am reaing compatability issues with 9 and the ojdbc7, should I remove ojdbc6 all together and replace with ojdbc6 or keep both? If I keep both how do I target one driver vs. the other