We substitute "WHERE (1=0)" in that query because its a preliminary query intended to get column names. Since we are not interested in the data, we use a trick condition to avoid getting any data. Don't worry - we get the data later.
To fix the error, simply remove the semi-colon from your query so: "select distinct IDSBACCOUNT from SBACCOUNT where \$CONDITIONS" instead of: "select distinct IDSBACCOUNT from SBACCOUNT where \$CONDITIONS;" Gwen On Mon, Jan 19, 2015 at 10:02 AM, Tony Burton <[email protected]> wrote: > Hi, > > > > Relative Sqoop newbie here. I’ve been trying to connect to two data sources > using sqoop, one being an AWS Reshift data warehouse (which works), the > other being an Oracle database, which doesn’t. I’m importing the data from > the respective data source to AWS S3 – again this works with the > postgres-flavoured Redshift, doesn’t work with Oracle. > > > > I’m using Sqoop version 1.4.5 on Ubuntu 12.04, hadoop 1.2.1, and I’ve got > I've got ojdbc6.jar in my sqoop/lib. I’ve tried what I think are appropriate > options for –driver and –connection-manager, with no further success. If I > should be using these options, what arguments should I be using? > > > > I’ve been referring to guidelines at > http://sqoop.apache.org/docs/1.4.5/SqoopUserGuide.html and can’t see any > obvious hints. I’m also aware of the ORA-00933 error, which I have also come > across, but can’t replicate at the moment. This error was independent of any > combination of –driver and –connection-manager arguments. > > > > Also, can someone tell me why my ‘where \$CONDITIONS’ gets substituted as > ‘where (1=0)’ – this doesn’t seem to make any sense. > > > > Thanks in advance for any suggestions – sqoop command and exception are > shown below. An equivalent sqoop eval will fail with a similar message. > > > > Tony > > > > > > > > sqoop import –connect jdbc:oracle:thin:@//<hostname>:<port>/<database> > > --query "select distinct IDSBACCOUNT from SBACCOUNT where \$CONDITIONS;" > > --target-dir s3n://<aws access key>:<aws secret key>@path/to/storage/ > > --num-mappers 1 --username <username> --password <password> > > > > > > > > 15/01/19 17:47:38 INFO sqoop.Sqoop: Running Sqoop version: 1.4.5 > > 15/01/19 17:47:38 WARN tool.BaseSqoopTool: Setting your password on the > command-line is insecure. Consider using -P instead. > > 15/01/19 17:47:38 INFO oracle.OraOopManagerFactory: Data Connector for > Oracle and Hadoop is disabled. > > 15/01/19 17:47:38 INFO manager.SqlManager: Using default fetchSize of 1000 > > 15/01/19 17:47:38 INFO tool.CodeGenTool: Beginning code generation > > 15/01/19 17:47:39 INFO manager.OracleManager: Time zone has been set to GMT > > 15/01/19 17:47:39 INFO manager.SqlManager: Executing SQL statement: > > select distinct IDSBACCOUNT from SBACCOUNT where (1 = 0) ; > > 15/01/19 17:47:39 ERROR manager.SqlManager: Error executing statement: > > java.sql.SQLSyntaxErrorException: ORA-00911: invalid character > > > > java.sql.SQLSyntaxErrorException: ORA-00911: invalid character > > > > 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.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.getColumnTypesForQuery(SqlManager.java:233) > > at > org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:304) > > at > org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1773) > > at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1578) > > 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:601) > > at org.apache.sqoop.Sqoop.run(Sqoop.java:143) > > at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65) > > 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) > > 15/01/19 17:47:39 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:1584) > > 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:601) > > at org.apache.sqoop.Sqoop.run(Sqoop.java:143) > > at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65) > > 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) > >
