Reply
New Contributor
Posts: 1
Registered: ‎05-08-2019

Cannot pull data from SQL SERVER (case sensitive database) using SQOOP

[ Edited ]

Hi Team,

 

I have a SQL server database whose collation is Latin1_General_CS_AS which is case sensitive. My intention is to pull data from SQL server and load it to HDFS. Before starting the process I want to ensure I could connect sql DB . so that I have run the below command but it throws error.

 

sqoop list-databases --connect "jdbc:sqlserver://SQL-SERVER-IP" --username sa --password XXXXX

 

The Error is :

19/05/08 02:08:16 ERROR manager.CatalogQueryManager: Failed to list databases
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'SYS.DATABASES'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677)
at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.java:59)
at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:243)
at org.apache.sqoop.Sqoop.main(Sqoop.java:252)

 

this is because of case sensitive. Here we cannot change the collation of sql database . is there any way to achieve this???? pls someone help me to resolve this issue.

Posts: 1,892
Kudos: 432
Solutions: 302
Registered: ‎07-31-2013

Re: Cannot pull data from SQL SERVER (case sensitive database) using SQOOP

Yes, unfortunately, this is due to your case sensitive configuration.

Sqoop's hard-coding the catalog names in upper-case for SQL Server queries [1].

Could you please log a JIRA over at https://issues.apache.org/jira/projects/SQOOP describing the issue? That could help get traction into changing SQL Server manager queries to lower-case, which should work in case sensitive/insensitive modes (I think).

As a workaround to 'list-databases' you can try the 'eval' sub-command instead which executes arbitrary SQL from user input: https://archive.cloudera.com/cdh5/cdh/5/sqoop/SqoopUserGuide.html#_literal_sqoop_eval_literal

[1] - https://github.com/apache/sqoop/blob/trunk/src/java/org/apache/sqoop/manager/SQLServerManager.java#L...
Announcements