Search code examples
google-bigqueryodbcpyodbcunixodbc

Pyodbc with BigQuery

I am trying to make Pyodbc working with Google BigQuery. The ODBC manager I have installed is unixodbc (ubuntu) The configuration should be ok with Simba drivers, since the SQL command works and I am able to perform a query from there.

However when using Pyodbc I got stuck. this is the code:

import pyodbc

dbname = 'testdb'
driver_str = '{Simba ODBC Driver for Google BigQuery 64-bit}'
cnxn = pyodbc.connect(driver=driver_str, database=dbname)

c = conn.cursor()
c.execute('SELECT * FROM tablename')
print(c.fetchone())

It producs the following error:

Traceback (most recent call last):
  File "/home/virus/work/lutech/wind/usecase3/test_odbc.py", line 48, in <module>
    cnxn = pyodbc.connect(driver=driver_str, database=dbname)
pyodbc.OperationalError: ('08001', '[08001] [unixODBC][Simba][DSI] An error occurred while attempting to retrieve the error message for key \'UnableToEstConn\' and component ID 1: Could not open error message files - Check that "/home/virus/work/lutech/wind/simba/googlebigqueryodbc/lib/64/$(INSTALLDIR)/ErrorMessages/en-US/ODBCMessages.xml" or "/home/virus/work/lutech/wind/simba/googlebigqueryodbc/lib/64/$(INSTALLDIR)/ErrorMessages/ODBCMessages_en-US.xml" exists and are accessible. MessageParameters=["{[Catalog] [OAuthMechanism]}"] (-1) (SQLDriverConnect)')

I do not understand what it means, but it is referring to a file that is in the Simba Error folder.

Any help?

Solution

  • I solved, with a very intensive try&error approach. Now it is very clear. I used local user configuration files, to avoid permission issues. Those in /etc/ are empty.

    This is the content of my .odbcinst.ini file:

    $ cat .odbcinst.ini 

[ODBC Drivers]
Simba ODBC Driver for Google BigQuery 64-bit=Installed
[Simba ODBC Driver for Google BigQuery 64-bit]
Description=Simba ODBC Driver for Google BigQuery (64-bit)
Driver=<local user installation path>/simba/googlebigqueryodbc/lib/64/libgooglebigqueryodbc_sb64.so

    and here my .odbc.ini:

    $ cat .odbc.ini 

[bigquery_odbc]
Driver=Simba ODBC Driver for Google BigQuery 64-bit
Catalog=<gcp project id>
OAuthMechanism=0
Email= <email service account>
KeyFilePath=<path to the json file downloaded when creating the service account>

    Here you should be able to successfully execute isql -v bigquery_odbc

    Now if I try to connect using pyodbc, I get the error above. First fix the path of the installation as expressed in the configuration file and also the UTF encoding as specified here

    $ cat <local user installation path>/simba/googlebigqueryodbc/lib/64/simba.googlebigqueryodbc.ini

# To use this INI file, replace $(INSTALLDIR) with the
# directory the tarball was extracted to.

[Driver]
DriverManagerEncoding=UTF-16

ErrorMessagesPath=<local user installation path>simba/googlebigqueryodbc/ErrorMessages
LogLevel=0
LogPath=<log path>
LogFileCount=5
LogFileSize=10

    When calling pyodbc, it worked:

    dataset_name = <bigquery dataset name>
DSN = 'bigquery_odbc'
conn_str = "DSN={}".format(DSN)
cnxn = pyodbc.connect(conn_str, autocommit=True) # DO NOT forget autocommit param
cursor = cnxn.cursor()
cursor.execute('select * from {}.table;'.format(dataset_name))
print(cursor.fetchone())

    I struggled a lot with this configuration. Hope it helps others