CODEWITHSUNDEEP
google-bigquery
Thirumalai Muthu Palani
Thirumalai Muthu Palani

Reputation: 123

Bigquery Simba ODBC autocommit

We are using Microstrategy to connect to Google Bigquery. The Microstrategy report is issuing Insert statements to bigquery which are failing with the following error message:

Push Auto Commit failed. 
Error type: Odbc error. Odbc operation attempted: SQLSetConnectOption. Option: SQL_ATTR_AUTOCOMMIT. [HYC00:11470: on SQLHANDLE] [MicroStrategy][ODBC] (11470) Transactions are not supported.

Upvotes: 0

Views: 1809

Answers (2)

Roee Anuar
Roee Anuar

Reputation: 3448

First, download and configure the latest ODBC driver from here:

Next - use the connection like this (note the IgnoreTransactions parameter):

import pyodbc
import pandas as pd

conn = pyodbc.connect(r'Driver={Simba ODBC Driver for Google BigQuery};OAuthMechanism=0;Catalog=<projectID>;KeyFilePath=<path to json credentials>;Email=<email of service account>;IgnoreTransactions=1')

qry = 'select * from <path to your table>'
data = pd.read_sql(qry,conn)

Upvotes: 1

Tamir Klein
Tamir Klein

Reputation: 3642

I see two possible options on how to solve this issue:

Option 1

Per Simba ODBC release notes which are found in this link you need to make sure you are using version 2.1.16 and that you have enabled the IgnoreTransactions configuration option

[GAUSS-634] Ignoring Transaction Data

The driver can now be configured to ignore transaction data in query results. To do this, enable the IgnoreTransactions configuration option. For details, see the Installation and Configuration Guide.

Another interesting option in this version

[GAUSS-663] Support for MERGE

The driver now supports MERGE as a DML statement.

Check the Advanced Options in the ODBC SIMBA configuration menu

enter image description here

Option 2

If you are connected to Microstrategy via Linked connection you need to follow the instructions in Simba FAQ page

Insert, Update, and Delete fail with an error saying the Linked Server doesn’t support the required transaction interfaces.
Linked Server does not use ODBC natively, it uses OLE DB and to talk to ODBC it uses the Microsoft OLE DB to ODBC bridge. This is where the problem arises. You can work around this by disabling transactions for DML queries with the provider. Delete the linked server connection to the data source, then open the Linked Server Providers and right click on the MDASQL one. Select the “Non-transacted updates” and then recreate the linked server and try again.

Upvotes: 1

Related Questions