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.

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

Highlighted

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

Contributor
 
9 REPLIES 9
Highlighted

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

Super 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"
Highlighted

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

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.

Highlighted

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

Explorer

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

Highlighted

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

Hi @Rajendra Manjunath,

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

13682-import-error-2.jpg

Highlighted

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

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
Highlighted

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

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

Highlighted

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

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

Highlighted

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

Explorer

Hi @Rajendra Manjunath,

Did it work for you? Please confirm.

Thanks

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

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