Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Insert records into SQL Server using Spark SQL (version 2) with JDBC

avatar
Contributor

I am trying to load records into MS SQL SERVER through Spark 2 using Spark SQL and JDBC connectivity. The program compiled successfully. Also ran without any errors. But records are not inserted into SQL Server. Below I mentioned the code

Spark Code Block:

LeadsDF.write  
.format("jdbc")  
.option("url", "jdbc:sqlserver://localhost:1433;databaseName=LeadTester")  
.option("dbtable", TestTable)  
.option("user", "XXX")  
.option("password", "XXXXX")  .mode(SaveMode.Append)


SBT Dependencies:

name := "EDWETL"
version := "1.0"
scalaVersion := "2.11.8"
libraryDependencies += "com.microsoft.sqlserver" % "mssql-jdbc" % "6.1.0.jre8" %"provided"
libraryDependencies += "org.apache.spark" % "spark-core_2.11" % "2.0.2"
libraryDependencies += "org.apache.spark" % "spark-sql_2.11" % "2.0.0"
libraryDependencies += "com.thetransactioncompany" % "java-property-utils" % "1.8"
mainClass in (Compile,run) := Some("ssss.HBaseToSQLLoader.Main")

Spark-Submit:

spark-submit \

--class ssss.HBaseToSQLLoader \

--verbose \

--master local \

--driver-class-path mssql-jdbc-6.2.1.jre8.jar \

--num-executors 2 \

--driver-memory 512m \

--executor-memory 512m \

--executor-cores 1 \

/home/maria_dev/Mahe/edwetl_Main.jar

1 REPLY 1

avatar
Contributor

This problem got solved after adding "Auto commit" to true. Below is the updated code.

val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
val connectionProperties = new java.util.Properties()
connectionProperties.setProperty("Driver", driverClass)
connectionProperties.setProperty("AutoCommit", "true")