sqoop-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anıl Chalil (JIRA) <j...@apache.org>
Subject [jira] [Comment Edited] (SQOOP-1292) SQL Server connector/driver import issues - "invalid object name" for table name with a period in the name
Date Sun, 28 Jan 2018 11:37:00 GMT

    [ https://issues.apache.org/jira/browse/SQOOP-1292?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16342538#comment-16342538
] 

Anıl Chalil edited comment on SQOOP-1292 at 1/28/18 11:36 AM:
--------------------------------------------------------------

I think correct command should be 
{code:java}
sqoop import -connect "jdbc:sqlserver://x.x.x.x:1533;database=MyDatabase" --username omitted
--password omitted --table "DimCounterParty" --split-by CounterpartyKey --target-dir /myDatabase-dir/myTable
-- --schema cube{code}
this will generate the correct sql. Also for --schema parameter you really should put another
-- before- -schema. If you want to give NOLOCK hint then:
{code:java}
sqoop import -connect "jdbc:sqlserver://x.x.x.x:1533;database=MyDatabase" --username omitted
--password omitted --table "DimCounterParty" --split-by CounterpartyKey --target-dir /myDatabase-dir/myTable
-- --schema cube --table-hints NOLOCK{code}
 

 


was (Author: capacman):
I think correct command should be 

sqoop import --connect "jdbc:sqlserver://x.x.x.x:1533;database=MyDatabase" --username omitted
--password omitted --table "DimCounterParty" --split-by CounterpartyKey --target-dir /myDatabase-dir/myTable
-- --schema cube

this will generate the correct sql. Also for --schema parameter you really should put another
-- before --schema. If you want to give NOLOCK hint then:

sqoop import --connect "jdbc:sqlserver://x.x.x.x:1533;database=MyDatabase" --username omitted
--password omitted --table "DimCounterParty" --split-by CounterpartyKey --target-dir /myDatabase-dir/myTable
-- --schema cube --table-hints NOLOCK

 

 

> SQL Server connector/driver import issues - "invalid object name" for table name with
a period in the name
> ----------------------------------------------------------------------------------------------------------
>
>                 Key: SQOOP-1292
>                 URL: https://issues.apache.org/jira/browse/SQOOP-1292
>             Project: Sqoop
>          Issue Type: Bug
>          Components: connectors/sqlserver
>    Affects Versions: 1.4.4
>         Environment: Hortonworks Sandbox 2.0
>            Reporter: Hari Sekhon
>            Priority: Major
>
> Hi,
> I'm seeing an issue which looks like a bug relating to the table name when trying to
import a table (a view actually) from SQL Server 2012:
> sqoop import --connect "jdbc:sqlserver://x.x.x.x:1533;database=MyDatabase" --username
omitted --password omitted --table "cube.DimCounterParty" --split-by CounterpartyKey --target-dir
/myDatabase-dir/myTable
> 14/03/07 09:53:53 INFO sqoop.Sqoop: Running Sqoop version: 1.4.4.2.0.6.0-76
> 14/03/07 09:53:53 WARN tool.BaseSqoopTool: Setting your password on the command-line
is insecure. Consider using -P instead.
> 14/03/07 09:53:53 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output.
You can override
> 14/03/07 09:53:53 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
> 14/03/07 09:53:53 INFO manager.SqlManager: Using default fetchSize of 1000
> 14/03/07 09:53:53 INFO tool.CodeGenTool: Beginning code generation
> 14/03/07 09:53:54 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM [cube.DimCounterParty]
AS t WHERE 1=0
> 14/03/07 09:53:54 ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException:
Invalid object name 'cube.DimCounterParty'.
> com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'cube.DimCounterParty'.
>         at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)
>         at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
>         at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
>         at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
>         at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:283)
>         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:1298)
>         at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1110)
>         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.ImportTool.run(ImportTool.java:502)
>         at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>         at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>         at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>         at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
> 14/03/07 09:53:54 ERROR tool.ImportTool: Encountered IOException running import job:
java.io.IOException: No columns to generate for ClassWriter
>         at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1116)
>         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.ImportTool.run(ImportTool.java:502)
>         at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>         at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>         at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>         at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
> However if I add to the command:
> --driver com.microsoft.sqlserver.jdbc.SQLServerDriver
> then it works fine and imports correctly with the additional warning message:
>  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.
> I suspect this is because it's ended up using a different connection manager that is
less stringent with the table names?
> I can't find a connection manager string for SQL Server to appease that warning message
and using the same class for the connection manager I get:
> ERROR tool.BaseSqoopTool: Got error creating database manager: java.io.IOException: java.lang.NoSuchMethodException:
com.microsoft.sqlserver.jdbc.SQLServerDriver.<init>(java.lang.String, com.cloudera.sqoop.SqoopOptions)
>         at org.apache.sqoop.ConnFactory.getManager(ConnFactory.java:165)
>         at org.apache.sqoop.tool.BaseSqoopTool.init(BaseSqoopTool.java:225)
>         at org.apache.sqoop.tool.ImportTool.init(ImportTool.java:84)
>         at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:490)
>         at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
>         at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
>         at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
>         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
>         at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
>         at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
> Caused by: java.lang.NoSuchMethodException: com.microsoft.sqlserver.jdbc.SQLServerDriver.<init>(java.lang.String,
com.cloudera.sqoop.SqoopOptions)
>         at java.lang.Class.getConstructor0(Class.java:2706)
>         at java.lang.Class.getDeclaredConstructor(Class.java:1985)
>         at org.apache.sqoop.ConnFactory.getManager(ConnFactory.java:151)
>         ... 9 more
> Is there another connection manager string I should be using? Everywhere that I've seen
uses just the --connect string and omits the --driver as recommended but in this case it fails
on the table name.
> Is the fact that I've been able to work around the issue by explicitly specifying the
driver because the generic connection manager that results is more forgiving for table names
than the SQL Server connector?
> Thanks
> Hari Sekhon
> http://www.linkedin.com/in/harisekhon



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message