sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Arvind Prabhakar <arv...@apache.org>
Subject Re: Scoop connectivity to Oracle 8
Date Sat, 12 May 2012 17:05:46 GMT
Hi Chaitanya,

It appears that one of the methods that Sqoop relies on -
setSessionTimeZone was introduced in Oracle 9i onwards. This makes the
built in connector for Oracle unlikely to work with prior versions of the
database.

If you would like to create a connector to support Oracle 8, we will be
happy to guide you through the steps necessary to get that committed. The
first step would be to open a feature Jira and add your findings to it.

Thanks,
Arvind Prabhakar

On Tue, May 8, 2012 at 11:16 AM, Chaitanya Kocharlakota <
chaitu.sravi@gmail.com> wrote:

> Hi,
>
> I am trying to connect Sqoop (Sqoop 1.3.0-cdh3u3) to Oracle 8 using the
> jar file (classes12.jar placed in /usr/lib/sqoop/lib folder). I am not able
> to retrieve the data from the table as it gives me the error "Unable to set
> the Timezone". Here is more info:
>
> Command1: --> Could not set Timezone error
> *sqoop list-tables --connect jdbc:oracle:thin:@nn.nn.nn.nn:1521/XXXXXX
> --username xxxxxxx --password zzzzzzzzz --verbose*
> 12/05/08 12:55:49 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> 12/05/08 12:55:49 WARN tool.BaseSqoopTool: Setting your password on the
> command-line is insecure. Consider using -P instead.
> 12/05/08 12:55:49 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/08 12:55:49 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/08 12:55:49 DEBUG manager.DefaultManagerFactory: Trying with scheme:
> jdbc:oracle:thin:@nn.nn.nn.nn
> 12/05/08 12:55:49 DEBUG manager.OracleManager$ConnCache: Instantiated new
> connection cache.
> 12/05/08 12:55:49 INFO manager.SqlManager: Using default fetchSize of 1000
> 12/05/08 12:55:49 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> com.cloudera.sqoop.manager.OracleManager@70cb6009
> 12/05/08 12:55:49 DEBUG manager.OracleManager: Creating a new connection
> for jdbc:oracle:thin:@nn.nn.nn.nn:1521/XXXXXX, using username: xxxxxxx
> 12/05/08 12:55:49 DEBUG manager.OracleManager: No connection paramenters
> specified. Using regular API for making connection.
> 12/05/08 12:55:50 WARN manager.OracleManager: Time zone GMT could not be
> set on Oracle database.
> 12/05/08 12:55:50 INFO manager.OracleManager: Setting default time zone:
> GMT
> 12/05/08 12:55:50 ERROR manager.OracleManager: Could not set time zone for
> oracle connection
> java.lang.reflect.InvocationTargetException
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>         at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>         at java.lang.reflect.Method.invoke(Method.java:597)
>         at
> com.cloudera.sqoop.manager.OracleManager.setSessionTimeZone(OracleManager.java:365)
>         at
> com.cloudera.sqoop.manager.OracleManager.makeConnection(OracleManager.java:321)
>         at
> com.cloudera.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:53)
>         at
> com.cloudera.sqoop.manager.OracleManager.listTables(OracleManager.java:688)
>         at
> com.cloudera.sqoop.tool.ListTablesTool.run(ListTablesTool.java:51)
>         at com.cloudera.sqoop.Sqoop.run(Sqoop.java:146)
>         at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
>         at com.cloudera.sqoop.Sqoop.runSqoop(Sqoop.java:182)
>         at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:221)
>         at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:230)
>         at com.cloudera.sqoop.Sqoop.main(Sqoop.java:239)
> Caused by: java.sql.SQLException: ORA-01874: time zone hour must be
> between -12 and 13
>
> Command2:  --> Successfully lists tables
> *sqoop list-tables --driver oracle.jdbc.OracleDriver **--connect
> jdbc:oracle:thin:@nn.nn.nn.nn:1521/XXXXXX --username xxxxxxx --password
> zzzzzzzzz --verbose*
> 12/05/08 13:03:37 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> 12/05/08 13:03:37 WARN tool.BaseSqoopTool: Setting your password on the
> command-line is insecure. Consider using -P instead.
> 12/05/08 13:03:37 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/08 13:03:37 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/08 13:03:37 INFO manager.SqlManager: Using default fetchSize of 1000
> 12/05/08 13:03:37 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> com.cloudera.sqoop.manager.GenericJdbcManager@a39ab89
> 12/05/08 13:03:37 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
> GENERIC_LIST
> GENERIC_LIST_ITEM_50
> GENERIC_LIST_ITEM_500
> GENERIC_LIST_ITEM_500_BKP
>
> Command3:  --> When I use the above parameters in the options file and
> create a sqoop job and execute it, I get "SQL command not properly ended"
> error.
> *sqoop-job --meta-connect jdbc:hsqldb:hsql://xxxxxxxx.xxxx.com:16000/sqoop--exec
> * *zzzzzzzz* *--verbose*
> 12/05/08 13:07:02 DEBUG tool.JobTool: Enabled debug logging.
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Checking for table:
> SQOOP_ROOT
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Found table: SQOOP_ROOT
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Looking up property
> sqoop.hsqldb.job.storage.version for version null
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage:  => 0
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Looking up property
> sqoop.hsqldb.job.info.table for version 0
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage:  => SQOOP_SESSIONS
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Checking for table:
> SQOOP_SESSIONS
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Found table:
> SQOOP_SESSIONS
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Restoring job: zzzzzzzz
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Job: witt_song; Getting
> properties with class schema
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Job: witt_song; Getting
> properties with class SqoopOptions
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Job: witt_song; Getting
> properties with class config
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: System property set: 0
> 12/05/08 13:07:02 DEBUG hsqldb.HsqldbJobStorage: Stored property set: 0
> 12/05/08 13:07:02 DEBUG sqoop.ConnFactory: Loaded manager factory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/08 13:07:02 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
> com.cloudera.sqoop.manager.DefaultManagerFactory
> 12/05/08 13:07:02 INFO manager.SqlManager: Using default fetchSize of 1000
> 12/05/08 13:07:02 DEBUG sqoop.ConnFactory: Instantiated ConnManager
> com.cloudera.sqoop.manager.GenericJdbcManager@518bf072
> 12/05/08 13:07:02 INFO tool.CodeGenTool: Beginning code generation
> 12/05/08 13:07:02 DEBUG manager.SqlManager: No connection paramenters
> specified. Using regular API for making connection.
> 12/05/08 13:07:03 DEBUG manager.SqlManager: Using fetchSize for next
> query: 1000
> 12/05/08 13:07:03 INFO manager.SqlManager: Executing SQL statement: *SELECT
> t.* FROM AIRPLAY AS t WHERE 1=0*
> 12/05/08 13:07:03 ERROR manager.SqlManager: Error executing statement:
> java.sql.SQLException: ORA-00933: SQL command not properly ended
>
> java.sql.SQLException: ORA-00933: SQL command not properly ended
>         at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
>         at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
>         at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:753)
>         at
> oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:218)
>         at
> oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:812)
>         at
> oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1050)
>         at
> oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:853)
>         at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1155)
>         at
> oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3414)
>         at
> oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3459)
>         at
> com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:487)
>         at
> com.cloudera.sqoop.manager.SqlManager.execute(SqlManager.java:496)
>         at
> com.cloudera.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:195)
>         at
> com.cloudera.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:179)
>         at
> com.cloudera.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:116)
>         at
> com.cloudera.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1226)
>         at
> com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:1051)
>         at
> com.cloudera.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:84)
>         at
> com.cloudera.sqoop.tool.ImportTool.importTable(ImportTool.java:370)
>         at com.cloudera.sqoop.tool.ImportTool.run(ImportTool.java:456)
>         at com.cloudera.sqoop.tool.JobTool.execJob(JobTool.java:233)
>         at com.cloudera.sqoop.tool.JobTool.run(JobTool.java:288)
>         at com.cloudera.sqoop.Sqoop.run(Sqoop.java:146)
>         at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
>          at com.cloudera.sqoop.Sqoop.runSqoop(Sqoop.java:182)
>         at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:221)
>         at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:230)
>         at com.cloudera.sqoop.Sqoop.main(Sqoop.java:239)
>
> It looks like overriding the driver is not the right way of doing it. But
> unless, I override the driver, it is not able to go past the timezone error
> and after looking at the code, it looks like it is executing "SELECT
> CURRENT_TIMESTAMP" in Oracle which does not work in Oracle 8.
>
> Please let me know how to handle this issue.
>
> Thanks,
> Chaitanya
>

Mime
View raw message