Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Sqoop >> mail # user >> sqoop2 export to microsoft SQL server - supported jdbc connectors


Copy link to this message
-
sqoop2 export to microsoft SQL server - supported jdbc connectors
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.