Get On-boarded with HANA XSA and Python Application using hdbcli

In this post, I will be discussing on how to connect Python application to HANA XSA using hdbcli. I will also include what are the prerequisites to get the environment set up and will demo the end to end execution steps using the screen shots.

Some previous knowledge on Python will be great but not required as I will explain everything in detail as we move further.

As for the system setup, I will be using SAP HANA Enterprise edition running on openSUSE12 Linux machine. Connecting using Windows system to access HANA XSA through CLI. All the steps can be done using SAP HANA Express Edition too, download information is provided in this link https://www.sap.com/cmp/td/sap-hana-express-edition.html

For Python I will be using Jupyter notebook, which is available from Ananconda distribuition. All the setup is on Windows, you can of course use Linux system too.

Prerequisite for Connecting Python Application to XSA

Python runtime should be installed on XSA system. Unlike Node.js and Java, XSA only comes with build pack for python. To install the runtime you can check this blog . SAP Web IDE does not provides development environment for Python, therefore you can use any external code editor (like spyder, jupyter).

Once Python runtime is successfully installed on XSA, you can check using xs Command.

xs runtimes

In order to connect to the application, you need to have services created in XSA. These services will bind itself to the application and will help in achieving the requirements. I will make use of a user-provided-service to connect and execute the SQL query on tables through Python script. The execution result will give us the fetched data which will confirm the successful connection.

To create the user provided services, use the xs command in CLI.

xs create-user-provided-service UPS_XSA -p "host, port, dbname, username, password"

Service name can be any custom name and the parameters should be according to the XSA system. For example, host and port, it is the name of the XSA system and the port it uses. Dbname, username and password are the details of the database and user credentials.

After executing the create user provided service command, you can check if service got created.

xs services

Application Directory with Manifest file

At this step you are ready to use the user provided service in manifest.yml file. Through this service Python application can authenticate and access the database. In order to start with the creation, first you need to assign a directory for the application. In the same location create following listed folder and files:

1. Vendor (folder)
2. Requirements.txt (file)
3. Runtime.txt    (file)
4. PythonScript.py  (file)
5. Manifest.yml  (file)

Open Manifest.yml and define the values for different attributes.

>>> manifest.yml

applications:
  - name: PythonScript
    command: python PythonScript.py
    services:
     - UPS_XSA

Similarly the requirements.txt will contain the information of the packages used by the script and runtime.txt will have the python runtime details.

>>> requirements.txt

Flask==1.1.2
hdbcli==2.3.119
cfenv==0.5.3

>>> runtime.txt

python-3.7.4

After defining the python library requirements, you should also include the packages in the vendor folder. The reason is during deployment, in the live environment, system will not be able to make network calls to download Python libraries. So vendor folder should contain the packages as per the requirements.txt file. You can do this by running a pip download command.

pip download -r requirements.txt -d vendor

You need to make sure to get the packages compatible for the platform where XSA system is running. So if it is on Linux, make sure to get the packages for Linux OS only. This can be done by adding another parameter to pip download command.

Vendor folder will contain all the required packages compatible on OS level as well as on architectural level (i686).

Python Script for connecting to XSA using HDBCLI package.

Once you have defined the application architecture with various files and folder. Let’s go on further to create a Python application that leverages hdbcli package and user provided service to connect and execute the SQL queries.

Open the .py file created in the earlier steps. First off. make sure the libraries which are required are present in requirements.txt file along with their version details. You now need to download the packages into vendor folder. During the deployment XSA will try to find the library in vendor directory and will install using the .whl file.

Import the packages in Python file.

>>> PythonSript.py

import os

import sys

from flask import Flask

from flask import Flask, Response

from cfenv import AppEnv

from hdbcli import dbapi

use cfenv to get the environment details of the User Provided Service (UPS). Earlier you made the changes to Manifest.yml file by providing the -services attribute value. In Python we can use it by invoking get_service function.

#create an instance of app environment

env = AppEnv()

ups_var = env.get_service(name='UPS_XSA')

At the time of deployment, as we have already bound the service to the application using manifest.yml, python function will fetch the details of the supplied UPS and create the connection.

You can now define a new function and get the connection details using dbapi from hdbcli library.

@app.route('/hana')

def UPS_Connect():  

    conn = dbapi.connect(address=ups_var.credentials['host'], 

                             port= int(ups_var.credentials['port']),

                             user = ups_var.credentials['user'],

                             password = ups_var.credentials['password'],

                             CURRENTSCHEMA=ups_var.credentials['schema'])

    cursor = conn.cursor()

Once the you create a cursor for the connection, you can execute any SQL query and fetch the data from the XSA containers.

    #execute SQL query

    cursor.execute("SELECT count(*) as COUNT FROM DUMMY_TABLE", {})

After writing the python code, now you need to deploy the whole application to XSA. I will be using CLI to login and push the application.

xs login

Provide the API_URL and credentials for the XSA system. It will display the screen once you successfully log on.

Next step is to deploy the application you have been creating since. Use the xs push command with path parameter and deploy the whole application. If the deployment is successful, it will show the success status with the URL that gets bound to the application during staging.

xs push py -p <application_path>

You can use the URL to view the output of the application in browser.

Great! now you have successfully created a Python application in an external IDE and deployed on SAP HANA XSA using Command Line Interface. However, If you are facing any issue in following the steps or you are getting any related errors please feel free to drop a comment. I would be more than happy to provide the relevant details.