Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

how to load the data from sql server to hdfs using sqoop?

avatar
Rising Star
 
9 REPLIES 9

avatar
Master Guru

I have often used this article:

https://danieladeniji.wordpress.com/2013/05/06/hadoop-sqoop-importing-data-from-microsoft-sql-server...

Syntax:
      sqoop import --connect jdbc:sqlserver://sqlserver-name \
          --username <username> \
          --password <password> \
          --driver   <driver-manager-class> \
          --table    <table-name> \
          --target-dir  <target-folder-name>

Sample: 
      sqoop import --connect "jdbc:sqlserver://labDB;database=demo" \
           --username sqoop  \
           --password simp1e \
           --driver   com.microsoft.sqlserver.jdbc.SQLServerDriver \
           --table    "dbo.customer" \
           --target-dir  "/tmp/dbo-customer"

avatar
Expert Contributor
@omkar pathallapalli

Just add the SQL Server jdbc jar(sqljdbc42.jar) to the sqoop lib folder and try to sqoop using the syntax provided above bu Sunile.

avatar
Contributor

I am getting the error regarding specifying connection manager even though I have added the jar file at /usr/hdp/current/sqoop_client/lib

Can anyone help?

I have used the same command as given above.

Error-

WARN sqoop.ConnFactory: Parameter --driver is set to an explicit driver however appropriate connection manager is not being set (via --connection-manager). Sqoop is going to fall back to org.apache.sqoop.manager.GenericJdbcManager. Please specify explicitly which connection manager should be used next time. 17/06/20 11:28:48 INFO manager.SqlManager: Using default fetchSize of 1000 17/06/20 11:28:48 INFO tool.CodeGenTool: Beginning code generation 17/06/20 11:28:48 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: com.microsoft.jdbc.sqlserver.SQLServerDriver

avatar

Hi @Rajendra Manjunath,

can you help me to identify whats wrong on my sqoop commands?

13682-import-error-2.jpg

avatar
Expert Contributor

@Harold Allen Badilla In your command, you are using mysql database connection string i.e "--connect jdbc:mysql://XXXXX" with "--driver" option with value as SQL Server driver class name. So your connection string should be changed as SQL Server format.

Proper command syntax will look something like this,

sqoop import --connect --connect "jdbc:sqlserver://<Server_Host>:<Server_Port>;databaseName=<DB_Name>" \
--driver com.microsoft.sqlserver.jdbc.SQLServerDriver \
--username XXXXXXXXXXXX --password XXXXXXXXXXX \
--table dbo.knal \
--target-dir /tmp/db0-customer

avatar

Hi @Rajendra Manjunath,

I try to revise my commands , i dont see any error but still no data transferred on the hdfs directory, please the image

13696-import-error-3.jpg

avatar
Expert Contributor

@Harold Allen BadillaDid you add the sqljdbc42.jar to sqoop lib path?, if not please add the jar.

And driver class name as "com.microsoft.sqlserver.jdbc.SQLServerDriver".

avatar
Contributor

Hi @Rajendra Manjunath,

Did it work for you? Please confirm.

Thanks