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

Sqoop2 export to MSSQL fails

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 2.0.0, 1.99.3, 1.99.4
    • Fix Version/s: 1.99.3
    • Component/s: sqoop2-server
    • Labels:
    • Environment:

      MS Windows
      MS SQL Server

      Description

      One of our customer reported sqoop2 failure when exporting
      table to MSSQL. The export fails during transfer from stage table to target
      table.

      <JT stderr logs>
      2014-07-15 19:33:11,681 [ main ] INFO
      org.apache.sqoop.connector.jdbc.GenericJdbcExportDestroyer - Job completed,
      transferring data from stage table to destination table.
      log4j:ERROR Attempted to append to closed appender named [ maprfsTLA ].
      2014-07-15 19:33:11,933 [ main ] ERROR
      org.apache.sqoop.connector.jdbc.GenericJdbcExecutor - Got SQLException while
      migrating data from: reg_stage to: reg
      com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the
      keyword 'SELECT'.
      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.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
      at
      com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
      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.SQLServerStatement.executeUpdate(SQLServerStatement.java:642)
      at
      org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.migrateData(GenericJdbcExecutor.java:91)
      at
      org.apache.sqoop.connector.jdbc.GenericJdbcExportDestroyer.moveDataToDestinationTable(GenericJdbcExportDestroyer.java:55)
      at
      org.apache.sqoop.connector.jdbc.GenericJdbcExportDestroyer.destroy(GenericJdbcExportDestroyer.java:39)
      at
      org.apache.sqoop.connector.jdbc.GenericJdbcExportDestroyer.destroy(GenericJdbcExportDestroyer.java:26)
      at
      org.apache.sqoop.job.mr.SqoopDestroyerExecutor.executeDestroyer(SqoopDestroyerExecutor.java:65)
      at
      org.apache.sqoop.job.mr.SqoopNullOutputFormat$DestroyerOutputCommitter.commitJob(SqoopNullOutputFormat.java:70)
      at org.apache.hadoop.mapred.Task.runJobCleanupTask(Task.java:1102)
      at org.apache.hadoop.mapred.MapTask.run(MapTask.java:333)
      at org.apache.hadoop.mapred.Child$4.run(Child.java:282)
      at java.security.AccessController.doPrivileged(Native Method)
      at javax.security.auth.Subject.doAs(Subject.java:415)
      at
      org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1117)
      at org.apache.hadoop.mapred.Child.main(Child.java:271)

      </JT stderr logs>

      Additional Info:

      • The issue is re-producible
      • Import from MSSQL works fine.

      Steps to re-produce:

      • Import a MSSQL table using sqoop2
      • Try to export the same table to MSSQL.

      Job info (from my test box):

      ---------------
      sqoop:000> update job --jid 1
      Updating job with id 1
      Please update job metadata:
      Name: to MSworld

      Database configuration

      Schema name: dbo
      Table name: reg
      Table SQL statement:
      Table column names: id,name
      Stage table name: reg_stage
      Clear stage table: true

      Input configuration

      Input directory: /sqoop/reg2
      ---------------

      Throttling resources

      Extractors:
      Loaders:
      Job was successfully updated with status FINE
      ----------------
      The query looks like this:

      INSERT INTO TestTable ( SELECT * FROM StageTable )

      Apparently MSSQL does not allow parentheses around the SELECT part of the
      statement.

      This would work.
      INSERT INTO TestTable SELECT * FROM StageTable

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                osayankin Oleksiy Sayankin
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: