ERROR: Writing to Unity Catalog from Remote Spark using JDBC

DumbBeaver
New Contributor II

‎01-30-2024 05:20 AM

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 )
0 Kudos

feiyun0112
Honored Contributor

‎01-30-2024 07:11 AM

 

%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

 

View solution in original post

DumbBeaver
New Contributor II
In response to feiyun0112

‎01-30-2024 11:35 AM - edited ‎01-30-2024 11:35 AM

Thanks, it worked!

0 Kudos