Python Connection to Hive

Question

I installed the Hortonworks Hive ODBC driver and created a connection in the Data sources. I tested it and it worked successfully.

I installed PyODBC and wrote the following code

import os, sys, pyodbc;
con = pyodbc.connect("DSN=MyCon")

I got error

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('HYC00', '[HYC00] [Hortonworks][ODBC] (11470) Transactions are not supported. (11470) (SQLSetConnnectAttr(SQL_ATTR_AUTOCOMMIT))')

I also tried

import pyodbc, sys, os
pyodbc.pooling = False
pyodbc.autocommit = False
con = pyodbc.connect("DSN=MyCon")

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('HYC00', '[HYC00] [Hortonworks][ODBC] (11470) Transactions are not supported. (11470) (SQLSetConnnectAttr(SQL_ATTR_AUTOCOMMIT))')

also tried

con = pyodbc.connect("DSN=Tenet", autocommit=False)


Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('HYC00', '[HYC00] [Hortonworks][ODBC] (11470) Transactions are not supported. (11470) (SQLSetConnnectAttr(SQL_ATTR_AUTOCOMMIT))')

Solution

I solved it..... I am not deleting my question and putting the answer here

pyodbc.autocommit = True
con = pyodbc.connect("DSN=MyCon", autocommit=True)

This was done based on advice of this read

https://code.google.com/p/pyodbc/issues/detail?id=162

** thanks to the advice from Kyle Porter below... it totally makes sense now **

Comments

[KylePorter]

Slight clarification, you're aren't actually turning off autocommit, you're specifying to pyodbc to keep autocommit on. ODBC says autocommit should be on by default, Python says it should be off, so pyodbc tries to turn autocommit off as a first step. This fails when drivers/databases don't support transactions (like Hive) which is why you get this error. Telling pyodbc that it should keep autocommit on means it doesn't make the call to turn autocommit off to the driver, and everything then works as it should.

[nsivakr]

Brilliant answer. Was struggling and found this to be useful.