sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chaitanya Kocharlakota <chaitu.sr...@gmail.com>
Subject Scoop connectivity to Oracle 8
Date Tue, 08 May 2012 18:16:08 GMT
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