question Re: how to load the data from sql server to hdfs using sqoop? in Support Questions

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

omkarmotoe — Fri, 16 Sep 2022 11:04:26 GMT

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

sunile_manjee — Mon, 13 Feb 2017 22:52:34 GMT

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"

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

rajsyrus — Mon, 13 Feb 2017 22:52:35 GMT

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

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

haroldallenbadi — Mon, 19 Aug 2019 11:47:25 GMT

Hi @Rajendra Manjunath,

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

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

rajsyrus — Thu, 16 Mar 2017 14:48:42 GMT

@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

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

haroldallenbadi — Mon, 19 Aug 2019 11:47:17 GMT

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

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

vantinttt — Fri, 17 Mar 2017 11:36:06 GMT

Hi @Harold Allen Badilla

You can find in post here: https://community.hortonworks.com/questions/1941/sqoop-connector-for-microsoft-sql-server.html

https://learningintheopen.org/2013/05/06/hadoop-sqoop-importing-data-from-microsoft-sql-server/

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

rajsyrus — Fri, 17 Mar 2017 12:34:34 GMT

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

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

varunjoshi — Tue, 20 Jun 2017 20:05:10 GMT

Hi @Rajendra Manjunath,

Did it work for you? Please confirm.

Thanks

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

varunjoshi — Tue, 20 Jun 2017 23:45:09 GMT

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