Hi Peter, thanks for your reply,
In the following line I will refer to the command : " sqoop import --
connect jdbc:oracle:thin:@//DBServer:1521/DBname --username myuser --
target-dir import_dir --split-by ID " AS "sqoop import ..."
I retried with the suggested modification and I got :
"ERROR tool.ImportTool: Encountered IOException running import job:
java.io.IOException: Query [select * from TABLE_NAME] must contain
'$CONDITIONS' in WHERE clause.
at com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:913)
I then tried both:
> sqoop import ... --query 'select * from TABLE_NAME where $CONDITIONS' --where 'ID < 1000' --verbose -P
and sqoop import ... --query 'select * from TABLE_NAME where
$CONDITIONS' --where 'ID < 1000 and $CONDITIONS'
And I still got :
"Encountered IOException running import job: java.io.IOException:
ORA-00933: SQL command not properly ended
at
com.cloudera.sqoop.mapreduce.db.DataDrivenDBInputFormat.getSplits(DataDrivenDBInputFormat.java:
201) "
I changed to :
> sqoop import ... --table TABLE_NAME --where --where 'ID < 1000' --verbose -P
This time the job fails with error :
11/05/10 11:26:56 INFO mapred.JobClient: map 0% reduce 0%
11/05/10 11:27:11 INFO mapred.JobClient: Task Id :
attempt_201104261126_0045_m_000002_0, Status : FAILED
java.lang.NullPointerException
at
com.cloudera.sqoop.mapreduce.db.DataDrivenDBRecordReader.getSelectQuery(DataDrivenDBRecordReader.java:
87)
at
com.cloudera.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:
225)
at org.apache.hadoop.mapred.MapTask
$NewTrackingRecordReader.nextKeyValue(MapTask.java:455)
at
org.apache.hadoop.mapreduce.MapContext.nextKeyValue(MapContext.java:
67)
at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:143)
at
com.cloudera.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:
187)
at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:646)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:322)
at org.apache.hadoop.mapred.Child$4.run(Child.java:268)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at
org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:
1115)
at org.apache.hadoop.mapred.Child.main(Child.java:262)
attempt_201104261126_0045_m_000002_0: log4j:WARN No appenders could be
found for logger (org.apache.hadoop.hdfs.DFSClient).
attempt_201104261126_0045_m_000002_0: log4j:WARN Please initialize the
log4j system properly.
11/05/10 11:27:12 INFO mapred.JobClient: Task Id :
attempt_201104261126_0045_m_000001_0, Status : FAILED
Even the full import below fails with the same error :
> sqoop import ... --table TABLE_NAME --verbose -P
Now trying with Oraopp :
> sqoop import -D oraoop.logging.level=debug ...--table TABLE_NAME --where 'ID < 1000'
gives me the error :
11/05/10 11:33:10 DEBUG tool.BaseSqoopTool: Enabled debug logging.
Enter password:
11/05/10 11:33:14 DEBUG util.ClassLoaderStack: Checking for existing
class: com.quest.oraoop.OraOopManagerFactory
11/05/10 11:33:14 DEBUG util.ClassLoaderStack: Class is already
available. Skipping jar /Tools/Sqoop/lib/oraoop-1.2.0.62.jar
11/05/10 11:33:14 DEBUG sqoop.ConnFactory: Added factory
com.quest.oraoop.OraOopManagerFactory in jar /Tools/Sqoop/lib/
oraoop-1.2.0.62.jar specified by /Tools/sqoop-1.2.0-cdh3u0/bin/../conf/
managers.d/oraoop
11/05/10 11:33:14 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.quest.oraoop.OraOopManagerFactory
11/05/10 11:33:14 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
11/05/10 11:33:14 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.quest.oraoop.OraOopManagerFactory
11/05/10 11:33:14 DEBUG oraoop.OraOopUtilities: Enabled OraOop debug
logging.
11/05/10 11:33:14 DEBUG oraoop.OraOopManagerFactory: OraOop can be
called by Sqoop!
11/05/10 11:33:15 DEBUG oraoop.OraOopUtilities: The Oracle table
context has been derived from:
oracleConnectionUserName = MYUSER
tableStr = TABLE_NAME
as:
owner : MYUSER
table : TABLE_NAME
11/05/10 11:33:15 WARN oraoop.OraOopManagerFactory: Unable to
determine the Oracle-type of the object named TABLE_NAME owned by
TABLE_OWNER.
Error:
ORA-00942: table or view does not exist
11/05/10 11:33:15 WARN oraoop.OraOopManagerFactory: Unable to
determine whether the Oracle table TABLE_NAME.TABLE_OWNER is an index-
organized table.
Error:
ORA-00942: table or view does not exist
11/05/10 11:33:15 INFO oraoop.OraOopManagerFactory:
*******************************************
*** Using OraOop 1.2.0.62 ***
*** Copyright 2011 Quest Software, Inc. ***
*** ALL RIGHTS RESERVED. ***
*******************************************
11/05/10 11:33:15 INFO oraoop.OraOopManagerFactory: Oracle Database
version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -
64bi
11/05/10 11:33:15 INFO oraoop.OraOopManagerFactory: This Oracle
database is not a RAC.
11/05/10 11:33:15 WARN oraoop.OraOopManagerFactory: Unable to parse
the JDBC connection URL "jdbc:oracle:thin:@//ncepspdb01:1521/
funcsysFQSFUNC1.nce.amadeus.net" as a connection that uses the Oracle
'thin' JDBC driver.
This problem prevents OraOop from being able to dynamically generate
JDBC URLs that specify 'dedicated server connections' or spread mapper
sessions across multiple Oracle instances.
If the JDBC driver-type is 'OCI' (instead of 'thin'), then load-
balancing should be appropriately managed automatically.
11/05/10 11:33:15 DEBUG sqoop.ConnFactory: Instantiated ConnManager
com.quest.oraoop.OraOopConnManager@186fa9fc
11/05/10 11:33:15 INFO tool.CodeGenTool: Beginning code generation
11/05/10 11:33:15 DEBUG oraoop.OraOopOracleQueries:
getTableColumns() : sql =
SELECT column_name, data_type FROM dba_tab_columns WHERE owner = ?
and table_name = ? and (DATA_TYPE IN
('BINARY_DOUBLE','BINARY_FLOAT','BLOB','CHAR','CLOB','DATE','FLOAT','LONG','NCHAR','NCLOB','NUMBER','NVARCHAR2','ROWID','URITYPE','VARCHAR2')
OR DATA_TYPE LIKE 'INTERVAL YEAR(%) TO MONTH' OR DATA_TYPE LIKE
'INTERVAL DAY(%) TO SECOND(%)' OR DATA_TYPE LIKE 'TIMESTAMP(%)' OR
DATA_TYPE LIKE 'TIMESTAMP(%) WITH TIME ZONE' OR DATA_TYPE LIKE
'TIMESTAMP(%) WITH LOCAL TIME ZONE') ORDER BY column_id
11/05/10 11:33:15 ERROR sqoop.Sqoop: Got exception running Sqoop:
java.lang.RuntimeException: java.sql.SQLException: ORA-00942: table or
view does not exist
java.lang.RuntimeException: java.sql.SQLException: ORA-00942: table or
view does not exist
at
com.quest.oraoop.OraOopConnManager.getColumnNamesInOracleTable(OraOopConnManager.java:
119)
at
com.quest.oraoop.OraOopConnManager.getSelectedColumnNamesInOracleTable(OraOopConnManager.java:
130)
at
com.quest.oraoop.OraOopConnManager.getColTypesQuery(OraOopConnManager.java:
193)
at
com.cloudera.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:
160)
at
com.quest.oraoop.OraOopConnManager.getColumnTypes(OraOopConnManager.java:
455)
at com.cloudera.sqoop.orm.ClassWriter.generate(ClassWriter.java:908)
at com.cloudera.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:
82)
at com.cloudera.sqoop.tool.ImportTool.importTable(ImportTool.java:
337)
at com.cloudera.sqoop.tool.ImportTool.run(ImportTool.java:423)
at com.cloudera.sqoop.Sqoop.run(Sqoop.java:144)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
at com.cloudera.sqoop.Sqoop.runSqoop(Sqoop.java:180)
at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:218)
at com.cloudera.sqoop.Sqoop.main(Sqoop.java:228)
Caused by: java.sql.SQLException: ORA-00942: table or view does not
exist
When Trying the list-databases command :
> sqoop list-databases -D oraoop.logging.level=debug ...
I get an exlicit error because even though OraOop displays a warning
that it doesn't support the LIST_DATABASE Command, it tells me that it
seems I don't have privileges on DBA Table which is correct:
11/05/10 11:42:35 DEBUG tool.BaseSqoopTool: Enabled debug logging.
Enter password:
11/05/10 11:42:39 DEBUG util.ClassLoaderStack: Checking for existing
class: com.quest.oraoop.OraOopManagerFactory
11/05/10 11:42:39 DEBUG util.ClassLoaderStack: Class is already
available. Skipping jar /Tools/Sqoop/lib/oraoop-1.2.0.62.jar
11/05/10 11:42:39 DEBUG sqoop.ConnFactory: Added factory
com.quest.oraoop.OraOopManagerFactory in jar /Tools/Sqoop/lib/
oraoop-1.2.0.62.jar specified by /Tools/sqoop-1.2.0-cdh3u0/bin/../conf/
managers.d/oraoop
11/05/10 11:42:39 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.quest.oraoop.OraOopManagerFactory
11/05/10 11:42:39 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
11/05/10 11:42:39 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.quest.oraoop.OraOopManagerFactory
11/05/10 11:42:39 DEBUG oraoop.OraOopUtilities: Enabled OraOop debug
logging.
11/05/10 11:42:39 DEBUG oraoop.OraOopManagerFactory: OraOop can be
called by Sqoop!
11/05/10 11:42:39 DEBUG oraoop.OraOopManagerFactory: The Sqoop tool
name "LIST-DATABASES" is not supported by OraOop
java.lang.IllegalArgumentException: No enum const class
com.quest.oraoop.OraOopConstants$Sqoop$Tool.LIST-DATABASES
at java.lang.Enum.valueOf(Enum.java:196)
at com.quest.oraoop.OraOopConstants$Sqoop
$Tool.valueOf(OraOopConstants.java:312)
....
.....
11/05/10 11:42:39 DEBUG manager.OracleManager: Creating a new
connection for jdbc:oracle:thin:@//DBServer:1521/DBname
11/05/10 11:42:40 INFO manager.OracleManager: Time zone has been set
to GMT
11/05/10 11:42:40 ERROR manager.OracleManager: The catalog view
DBA_USERS was not found. This may happen if the user does not have DBA
privileges. Please check privileges and try again.
11/05/10 11:42:40 DEBUG manager.OracleManager: Full trace for
ORA-00942 exception
java.sql.SQLException: ORA-00942: table or view does not exist
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
145)
P.S : the following is without OraOop
Something quite strange to me is when I run the import with a single
mapper in this way :
> sqoop import ... --table TABLE_NAME --split-by OWNER_ID -m1 --verbose -P
It fails with error :
11/05/10 11:52:33 INFO mapred.JobClient: Running job:
job_201104261126_0051
11/05/10 11:52:34 INFO mapred.JobClient: map 0% reduce 0%
11/05/10 11:52:41 INFO mapred.JobClient: Task Id :
attempt_201104261126_0051_m_000000_0, Status : FAILED
java.lang.NullPointerException
at
com.cloudera.sqoop.mapreduce.db.DataDrivenDBRecordReader.getSelectQuery(DataDrivenDBRecordReader.java:
87)
at
com.cloudera.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:
225)
But when I run it with --query parameter and single mapper, it works
fine :
> sqoop import ... --query 'select * from TABLE_NAME where ID<1000 AND $CONDITIONS' -m1 --verbose -P
How could you explain this behaviour and how to solve definitely the
parallel import issue mentioned above ?
Thanks in advance,
Regards,
Ed
On May 10, 1:36 am, Peter Hall <
Peter.H...@quest.com> wrote:
> Hi Ed,
>
> Split the where clause into a separate argument:
>
> sqoop import --connect jdbc:oracle:thin:@//DBServer:1521/DBname --username myuser --target-dir import_dir --split-by ID --query 'select * from TABLE_NAME' --where 'where ID < 100 and $CONDITIONS' --verbose -P
>
> Sqoop needs to know if you are using a where clause or not so it can modify the clause to split the work for parallel processing by multiple mappers.
>
> With Oraoop: Would you mind turning on debug logging and telling me what query is causing an error? Add -D oraoop.logging.level=debug
>
> Cheers,
> Peter Hall
> Quest Software
>
> ________________________________________
> From: ed Djatsa [
djatsa...@gmail.com]