Are you a Python enthusiast with an SAP HANA Cloud, data lake? Well, look no further because today I am writing about making a direct connection to an SAP HANA Cloud, data lake with the python package pyodbc. Currently, there are two ways to interact with data stored in an SAP HANA Cloud, data lake using Python. One is to use the existing Python hdbcli and establish a connection to an SAP HANA database with a remote connection to an SAP HANA Cloud, data lake. However, this approach requires that the SAP HANA database has virtual tables established with the data of interest. The second way to interact with the data is using pyodbc to make a direct connection. Thankfully, it is relatively simple to do.
Installing the SAP IQ ODBC Driver
The first requirement to make the connection is to have installed the appropriate SAP IQ ODBC driver. This can be found in the SAP Software Center and I would recommend downloading the latest SAP HANA Data Lake Client package. I will be downloading the package for my windows system, but ensure that the package installed is supported the operating system of the platform the python script will be executed on.
Once downloaded, install the SAP HANA Data Lake Client package and verify that the ODBC driver has been recognized by the system. In windows, this can be done in the control panel. Open the control panel and search for “odbc”.
From here open either of the “Set up ODBC data sources (XX-bit)” and look for an entry in the User Data Source that contains the “Sybase IQ” driver. Alternatively, click “Add…” and check that “Sybase IQ” is an option in the driver selection menu.
With the driver installation verified the connection can be made with python.
Making a Connection with Python
Now its time for the fun part, making the connection! First, open a terminal and install the pyodbc package with the following command pip install pyodbc. Then, open a python script or jupyter notebook and paste the code template below which I will fill in after.
import pyodbc
driver = "Sybase IQ"
UID = ""
PWD = ""
host = ""
CONN_STR = f'DRIVER={driver};UID={UID};PWD={PWD};host={host};ENC=TLS(tls_type=rsa;direct=yes)'
cnxn = pyodbc.connect(CONN_STR)
cursor = cnxn.cursor()
for row in cursor.execute('SELECT * FROM DUMMY'):
print(row)
The only thing left to do is paste in the host and credentials for the SAP HANA Cloud, data lake instance. Head over to the SAP BTP cockpit and copy the SQL end point from the Data Lake Instances menu.
Paste the SQL Endpoint into the host variable in the code template and fill out the UID and PW with the user and password you wish to connect with. Once that is done, run the script and voila! The connection should establish and an output of “(0, )” should be printed.
That’s it! Any SQL query that can by typed can be sent to the SAP HANA Cloud, data lake through a direct connection with pyodbc.
If you've found this blog post useful feel free to give it a like or comment! Questions are welcome in the comment section below or on the community questions page. For more information on SAP HANA Cloud, data lake there are detailed step-by-step tutorials here.
- SAP Managed Tags:
