question Re: Unable to import data from SQL Server to hive using Sqoop in Support Questions

Unable to import data from SQL Server to hive using Sqoop

safianjum123 — Thu, 18 Jan 2018 15:44:48 GMT

Hi,
I have some databases in SQL Server 2012 Express and I used Sqoop to import them to hive. The process worked fine.
I had an Attendence databses which is of 40GB .mdf file. I installed SQL Server 2014 Developer Edition to restore that database as SQl Server Express cant go beyond 10GB.
After restoring the Attendence Database when I try to import it into hive using sqoop i am getting following exception:

18/01/18 12:25:27 ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "Attendance" requested by the login. The login failed. ClientConnectionId:882fd1da-cf02-43cc-af47-03025da7400b com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "Attendance" requested by the login. The login failed. ClientConnectionId:882fd1da-cf02-43cc-af47-03025da7400b at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:279) at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:99) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4346) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3160) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43) at org.apache.sqoop.Sqoop.main(Sqoop.java:243) 18/01/18 12:25:27 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1659) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:488) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:615) at org.apache.sqoop.Sqoop.run(Sqoop.java:147) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:225) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:234) at org.apache.sqoop.Sqoop.main(Sqoop.java:243)

Following is my Sqoop Command:

sqoop import --driver com.microsoft.sqlserver.jdbc.SQLServerDriver --connect "jdbc:sqlserver://192.168.3.122:1433;databaseName=Attendance" --username SA --password 43434asw -m 1 --table dbo.UserUpdates --hive-table staging_attendance.UserUpdates --fields-terminated-by ',' --hive-import --hive-overwrite --outdir /root/staging_attendance

I have already checked for spelling mistakes, incorrect ip, port, password but thay all are correct.

Is this error due to multiple instance of SQL Server or something else, Please provide the solution.

Thanks.

Re: Unable to import data from SQL Server to hive using Sqoop

jsensharma — Thu, 18 Jan 2018 16:07:56 GMT

@Safi Ahmed

Can you please try the following syntax to pass the username & password as part of the connection URL and try to list the tables in the DB to see if this works? This is just to validate if the DB is rejecting the credentials or the specific database / table that you are trying to access is causing some issue here.

This is just to see of the mentioned user has access issues on DB "Attendance" ?

# sqoop  list-tables  
--driver com.microsoft.sqlserver.jdbc.SQLServerDriver 
--connect "jdbc:sqlserver://xxxxxxxxxxx:1433;databaseName=Attendance;user=SA;password=43434asw"

Re: Unable to import data from SQL Server to hive using Sqoop

safianjum123 — Fri, 19 Jan 2018 10:12:44 GMT

Many thanks @Jay Kumar SenSharma for your help.

I found the problem, the problem was with multiple instances of SQL Server on same port. Configuring the port and other TCP settings has solved it.

Re: Unable to import data from SQL Server to hive using Sqoop

Bishal — Tue, 24 Aug 2021 15:04:13 GMT

Can you please help what you meant by "Other TCP settings", we have a similar problem need this to troubleshoot it. Thanks in advance !!

1. Did you change the port from 1433 to something else ? like 14XX
2. What changes in TCP/IP side had been done ?

Re: Unable to import data from SQL Server to hive using Sqoop

VidyaSargur — Wed, 25 Aug 2021 06:30:53 GMT

@Bishal as this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post.