You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@sqoop.apache.org by Suhas Satish <su...@gmail.com> on 2014/07/16 20:18:04 UTC
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.
Re: sqoop2 export to microsoft SQL server - supported jdbc connectors
Posted by Suhas Satish <su...@gmail.com>.
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 <ab...@cloudera.com> 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 <su...@gmail.com>
> 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.
>>
>
>
Re: sqoop2 export to microsoft SQL server - supported jdbc connectors
Posted by Abraham Elmahrek <ab...@cloudera.com>.
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 <su...@gmail.com>
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.
>