Spark SQL JDBC returning only column names

Question

I'm querying a database table using Spark SQL in Scala 2.11. I've followed the examples given in the Internet.

The db I'm using: mariadb 5.5 on Ubuntu 14.04 The table (SOURCES) I'm querying has the following columns:

`srcname` char(60) NOT NULL,
`endpoint` char(255) NOT NULL,
`pan` char(60) NOT NULL,
`vid` char(10)

Here's my code:

val sparkSession = SparkSession.builder().master("local[*]").appName("somename").getOrCreate()
val df1 = sparkSession.read
.format("jdbc")
.option("driver", "org.mariadb.jdbc.Driver")
.option("url", url)
.option("dbtable", "SOURCES")
.option("user", username)
.option("password", password)
.load()   

df1.show()

This gives me the following output:

+-------+--------+---+---+
|srcname|endpoint|pan|vid|
+-------+--------+---+---+
|srcname|endpoint|pan|vid|
|srcname|endpoint|pan|vid|
|srcname|endpoint|pan|vid|
+-------+--------+---+---+

i.e. repeats the column names for each row without giving the data. My table has 3 rows. I've tried changing the number of rows in db and my output changes accordingly.

I've also tried the other way mentioned in various sites:

val prop = new java.util.Properties
prop.setProperty("user",username)
prop.setProperty("password",password)
prop.setProperty("driver","org.mariadb.jdbc.Driver")

val df2 = sparkSession.read.jdbc(url, "SOURCES", "srcname", 0, 5, 1, prop) 
df2.show()

This also gives the same output.

Here are my spark dependencies:

compile 'org.apache.spark:spark-core_2.11:2.0.0'
compile 'org.apache.spark:spark-sql_2.11:2.0.0'

Any help regarding what is going wrong here?

Answer 1

Figured out the issue. It is the jdbc driver

'org.mariadb.jdbc:'mariadb-java-client:1.5.4' 

that's causing the issue. Used mysql driver i.e.

'mysql: mysql-connector-java:5.1.6' 

and it works like a charm