Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Plain View
Sqoop >> mail # user >> Importing from OracleDB


+
James Hogarth 2013-02-01, 13:37
Copy link to this message
-
Re: Importing from OracleDB
Hi James,
I'm afraid that using synonyms is not supported by Oracle driver. The problem is that Oracle driver is issuing following query to get columns for imported table:

  SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = ? AND TABLE_NAME = ? ORDER BY COLUMN_ID

This query returns no rows for synonym and thus import fails.

As a workaround I would recommend to try out Generic JDBC Connector that is not using this query to fetch table metadata. You can force Sqoop to use Generic JDBC Connector by passing "--driver" parameter with value oracle.jdbc.OracleDriver, e.g.

  sqoop import --connect ... --driver oracle.jdbc.OracleDriver

Another solution that might possibly help you is to install OraOop connector, but I'm not sure whether it supports synonyms or not.

Jarcec

On Fri, Feb 01, 2013 at 01:37:26PM +0000, James Hogarth wrote:
> 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.
+
James Hogarth 2013-02-01, 18:14
+
Jarek Jarcec Cecho 2013-02-01, 18:52
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB