sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tony Burton <TBur...@Sportingsolutions.com>
Subject RE: Can't import or eval from Oracle database
Date Tue, 20 Jan 2015 12:11:29 GMT
Thanks Gwen! I suspected it might be something straightforward.

I'll let you know if I hit any other snags, meanwhile my Oracle data is writing itself nicely
to S3.

Tony




-----Original Message-----
From: Gwen Shapira [mailto:gshapira@cloudera.com] 
Sent: 19 January 2015 19:20
To: user@sqoop.apache.org
Subject: Re: Can't import or eval from Oracle database

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.j
> ava:208)
>
>     at
> oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPrepared
> Statement.java:886)
>
>     at
> oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatemen
> t.java:1175)
>
>     at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatemen
> t.java:1296)
>
>     at
> oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrepa
> redStatement.java:3613)
>
>     at
> oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePrepared
> Statement.java:3657)
>
>     at
> oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OracleP
> reparedStatementWrapper.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(SqlManage
> r.java:269)
>
>     at
> org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManag
> er.java:240)
>
>     at
> org.apache.sqoop.manager.SqlManager.getColumnTypesForQuery(SqlManager.
> java:233)
>
>     at
> org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:3
> 04)
>
>     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