CODEWITHSUNDEEP
sql-serverjdbcsqoop
Dennis Jaheruddin
Dennis Jaheruddin

Reputation: 21563

Sqoop can list tables but not import data

I am able to succesfully view the list of databases or tables on an SQL server, for instance like this:

sqoop list-tables  --connect 'jdbc:sqlserver://10.11.12.13; database=myDBname; username=jaheruddin; password=myPW'

However, when I try to get even 1 row of data, an error occurs. The sqoop command works fine from another server, so that does not appear to be the problem, but for illustration it looks like this:

sqoop import --m 1  --connect 'jdbc:sqlserver:10.11.12.13; database=myDB; username=jaheruddin; password=myPW' --query "SELECT top 1 *  FROM dbo.myTable WHERE  \$CONDITIONS" --target-dir 'sqoop_test'

The error message I receive is:

Error: java.lang.RuntimeException: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 10.11.12.13, port 1433 has failed. Error: "connect timed out. 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.".

My question is now:

Why do I get a warning about TCP/IP at a port, whilst I am succesfully able to get a list of tables?

Upvotes: 0

Views: 630

Answers (1)

Dennis Jaheruddin
Dennis Jaheruddin

Reputation: 21563

The cause appears to be that the IP address used for getting a list of tables, may not be the same as the IP address used for getting actual data.

In setup where you use Sqoop on a hadoop cluster, and run commands from a master node, commands like list-tables are coming directly from the master. However, regular data pulling jobs are executed on the cluster.

From here the solution is clear:

Make sure all machines in your cluster have access to the sql server

To validate that this is the problem, you can run:

curl 10.1.1.208:1433

On the master you will get something like:

curl: (52) Empty reply from server

Whilst on machines without proper access, you will get a different response.

Upvotes: 0

Related Questions