Thanks Abe. The export query that fails is INSERT INTO TestTable ( SELECT * FROM StageTable )
MSSQL server does not support the paranthesis around the select. This works - INSERT INTO TestTable SELECT * FROM StageTable Cheers, Suhas. On Wed, Jul 16, 2014 at 11:25 AM, Abraham Elmahrek <[email protected]> wrote: > Hey there, > > Sqoop2 is still under active development and currently does not support > specialized connectors. You can, however, use the Generic JDBC Connector to > extract information from MSSQL and vice/versa. It seems like you did run > into a bug with MSSQL export. I've created a Jira to track this: > https://issues.apache.org/jira/browse/SQOOP-1383. > > -Abe > > > On Wed, Jul 16, 2014 at 11:18 AM, Suhas Satish <[email protected]> > wrote: > >> Hi there, >> Does sqoop2 support specialized connectors for microsoft SQL server? Or >> does it rely on generic jdbc connector? >> >> I see conflicting information here - >> >> >> https://www.cloudera.com/content/cloudera-content/cloudera-docs/CDH5/latest/CDH5-Installation-Guide/cdh5ig_sqoop2_configure.html >> under >> Installing the Microsoft SQL Server JDBC Driver >> Download the Microsoft SQL Server JDBC driver from >> http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774 >> and copy it to the /var/lib/sqoop2/ directory. For example: >> $ curl -L ' >> http://download.microsoft.com/download/0/2/A/02AAE597-3865-456C-AE7F-613F99F850A8/sqljdbc_4.0.2206.100_enu.tar.gz' >> | tar xz >> $ sudo cp sqljdbc_4.0/enu/sqljdbc4.jar /var/lib/sqoop2/ >> ---------------------------------------------------------- >> and here - >> >> >> https://www.cloudera.com/content/cloudera-content/cloudera-docs/CDH5/latest/CDH5-Installation-Guide/cdh5ig_sqoop_vs_sqoop2.html >> Connectors for all major RDBMS: Not supported. >> Workaround: Use the generic JDBC Connector which has been tested on the >> following databases: Microsoft SQL Server, PostgreSQL, MySQL and Oracle. >> >> This connector should work on any other JDBC compliant database. However, >> performance might not be comparable to that of specialized connectors in >> Sqoop. >> ------------------------------------------------------------------------- >> >> I am able to import table from microsoft sql but the export fails as >> follows - >> >> 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 >> >> --------------------------------------------------- >> Thanks, >> Suhas. >> > >
