topic Re: Issue with quotes in struct type columns when using ODBC in Data Engineering

Issue with quotes in struct type columns when using ODBC

Anonymous — Mon, 20 Dec 2021 14:57:10 GMT

I'm trying to connect to Databricks using pyodbc and I'm running into an issue with struct columns.

As far as I understand, struct columns and array columns are not supported by pyodbc, but they are converted to JSON. However, when there are nested columns that contain quotes (") , the JSON that is returned is invalid, i.e. the quotes are not escaped.

Dataset I used:

from pyspark.sql import Row
 
data = [
  Row(nested=Row(nested_column="nested with a \" character")),
]
df = spark.sparkContext.parallelize(data).toDF()
 
(
  df.write
  .format("delta")
  .mode("overwrite")
  .option("path", "/delta/quotes_issue/")
  .saveAsTable("default.quotes_issue")
)

Querying the data afterwards through pyodbc (using docs here)

conn = pyodbc.connect("DSN=Databricks_Cluster", autocommit=True)
 
cursor = conn.cursor()
cursor.execute("SELECT * FROM default.quotes_issue")
rows = cursor.fetchall()
for r in rows:
    print(r)

This gives me the following invalid JSON output.

'{"nested_column":"nested with a " character"}'

I've tried with both the Databricks Compute cluster endpoint, as well as the newer SQL Endpoint. Also tried the Databricks SQL Connection for Python, but they all have the same problem.

Is there a way to configure Databricks, or the Simba driver somehow to escape quotes correctly?

Re: Issue with quotes in struct type columns when using ODBC

Anonymous — Wed, 22 Dec 2021 08:39:27 GMT

Hi @Kaniz Fatma ,

Thanks for the reply. I'm still curious to the answer for the question above, but I did manage to find a work around.

Work Around

Using the to_json method, you can let Spark convert all the complex columns into json, instead of relying on the SQL endpoint / Simba Driver to do this for you. If you want to completely automate the use of to_json, the describe table statement can help you find out which datatypes your columns have.

Re: Issue with quotes in struct type columns when using ODBC

Anonymous — Wed, 22 Dec 2021 13:03:29 GMT

Hi @Kaniz Fatma ,

My question wasn't related to connecting Databricks to SQL Server, but on an error I'm getting while connecting to Databricks using pyodbc.

Thanks,

Derk

Re: Issue with quotes in struct type columns when using ODBC

Anonymous — Wed, 22 Dec 2021 14:12:32 GMT

Hi @Kaniz Fatma , thanks for the reply. I think I misunderstood. So the suggestion is to install a number of package and update pyodbc on the Databricks cluster you are using? Will give it a try.

Re: Issue with quotes in struct type columns when using ODBC

Anonymous — Thu, 23 Dec 2021 10:48:26 GMT

Thanks @Kaniz Fatma (Databricks) . Have tried adding above script as a startup script to the cluster configuration I'm using within databricks, but issue remains.

Re: Issue with quotes in struct type columns when using ODBC

BilalAslamDbrx — Tue, 28 Dec 2021 00:21:43 GMT

@Derk Crezee - I learned something today. Apparently ODBC does not convert to JSON. There is no defined spec on how to return complex types, in fact that was added only in SQL 2016. That's exactly what you are running into!

End of history lesson 😄 Here's a fix:

SELECT to_json(nested)
FROM default.quotes_issue

This will return:

{"nested_column":"nested with a \" character"}

which is valid JSON.

Re: Issue with quotes in struct type columns when using ODBC

Anonymous — Mon, 03 Jan 2022 18:46:34 GMT

Hey Bilal,

Thanks for your reply. This indeed works and is what I'm currently using as a work around (see one of the previous posts).

Thanks,

Derk

Re: Issue with quotes in struct type columns when using ODBC

BilalAslamDbrx — Tue, 04 Jan 2022 10:53:48 GMT

@Derk Crezee sorry I missed that you already had a fix for this.