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

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.

On 1 Feb 2013 17:27, "Jarek Jarcec Cecho" <[EMAIL PROTECTED]> wrote:
>
> 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
>

Hi Jarcec

Thanks for confirming that.

In the end my workaround with specifying the columns capitalised ended up
working nicely and isn't so bad seeing as I needed to cast the oracle type
'number' to Long/BIGINT on a couple of the columns anyway...

Cheers

James

Hi James,
I'm glad to hear that you found reasonable workaround! If you think that supporting synonyms make sense, please do not hesitate and open new JIRA [1] for that.

Jarcec

Links:
1: https://issues.apache.org/jira/browse/SQOOP

On Fri, Feb 01, 2013 at 06:14:21PM +0000, James Hogarth wrote:
> On 1 Feb 2013 17:27, "Jarek Jarcec Cecho" <[EMAIL PROTECTED]> wrote:
> >
> > 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
> >
>
> Hi Jarcec
>
> Thanks for confirming that.
>
> In the end my workaround with specifying the columns capitalised ended up
> working nicely and isn't so bad seeing as I needed to cast the oracle type
> 'number' to Long/BIGINT on a couple of the columns anyway...
>
> Cheers
>
> James