Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Sqoop, mail # user - Importing from OracleDB


Copy link to this message
-
Importing from OracleDB
James Hogarth 2013-02-01, 13:37
Hitting an interesting test case here using
sqoop-1.4.1+54-1.cdh4.1.2.p0.21.el6.noarch ...

I have an OracleDB where I need to do a daily grab of a particular table -
ultimately it needs to end in hive...

The OracleDB is running 11.2.0.2 and I'm using the 11.2.0.3 (ie current)
jdbc thin (java6 of course) driver.

I've tried quite the variety of combination of username and table name
capitalization options (as per that known issue) both quoting and not
quoting. I should note that for security reasons on this particular
database I have access to a synonym only and not a standard table.

If I don't specify the columns to import (so attempt to let sqoop
automatically get the list of columns for the table) I get:

[me@client ~]$ sqoop import --connect jdbc:oracle:thin:@database:1525:SID
--username user --password pass --table table1  --num-mappers 1
--target-dir table1_temp  --verbose

13/02/01 11:38:23 DEBUG tool.BaseSqoopTool: Enabled debug logging.
13/02/01 11:38:23 WARN tool.BaseSqoopTool: Setting your password on the
command-line is insecure. Consider using -P instead.
13/02/01 11:38:23 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
13/02/01 11:38:23 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.cloudera.sqoop.manager.DefaultManagerFactory
13/02/01 11:38:23 DEBUG manager.DefaultManagerFactory: Trying with scheme:
jdbc:oracle:thin:@database:1525
13/02/01 11:38:23 DEBUG manager.OracleManager$ConnCache: Instantiated new
connection cache.
13/02/01 11:38:23 INFO manager.SqlManager: Using default fetchSize of 1000
13/02/01 11:38:23 DEBUG sqoop.ConnFactory: Instantiated ConnManager
org.apache.sqoop.manager.OracleManager@16bdb503
13/02/01 11:38:23 INFO tool.CodeGenTool: Beginning code generation
13/02/01 11:38:23 DEBUG manager.OracleManager: Using column names query:
SELECT t.* FROM table1 t WHERE 1=0
13/02/01 11:38:23 DEBUG manager.OracleManager: Creating a new connection
for jdbc:oracle:thin:@database:1525:SID, using username: user
13/02/01 11:38:23 DEBUG manager.OracleManager: No connection paramenters
specified. Using regular API for making connection.
13/02/01 11:38:24 INFO manager.OracleManager: Time zone has been set to GMT
13/02/01 11:38:24 DEBUG manager.SqlManager: Using fetchSize for next query:
1000
13/02/01 11:38:24 INFO manager.SqlManager: Executing SQL statement: SELECT
t.* FROM table1 t WHERE 1=0
13/02/01 11:38:24 DEBUG manager.OracleManager$ConnCache: Caching released
connection for jdbc:oracle:thin:@database:1525:SID/username
13/02/01 11:38:24 DEBUG orm.ClassWriter: selected columns:
13/02/01 11:38:24 ERROR tool.ImportTool: Imported Failed: Attempted to
generate class with no columns!

Specifying the columns writes to HDFS okay:

[me@client ~]$ sqoop import --connect jdbc:oracle:thin:@database:1525:SID
--username user --password pass --table table1 --columns
col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12
--num-mappers 1 --target-dir table1_temp  --verbose
13/02/01 12:54:22 DEBUG tool.BaseSqoopTool: Enabled debug logging.
13/02/01 12:54:22 WARN tool.BaseSqoopTool: Setting your password on the
command-line is insecure. Consider using -P instead.
13/02/01 12:54:22 DEBUG sqoop.ConnFactory: Loaded manager factory:
com.cloudera.sqoop.manager.DefaultManagerFactory
13/02/01 12:54:22 DEBUG sqoop.ConnFactory: Trying ManagerFactory:
com.cloudera.sqoop.manager.DefaultManagerFactory
13/02/01 12:54:22 DEBUG manager.DefaultManagerFactory: Trying with scheme:
jdbc:oracle:thin:@database:1525
13/02/01 12:54:22 DEBUG manager.OracleManager$ConnCache: Instantiated new
connection cache.
13/02/01 12:54:22 INFO manager.SqlManager: Using default fetchSize of 1000
13/02/01 12:54:22 DEBUG sqoop.ConnFactory: Instantiated ConnManager
org.apache.sqoop.manager.OracleManager@6719dc16
13/02/01 12:54:22 INFO tool.CodeGenTool: Beginning code generation
13/02/01 12:54:22 DEBUG manager.OracleManager: Using column names query:
SELECT t.* FROM table1 t WHERE 1=0
13/02/01 12:54:22 DEBUG manager.OracleManager: Creating a new connection
for jdbc:oracle:thin:@database:1525:SID, using username: user
13/02/01 12:54:22 DEBUG manager.OracleManager: No connection paramenters
specified. Using regular API for making connection.
13/02/01 12:54:23 INFO manager.OracleManager: Time zone has been set to GMT
13/02/01 12:54:23 DEBUG manager.SqlManager: Using fetchSize for next query:
1000
13/02/01 12:54:23 INFO manager.SqlManager: Executing SQL statement: SELECT
t.* FROM table1 t WHERE 1=0
13/02/01 12:54:23 DEBUG orm.ClassWriter: selected columns:
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col1
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col2
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col3
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col4
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col5
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col6
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col7
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col8
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col9
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col10
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col11
13/02/01 12:54:23 DEBUG orm.ClassWriter:   col12
13/02/01 12:54:23 DEBUG orm.ClassWriter: Writing source file:
/tmp/sqoop-user/compile/485bb295736ca89d6d471d61c3690220/table1.java
13/02/01 12:54:23 DEBUG orm.ClassWriter: Table name: table1
13/02/01 12:54:23 DEBUG orm.ClassWriter: Columns: col1:93, col2:2, col3:12,
col4:12, col5:12, col6:12, col7:12, col8:12, col9:12, col10:2, col11:12,
col12:2,
13/02/01 12:54:23 DEBUG orm.ClassWriter: sourceFilename is table1.java
13/02/01 12:54:23 DEBUG orm.CompilationManager: Found existing
/tmp/sqoop-user/compile/485bb295736ca89d6d471d61c3690220/
13/02/01 12:54:23 INFO orm.CompilationManager: HADOOP_HOME is
/usr/lib/hadoop
13/02/01 12:54:23 DEBUG orm.CompilationManager: Adding source file:
/tmp/sqoop-user/compile/485bb295736ca89d6d471d61c3690220/table1.java
13/02/01 12:54:23 DEBUG orm.CompilationManager: Invoking javac with args:
13/02/01 12:54:23 DEBUG orm.Compi