topic Simba ODBC batch queries in Data Engineering

Simba ODBC batch queries

DBUser2 — Wed, 04 Sep 2024 16:32:45 GMT

I'm using Simba ODBC driver to Connect to databricks. Since this driver doesn't support transactions, I was trying to run a DELETE and then INSERT query from a within a single execute, but I get an error. Is there an alternate way to perform a batch of operations together so that if one fails, others aren't commited.

org.apache.spark.sql.catalyst.parser.ParseException: [PARSE_SYNTAX_ERROR] Syntax error at or near 'DELETE': extra input 'DELETE'. SQLSTATE: 42601 (line 2, pos 1)

The query that I'm trying to run through single command.ExecuteNonQuery() is:

INSERT into test.samples.employee values('5','value5'); DELETE FROM test.samples.employee where empid = 1;

Thanks

Re: Simba ODBC batch queries

PiotrMi — Sat, 07 Dec 2024 11:02:31 GMT

Hey @DBUser2

It looks that delete is not supported at all based on documentation:

Write-back
The Simba Apache Spark ODBC Connector supports translation for the following
syntax when connecting to a Spark Thrift Server instance that is running Spark 1.3 or
later:
l INSERT
l CREATE
l DROP
Spark does not support UPDATE or DELETE syntax.
If the statement contains non-standard SQL-92 syntax, then the connector is unable to
translate the statement to SQL and instead falls back to using HiveQL.

Doc:

https://docs.databricks.com/en/_extras/documents/Simba-Apache-Spark-ODBC-Connector-Install-and-Configuration-Guide.pdf