Uploaded image for project: 'Sqoop'
  1. Sqoop
  2. SQOOP-1292

SQL Server connector/driver import issues - "invalid object name" for table name with a period in the name

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.4.4
    • Fix Version/s: None
    • Component/s: connectors/sqlserver
    • Labels:
      None
    • Environment:

      Hortonworks Sandbox 2.0

      Description

      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

        Activity

        Hide
        anjireddy3 anji added a comment -

        Please update if this issue has been resolved now. I am seeing similar issue while importing data from oracle DB to HDFS.

        Show
        anjireddy3 anji added a comment - Please update if this issue has been resolved now. I am seeing similar issue while importing data from oracle DB to HDFS.
        Hide
        osvyryd Olga Svyryd added a comment -

        Hello all.
        Do you have any updates about this issue?
        I'm using Sqoop for exporting data from Hive into SQl Server. And also was faced with such problem of 'invalid object name'.
        I added property --driver com.microsoft.sqlserver.jdbc.SQLServerDriver but my log comes to next state and then just freezes:

        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.
        INFO manager.SqlManager: Using default fetchSize of 1000
        INFO tool.CodeGenTool: Beginning code generation
        DEBUG manager.SqlManager: Execute getColumnInfoRawQuery : SELECT t.* FROM mySchema.myTable AS t WHERE 1=0
        DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
        DEBUG manager.SqlManager: Using fetchSize for next query: 1000
        INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM mySchema.myTable AS t WHERE 1=0

        Do you have any recommendations how to overcome this problem?

        Thanks in advance,
        Olga Svyryd

        Show
        osvyryd Olga Svyryd added a comment - Hello all. Do you have any updates about this issue? I'm using Sqoop for exporting data from Hive into SQl Server. And also was faced with such problem of 'invalid object name'. I added property --driver com.microsoft.sqlserver.jdbc.SQLServerDriver but my log comes to next state and then just freezes: 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. INFO manager.SqlManager: Using default fetchSize of 1000 INFO tool.CodeGenTool: Beginning code generation DEBUG manager.SqlManager: Execute getColumnInfoRawQuery : SELECT t.* FROM mySchema.myTable AS t WHERE 1=0 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection. DEBUG manager.SqlManager: Using fetchSize for next query: 1000 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM mySchema.myTable AS t WHERE 1=0 Do you have any recommendations how to overcome this problem? Thanks in advance, Olga Svyryd

          People

          • Assignee:
            Unassigned
            Reporter:
            harisekhon Hari Sekhon
          • Votes:
            0 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

            • Created:
              Updated:

              Development