Reply
baj
New Contributor
Posts: 6
Registered: ‎06-16-2014

Sqoop import failed, but can connect to list database and table

I'm using CDH5.0.2 and sqoop2 installed. I do NOT have sqoop1 client installed. I basically have 2 host. 1 is running the CDH Manager. I will call it CDH-MANAGER host, and the other is running Pseudo distributed hadoop. I will call thist HADOOP node.  

 

I'm trying to inject data from SQL server to HDFS using Sqoop. 

 

I can issue the following command with no problem from my HADOOP node:

sqoop list-databases --connect jdbc:sqlserver://sqlhost --username test --password ***
sqoop list-tables --connect jdbc:sqlserver://sqlhost --username test --password ***

 


I did get a warning that says:

Warning: /opt/cloudera/parcels/CDH-5.0.2-1.cdh5.0.2.p0.13/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail.

 

but i'm getting the appropriate resut for both command. However, when i try to do an import by issuing:

sqoop import --connect jdbc:sqlserver://sqlhost/purchases --username test1 --password *** --table purchase

 

I still got the same warning, but also the following error. I actually tried using IP. What am i doing wrong??

 

14/06/17 02:46:23 ERROR manager.SqlManager: Error executing statement: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 54.xxx.xxx.xxx/purchases, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 54.xxx.xxx.xxx/purchases, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:826)
at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:685)
at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:708)
at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:243)
at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:226)
at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:347)
at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1298)
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1110)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
at org.apache.sqoop.Sqoop.main(Sqoop.java:240)
14/06/17 02:46:23 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter
at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1116)
at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:96)
at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:396)
at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506)
at org.apache.sqoop.Sqoop.run(Sqoop.java:147)
at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222)
at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231)
at org.apache.sqoop.Sqoop.main(Sqoop.java:240)

 

 

 

Cloudera Employee abe
Cloudera Employee
Posts: 109
Registered: ‎08-08-2013

Re: Sqoop import failed, but can connect to list database and table

Hey there,

 

The commands you are issuing are for Sqoop1.

 

Also, it seems your connection string format is a little off. It should take on the following form: jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]. For example:

jdbc:sqlserver://127.0.0.1:1433;database=test;username=sqoop;password=hadoop.

 

-Abe

baj
New Contributor
Posts: 6
Registered: ‎06-16-2014

Re: Sqoop import failed, but can connect to list database and table

In my test, the user only have access to 1 database. so i use this connection string. providing the database name seems to fail, but this conection string works!

 

 

sqoop import --connect jdbc:sqlserver://54.88.28.90 --username test1 --password test --table purchase

New Contributor
Posts: 1
Registered: ‎04-27-2016

Re: Sqoop import failed, but can connect to list database and table

After god knows how many attempts and following the sqoop documentation, this is the one syntax that is actually working for me. Thanks a lot!
Soz
New Contributor
Posts: 5
Registered: ‎09-17-2017

Re: Sqoop import failed, but can connect to list database and table

I am facing the same sort of issue. Is this anything to do with Network?

New Contributor
Posts: 1
Registered: ‎05-09-2018

Re: Sqoop import failed, but can connect to list database and table

i have tried to execute Import query as below:

sqoop import --connect jdbc:mysql://DNS-Ip address where mysql installed:3306/mydatabasename \
--username root -P \
--table emp --target-dir /hadoop/ -m 1

 

but it got stucked as showing the message as below.

INFO mapreduce.ImportJobBase: Beginning import of table-name

 

can someone please help me on the same...?

Announcements