Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

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

New Contributor

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.

1 REPLY 1
Highlighted

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

Master Guru
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...