topic Re: ERROR: Writing to Unity Catalog from Remote Spark using JDBC in Data Engineering

ERROR: Writing to Unity Catalog from Remote Spark using JDBC

DumbBeaver — Tue, 30 Jan 2024 13:20:33 GMT

This is my code here.

df = spark.createDataFrame([[1,1,2]], schema=['id','first_name','last_name'])

(df.write.format("jdbc") 

    .option("url",  <jdbc-url>)

    .option("dbtable","hive_metastore.default.test")

    .option("driver", "com.databricks.client.jdbc.Driver")

    .mode("overwrite")

    # .saveAsTable("test")

    .save()

)

The Error

An error occurred while calling o273.save.
: java.sql.SQLException: [Databricks][JDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: 42601, 
Query: CREATE TAB***, Error message from Server: org.apache.hive.service.cli.HiveSQLException: 
Error running query: [PARSE_SYNTAX_ERROR] 
org.apache.spark.sql.catalyst.parser.ParseException: 
[PARSE_SYNTAX_ERROR] Syntax error at or near '\"id\"'.(line 1, pos 19)

== SQL ==
CREATE TABLE test (\"id\" BIGINT , \"first_name\" BIGINT , \"last_name\" BIGINT )

Re: ERROR: Writing to Unity Catalog from Remote Spark using JDBC

feiyun0112 — Tue, 30 Jan 2024 15:11:07 GMT

%scala import org.apache.spark.sql.jdbc.{JdbcDialect, JdbcDialects} JdbcDialects.registerDialect(new JdbcDialect() { override def canHandle(url: String): Boolean = url.toLowerCase.startsWith("jdbc:databricks:") override def quoteIdentifier(column: String): String = column })

you need register a JDBC Spark dialect to remove quote in column name

Re: ERROR: Writing to Unity Catalog from Remote Spark using JDBC

DumbBeaver — Tue, 30 Jan 2024 19:35:39 GMT

Thanks, it worked!