How to use transaction when connecting to Databricks using Simba ODBC driver

DBUser2 — Thu, 29 Aug 2024 16:26:26 GMT

I'm connecting to a databricks instance using Simba ODBC driver(version 2.8.0.1002). And I am able to perform read and write on the delta tables. But if I want to do some INSERT/UPDATE/DELETE operations within a transaction, I get the below error, and I am looking for advice on how to enable the databricks connection to use transactions:

ERROR 41116 Simba::ODBC::Connection::SQLSetConnectAttr: [Simba][ODBC] (11470) Transactions are not supported.

Thanks

DBUser2

Re: How to use transaction when connecting to Databricks using Simba ODBC driver

florence023 — Fri, 30 Aug 2024 09:41:37 GMT

@DBUser2 wrote:
I'm connecting to a databricks instance using Simba ODBC driver(version 2.8.0.1002). And I am able to perform read and write on the delta tables. But if I want to do some INSERT/UPDATE/DELETE operations within a transaction, I get the below error, and I am looking for advice on how to enable the databricks connection to use transactions:

ERROR 41116 Simba::ODBC::Connection::SQLSetConnectAttr: [Simba][ODBC] (11470) Transactions are not supported.

Thanks

DBUser2

Hi DBUser2,

The error you’re encountering, ERROR 41116 Simba::ODBC::Connection::SQLSetConnectAttr: [Simba][ODBC] (11470) Transactions are not supported, indicates that the Simba ODBC driver does not support transactions for Databricks. This is a known limitation of the driver.

Workarounds and Alternatives
Auto-Commit Mode: Since transactions are not supported, you can rely on auto-commit mode for your operations. This means each operation (INSERT, UPDATE, DELETE) will be committed immediately without the need for explicit transaction control.
Batch Processing: If you need to perform multiple operations atomically, consider using batch processing techniques where you group multiple operations into a single batch and execute them together.
Databricks SQL: Use Databricks SQL endpoints for executing SQL queries. Databricks SQL supports ACID transactions on Delta tables, which might be more suitable for your needs.
Example of Auto-Commit Mode
Ensure that your connection string or session settings enable auto-commit mode. Here’s a pseudocode example:

import pyodbc # Connection string with auto-commit enabled conn_str = 'DRIVER={Simba ODBC Driver};Server=your_server;Database=your_database;UID=your_username;PWD=your_password;AutoCommit=True' conn = pyodbc.connect(conn_str) # Perform your operations cursor = conn.cursor() cursor.execute("INSERT INTO your_table (column1, column2) VALUES (value1, value2)") cursor.execute("UPDATE your_table SET column1 = new_value WHERE condition") cursor.execute("DELETE FROM your_table WHERE condition") # Close the connection conn.close()

Hope this will help you.
Best regards,
florence023

Re: How to use transaction when connecting to Databricks using Simba ODBC driver

DBUser2 — Fri, 30 Aug 2024 15:23:46 GMT

Thanks for the information. Would you be able to give an example of batch processing using the ODBC driver? That'll be great.

Thanks

DBUser2

topic Re: How to use transaction when connecting to Databricks using Simba ODBC driver in Data Engineering

How to use transaction when connecting to Databricks using Simba ODBC driver

Re: How to use transaction when connecting to Databricks using Simba ODBC driver

Re: How to use transaction when connecting to Databricks using Simba ODBC driver