hsilva hsilva - 23 days ago 6
Java Question

Cannot load sql table to r through SparkR

I'm trying to load an SQL table in R through sparkR. I have the following code:

Sys.setenv(SPARK_HOME = "C:/Users/hms/Desktop/spark-2.0.1-bin-hadoop2.7/spark-2.0.1-bin-hadoop2.7",
HADOOP_HOME = "C:/Users/hms/Desktop/spark-2.0.1-bin-hadoop2.7/spark-2.0.1-bin-hadoop2.7/tmp/hadoop")
.libPaths(c(file.path(Sys.getenv("SPARK_HOME"), "R", "lib"), .libPaths()))

library(SparkR)

sc<-sparkR.session( enableHiveSupport = FALSE, master="local[*]",
sparkHome=Sys.getenv("SPARK_HOME"),
sparkConfig = list(spark.driver.memory="2g",
spark.driver.extraClassPath="C:/Users/hms/Desktop/spark-2.0.1-bin-hadoop2.7/spark-2.0.1-bin-hadoop2.7/jars/sqljdbc4.jar",
spark.executor.extraClassPath="C:/Users/hms/Desktop/spark-2.0.1-bin-hadoop2.7/spark-2.0.1-bin-hadoop2.7/jars/sqljdbc4.jar",
spark.sql.warehouse.dir="C:/Users/hms/Desktop/spark-2.0.1-bin-hadoop2.7/spark-2.0.1-bin-hadoop2.7/tmp/hadoop/bin") )



persons <- read.jdbc("jdbc:sqlserver://xpto//xpto:1433;databaseName=XPTO_XPTO", "table.XPTO", user = "XPTO", password = "XPTO")


When I run this code as it is, I get the following error:

Error in invokeJava(isStatic = FALSE, objId$id, methodName, ...) :
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host srvptsqlsdd02//sddev02_2k12, 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.conn


Can someone give a hint on what can be the problem?

Thank you in advance

Answer

I solved this problem by doing

persons <- read.jdbc("jdbc:sqlserver://xpto\\xpto:1433...
Comments