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.

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

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

New Contributor

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

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

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

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

New Contributor

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

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

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!

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

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

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

New Contributor

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