insert data into Microsoft SQL server using Spark

Question

I am trying to insert data into sql server using spark using the below Jdbc methods.

Option 1:

    prop.put("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
dataf.write.mode(org.apache.spark.sql.SaveMode.Append).jdbc(url,table_name, prop)

Table is already created. Appending new data.Job Error-ed with the below exception

Exception in thread "main"

com.microsoft.sqlserver.jdbc.SQLServerException: CREATE TABLE permission denied in database

Question is : Why create table permission is required for appending the data?

Option2:

    prop.put("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils.saveTable(dataf, url, table_name, prop)

Above command working from spark-shell. when the same is used in scala code and packaged with dependencies giving below exception

Exception in thread "main" java.sql.SQLException: No suitable driver at java.sql.DriverManager.getDriver(DriverManager.java:315)

I tried setting driver class-path and executor class-path and also --jars still no luck. Included sqljdbc4.jar in driver-classpath and --jars. Copied sqljdbc4.jar to all worker nodes as well still no luck.

Any Ideas on this?

If something doesn't work after packaging then build definition and submit command would be useful. — zero323
I am using spark-submit and insertion to hive table is working fine.only problem is with sql server. — yoga

yoga yoga · Accepted Answer · 2016-08-04T18:40:53

After Lot of searching and Testing, I found the answer. It might be useful for someone.

Option 1: This is because of bug in spark 1.5.X. the same was resolved in 1.6.x and later. Because of the bug, It always try to create a new table.

Option2: This causes because , driver name on classpath given priority than properties we are passing as argument. Workaround for this is to create connection and then invoke savetable.

 workaround if you are using spark 1.5.x or lower.
      JdbcUtils.createConnection(url, prop)
       JdbcUtils.saveTable()

insert data into Microsoft SQL server using Spark

1 Answers