HANA connectivity with ODBC on Windows using Python
Introduction
As part of the Big Data group, my assignment was to learn and document HANA learnings, and possible extend/find some of the uses that have not quite been documented yet. My first task was to find a way to connect with HANA from a Windows machine using an ODBC/JDBC connection. It was tricky at first to find a way to connect with ODBC, but digging into HANA online resources (corporate and community web portals) helped in the end. I also got to learn a lot about HANA technology and associated tools.
HANA ODBC connection through Windows (step 1)
This part was tricky because you will not find the proper driver using the regular ODBC connection wizard on Control Panel -> Administrative Tools -> Data Sources (ODBC) . The procedure is described here: https://wiki.wdf.sap.corp/wiki/display/HanaCnt/HANA+UI+Clients+Installation#HANAUIClientsInstallation-CreateanODBCDataSource .
Testing the ODBC connection using HANA Command Line Interface (step 2)
It is also easy to open up a HANA CLI once you have the ODBC connection installed and you have a working installation of the HANA Client on your system. All you need to do is execute: C:\Program Files (x86)\SAP\hdbclient\hdbsql.exe from the Windows Command Line;
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\SomeUser>cd C:\Program Files (x86)\SAP\hdbclient
C:\Program Files (x86)\SAP\hdbclient>hdbsql.exe
Welcome to the SAP HANA Database interactive terminal.
Type: \h for help with commands
\q to quit
hdbsql=>
Loading the ODBC Driver using Python (step 3)
You should be able to use any scripting programming language to do this part. Since we were using Python extensively in our project I did this part using Python. I used the pyodbc module which can be found here: http://code.google.com/p/pyodbc/ . So if you do:
import pyodbc
conn = pyodbc.connect(‘DSN=<the_name_of_the_connection_you_created_in_step_1>;UID=<user_id>;PWD=<some_password>’)
cursor = conn.cursor()
this should create an ODBC connection, along with a cursor, the later of which can be used to execute SQL Scripts (HANA queries) and fetch the results, such as:
cursor.execute(“SELECT * FROM <SCHEMA>.<TABLE> ORDER BY <FIELD>”)
print cursor.fetchall()
Endnote
Thanks to a post by Ethan: http://scn.sap.com/community/developer-center/hana/blog/2012/09/14/hana-with-odbc-on-ubuntu-1204
Hi Parvez,
Is there a way to cofigure the connection without goin to DSN..I have installed the HDBODBC32 driver... like the one below for HANA...
DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password
In the above connection striing i tried with our driver name HDBODBC32 and other required information...but it doesnt work...im getting the error "Data source name not found and no default driver specified"
It works fine when i configure DSN...but it would be great if i can set it from python code...
Regards,
Vinoth.
Hi Parvez,
I got the solution, below is it,
pyodbc.connect('DRIVER={%s};ServerNode=%s;DATABASE=%s;UID=%s;PWD=%s'%(driver,server,database,userid,pswd))
Regards,
Vinoth.
Hi what is "database" in this case?
Hi, step 1 points to a death URL. What needs to be done in step 1?
Unfortunately the link points to a SAP internal WIKI.
This is tricky to check from within SAP network as you will always find the WIKI ...
However, all you've to do is to setup a ODBC data source on your system via the ODBC Data Source Administration panel from your Windows control panel (just type odbc into the search bar).
(if your Windows is 64-Bit then you should use the 64-Bit version of R and the 64-Bit version of the HANA ODBC driver. In case you use the 32-Bit version of R the 32-Bit HANA ODBC driver is required. To setup a data source for this one on a 64-Bit Windows you need to start the 32-Bit version of the Data Source Administration via program C:\Windows\SysWOW64\odbcad32.exe )
Once you are there, you simply create a system data source.
The connection test can also be done from this dialogue box - simply click on "Connect" and type in the logon data.
The connection test via HDBSQL however, does not check the ODBC connectivity as HDBSQL doesn't use ODBC at all. The HSQL call above actually does only open the tool and does not connect to anything... 😉
Finally, if you want to avoid creating the DSN beforehand and want to declare the connection parameters dynamically, the only way I found it to be working is by using the SERVERNODE parameter like this:
conn = pyodbc.connect('driver={HDBODBC};SERVERNODE={<hostname>:<port>}', uid='<username>', pwd='<password>')
For example:
conn = pyodbc.connect('driver={HDBODBC};SERVERNODE={vml3012:30015}', uid='LARS', pwd='***')
would connect me to my HANA toy system.
Cheers, Lars
I created the DSN (32-Bit) and was able to test it successfully using the dialogue box. However, when I execute a few lines of Python code similar to what is described in this blog I see no results. What could be going wrong?
This is what I am trying to run:
import pyodbc
cnxn = pyodbc.connect('DSN=HDB;UID=******;PWD=******'')
cursor = cnxn.cursor()
cursor.execute("SELECT COUNT(*) from *****.******")
print cursor.fetchall()