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. Want to know more about what has changed? Check out the Community News blog.

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

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

Expert Contributor

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)

5 REPLIES 5

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

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

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

Expert Contributor

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. :)

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

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

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

Expert Contributor

Yes :)

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

New Contributor

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