Hari Sekhon created SQOOP-1292:
----------------------------------
Summary: 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
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
(v6.2#6252)
