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