Re: how to load the data from sql server to hdfs u... - Cloudera Community - 107202

Support Questions

Find answers, ask questions, and share your expertise

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