spark databricks jdbc driver integration return rows having column names as values

[manohar3]

Hi all,

i am using below to code to query table but query returns rows having column names as values

spark.read
   .format("jdbc")
   .option("url", "jdbc:databricks://acme.cloud.databricks.com:443/myschema;transportMode=http;ssl=1;httpPath=<httppath>;AuthMech=3;UID=token;PWD=<token>;")
   .option("dbtable", "mytable")
   .load().show()

+---+---------+
|key|value|
+---+---------+
|key|value|
|key|value|
|key|value|
|key|value|

jdbc driver version is 2.6.36

note that odbc driver works but i dont support struct, map, array as column names .

[manohar3]

any idea what causing this? please note that above query i am running from standalone spark application also when i run above in databricks notebook i see expected result

[Kaniz]

Hi @manohar3, It seems like you’re experiencing an issue where your JDBC query is returning the column names as values. This could be due to a variety of reasons. 

Here are a few things you might want to check:

ResultSet Metadata: In JDBC, you can retrieve column names from the ResultSet metadata. If there’s an issue with how the metadata is being interpreted, it could lead to column names being returned as values. You might want to check how your JDBC driver is handling ResultSet metadata.

Alias Handling: How your JDBC driver handles aliases could also be a factor. If you’re using aliases in your query, try running a query without them to see if you get the same result.

Driver Version: You mentioned that you’re using JDBC driver version 2.6.36. It might be worth checking if there are any known issues with this version of the driver that could be causing your problem.

Null Values: If some of the rows other than UUID have nulls, it could potentially lead to issues. You might want to handle null values in your query or in your data processing after the query.

Data Types: Check if the data types of your columns match what’s expected by the JDBC driver.

[manohar3 · Accepted solution]

This seems to be issue with spark and was able to fix issue by following posts

https://stackoverflow.com/questions/47020379/bigquery-simba-jdbc-error-with-spark

https://stackoverflow.com/questions/68013347/how-to-register-a-jdbc-spark-dialect-in-python/71913245...

fix is to update function in JdbcDialects.scala

def quoteIdentifier(colName: String): String = {
colName
}

https://github.com/apache/spark/blob/ce5ddad990373636e94071e7cef2f31021add07b/sql/core/src/main/scal...

[Kaniz]

That’s a great tip! Thank you for sharing your knowledge with the community. I’m sure many people will find it helpful. Keep up the good work! 😊