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.

Unable to connect to Sqlserver using Sqoop

Unable to connect to Sqlserver using Sqoop

New Contributor

I am trying to use sqoop to copy a table from local Sql Server. I am on a Windows 10 laptop with VMWare Workstation installed with the sandbox. Everything works fine from Ambari - am sure my sandbox is installed correctly.

Am using the Web client (my_site:4200) for sqoop.

As a test, the "sqoop version" command works fine.

I then tried to run the 'list databases' command in sqoop - before doing the import. I get the following error:

could not load db driver class com.microsoft.sqlserver.jdbc.sqlserverdriver

I have looked at the suggested solutions by the community.

I downloaded the microsoft jdbc driver jar ("sqljdbc42.jar"). Using WinSCP, I copied to file to the linux directory (as suggested by @Artem Ervits /usr/hdp/current/sqoop-client/lib.

Ran the command again in the shell - same error.

I copied the jar file to the usr/lib/sqoop/lib folder. Got same error.

I understand I have copied the jar file to the linux file system. Don't I have to copy it to a path that hadoop understands. Looking at the folder structure in WinSCP, I cannot make out which folders are being used by hadoop. There was no 'hdp' folder under 'usr' - I created that folder. I am sure I am missing something here.

To be clear, I only have this one VM install with sandbox on my laptop. So WinSCP cannot be showing me a wrong folder tree (am using the WinSCP User Interface to copy the jar). Am attaching an image here showing the WinSCP structure.

40440-winscp.png

regards

Vinay

1 REPLY 1
Highlighted

Re: Unable to connect to Sqlserver using Sqoop

Hi @Vinay Sikka,

Here are the steps I use to connect to an instance of SQL Server:

Setup SQL Server (Windows)

1. Create SQL Server account hadoop\hadoop

2. Disable shared memory

Configure Hadoop (Linux)

4. Download sql driver to /usr/local.

  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

5. Copy driver to library

  cp sqljdbc_4.0/enu/sqljdbc4.jar /usr/hdp/current/sqoop-server/lib

6. List databases to check connection

sqoop list-databases --connect jdbc:sqlserver://<IP Address>:<Port Number> --username <Username> --password <Password>

EX: sqoop list-databases --connect jdbc:sqlserver://192.168.56.101:1433 --username hadoop --password hadoop

7. Import schema

sqoop import --connect "jdbc:sqlserver://<IP Address>;database=<Database Name>;username=<Username>;password=<Password>" --table <Table Name> --hive-import -- --schema <Schema Name>

sqoop import --connect EX:"jdbc:sqlserver://192.168.56.101:1433;database=AdventureWorksDW2014;username=hadoop;password=hadoop" --table DimCustomer --hive-import

Don't have an account?
Coming from Hortonworks? Activate your account here