You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by "Kallinteris, Christos" <ka...@initions.com> on 2013/10/30 10:40:40 UTC

import-all-tables fails with exception 'Invalid object name' on non dbo schemas

Hello!

I would like to import a complete database from SQL Server into HDFS and from there to HIVE to have some data to play with.

I figured out the necessary connection string and I get the first few tables imported but then the job throws the invalid object name exception. As far as I can tell this happens when the job encounters a table that has a schema other than dbo.

Is this a known issue and if yes, is there a way around it?

I'm using Sqoop 1.4.3-cdh4.4.0 on the latest stable Cloudera distribution.

Thanks!

Christos

--

initions AG
Christos Kallinteris
Weidestraße 120a
D-22083 Hamburg

t:   +49 (0) 40 / 41 49 60-79
f:   +49 (0) 40 / 41 49 60-11
e:  kallinteris@initions.com<ma...@initions.com>
w: www.initions.com<http://www.initions.com/>


Vollständiger Name der Gesellschaft: initions innovative IT solutions AG
Sitz der Gesellschaft: Hamburg
Handelsregister Hamburg B 83929
Aufsichtsratsvorsitzender: Dr. Michael Leue
Vorstand: Dr. Stefan Anschütz, André Paul Henkel, Dr. Helge Plehn

Re: import-all-tables fails with exception 'Invalid object name' on non dbo schemas

Posted by Jarek Jarcec Cecho <ja...@apache.org>.
Hi Christos,
this is the log I was looking for :-) 

> 13/10/31 16:47:48 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.

Would you mind trying the command without --driver option as the WARNING is suggesting? You might find more background why using --driver option is not desirable in most cases in our blog post [1].

Jarcec

Links:
1: https://blogs.apache.org/sqoop/entry/connectors_and_drivers_in_the

On Thu, Oct 31, 2013 at 03:54:21PM +0000, Kallinteris, Christos wrote:
> Hello Jarcec,
> 
> Here is the command and the response I get on the console. Where would I find the log for sqoop? There is no corresponding directory under /var/log.
> 
> Thank you very much!
> 
> Christos
> 
> --
> ck@SVR-HDP01:~$ sqoop import-all-tables --connect "jdbc:sqlserver://192.168.207.194\\SQL2008R2;user=sqoop;password=password;databasename=DWH_BSP;" --driver com.microsoft.sqlserver.jdbc.SQLServerDriver --username sqoop --password password  --hive-import --verbose
> Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
> Please set $HCAT_HOME to the root of your HCatalog installation.
> 13/10/31 16:47:48 DEBUG tool.BaseSqoopTool: Enabled debug logging.
> 13/10/31 16:47:48 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
> 13/10/31 16:47:48 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
> 13/10/31 16:47:48 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
> 13/10/31 16:47:48 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
> 13/10/31 16:47:48 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
> 13/10/31 16:47:48 INFO manager.SqlManager: Using default fetchSize of 1000
> 13/10/31 16:47:49 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
> 13/10/31 16:47:50 INFO tool.CodeGenTool: Beginning code generation
> 13/10/31 16:47:50 DEBUG manager.SqlManager: Execute getColumnTypesRawQuery : SELECT t.* FROM tblAdresse AS t WHERE 1=0
> 13/10/31 16:47:50 DEBUG manager.SqlManager: Using fetchSize for next query: 1000
> 13/10/31 16:47:50 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM tblAdresse AS t WHERE 1=0
> 13/10/31 16:47:50 ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tblAdresse'.
> com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tblAdresse'.
>         at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
>         at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
>         at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
>         at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:674)
>         at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:683)
>         at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:240)
>         at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:223)
>         at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:347)
>         at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1277)
>         at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1089)
>         at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
>         at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
>         at org.apache.sqoop.tool.ImportAllTablesTool.run(ImportAllTablesTool.java:105)
>         at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
>         at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>         at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
>         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
>         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
>         at org.apache.sqoop.Sqoop.main(Sqoop.java:238)
> 13/10/31 16:47:50 ERROR tool.ImportAllTablesTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
> 
> --
> 
> initions AG
> Christos Kallinteris
> Weidestraße 120a
> D-22083 Hamburg
> 
> t:   +49 (0) 40 / 41 49 60-79
> f:   +49 (0) 40 / 41 49 60-11
> e:  kallinteris@initions.com
> w: www.initions.com
>  
> 
> Vollständiger Name der Gesellschaft: initions innovative IT solutions AG
> Sitz der Gesellschaft: Hamburg
> Handelsregister Hamburg B 83929
> Aufsichtsratsvorsitzender: Dr. Michael Leue
> Vorstand: Dr. Stefan Anschütz, André Paul Henkel, Dr. Helge Plehn
> 
> 
> -----Ursprüngliche Nachricht-----
> Von: Jarek Jarcec Cecho [mailto:jarcec@apache.org] 
> Gesendet: Donnerstag, 31. Oktober 2013 16:31
> An: user@sqoop.apache.org
> Betreff: Re: import-all-tables fails with exception 'Invalid object name' on non dbo schemas
> 
> Hi Christos,
> would you mind sharing with us entire Sqoop command and associated log generated with parameter --verbose?
> 
> Jarcec
> 
> On Wed, Oct 30, 2013 at 09:40:40AM +0000, Kallinteris, Christos wrote:
> > Hello!
> > 
> > I would like to import a complete database from SQL Server into HDFS and from there to HIVE to have some data to play with.
> > 
> > I figured out the necessary connection string and I get the first few tables imported but then the job throws the invalid object name exception. As far as I can tell this happens when the job encounters a table that has a schema other than dbo.
> > 
> > Is this a known issue and if yes, is there a way around it?
> > 
> > I'm using Sqoop 1.4.3-cdh4.4.0 on the latest stable Cloudera distribution.
> > 
> > Thanks!
> > 
> > Christos
> > 
> > --
> > 
> > initions AG
> > Christos Kallinteris
> > Weidestraße 120a
> > D-22083 Hamburg
> > 
> > t:   +49 (0) 40 / 41 49 60-79
> > f:   +49 (0) 40 / 41 49 60-11
> > e:  kallinteris@initions.com<ma...@initions.com>
> > w: www.initions.com<http://www.initions.com/>
> > 
> > 
> > Vollständiger Name der Gesellschaft: initions innovative IT solutions 
> > AG Sitz der Gesellschaft: Hamburg Handelsregister Hamburg B 83929
> > Aufsichtsratsvorsitzender: Dr. Michael Leue
> > Vorstand: Dr. Stefan Anschütz, André Paul Henkel, Dr. Helge Plehn
> >

AW: import-all-tables fails with exception 'Invalid object name' on non dbo schemas

Posted by "Kallinteris, Christos" <ka...@initions.com>.
Hello Jarcec,

Here is the command and the response I get on the console. Where would I find the log for sqoop? There is no corresponding directory under /var/log.

Thank you very much!

Christos

--
ck@SVR-HDP01:~$ sqoop import-all-tables --connect "jdbc:sqlserver://192.168.207.194\\SQL2008R2;user=sqoop;password=password;databasename=DWH_BSP;" --driver com.microsoft.sqlserver.jdbc.SQLServerDriver --username sqoop --password password  --hive-import --verbose
Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
13/10/31 16:47:48 DEBUG tool.BaseSqoopTool: Enabled debug logging.
13/10/31 16:47:48 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/10/31 16:47:48 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
13/10/31 16:47:48 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
13/10/31 16:47:48 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
13/10/31 16:47:48 WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time.
13/10/31 16:47:48 INFO manager.SqlManager: Using default fetchSize of 1000
13/10/31 16:47:49 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
13/10/31 16:47:50 INFO tool.CodeGenTool: Beginning code generation
13/10/31 16:47:50 DEBUG manager.SqlManager: Execute getColumnTypesRawQuery : SELECT t.* FROM tblAdresse AS t WHERE 1=0
13/10/31 16:47:50 DEBUG manager.SqlManager: Using fetchSize for next query: 1000
13/10/31 16:47:50 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM tblAdresse AS t WHERE 1=0
13/10/31 16:47:50 ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tblAdresse'.
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tblAdresse'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:674)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:683)
        at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:240)
        at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:223)
        at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:347)
        at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1277)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1089)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
        at org.apache.sqoop.tool.ImportAllTablesTool.run(ImportAllTablesTool.java:105)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)
13/10/31 16:47:50 ERROR tool.ImportAllTablesTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter

--

initions AG
Christos Kallinteris
Weidestraße 120a
D-22083 Hamburg

t:   +49 (0) 40 / 41 49 60-79
f:   +49 (0) 40 / 41 49 60-11
e:  kallinteris@initions.com
w: www.initions.com
 

Vollständiger Name der Gesellschaft: initions innovative IT solutions AG
Sitz der Gesellschaft: Hamburg
Handelsregister Hamburg B 83929
Aufsichtsratsvorsitzender: Dr. Michael Leue
Vorstand: Dr. Stefan Anschütz, André Paul Henkel, Dr. Helge Plehn


-----Ursprüngliche Nachricht-----
Von: Jarek Jarcec Cecho [mailto:jarcec@apache.org] 
Gesendet: Donnerstag, 31. Oktober 2013 16:31
An: user@sqoop.apache.org
Betreff: Re: import-all-tables fails with exception 'Invalid object name' on non dbo schemas

Hi Christos,
would you mind sharing with us entire Sqoop command and associated log generated with parameter --verbose?

Jarcec

On Wed, Oct 30, 2013 at 09:40:40AM +0000, Kallinteris, Christos wrote:
> Hello!
> 
> I would like to import a complete database from SQL Server into HDFS and from there to HIVE to have some data to play with.
> 
> I figured out the necessary connection string and I get the first few tables imported but then the job throws the invalid object name exception. As far as I can tell this happens when the job encounters a table that has a schema other than dbo.
> 
> Is this a known issue and if yes, is there a way around it?
> 
> I'm using Sqoop 1.4.3-cdh4.4.0 on the latest stable Cloudera distribution.
> 
> Thanks!
> 
> Christos
> 
> --
> 
> initions AG
> Christos Kallinteris
> Weidestraße 120a
> D-22083 Hamburg
> 
> t:   +49 (0) 40 / 41 49 60-79
> f:   +49 (0) 40 / 41 49 60-11
> e:  kallinteris@initions.com<ma...@initions.com>
> w: www.initions.com<http://www.initions.com/>
> 
> 
> Vollständiger Name der Gesellschaft: initions innovative IT solutions 
> AG Sitz der Gesellschaft: Hamburg Handelsregister Hamburg B 83929
> Aufsichtsratsvorsitzender: Dr. Michael Leue
> Vorstand: Dr. Stefan Anschütz, André Paul Henkel, Dr. Helge Plehn
>

Re: import-all-tables fails with exception 'Invalid object name' on non dbo schemas

Posted by Jarek Jarcec Cecho <ja...@apache.org>.
Hi Christos,
would you mind sharing with us entire Sqoop command and associated log generated with parameter --verbose?

Jarcec

On Wed, Oct 30, 2013 at 09:40:40AM +0000, Kallinteris, Christos wrote:
> Hello!
> 
> I would like to import a complete database from SQL Server into HDFS and from there to HIVE to have some data to play with.
> 
> I figured out the necessary connection string and I get the first few tables imported but then the job throws the invalid object name exception. As far as I can tell this happens when the job encounters a table that has a schema other than dbo.
> 
> Is this a known issue and if yes, is there a way around it?
> 
> I'm using Sqoop 1.4.3-cdh4.4.0 on the latest stable Cloudera distribution.
> 
> Thanks!
> 
> Christos
> 
> --
> 
> initions AG
> Christos Kallinteris
> Weidestraße 120a
> D-22083 Hamburg
> 
> t:   +49 (0) 40 / 41 49 60-79
> f:   +49 (0) 40 / 41 49 60-11
> e:  kallinteris@initions.com<ma...@initions.com>
> w: www.initions.com<http://www.initions.com/>
> 
> 
> Vollständiger Name der Gesellschaft: initions innovative IT solutions AG
> Sitz der Gesellschaft: Hamburg
> Handelsregister Hamburg B 83929
> Aufsichtsratsvorsitzender: Dr. Michael Leue
> Vorstand: Dr. Stefan Anschütz, André Paul Henkel, Dr. Helge Plehn
>