Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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.

 
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB