sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gwen Shapira <gshap...@cloudera.com>
Subject Re: Can't import or eval from Oracle database
Date Mon, 19 Jan 2015 19:19:33 GMT
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
<TBurton@sportingsolutions.com> 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)
>
>

Mime
View raw message