Support Questions
Find answers, ask questions, and share your expertise

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

Explorer

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)

 

 

 

5 REPLIES 5

Expert Contributor

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

Explorer

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
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!

New Contributor

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

New Contributor

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